Professional > Data management scripting > Publishing data > Cognos Framework Manager models > Building simple reports in Cognos Report Studio
 
Building simple reports in Cognos Report Studio
This section discusses various methods for creating simple reports, that are based on data that has been published via UNICOM Intelligence, and covers many of the standard crosstabs that are commonly used in Market Research (which typically differ than crosstabs that are used in other business intelligence reporting). This section assumes basic Cognos Report Studio knowledge.
Creating and customizing simple reports
The following examples demonstrate building and customizing simple reports that a based on the UNICOM Intelligence Museum sample.
Creating a simple report
1 In Cognos Report Studio, select the published UNICOM Intelligence Museum package.
2 Create a new report using a crosstab, by either selecting Crosstab on the first screen or from the toolbox.
3 In Page Explorer:
▪Add Gender as a top dimension.
▪Add Age as a top dimension.
▪Add Biology as a side dimension.
▪Select Serial as the measure.
4 The report specification is:
Serial
<#gender#>
<#age#>
<#biology#>
<#1234#>
<#1234#>
<#biology#>
<#1234#>
<#1234#>
5 The report is rendered as:
Serial
 
Female
Male
 
11-16 years
17-20 years
21-24 years
25-34 years
35-44 years
45-54 years
55-64 years
65+ years
 
1
 
 
1
 
 
 
 
 
 
 
 
No
 
175
258
 
32
43
61
133
72
51
27
14
Yes
 
88
81
 
