This time I'm posting a shorter
note, but it is a tip that I think it might be useful.
This is a requirement raised while working with a Business Partner. They needed to create 3 related list reports in Cognos 10.1.1 with different types of grouping.
The example is described below:
The lowest level would show the full detail. The example used has 2 dimensions: Meter (Feeder > Meter) and Time (Year > Month > Day); and one measure: Value
The second level would show the Maximum Value for a particular month for each Meter
The third level would have to group and sum those Maximum values by Feeder and month.
- The first query (Query1) is just standard drag and
drop list. Add the required columns (Feeder, Meter, Month and value) to
- Set the Aggregation Function to Max in the properties
pane for VALUE.
Refer to Report List 2 for the desired result.
- Create a second query (Query3). Add three Data Items
to the query and set the following expressions:
- Data Item 1 -> [Query1].[FEEDER]
- Data Item 2 -> [Query1].[MONTHKEY]
- Data Item 3 -> Total([Query1].[VALUE])
- Add a new List to the report. On the List properties, change the
Query property to the new query (Query3).
- Add the columns from Query3 to the new List and Test.
The final result is the expected
Summary of the Maximum values:
Remember to use the "Total" function. If you don't use it in the Data Item 3, you will only get the first value of the previous report.