IBM Support

How to format a crosstab measure in Report Studio based on another crosstab measure?



This is one of the new features in IBM Cognos 10.x Report Studio. Previously, formatting one crosstab measure based on another crosstab mesure was not possible.


Trying to format one crosstab measure based on another crosstab mesure.

Resolving The Problem

This Technote explores a newly introduced feature in IBM Cognos 10.x. The following steps can be carried out on a IBM Cognos 10.x installation with the sample packages installed and configured.

1. Open Report Studio with the Sales and Marketing (conformed) package.
2. Create a new crosstab report
3. In Crosstab rows insert Mountaineering Equipment from Product line and all the members under Mountaineering Equipment (Rope, Safety, Climbing Accessories, Tools) in the adjacent Crosstab row.
4. In Crosstab columns insert Fax from Order Method and the measures Quantity, Revenue and Gross Profit in the next column. The Crosstab Report should look like following:

5. Select all the cells for the column Quantity, right click and select Style, Conditional Styles, or in the Properties pane double-click the Conditional Styles property. Also, after selecting the cells the Conditional Styles button in the toolbar can be clicked for the same purpose.
6. Click the Add button and New Conditional Style
7. Select Quantity from the list of data items. Depending on the data type of the data item Report Studio will select the type of Conditional Style that you can use. For Quantity the Condition Style will be Numeric Range. The other conditional styles can be String, date/time, date, time, interval etc.
8. Define four different thresholds of values 20000. 40000. 60000 and 80000 and specify the titles of intervals as:
a. 0-20000 : Poor
b. 20001-40000: Below Average
c. 40001-60000: Average
d. 60001-80000: Very Good
e. 80001 and above: Excellent

The following is the screenshot of the threshold definitions:

9. Save this condition style as Conditional Style 1.
10. Now execute the report, the report will show only the conditional styles for the measure Quantity as defined:

11. Now, select the cells of the other two measure columns Revenue and Gross Profit, right click and select Style, Conditional Styles.
12. Click the Add button and Use Existing Conditional Style.
13. Select Conditional Style 1 and click OK.
14. Execute the report again. This time you will find that the same conditional style for Quantity has been applied for Revenue and Gross Profit measures too:

[{"Product":{"code":"SSEP7J","label":"Cognos Business Intelligence"},"Business Unit":{"code":"BU059","label":"IBM Software w\/o TPS"},"Component":"Report Studio","Platform":[{"code":"PF002","label":"AIX"},{"code":"PF010","label":"HP-UX"},{"code":"PF016","label":"Linux"},{"code":"PF027","label":"Solaris"},{"code":"PF033","label":"Windows"}],"Version":"10.2;10.2.1;;10.2.2","Edition":"","Line of Business":{"code":"LOB10","label":"Data and AI"}},{"Product":{"code":"SSEP7J","label":"Cognos Business Intelligence"},"Business Unit":{"code":"BU059","label":"IBM Software w\/o TPS"},"Component":"Report Studio","Platform":[{"code":"PF002","label":"AIX"},{"code":"PF010","label":"HP-UX"},{"code":"","label":"HP Itanium"},{"code":"PF016","label":"Linux"},{"code":"PF027","label":"Solaris"},{"code":"PF033","label":"Windows"}],"Version":"8.4","Edition":"","Line of Business":{"code":"LOB10","label":"Data and AI"}}]

Document Information

Modified date:
15 June 2018