Displaying Percentages Properly in a Drill Down Report

Posted on 2007-08-01
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

    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

    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. :)

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Enabling OSINT in Activity Based Intelligence

    Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

    In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
    This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
    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.…
    This video discusses moving either the default database or any database to a new volume.

    737 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

    Need Help in Real-Time?

    Connect with top rated Experts

    15 Experts available now in Live!

    Get 1:1 Help Now