Report Studio Intro 6
Report Studio Intro 6
Report Studio Intro 6
Report Studio
Introduction
Elisabetta Zodeiko
5/11/2010
Princeton University
4. Filters ..................................................................................................................... 72
Concepts .............................................................................................................................................................74
Opening the Filters Dialog Window ...................................................................................................................75
6. Calculations.......................................................................................................... 151
Calculations ......................................................................................................................................................153
Adding a Calculated Column ............................................................................................................................154
Understanding Aggregation .............................................................................................................................158
Adding Summary Totals to a Footer (Totaling a Column) ................................................................................159
Remove the Summary Footer ..........................................................................................................................162
Grouped Summary Footers ..............................................................................................................................163
Summary Calculation Without Detail ...............................................................................................................167
Course Overview
Princeton Information Warehouse
Inside Cognos Connection
Accessing Report Studio
The Report Studio Windows
Insertable Objects Window
Model Tab
Query Items Tab
Toolbox Tab
Properties Window
Report Window
The Report Studio Toolbars
Explorer Bar
In this course, students will use the IBM Cognos Report Studio tool to learn different to report writing
techniques, including creating, sorting, filtering, prompting, calculating, and formatting data. Lessons focus on
linear report creation, independent of any specific data set.
Note: All Report Writers must use IE as their browser. Report Studio and Query Studio require Active X
plug-ins which only IE supports.
Performance goals
Upon successful completion of this course, students will have an understanding of:
Source Data
System Nightly Loads into the IW appears in
Data IW
The Princeton University IW site communicates useful information to the Princeton Community, including:
To log into the Cognos Connection tool, click on “Enter the Information Warehouse” link, found on the IW
homepage. You will need a valid Princeton Username and Password.
• A Yellow Reports Folders contain all reports for a given data set.
• A Blue Package Folder is the Cognos connection to the database for any given data set, and
contains a business view of the data that is used to create reports.
o The term “Package” is associated with a Blue Folder because any Blue Folder is the result
of a published Data Set from the Cognos Developer tool, Framework Manager. For
example, an HR data set is created in the Framework Manager tool by OIT, the data is
published to Cognos Connection and is accessible to Report Writers through the Blue
Folder.
o No reports are ever saved in any Blue Package Folders.
• Public Folders displays all Yellow Reports Folders and Blue Package Folders.
• My Folders is a User’s secured area within Cognos. Only the individual User can place and access
items inside My Folders.
The Inbox folder is the holding area for reports written by report writers, allowing Package Custodians to
test reports before moving them to the Report Viewer area within the Yellow Reports Folder. All report
writers have the ability to save reports here, or “write” abilities to the Inbox.
Editing or modifying reports that have already been created saves time by not having to recreate the same
underlying items over and over in each new report. An efficient time saver is to open a report, which can
serve as a “template”, then modify and save it under a different name, within either the Inbox folder or My
Folders location.
STEPS:
2. Click the Report Studio Icon located to the right-side of the report name.
3. Report Studio will open with the chosen report’s specifications along with the correct underlying data
source.
Report Studio can be opened first, directly from the Launch button on the Public Folders menu, giving you the
opportunity to first choose an underlying data source.
GOAL: To open Report Studio directly from the Public Folders Menu
STEPS:
1. On the Public Folders tab, in the upper-right corner menu-bar, click Launch > Report Studio.
2. In the new window, choose the Cognos 8 Training Blue Package Folder.
5. In the new drop-down menu, select Public Folders, then navigate to the Cognos 8 Training – Reports
Yellow Folder.
Report Studio is comprised of several windows and toolbars that are used to create or modify the report:
The Insertable Objects pane is where the building and report modification takes place. An object is any
item in a report, such as text, a picture, list, table, etc. The Insertable Objects pane consists of three
tabs:
• Model tab –Displays all of the query items that are in the model. The query items can be
added to the report.
• Query Items tab – Once an item is added to the report, it is shown on the query items tab.
The tab displays all of the queries that are currently in the report. Use this tab to quickly find
items in your report instead of navigating through the entire model.
• Toolbox tab – Contains the objects that you can add to your report.
The Model tab displays all of the descriptive query subjects that contain query items that are in the model.
Opening each query subject will display related items that are contained within.
The Query Items tab displays all of the queries that are currently used in the report. Use this tab to quickly
find items used in your report instead of navigating through the entire model.
The Properties window allows the user to change the appearance or behavior of items within the report.
For example, you can manipulate the Text Source, Data, Color and Background, Text, Position, etc. of a
Text Item in the report. (All objects in the report, including the title page and report pages, have
properties that can be changed.)
The Report window contains the area in which the report layout is displayed as it is being created.
Build Prompt Page. Automatically builds a prompt, related filter, and prompt page
for any field in a report.
Drill Through Definition. Set up drill-through access in a source report to link two reports
containing related information.
Help. Access IBM Cognos Report Studio documentation.
Text Formatting Buttons. Use these buttons to format text within a report, including displayed field data.
Table Formatting Buttons. Use these buttons to format tables within a report.
The Report Studio window also contains an Explorer Bar that further allows navigation and manipulation
of reports.
The Explorer Bar allows you to manipulate your report in three different ways:
• Page Explorer – Use this to change between title pages, report pages, and prompt pages, or to add
new pages to the report.
• Query Explorer – Use this to modify the query.
• Condition Explorer – Use this to change the appearance of the report based on conditions within the
data.
GOAL: To create a new report illustrating everyone affiliated with the University.
STEPS:
1. On the Public Folders tab, in the upper-right corner menu-bar, click Launch > Report Studio.
2. In the new window, choose the Cognos 8 Training Blue Package Folder.
6. In the Insertable Objects window, open Cognos 8 Training Data by clicking the plus sign or by double
clicking on the name.
7. Open Personal Data table by clicking the plus sign or by double clicking on the name of the item.
STEPS:
4. Close the Report Viewer window by clicking the Close link in the upper right corner.
STEPS:
3. In the Name field, name the report Personal Data report, followed by your initials.
Removing a column may be necessary if the information is no longer needed for reporting purposes, or if
you want to produce a report but do not want the recipients to see certain details.
STEPS:
1. Click the Primary University Affiliation Status Description column header (the last column).
Tip: Cutting a column instead of deleting a column leaves the data item in the query, but does not
display the column on the report page. Later in this chapter we will learn how to remove a column from
the report while keeping it in the query.
STEPS:
1. In the Insertable Objects window, click on the Query Items tab. Examine the Query. Note that the
Primary University Affiliation Status Description column that we just deleted is not in the query.
GOAL: To sort report data. To add a multiple sort and to remove a sort.
STEPS:
4. The Sort Ascending icon appears as an up-arrow in the first cell of the Last Name column.
6. Notice the sort order for the two rows with the Last Name “Amon.”
More than one column can be sorted within the same report. Additionally, one column can be sorted in
reverse order of the other column(s) if desired. Currently, the primary sort is an ascending sort on Last
Name. We will now change First Name to sort in descending order within Last Name.
3. Click the Run Report icon Notice the change for the two rows with the Last Name “Amon.”
Now we will use Cut from the Edit menu to remove columns from the report. This effectively hides the
columns. Although the data is no longer displayed in the report, using Cut still retains the column in the
query. This allows us to use the item for other purposes, such as sorting on the hidden column.
STEPS:
1. Ctrl-Click on the Last Name and First Name column headings so that both columns are selected.
3. The Last Name and First Name columns are removed from the report.
Note: You may also use the following methods to hide a column and sort on it even though it is not in the
report.
• Query Explorer : Do not add the column to the report. Instead, click the Query Explorer bar and
then click Query 1. From the Insertable Objects window, drag and drop the column into the
Facts window. Highlight the column in the Facts window and change the Sort order in the
Properties window.
• Properties window/ Box Type: Add the column to the report and sort on it. Then highlight
the column heading AND the column itself, and in the Properties window and select Box Type
as None. This will also hide the column so it is not in the report.
• Cutting a column by clicking the Cut icon removes the column from the report, but leaves the
data item in the query.
Use Cut for those times when you need to leave an item in the query, but do not wish to show it
in the report.
After running a report, or just viewing the query, it is easy for the user to change the way the columns
appear in the report.
STEPS:
2. Drag the highlighted ID column heading to the end of the report, after Primary University Affiliation Group
Description. Be sure to wait until there is a vertical, triple-flashing bar.
Increasing the information in a report is easily accomplished by adding new columns to the report. There
are several ways to add columns to a report:
• Double-clicking on the item.
• Right-clicking on the item and choosing Insert.
• Or clicking and dragging the item to the report.
STEPS:
1. Make sure the Country Code column heading in the report is highlighted.
2. In the Insertable Objects window, in the Model tab, under Personal Data, locate Full Name.
4. Full Name has been added as the first column in the report.
Note: The new column will be placed before the highlighted column. If no column is highlighted the
new column will be placed at the end of the report.
The title area of the report is easily altered as the reporting changes are made within the body of the
report. The title shows in the Report Viewer and also when the report is printed.
STEPS:
1. In the Title area, double-click the Double click to edit text link.
3. Click OK.
Grouping a column of data makes it easier to find data, as all like items are placed within the same
grouping. After grouping data, the columns can have a count, total, average or the like applied to them.
STEPS:
3. Move the Primary University Affiliation Description and Primary University Affiliation Group
Description (the last two columns) to the beginning of the report.
7. Click the Group/Ungroup icon Notice the new icon in the Primary University Affiliation
Description List Column Body.
14. Page down through the report or click Bottom to see the various types of data available.
Level Span controls how often the report creator or user chooses to show a particular item within a group.
STEPS:
1. We will create a new report for this exercise. From the File menu, select New. Select List and click
OK.
12. In order for the Country of Citizenship to repeat each time the Citizenship Status Description
changes; the Level Span association must be changed.
16. Click Citizenship Status Description in the drop down list. Note: Only other grouped fields will appear in
the list.
Sections are similar to grouping; however, a section differs in the fact that it shows the query item as the
heading of a section or area within the report.
STEPS:
7. Click Save.
Note: Country no longer repeats for each Citizenship Status Description, as it did for Level Spanning. If
you wish to have a section for each grouping (ie. a section for Country and a “sub-section” for Citizenship
Status Description within country), select both column headings in the report, and select Create Section.
Note: A field must be grouped or sectioned in order for Report Studio to break upon it.
STEPS:
4. Click on Page1.
5. From the toolbox tab , drag over a Page Set and place it above Page1 in the Page
Explorer.
9. Click the drop-down arrow which appears next to Query 2. Select Query 1.
10. Also in the Properties section, click on the Grouping and Sorting property.
Concepts
Opening the Filters Dialog Window
The Filters Dialog Window
Detail Filter Expression
Filtering on a Single Item from the Source (Source tab)
Filtering on a Single Item from the Query(s)
Usage (Required, Optional, Disabled)
Filtering on Multiple Items (Using “in”)
Filtering on a Date Range (Using “between”)
Filtering Text (Using “starts with” )
Filter Text (Using “contains”)
A filter reduces the amount of data in a report by the criteria you choose. You can filter one or more
item(s), by a precise match, or by a partial match.
Expressions:
A filter expression is always comprised of these pieces:
The column name, which can be taken from the report
An operator, such as =
The data we are searching for
Operators:
= Equal to. Must find a precise match.
<> Not equal to. Shows everything except the match.
In Matches a list of items.
Not In Shows everything except the matches.
Starts With Retrieves everything that begins with the characters or phrase.
Contains Retrieves everything that contains the matching characters or phrase.
Is Missing Retrieves blanks.
The operator indicates what kind of match. Below is a table of common operators:
STEPS:
1. Start a New List Report. From the Personal data folder add:
• Last Name
• First Name
• Country Code
• Street Address 1
• City
• State
• Postal Code
• Birthdate
2. Click the Filters icon and the Filters Dialog Window opens.
The Filters window consists of two tabs, Detail Filters, and Summary Filters. Detail Filters apply to rows
in the report. Summary Filters apply to groups in the report. Summary filters are also used to apply to
an item not in the package, such as a calculated item.
Note: Aggregation is discussed in the calculations chapter. It is covered in greater detail in the Report
Studio Level II training manual.
From the Filters window, click the Add icon to open the Detail Filter Expression window.
Source tab
The Source tab allows you to filter on any item in the package. The Data Items tab allows you to filter on
items in the report. The Query Items tab allows you to filter on items from other queries in your report.
The Functions tab allows you to create filter calculations. The Parameters tab allows you to use the
input derived from users when they answer the parameter.
The Data Items tab allows you to filter on items in the report.
The Query Items tab allows you to filter on items from other queries in your report.
When building a filter, you can specify the data type. (This is optional; if you know the data you are
looking for, you can type it directly into the expression, provided you use the correct syntax.)
The Parameters tab allows you to use the input derived from users when they answer the parameter.
Report Studio provides the flexibility to either filter on an item in the model, or on an item in the report.
The Source tab is the location to use to filter on an item in the model.
STEPS:
2. Run the report to see the report before any filters are applied.
4. On the toolbar, click the Filters icon and the Filters window opens.
8. Open the Operators folder and double-click the equal sign “=”.
9. Click the Source tab and make sure Primary University Affiliation Description is highlighted.
13. In the Detail Filter Expression window, click the Validate icon. The Report Studio window will
show the validation process with the server.
21. Run the report. (You will see the applied filter on Employee.)
When creating reports that contain filters, it is common to show the column in the report that
corresponds to the filtered subject. For instance, if you were filtering on Binoculars, generally you would
show the Product type column to re-emphasize that the report is limited to just one product type.
Note: If a data item is deleted from the Query, any filter referencing that data item will no longer work;
the report will not run.
STEPS:
5. Click the Query Items tab to display the data items used in the report query.
7. In the Expression Definition window, click the cursor at the end of the word Country Code and type an
equal sign “=”.
12. Click OK and you will see the newly created filter.
As mentioned in the concepts section at the beginning of the chapter, after a filter is created, there are
three choices available: Required, Optional, and Disabled.
The previous report defaulted to Required. In other words, the filter that was created ([Country
Code]=’CAN’) is required or necessary. However, the filtering option can be changed to Optional, or
Disabled.
Choosing Optional means the filter does not have to be used in order for the report to run. In the case
of a prompt, the report will run even if you do not choose anything from the prompt.
Choosing Disabled allows the user to run the report as if there was no filter. Therefore, debugging or
trouble-shooting is easy and convenient as the filter is temporarily “turned off”.
STEPS:
5. Click OK.
6. Run the report and notice that all countries are showing.
When using the equal sign “=”, the expression can only equal one item. However, if you want to create a
filter that can look for multiple items, it is most effective to use an “in” statement that allows you to filter
on multiple items.
STEPS:
4. From the Detail Filters tab click on the [Country]='Canada' filter. Make sure the Usage is set to
Required.
6. Place your cursor at the end of the current filter statement and delete ='CAN'.
8. On the left, under Personal Data, click Country Code once and then click the Select Multiple Values icon
11. From the Source tab, under Personal Data, click once on the Country Code data field.
13. On the left side, highlight HKG and click the Add icon to add Hong Kong to the right side.
17. Click OK to view the filter in the Filters window, and OK again to return to the Report Page.
EXERCISE
Tip: You can type the values directly into the Expression Definition.
The “between” expression allows the user to look for anything that happened within a range of dates,
such as a beginning and an ending date.
STEPS:
5. Click the cursor at the end of the Expression Definition and type a space.
8. Click OK.
Note: Although the word “between” is used, it actually includes the beginning and end date that is in the
expression. September 16, 1978 and December 31, 1980 are included in the filter.
The “starts with” and “contains” operators are very useful if you are not sure how to spell the entire name
or if you know only a portion of the name.
GOAL: To find all the last names that starts with ‘am’.
STEPS:
1. Open the Pre Filter report and save it as Filter Starts With.
8. Notice under the Information heading it shows how the Starts With expression syntax should be
written: “string1 STARTS WIH string2”
Note: Instead of double-clicking on the operator “Starts With”, it can be typed directly into the
Expression.
11. Double-click string to add a set of single quotes after “starts with”.
Note: Last name is a text field; therefore, you must use the single quotes around the text.
13. Type am. Whatever appears in the string, in this case, am, must exactly match the record stored in
the database table.
15. If there are no errors, click OK to see the newly created filter.
20. Make sure the filter is highlighted and then click Edit.
21. Click the cursor in front of [Report Net Training Data].[Personal Data].[Last Name].
22. Click the Functions tab and open the Common Functions folder.
Note: Spacing in the Expression does not matter. Notice how the function “lower” is on a different line
than the rest of the expression. Again, Spacing in an Expression does not matter.
25. Click the Validate icon and confirm there are no errors.
28. Run the report. The report now shows last names beginning with ‘Am.”
GOAL: To filter the report to find all the last names which contain “am”.
STEPS:
3. In the Filters window, make sure the current filter is highlighted and click the Edit icon.
4. Delete the last part of the current filter, “starts with ‘am’”.
5. From the Functions tab, open the Operators folder, and double-click contains.
7. Click the cursor between the single quote marks and type am.
8. Click the Validate icon and make sure there are no errors.
9. Click OK.
Note: Last names that start with ‘Am’ as well as last names containing ‘am’ are included.
EXERCISE (This exercise is needed for the Prompt section that follows.)
When you create a filter in the filter window, your filter criteria remains static. Prompts allow the user
to change their criteria each time they run the report. The filter dynamically changes when the user
responds to the prompt.
A parameter is a placeholder that requires a value to determine what data on which to report. This
placeholder is a parameterized filter. A prompt asks the user to provide the value for the parameter.
Prompts can be placed on a Prompt Page.
Every prompt will have an associated parameterized filter in the query. Every prompt does not need to
have a prompt page created.
When the user selects items on a report and creates a prompt page, Report Studio will choose an
appropriate prompt type. However, if the user adds a prompt item to a report or prompt page, the user
can choose any type of prompt available in the Insertable Objects pane according to his needs.
Date & Time Retrieves values based on a selected date and time.
Prompt Use this control to filter a date/time or timestamp column. This control is useful for
specifying ranges.
Retrieves data based on values selected from a list. Values are organized
Tree Prompt hierarchically.
This prompt is used with dimensional data, which is not how the Princeton
Information Warehouse is organized. Data in the Princeton Information Warehouse
is relational data.
Report Studio dynamically selects a prompt control based on the data type of the data
Generated item.
Prompt This control acts like a placeholder. When users run the report, the control is replaced
by the appropriate prompt control. For example, if users are prompted for date
values, the control is replaced by a date & time prompt.
STEPS:
4. On the toolbar, click the Filters icon and the Filters window opens.
Note: Placing question marks after the = changes the filter into a parameter filter.
7. Click Validate.
14. In the prompt window, there is a drop-down prompt. Click the drop-down arrow in the prompt
window. All values for Gender Description will display; Male, Female, Unknown.
Note: It does not matter what text is between the question marks in the expression syntax. We can
choose any Gender Description data item in the prompt when the report is run.
GOAL: To add a prompt page to the report, and add prompts on the report page.
STEPS:
1. Use the Pre Filter report. Save the report as Prompt Page.
2. Mouse over the Page Explorer bar. Click on the yellow Prompt Pages icon.
4. From the toolbox tab, drag over a Page into the Prompt Page list.
7. Set the number of columns to one and the number of rows to five.
9. From the toolbox tab, select and drag over a Text Item into the first row of the
table.
10. In the Text Item window, type: Please select an Affiliation Description:
12. From the toolbox tab, select and drag over a Value Prompt into the third row of
the table.
16. Open the Personal Data Query Subject, and click on the Primary University Affiliation Description
data item.
20. Accept the default selections, Notice the Name field displays “Query 2”. Click Finish.
22. Click once in Primary University Affiliation Description prompt box to activate the Properties
window that is located below the Insertable Objects window.
24. Double click on the text box in the Prompt Page Header. A text box will open.
28. The Prompt Page will open. From the drop-down list, select the Miscellaneous Primary University
Affiliation Description.
Note: The orange star indicates that you have to select at least one item. The orange arrow indicates
that nothing is selected yet.
30. The report returns rows which only have Miscellaneous as a Primary University Affiliation Status.
Sometimes there is a need to see the specific prompt value selections on the Report Page, after the report
is run. Using a Text Item, the prompt value can be displayed in the header of a report.
STEPS:
2. Mouse over the Page Explorer bar and click on Page1 under Report Pages.
3. Click once on the Text item in the Header of the Report Page.
4. In the Properties Window, under the Text Source Heading, click once in the Source type field and open the
drop down list.
• Anything inside the single quotes is exact text. The single quote expression is called a “String”
• The plus sign is code to add another item after the String. In this case, we are adding the
Princeton University Affiliation parameter.
9. From the Parameters tab, drag over AffiliationPrompt and place it after the plus sign in the
Expression Definition window.
17. The chosen Prompt Value, Miscellaneous, now displays in the header of the report.
When a Prompt is created on a Prompt Page, an additional Query is created, by default. Also, the
Prompt has a Parameterized Filter automatically created.
Note: Each time you build a prompt page, a filter is created so that the report data is narrowed down
according to the user’s prompt response.
GOAL: Identify the new Query and Parameterized Filter created by a Prompt.
STEPS:
2. Mouse over the Query Explorer bar. Notice there are two queries.
3. Click on Query2. Notice there is one data item there, Primary University Affiliation Description. Query 2
was created for the “Affiliation Prompt” in the Prompt Wizard.
4. Mouse over the Query Explorer bar again. Click on Query1. Query 1 is associated with Report Page 1,
and contains all the data items on Report Page 1.
7. Open the Primary University Affiliation Group Description Parameterized Filter in the Filter window.
STEPS:
2. Return to the prompt page by clicking on the Page Explorer bar. Under Prompt Pages, click on
Prompt Page 1.
5. Mouse over the Page Explorer bar. Under Report Pages click on Page1.
7. Open the Primary University Affiliation Group Description Parameterized Filter in the Filter window.
13. Click Finish. To see Miscellaneous rows, click the Bottom link.
STEPS:
4. Click on Build a Prompt Page button from the toolbar. Report Studio will automatically
build the Prompt Page, the Prompt, and Filter.
Questions:
1. Can you create a filter on something that is not visible in the report? If not, why not? If so, how?
2. If a report has a filter, and you want to run the report and not apply the filter, what do you do? Do
you delete the filter or do you skip the filter? What are the steps to your answer?
3. What is the “operator” that you need to use to filter on more than one item in a column? Is there
more than one operator you can use?
• Create a list report that is sorted by Country Description in ascending order that shows the following
information:
o ID
o Last name
o First name
o Country Description
o Street Address 1
o City
o State
o Postal Code
o Phone Number
• Add a prompt to the report that prompts for more than one Last name.
Answers:
1. Yes. A Filter can be created by using fields from either the Data Items or Source tab. If the field
used in the Filter is from the data items tab, and it also appears on the Report Page, the field should be cut,
not deleted, from the Filter Expression.
2. To run a report and not have the filter applied, you can disable the particular filter in the Filters
Window.
3. The Operator “IN” is needed to filter on more than one item in a column. Example: [Country Code]
in (‘JPN’, ‘HKG’) The Operator “=” will only return one item from a column. Example: [Country Code ]
= ‘AUS’ The Operator “between” will return values between a range, as in a range of birthdates.
Calculations
Adding a Calculated Column
Understanding Aggregation
Adding Summary Totals to a Footer (Totaling a Column)
Remove the Summary Footer
Grouped Summary Footers
Summary Calculation Without Detail
Complex and involved calculations are possible within Report Studio. This chapter will focus on the
easier and more commonly used calculations.
For instance, we can create a calculated column by multiplying one column with another. Grouping a
column allows you to keep all the similar data together, and to add summaries for each group. Some of
the summaries available are counting the number of rows, averaging the numbers in a group and
totaling.
STEPS:
A calculated column is a column created by the user, usually because the column does not exist in the model.
GOAL: To create a new Age calculated column by using the existing Birthdate data item.
STEPS:
2. Click and drag a Query Calculation to the right of the Birthdate column.
4. Click OK.
8. Click the Source tab and double-click Birthdate. Type a ) after Birthdate.
Note: You can also type directly into the Expression Definition area.
What is aggregation?
Aggregation is the summarization of grouped items. By default, Report Studio automatically groups
non-numeric (text or date) data and summarizes numeric data. This means that the numbers you see in
your reports are probably a summarization of the raw data in the database.
The User can turn off the Automatic aggregation. (Mouse over Query Explorer, click on a Query, under
the Properties Section, set Auto Group and Summarize to “No”. A detailed exercise is explained in the
ReportNet Level II course.)
Types of aggregation:
Some of the common types of aggregation are:
The default type of aggregation for each numeric data item is set in the package.
You can also create additional aggregates within the report. You can group a report and add summaries
to the group footers.
An ungrouped report will show counts, totals, etc. for the entire report. However, if the report is
grouped, and then counts, totals, etc. are added, the report will show the summaries for each individual
group and for the report as a whole.
STEPS:
1. Create a new list report with Country Description, Primary University Affiliation Description, and ID
as columns.
6. Click the drop down arrow to the right of the Aggregate icon
10. Click the Bottom link to go to the bottom of the report to view the footer.
1. Click in the Summary footer at the bottom of the report. The footer is selected.
3. The Summary footer and the ID count are deleted from the report.
STEPS:
2. Group the report by Country Description and Primary University Affiliation Description.
8. <ID> in bold is added at the bottom of the column, indicating a summary. Because the report is
grouped by Country Description, and Primary University Affiliation Description, a summary footer is added
for each grouping. The last footer (labeled <Summary> will show a summary for the entire report.
12. Close the Report Viewer and Save the report as Group Count ID.
You may wish to show aggregate data without showing the detail. To do this, we will create a column
that shows only the count of ID’s, without showing the actual ID numbers.
STEPS:
3. Group the Country Description and Primary University Affiliation Description columns.
4. Click on the ID List Body Column. In the Properties window, scroll down to Data Item.
5. Click the Aggregate Function area and then click the down arrow.
6. Choose Count.
8. In the Properties window, locate the Source Type field. Click on Source Type and then click on the
drop-down button.
9. Select Text.
10. Inside the Text window, type the new column name “Count ID”.
11. Again, in the Properties window, locate the Text field. Click on Text and then click on the ellipse.
Building a Report
Data Formats
Text Formats
Adding Headers and Footers
Formatting the Title
Adding a New Page
Understanding Select Ancestor
Running a report in PDF
Adding Graphics
STEPS:
Text and data within the report can be formatted to make the report easier to read, or to change to a
more commonly used format in your organization's reporting structure.
STEPS:
2. Click once in the Birthdate List Column Body (not the column heading).
In addition to the data format, the text format of the columns can be changed to make them stand out or
to make them easier to read.
STEPS:
2. Click once in the Country Description List Column Body (not the column heading).
Report Studio has several options when adding more detail to your report. Page headers and footers
and/or list headers and footers can be added to provide additional information to the users about the
contents of the report.
Page header and footer includes the following properties: background color, background image, border,
box type, conditional style, font, foreground color, horizontal alignment, padding size & overflow, spacing
& breaking, text flow and justification, vertical alignment, and white space.
List header appears at the beginning of a list for each grouped item, and is good for presenting
carry-forward group totals, or group identifiers.
List footer appears at the end of a list for each grouped item, and is good for presenting group totals.
STEPS:
3. Add Primary University Affiliation Description and Primary University Group Affiliation Description
as the first two columns.
7. Click OK.
Once you add a title, you can easily add formatting for emphasis.
STEPS:
2. In the Page Header area, double-click on the Double click to edit text to add a title.
7. Click on the text All University Affiliations so that it is selected. (The Properties window should read
“Text Item” in the title bar.)
8. From the Style Toolbar, click format the font to Arial, the font size to 24pt, and click the Bold icon to set
the text to bold.
Adding a new page enables you as a report user to add the page either as a title page before the body of
the report, or as a secondary page that may serve as an additional reporting page, or as a summary.
STEPS:
2. From the Report Studio Main Menu bar, click View > Report Pages.
3. From the toolbox tab drag over a Page item and place it above Page1 under the Report Pages header.
5. In the Properties Window, under Miscellaneous heading, locate the Name field.
Using the Select Ancestor button allows the user to select a group of related elements in a report, to
change their properties as a whole.
STEPS:
2. Click once in the third row of the table on the Title Page.
3. In the Properties Section, the Select Ancestor is at the Table Cell level.
7. From the Style menu, set the Bold and Center properties for the table.
8. From the Toolbox tab, drag over a Text item and place it in the third row of the table.
You may run your reports in various formats, including PDF, and change your parameters for printing
purposes.
STEPS:
1. Click the arrow next to the Run icon and select Run Report (PDF).
4. From the Report Studio Main Menu Bar, click File > PDF Page Setup.
6. Click OK.
7. Run the report to PDF. Notice, the RUN button is already set to deliver the report in PDF format:
Graphics can be added to a report, however, the graphic image must be saved on the Cognos Server.
Please contact the Data Warehousing and Integration Team to add images to the Cognos Server.
Crosstab Reports
Converting a List Report into a Crosstab
Nested Crosstabs
Crosstabs with Totals
Crosstab reports are useful for comparative analysis because they summarize data and display the results
in a two-dimensional grid.
Similar to list reports, Crosstab reports show data in columns and rows. However, the values at the
intersection of rows and columns show summarized information rather than detailed information.
Crosstabs must include at least three query items: one on rows, one on columns, and one to serve as a
measure or performance indicator defining what the data represents. In a crosstab report, data can be
nested to compare information using more than one query item in a column or in a row.
Note: Converting a list report to a crosstab is accomplished by selecting the column(s) that you want to
be the column(s) in the crosstab.
STEPS:
6. In the Properties Window, under the Data Item heading, set the Aggregate Function to “Count”.
7. In the Properties Window, under the Data Item heading, set the Rollup Aggregate Function to
“Total”. Setting the Rollup Aggregate Function to “Total” on the “ID” field is necessary for the list to
become a crosstab.
8. Click on the and Primary University Affiliation Description List Column Header.
Note: Converting a list report to a crosstab is accomplished by selecting the column(s) that you want to be
10. The report layout has changed from a list report to a crosstab with Primary University Affiliation
Description as the columns, Country Description as the rows, and ID as the measure.
EXERCISE:
GOAL: Nest data in a crosstab report to compare information using more than one query item in a
column or row.
STEPS:
2. Save it as Nested Crosstab. Currently, the Country Description data item represents the rows, and
the Primary University Affiliation Description data item represents the columns.
4. From the Source tab in the Insertable Objects window, click and drag Primary University
Affiliation Group Description to the drop zone after the Primary University Affiliation Description
item and release the mouse click.
6. Run the report. Scroll to the right to see all the countries.
STEPS:
3. Click the down arrow on the Aggregate icon and select Total.
7. Click Save.