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.

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:

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

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.

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:

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.

Now in the percentage column use the following expression:

``````=FORMAT(
SUM(Fields!Sales.Value,"Plant")/SUM(Fields!Sales.Value,"Company")
,"P2"
)
``````

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:

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:

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

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