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
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.
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