IBM Cognos 8 BI Report Studio has a feature that allows us to render data columns depending on certain conditions, for example, depending on user selection, filters or user privileges.
With this tutorial, I will show how to use the data in the report, specifically Data Summaries, as input for this conditional rendering. List and crosstabs reports don't have direct access to the data while rendering, so there are a few tricks that we need to do to make this requirement possible.
In this report I will hide two columns (Expense Group and Expense Category) using a Count total and Sum total.
Report sample output
- Cognos 8 BI Installed and configured
Your Cognos environment should be already installed and configured.
Table of Content:
- Create the structure for using summaries in conditional rendering
- Create the paging specifications
- Add the conditional rendering
Create the structure for using summaries in conditional rendering
Lists and crosstabs are not able to
access the totals when they render the report. For this reason, we need
to use an additional query (identical to the original query) to
calculate the data before the report renders. This way, we can use the
new query data as input for the conditional rendering. In this section,
configure a list report so that this can be achieved.
- Go to the Query explorer and make a copy of the
original query (Query1). We will refer Query1 as the display query. The
new query (Query2) will be referred as the summaries query.
- Open the summaries
query (Query2) from the Query
- Create a Data Input for each summary you wish to
calculate for the conditional rendering. For this exercise, we will
- A count summary - CountExpGroup: count([Expense group] for [Employee key])
- A total summary - TotalExpense: total ([Expense total] for [Employee key])
- Go to the Report page and drop a List object into the report canvas.
- Set the summaries
query (Query2) as the List's Query.
Select the List from the Properties pane and set the query under
- Drag and drop the display query list inside the new
- Go to the summary list Properties/Data/Properties and
select the recently created summary values that we will use for the
conditional rendering. This step is needed to make this values
available for calculations, even though they are not displayed.
- (Optional) If you wish to hide the summary list
borders and header, go to the List's properties/General / Column Titles
and set it to Hide.
Then, select the List Column Body on the properties and under
Box/Padding set every value to 0.
Create the paging specifications
If we follow the steps above, the
way paging works in the report will change. The report's main query
will be the summaries
query and not the display
query. This will make
it impossible for the report to change
pages using values on the display
query. We need to configure
paging with a Master-detail relationship between the two queries.
- Go to Report Pages using the Page explorer.
- Create a new Page set. Set the Query as the summary
query in Properties/Data/Query and add the required
grouping. For this
case we are using the Employee
key as Grouping
- Make sure page properties/Data/Query is set to the
- Create a Master-detail relationship for the display
- The report should be paging properly now, using the
Add the conditional rendering
Now we have the report working
normally and we have the data that we need available to create the
report conditional rendering. We just need to create the formulas
according to our needs. For this case, I will hide a column depending
on the amount of values. This can be used to hide columns that are
empty on some pages, but not on others, as required by our original
- Select, on the display list, the column you wish to render conditionally.
- Make sure you select the List Column. From
the Properties/Conditional/Render Variable, create a new Boolean
- Give a name to the variable and create the formula
for hiding the column. By default, the column will hide if the
condition is true.
For my example, I'll create conditions to hide 2 columns:
- One to hide Expense Group when the column has no values, using the summaries from the summary query: [Query2].[CountExpGroup]>0
- One to hide Expense Category when the total by group of Expense Total is greater than 40000: [Query2].[TotalExpense]>40000
- The report is ready. The results would be as following
In this tutorial we have learnt how
to create conditional rendering using totals. This could be used for
hiding columns when there is no data in the group, when we want to show
only if some totals are above a limit or in any other case
that requires a Summary value on it's condition.
This method will remove the lack of visibility of the Conditional Rendering over the Summary values.
- How do you hide an empty List or Crosstab on a report?: https://www-304.ibm.com/support/docview.wss?uid=swg21344613
- How do you create a Master Detail Relationship in Cognos 8?: https://www-01.ibm.com/support/docview.wss?uid=swg21339010
- Cognos 8 Information Center