6
39
34
59
19
4
6
2
Filtering records
In the previous report, the numbers in the empty columns are the result questions that contain null response values. To filter out the null responses:
1 In Query Explorer, open Query1 and drag biology into the Detail Filters section. This opens the Detail Filter Expression dialog.
2 Enter [biology] is not null in the Expression Definition field.
3 Click the validate button.
4 Click OK.
5 Switch to the Page Explorer view, and then run the report. The empty columns are no longer present.
Adding column labels
1 Select the Tool box tab in the Insertable Objects pane, click and drag the Text Item object, and place it just above the <#gender#> column in the Page Explorer section. This displays the Text dialog.
2 Input Gender in the Text dialog and click OK. The Gender label now displays above the <#gender#> column.
3 Select the Tool box tab in the Insertable Objects pane, click and drag the Text Item object, and place it just above the <#age#> column in the Page Explorer section. This displays the Text dialog.
4 Input Age in the Text dialog and click OK. The Age label now displays above the <#age#> column.
5 Run the report. The report now includes the column labels.
Adding summary rows and columns
1 In Page Explorer select <#biology#> from the table and add a summary row via the Data menu: Data Summarize Automatic Summary.
2 In Page Explorer select <#age#> from the table and add a summary column via the Data menu: Data Summarize Automatic Summary. The report specification displays as:
Serial
<#gender#>
<#age#>
Summary
<#biology#>
<#1234#>
<#1234#>
<#1234#>
Summary
<#1234#>
<#1234#>
<#1234#>
The report is rendered as:
Serial
Female
Male
11-16 years
17-20 years
21-24 years
25-34 years
35-44 years
45-54 years
55-64 years
65+ years
Summary
No
175
258
32
43
61
133
72
51
27
14
433
Yes
88
81
6
39
34
59
19
4
6
2
169
Summary
263
339
38
82
95
192
91
55
33
16
602
The summary column and row can be dragged and moved to represent the first column/row in the table.
The Summary heading text can also be changed to more suitable text (for example, Total). This is accomplished be selecting the appropriate Summary column or row and changing the Label value in the Properties pane.
Updating measures as count percentages
In Page Explorer select Serial from the table and update the following properties:
Expression
Click the ellipsis button and input the following expression:
percentage(count(distinct([PresentationView].[Dimensions].[Serial])))
Validate the expression before clicking OK and saving the expression.
Aggregate Function
Change the value to Calculated.
The report is rendered as:
Serial
Female
Male
11-16 years
17-20 years
21-24 years
25-34 years
35-44 years
45-54 years
55-64 years
65+ years
Summary
No
29%
43%
5%
7%
10%
22%
12%
8%
4%
2%
72%
Yes
15%
13%
1%
6%
6%
10%
3%
1%
1%
0%
28%
Summary
44%
56%
6%
14%
16%
32%
15%
9%
5%
3%
100%
Generating percentages from the column totals
1 In Page Explorer select the following:
▪Add Age as a top dimension.
▪Add Biology as a side dimension.
▪Select Serial as the measure.
2 Add an automatic summary via the Data menu: Data Summarize Automatic Summary.
3 Add the following filter:
[biology] is not null;
4 Update the following Measure properties in order to generate percentages that are based on the column values.
Expression
Click the ellipsis button and input the following expression:
Count(distinct([PresentationView].[Dimensions].[Serial])) / Total((count(distinct
([PresentationView].[Dimensions].[Serial])) ) For [PresentationView].[Dimensions].[age])
Validate the expression before clicking OK and saving the expression.
Aggregate Function
Change the value to Calculated.
5 Update the Measure display format by left-clicking the data column, selecting Data Format in the Properties panel, and clicking the ellipsis button. This displays the Data Format dialog.
6 Change the Format type to Percent and then change the Percentage Symbol value to %.
7 Use the previous steps to similarly update the Summary (biology) display format.
8 Select the Tool box tab in the Insertable Objects pane, than click and drag the Query Calculation object into the Page Explorer pane (the object should be placed in the crosstab next to <#age#>). This displays the Create Calculation dialog.
9 Input the following expression in the Expression Definition field:
Percentage(Total(count(distinct([PresentationView].
   [Dimensions].[Serial]))))
10 Validate the expression by clicking the validate button.
11 Click OK to apply the changes. The report specification displays as:
Serial
<#age#>
<Summary2>
<#biology#>
<#1234#>
<#1234#>
Summary
<#1234#>
<#1234#>
The report is rendered as:
Serial
11-16 years
17-20 years
21-24 years
25-34 years
35-44 years
45-54 years
55-64 years
65+ years
Summary2
No
84%
52%
64%
69%
79%
93%
82%
88%
72%
Yes
16%
48%
36%
31%
21%
7%
18%
12%
28%
Summary
100%
100%
100%
100%
100%
100%
100%
100%
100%
Saving the report specification
After applying the appropriate report settings, the report specification can be saved via the File menu: File Save
Creating multiple response reports
1 Create a new report using a crosstab, by either selecting Crosstab on the first screen or from the toolbox.
2 In Page Explorer:
▪Add Gender as a top dimension.
▪Add Age as a top dimension.
▪Add Museums as a side dimension.
▪Select Serial as the measure.
3 In Page Explorer select <#museums#> from the table and add a summary row via the Data menu: Data Summarize Automatic Summary. Repeat this process for <#age#>.
4 Create a filter with the expression: [museums] is not null.
The report specification displays as:
Serial
<#gender#>
<#age#>
Summary
<#museum#>
<#1234#>
<#1234#>
<#1234#>
Summary
<#1234#>
<#1234#>
<#1234#>
The report is rendered as:
Serial
Female
Male
11-16 years
17-20 years
21-24 years
25-34 years
35-44 years
45-54 years
55-64 years
65+ years
Summary
Museum of Design
43
49
6
14
14
21
15
12
8
2
92
National Museum of Science
124
209
27
47
50
107
51
30
16
5
333
Northern Gallery
10
11
2
2
7
3
2
3
2
 
21
Archeological Museum
8
18
3
5
4
7
4
2
1
 
26
Institute of Textiles and Fashion
16
31
4
7
10
12
5
3
5
1
47
National Art Gallery
12
7
1
5
5
4
2
2
 
 
19
Other
21
16
1
9
8
9
6
1
2
1
37
Summary
171
255
32
60
72
134
59
38
23
8
426
Sorting the category order
Categories are commonly displayed in a random order. To sort the category order:
1 In Page Explorer select <#museums#> from the table and click the sort button on the button toolbar. You are provided with various sort options.
2 Select the Ascending option. The report specification updates by displaying a sort icon next to <#museums#>. This indicates that sorting has been specified for the column.
Running the report displays information that is sorted on <#museums#>:
Serial
Female
Male
11-16 years
17-20 years
21-24 years
25-34 years
35-44 years
45-54 years
55-64 years
65+ years
Summary
Archeological Museum
8
18
3
5
4
7
4
2
1
 
26
Institute of Textiles and Fashion
16
31
4
7
10
12
5
3
5
1
47
Museum of Design
43
49
6
14
14
21
15
12
8
2
92
National Art Gallery
12
7
1
5
5
4
2
2
 
 
19
National Museum of Science
124
209
27
47
50
107
51
30
16
5
333
Northern Gallery
10
11
2
2
7
3
2
3
2
 
21
Other
21
16
1
9
8
9
6
1
2
1
37
Summary
171
255
32
60
72
134
59
38
23
8
426
Presenting categories in the same order that is defined in the MDD file
The following code sample provides the museums question definitions:
museums "Museums and galleries visited or plans to visit"
categorical [0..8]
{
  National_Museum_of_Science "National Museum of Science",
  Museum_of_Design "Museum of Design",
  Institute_of_Textiles_and_Fashion "Institute of Textiles and
      Fashion",
  Archeological_Museum "Archeological Museum",
  National_Art_Gallery "National Art Gallery",
  Northern_Gallery "Northern Gallery",
  Other "Other",
  Not_answered "Not answered" NA canfilter nofix
} defaultanswer({Not_answered});
In the previous Cognos reports, the categories do not display in the same order as that defined in the MDD file. To make the reports follow the MDD category order:
1 In Query Explorer, select Query1.
2 Expand the Codes folder in the Insertable Objects pane and drag museums.Index into the Query1 item in the Query Explorer pane.
3 Switch to Page Explorer and select Page1.
4 Select <#museums#> from the table and click the sort button on the button toolbar. You are provided with various sort options.
5 Select the Edit Layout Sorting... option. This displays the Sorting dialog.
6 Remove museums from the Sort list, add museums.Index to the Sort list, click the sort button, and then click OK.
Running the report displays information that is sorted identically to how categories are defined in the MDD file:
Serial
Female
Male
11-16 years
17-20 years
21-24 years
25-34 years
35-44 years
45-54 years
55-64 years
65+ years
Summary
National Museum of Science
124
209
27
47
50
107
51
30
16
5
333
Museum of Design
43
49
6
14
14
21
15
12
8
2
92
Institute of Textiles and Fashion
16
31
4
7
10
12
5
3
5
1
47
Archeological Museum
8
18
3
5
4
7
4
2
1
 
26
National Art Gallery
12
7
1
5
5
4
2
2
 
 
19
Northern Gallery
10
11
2
2
7
3
2
3
2
 
21
Other
21
16
1
9
8
9
6
1
2
1
37
Summary
171
255
32
60
72
134
59
38
23
8
426
Filtering records to exclude females
7 In Query Explorer, select Query1.
8 Create a filter with the expression:
[gender] <> 'Female' AND [museums] is not null
9 Switch to Page Explorer, select Page1, and run the report.
Serial
Male
11-16 years
17-20 years
21-24 years
25-34 years
35-44 years
45-54 years
55-64 years
65+ years
Summary
National Museum of Science
209
17
35
31
67
27
20
9
3
209
Museum of Design
49
1
10
6
11
8
8
4
1
49
Institute of Textiles and Fashion
31
4
6
4
8
4
2
2
1
31
Archeological Museum
18
3
5
3
2
2
2
1
 
18
National Art Gallery
7
1
2
2
1
1
 
 
 
7
Northern Gallery
11
1
1
6
1
1
 
1
 
11
Other
16
1
4
4
3
4
 
 
 
16
Summary
255
21
43
40
79
33
24
11
4
255
See also
Comparing UNICOM Intelligence Reporter reports to Cognos Report Studio reports
Applying percentages on column respondents instead of responses
Creating reports for grid questions
To create a grid question report
Presenting measure as a percentage based on the total row values
Adding a Mean column
Sorting on the Mean column
Creating reports for loop questions
Creating a report that is based on the Household level
Creating a report that is based on the Person level
Creating a report that is based on the TVDays level
Cognos Framework Manager models