Displaying Percentages Properly in a Drill Down Report

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?
Roxanne25Asked:
Who is Participating?
 
dsackerConnect With a Mentor Contract ERP Admin/ConsultantCommented:
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) & "%"
0
 
Roxanne25Author Commented:
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. :)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.