If you are unable to create a new account, please email support@bspsoftware.com

 

News:

MetaManager - Administrative Tools for IBM Cognos
Pricing starting at $2,100
Download Now    Learn More

Main Menu

how to set previous row value of a same column subtotals .. please....

Started by bujji, 11 Feb 2009 10:56:03 AM

Previous topic - Next topic

bujji

Hello All


i have a problem with group by subtotals for running total column, my report structure is below


TransactionDate,Description,Amount,Balance(RunningTotal)
Jan-1-2008,DIT_TRANS IS N,-300,-300
Jan-2-2008,DIT_TRANS IS N,-250,-550
Jan-2-2008,DIT_TRANS IS N,150,-400
Jan-3-2008,DIT_TRANS IS N,500,100
Jan-3-2008,DIT_TRANS IS N,750,850
Jan-3-2008,DIT_TRANS IS N,-100,750
-----
--------

now when i group on Transaction date, apply subtotals for amount column it is fine,
for subtotals of Balance(RunningTotal) column needs to shows its previous row of balance(running total) value

as below


TransactionDate,Description,Amount,Balance(RunningTotal)
Jan-1-2008,DIT_TRANS IS N,-300,-300
~~~~Group By here~~~~~~~  -300,-300 (here previous row of balance (running total is -300)
Jan-2-2008,DIT_TRANS IS N,-250,-550
Jan-2-2008,DIT_TRANS IS N,150,-400
~~~~Group By here~~~~~~~  -100,-400 (here previous row of balance (running total is -400)
Jan-3-2008,DIT_TRANS IS N,500,100
Jan-3-2008,DIT_TRANS IS N,750,850
Jan-3-2008,DIT_TRANS IS N,-100,750
~~~~Group By here~~~~~~~  1100,750 (here previous row of balance (running total is 750)

in the subtotals of balance columns is just the previous row value of balance column

Please Help me

Thanks in advance
Bujji

Rajaggopal

1. Click on Balance(Running Total) and click Summarize icon on the toolbar and select "Custom".
2. A Report expression window will appear containing [Balance(Running Total)].
3. Click Ok
4. Click on the Summary cell for Balance(Running Total) and choose Aggregate Function for this to "None" and Rollup Aggregate Function of the same to "Automatic".

I believe this is your desired result.

bujji

Hello Raj Gopal,

Thank you very much for your reply,

i tried it, i set up properties for summary cell(Aggregate to none, RollupAggregate to Calculate( which is default)) , but didn't work well. it shows some different values

i am required as it is in your result report (previous post), but in my report it didn't work out

could you please tell me, how did you calculate Final Column, what expression did you use for this column please

Best Regards
bujji

Rajaggopal

Hi,
For the Final Column i used the same logic that was suggested in your earlier topic. I used running-total(Expected Volume) + Constant - Total(Expected Volume). I used Total(Expected Volume) because it was a fact column which was aggregated with Total.

As for the summary cell, did you use "Custom" aggregate from the aggregate button? Also the Rollup Aggregate should be changed to "Automatic" and not "Calculated"