Report - Running Total with Filter Applied

[KBee]

Hi all,

I am trying to create a simple list report in Cognos, which would show cumulative open tickets. For reference, these are tickets requesting a service from the end user. I want the report to run daily and to show data only for the prior two days (e.g. yesterday, and the day before yesterday). I believe that I should be using the running total but as soon as I filter my report to show dates in question the running total runs only on the visible cells.

Example prior:

Calendar Date     Open Tickets (running total)
10/04/2024        437
11/04/2024        438
12/04/2024        441
13/04/2024        496
14/04/2024        594

Lets say we are running the report on 15th April, so I want to show data for the 13th and 14th. When I apply this filter I would get the following:

Calendar Date     Open Tickets (running total)
13/04/2024        55
14/04/2024        153

I can't get my head around how to work around this and would appreciate feedback.

Many thanks!

[MFGF]

Hi all,

I am trying to create a simple list report in Cognos, which would show cumulative open tickets. For reference, these are tickets requesting a service from the end user. I want the report to run daily and to show data only for the prior two days (e.g. yesterday, and the day before yesterday). I believe that I should be using the running total but as soon as I filter my report to show dates in question the running total runs only on the visible cells.

Example prior:

Calendar Date     Open Tickets (running total)
10/04/2024        437
11/04/2024        438
12/04/2024        441
13/04/2024        496
14/04/2024        594

Lets say we are running the report on 15th April, so I want to show data for the 13th and 14th. When I apply this filter I would get the following:

Calendar Date     Open Tickets (running total)
13/04/2024        55
14/04/2024        153

I can't get my head around how to work around this and would appreciate feedback.

Many thanks!

Hi,

The issue here is that your filter is excluding the prior rows from the query - which then means they are not available for use in the running total. There are two approaches I can think of that would work here:

1. Instead of filtering, conditionally hide the rows you don't want to see.
2. Use a separate, unfiltered query to bring in the dates and calculate the running totals, then feed this into the query your list uses, and then you should be able to filter your list query with no issues (the running totals are calculated in the first query and simply passed as static values to the list query).

Hope this points you in the right direction. If you need more details, let us know.

Cheers!

MF.

[KBee]

Thank you so much for this. I am new to Cognos so I did not know I was able to hide rows using Conditional Formatting but used your pointer to check out some videos and it worked perfectly! Much appreciated.