Use total of column in calculation in another column 1

[NorthNone]

Is it possible in Cognos Report Studio 8.3 or 8.4 to create a report that summarizes last year's budget as a total number, then use that total as part of a calculation in the same list report? The goal is to have a running total for the new budget AND show what percent it is of last year's budget.

Last Year's budget for this line 7,500 This year's budget Line 1 = 8,000 8,000 80% of last year's budget 8,000/10,000
Last Year's budget for this line 1,500 This year's budget Line 2 = 1,500 9,500 95% of last year's budget 9,500/10,000
Last Year's budget for this line 1,000 This year's budget Line 3 = 2,500 12,000 120% of last year's budget 12,000/10,000
--------Total last year budget 10,000 Total this yr budget 12,000

I can't get last year's budget to add up first so that I can use the total budget as part of my percentage calculation.
Assistance gratefully received and acknowledged!

 

[blom0344]

Yes, possible in perhaps more than one way.

Here are my 2 cents:

1. Create seperate queries, one for this year and a second one for last year.
2. Make sure the 2nd query only fetches 1 row (total for last year)
3. Add a dummy item to both queries (say: constant 1)
4. Create a 3rd query as a join between the first 2 over the dummy items.
5. Drag objects for query 1 and 2 into the 3rd query.
6. Add running-total on the required fact to query 3
7. Add calculation to query 3 that performs the division

Just tested it against some demo data, works like it should..

Ties Blom

 

[NorthNone]

Works a treat! I am MOST grateful. THANKS!!!!

 

[NorthNone]

P.S. Have a STAR!

 

[blom0344]

Okay, glad you got it solved and thank you for the star..

Ties Blom