By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
429,373 Members | 1,792 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 429,373 IT Pros & Developers. It's quick & easy.

Calculating % in a report with nothing but number data.

P: 23
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?
Sep 15 '08 #1
Share this Question
Share on Google+
2 Replies


NeoPa
Expert Mod 15k+
P: 31,436
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.
Sep 15 '08 #2

P: 23
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?
Sep 16 '08 #3

Post your reply

Sign in to post your reply or Sign up for a free account.