Cummulative Calculation with prompt at report level

[adoremars]

Hi All,

Below is the example on my requirement. Based on the month and balance i need to calculate the trial balance which is cummulative.

Month    Balance   Trial Balance
1   234   234
2   543   777
3   215   992
4   533   1525
5   344   1869

The req is i have month prompt and when i select value 4 the output list should have one row for month 4 with cummulative trial balance as below. What calculation can we do at the report level to get this?

Month    Balance   Trial Balance

4   533   1525

Thanks

[colt]

If it's an relational report, you can do the following:

item:           Def
[Month]:     ?SelMonth?
[Balance]:   TOTAL(CASE WHEN [Yourtable].[Month]=[Month]
                                        THEN [Yourtable].[Balance]
                                        ELSE 0
                               END)
[TrialBalance]:  TOTAL(CASE WHEN [Yourtable].[Month] <= [Month]
                                             THEN [Yourtable].[Balance]
                                             ELSE 0
                                    END)

You can set a detailfilter to [Yourtable].[Month]<= ?SelMonth?  where ?SelMonth? is your prompt value.
The point is you need all lines below the selected month to do the cumulation on Balance, but you have to aggregate them to only get 1 line result.

[kalyan_y]

Use the cognos function 'running_total' to get the 'Trial Balance' as the cumulative total.

Define a data item, name = [Trail Balance] and expression = running-total([Balance])
This makes, for each month, u can see the balance and the cumulative value under Trial Balance.

Have a filter on the month as [Month] = ?p_Month?

This should do