Troubleshooting
Problem
How to add a 'count' for the number of rows in a list report in the report header.
Resolving The Problem
Add a Data Item in Query Explorer and a Layout Calculation or Singleton in the report Header.
Steps:
- Open new list report in Report Studio (this example uses the GO Sales (query) model).
- Drag Retailer (from Sales (query) > Retailer) and Quantity (from Sales (query) > Sales) to the work area.
- Select the query in the Query Explorer, and from the toolbox, add a data item
- From Query Items, drag and drop Quantity, and edit the expression so that it looks like this:
count([Quantity] for report) - Click OK.
- Rename the data item to quantity_count.
- This data item can now be used in two ways:
- As a singleton:
- Go back to the page design. From the toolbox add a Singleton to the header
- Change the query of the singleton to be the same query as the data item from above
- Add the quantity_count data item to the singleton container
- As a layout calculation:
- In the Page Explorer, on Page1, from the toolbox, add a calculation (layout) to the header.
- In the Expression Definition, drag quantity_count into the expression and then click OK.
- If desired, add a label for the count by adding a Text Item from the toolbox to the right of the quantity_count data item. Enter text: 'Row Count: '
- Select the Page object, and set the Query property to the query that contains the dataitem
- Set the Properties property to expose the Quanity_count data item (by checking the box beside it).
- Run the report.
Note: the result will be returned based on the most granular level, so might return a too high value. If the requirement is the number of rows of the resultset, add an additional aggregation to the calculation, ie. count(total([Quantity]) for report)
[{"Product":{"code":"SSEP7J","label":"Cognos Business Intelligence"},"Business Unit":{"code":"BU059","label":"IBM Software w\/o TPS"},"Component":"Report Studio","Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"10.2.2;10.2.1;10.2","Edition":"","Line of Business":{"code":"LOB10","label":"Data and AI"}}]
Was this topic helpful?
Document Information
Modified date:
15 June 2018
UID
swg21339064