Displaying Percentages Properly in a Drill Down Report

Posted on 2007-08-01
Medium Priority
Last Modified: 2013-11-27
Hello, I have asked this question before and never gotten a response... hoping someone will know the answer to this.

In a drill down report that I have created with Visual Studio for the Report Manager... I have a field called Parent and a field called child then subsequent data fields (financial data).

I need to know how to get percentage rollups to show correctly on the report.  IE, when the report is at the highest level it uses the sum function on all the financial fields... this works fine if the field is a summable field.  However, if the field is a percentage field... it just adds up all the percentages instead of calcing the proper percentage for the level of rollup that the report is at.

Here is a data example:

PARENT                 CHILD                      REVENUE             MARGIN           MARGIN %
CompanyABC        Subsidiary1              1000                   100                 10%
CompanyABC        Subsidiary2              1000                   100                  10%
CompanyABC        Subsidiary3              1000                   100                  10%

Now, in the report (using a drill down) it will look like this at the highest level which is wrong:

PARENT                                                 REVENUE             MARGIN            MARGIN %
CompanyABC                                        3000                    300                 30%

What it should show is still a margin of 10% ... but its just adding up all the percentages beneath it.  When you drill down to the child level... it would of course be correct.

How do I make the report calc the percentages at each drill level?
Question by:Roxanne25
LVL 20

Accepted Solution

dsacker earned 2000 total points
ID: 19609363
Do not sum the MARGIN field. In the MARGIN for the sum, simply add the expression for the percentage, similar to as follows:

=Sum(Field!Revenue.Value) / Sum(Field!Margin.Value)

That will display as a fraction, but you can format it to either display as a percent, or you can alter it as follows:

=Sum(Field!Revenue.Value) * 100 / Sum(Field!Margin.Value) & "%"

Author Comment

ID: 19609585
Ah ok.... I was calc'ing these margins in the table already... so I guess I didn't think to calc it in the report.  I thought if i I did it that way it would still just sum it overall.

Thanks. :)

Featured Post

Upgrade your Question Security!

Add Premium security features to your question to ensure its privacy or anonymity. Learn more about your ability to control Question Security today.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

For most people, the WrapPanel seems like a magic when they switch from WinForms to WPF. Most of us will think that the code that is used to write a control like that would be difficult. However, most of the work is done by the WPF engine, and the W…
Whether you've completed a degree in computer sciences or you're a self-taught programmer, writing your first lines of code in the real world is always a challenge. Here are some of the most common pitfalls for new programmers.
This is Part 3 in a 3-part series on Experts Exchange to discuss error handling in VBA code written for Excel. Part 1 of this series discussed basic error handling code using VBA. http://www.experts-exchange.com/videos/1478/Excel-Error-Handlin…
Are you ready to place your question in front of subject-matter experts for more timely responses? With the release of Priority Question, Premium Members, Team Accounts and Qualified Experts can now identify the emergent level of their issue, signal…

839 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question