Use a row in a calculation

[Victor Herron]

Hi everyone,

I am stuck on this and I know there must be a way.

I have a report that needs to look like this.

Level 1 Account     Level 2 Account     Amount

Revenue                Revenue               100,000
  Sub Total                                          100,000
  % of Revenue                                           100%

Distrubution Cost  Marketing                 10,000
                              Advertisement            1,000
                              Other                          1,000
  Sub Total                                              12,000 
  % of Revenue                                              12%
Overheads             Building                     20,000
                              Salary                        20,000
   Sub Total                                              40,000
   % of Revenue                                              40%

I want to use the revenue row in a grouped footer to divide the group total by revenue.
There will only be one revenue row in the report.

Thus Over heads Sub Total/Revenue row
      40,000/100,000 = 40%

I need to get a case statement that selects the revenue row and uses it in the calculation.  I have tried using a tabular reference but this subquery makes the report runtime spiral out of acceptance.

Any help would be greatly appreciated. 

Thanks

Victor Herron

[BIsrik]

U could do this in two ways.
Method1:
try using child tabular models. Use two TM's one for the distribution and other costs sub total calculation and other for the revenue subtotal calculation. In the parent TM use a calculation like Distr Cost/Revenue something like that.

Method2.

Set the auto group and summarize of TM to No. And select only the columns needed for the calculation. No computation is done. In the query have the computation. Ex first have the revenue calcu and then use this revenue calc in the other calc.

And one more point...TRef will come in handy for performance when the report not executed in HTML.

Srik

[Victor Herron]

Thanks for your suggestion I had tried both of these and the sub query lead to the report time spiralling.  This is how I eventually solved it.

Added another Summary line. 
Deleted the summary and added a grouped calculation in its place.
Calculation was
CASE WHEN [Level ] = 'Revenue'
THEN 0
ELSE [Total Budget]
END

This meant the report was not trying to run the other calculation again just suming rows it had already calculated.  It all seemed too simple in the end.

Thanks

[bdybldr]

Victor,
Thanks for the update.Ã,  Please remember to close your posts when they're solved.Ã,  See the board rules for instructions.