jenhil34 jenhil34 - 1 year ago 89
SQL Question

SSRS Divide Sums from Different Scopes Returns 0

I have a grouped dataset. From the parent group down, the group names are:
Company > Plant > Details. In the end, I want to be able to take the sales of each plant and see what percentage it is of the entire company's sales. Let's say the company has a total sales of $500 and the sales of "Plant A" were $100 and "Plant B" sales were $400. I would image that I would need an expression at the Plant grouping level that was like:

=Sum(Sales)/Sum(Sales, "Company")

And I would get .2 for Plant A and .8 for Plant B. But if I do that, I get 0. I am at a complete loss. Any help with this would be greatly appreciated.

Additional information:

My exact setup is a little more complex than the example I gave below, but I believe the general idea still holds the same. I have a total of 6 groups:

enter image description here

The circled group is the equivalent of the "Plant" Group in my example. Here is the row group in my tablix for the GroupBy group (the one with the arrow pointing to it).

enter image description here

The expression that is circled in the picture above is the expression in question to get my percentage (right now really just a decimal, not formatted to be a percentage yet).

=sum(Fields!ActualCurrent.Value)/sum(Fields!ActualCurrent.Value, "Company")

Fields!ActualCurrent.Value is the equivalent of "Sales" in my example above. The expression above returns 0 for all groups. But yet, if I change it to

=sum(Fields!ActualCurrent.Value)+sum(Fields!ActualCurrent.Value, "Company")

It will produce the equivalent of $600 for "Plant A" and $900 for "Plant B."

I can't seem to find how it reacts as expected when adding the two sums, but produces 0 when I divide them.

Answer Source

It would be useful to see where are you using that expression to determine what is wrong here, but I think you can use this guide to get your desired result.

Create a tablix like this:

enter image description here

Note I've added Company and Plant fields as groups. Also I've deleted details group. Right click details and select Delete group and set Delete group only option.

enter image description here

Now in the percentage column use the following expression:


The sum of every plan divided by the sum of the whole company group. It is not necessary but I am using FORMAT function to format the float value returned by the expression to percentage format using two decimal places.

It should show something like this:

enter image description here

UPDATE: Try scoping the sum to your specific group: GroupBy

=sum(Fields!ActualCurrent.Value, "GroupBy")/sum(Fields!ActualCurrent.Value, "Company")

UPDATE 2: Format the cell to show decimal digits.

Use thiss expression:

sum(Fields!ActualCurrent.Value, "GroupBy")/sum(Fields!ActualCurrent.Value, "Company"),

It will format the value returned by the expression as a float with two decimal digits.

If you want to show the value in percentage format replace F2 in the expression for P2 (Percentage format with two decimal digits.)

Let me know if this helps.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download