0

I have a regular Table in SSRS. With 3 Groups...

(Parent) STORE - CLERK - PRODUCT (Child)

I have some regular aggregations. How many PRODUCTS Sold by a CLERK , How Many CLERKS Per STORE and Eventually How many PRODUCTS Per STORE

On top of the Regular Sums And Avgs, I need To Find Out The Percentage of PRODUCT (Type) Meaning a Particular value of that Group.

Example STORE 001 Has Sold 10 RADIOS (a PRODUCT) and There has Been 100 RADIOS sold by all Stores

So Basically What I Need is to show STORE 001 is Responsible for 10% of all RADIO Sales.

(A note: Ideally , I would Like to show this To adjust to the Data - So if I add new products It will group those as products (Naturally) but still give me those percentages)

Azberg
  • 1

1 Answers1

0
= fields!product.value / sum(fields!product.value)

in its most basic form you would want to use something like this.

The first will give you the total of the current row of data and the second will give you the total of all rows of that product.

Thus you would have 10 / 100 (per your example).

This is assuming that you have your data structured correctly. Depending on the structure of you report you may need to add a scope to your total summation to make sure that you are not totaling any other datasets that may reference the same product or field. 

sum(fields!product.value, "--your dataset here--")
SFrejofsky
  • 732
  • 5
  • 16
  • Thanks. I understand the division part. but, sum(fields!product.value) just gives me the sum of another scope. I just dont need the sum of fields!product.value I need the sum of that scope for a particular value (Product), If I put sum(fields!product.value) it gives me the sum of that scope for all the product types in that scope. – Azberg Nov 19 '14 at 18:36
  • That is why I specified that your data would have to be structured correctly. If you have report grouped by the items you are trying to see the total of the the compiler will recognize the group as its scope and return the total for only that group. – SFrejofsky Nov 19 '14 at 20:31
  • Yes, but, my data I want the total is grouped as a child so when I refer to that group it gives me the group amount sliced with its parent groups , I want the grouped amount not sliced up by the parents just the group alone regardless of the other groups. Thank you for your patience. – Azberg Nov 19 '14 at 20:44
  • You can sum the total of your "child" group by setting the scope to the child group. Add up the child groups to get your totals and divide. Due to the fact that you have a number of aggregations of the data you will have to add up the sub totals and work with the variables more algebraically. similar to ((sub1 + sub2 + sub3) / (tsub1 + tsub2 +tsub3)) = % – SFrejofsky Nov 20 '14 at 20:02