Desktop User Guides > 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.
The following examples demonstrate building and customizing simple reports that a based on the UNICOM Intelligence Museum sample.
Creating a 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 then drag biology into the Detail Filters section.
The Detail Filter Expression dialog opens
2 Enter [biology] is not null in the Expression Definition field, and then click the validate button.
3 Click OK. 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, and then drag the Text Item object to just above the <#gender#> column in the Page Explorer section.
The Text dialog opens.
2 Type Gender, and then click OK. The Gender label displays above the <#gender#> column.
3 Select the Tool box tab in the Insertable Objects pane, and then drag the Text Item object to just above the <#age#> column in the Page Explorer section.
The Text dialog opens.
4 Type Age in the Text dialog, and then click OK.
The Age label 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 then 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: click Data > 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 be changed to more suitable text, for example, Total. To do this, select the Summary column or row, and then change the Label value in the Properties pane.
Updating measures as count percentages
In Page Explorer, select Serial from the table, and then update the following properties:
Expression
Click the ellipsis button, and then enter the following expression:
percentage(count(distinct([PresentationView].[Dimensions].[Serial])))
Validate the expression, and then click OK to save it.
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: click Data > 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 then enter the following expression:
Count(distinct([PresentationView].[Dimensions].[Serial])) / Total((count(distinct
([PresentationView].[Dimensions].[Serial])) ) For [PresentationView].[Dimensions].[age])
Validate the expression.
Aggregate Function
Change the value to Calculated.
5 Update the Measure display format: click the data column, select Data Format in the Properties panel, and then click the ellipsis button.
The Data Format dialog opens.
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, and then drag the Query Calculation object into the Page Explorer pane (put the object in the crosstab next to <#age#>).
The Create Calculation dialog opens.
9 Enter 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, save the report specification: click File > Save
Creating multiple response reports
1 Create a new report using a crosstab, by selecting Crosstab either 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 then click the sort button on the button toolbar.
2 Select the Ascending option. The report specification displaying a sort icon next to <#museums#>.
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 then drag museums.Index into the Query1 item in the Query Explorer pane.
3 Switch to Page Explorer, and then select Page1.
4 Select <#museums#> from the table, and then click the sort toolbar button.
5 Select Edit Layout Sorting.
The Sorting dialog opens.
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
1 In Query Explorer, select Query1.
2 Create a filter with the expression:
[gender] <> 'Female' AND [museums] is not null
3 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
Comparing UNICOM Intelligence Reporter reports to Cognos Report Studio reports
Cognos Framework Manager models