Calculating % in a report with nothing but number data.

[J360]

I have a dynamic report that shows income statement items trended out for the last 12 months.

If, for example, the user selects to see Gross Revenue and Cost Of Materials, these measures will be the row items and the months will be the column headers. I've hidden the detail section where each transaction is stored and display the sums of the measures in the "Measure" Group header.

This works great, but now I need to insert a row for Cost of Materials as a percent of Gross Revenue. How do I specify to divide COM by GR? Of course the data is just numbers and the computer doesn't understand that I want to divide one by the other.

I've tried the formula = sum([200808])/sum([200808]) where 200808 is the column name, but of course no matter what header or footer i place this in I just get 100%. Is there any possible way to accomplish what I need?

[NeoPa]

You'll need some more detailed information in the question if anyone's to know what you're working with. Metadata would be a good start.

However, your last paragraph expresses surprise that =Sum([200808])/Sum([200808]) results in 1 (or 100%). Is this a typo? This is equivalent to X/X which will always return 1.

[J360]

You'll need some more detailed information in the question if anyone's to know what you're working with. Metadata would be a good start.

However, your last paragraph expresses surprise that =Sum([200808])/Sum([200808]) results in 1 (or 100%). Is this a typo? This is equivalent to X/X which will always return 1.

Exactly, that's not a typo at all. As the report is structured, that is the only way I can work out to enter any equation.

The table the report is based on is structured as such:

Columns - Manager; Branch; Item; 200808; 200807; ... 200708;
Rows - John MS Revenue $Data; $Data;....$Data;
Costs $Data; $Data; ...$Data;

So then the report is tabular. Manager and Branch go in the Branch header. Item has it's own header that displays a sum of the data for each month. the monthly data for each transaction goes in the detail section and is hidden.

this works fine for $ amounts, but I can't think of anyway to display in the Item header Costs as a Percentage of Revenues. Ideally, I would specify the sum of Costs/ sum of Revenues, but the data in the detail section is not setup this way. I thought maybe I should create tables with the percents already done and just use SQL to pull from them. However, there are 4 relevant % measures and 6 fully dynamic fields on which to calculate the data. The number of tables would be too large to be feasible.

Any suggestions?