-
% of Total in reporting services
Hello. I've been searching all over the web and cannot find anything specific to my issue. What i'm trying to do is create a report that shows total units being shipped from one place to another as well as the % of total units. I have the report working fine from a units standpoint, but i want to express the values as the % of total units. for example, lets say i have two warehouse WA and WB, and 3 destintation X, Y, Z. I want to show the number of units for all the pairs (WA-X, WA-Y,..., WB-Z). i also want to express thes values as a percentage. sum(WA-X)/sum(WA). this would tell me what % of units that were shipped out of WA, went to X. in order to do this, i need to have sum(WA) already cacluated, so a running total wouldn't do the trick. Is there way for me to pull a value from one group and use it in another.
The source of my data is a sql server relational db.
thanks
howie
-
Did you figure it out already?.
-
Nope. i'm developing a different approach and am hoping the customer will approve. if not, i will either need to figure this out or do it throughsql code. coding this in sql is going to be quite tedious.
-
This is what I did:
Create your report as usual, with detail and subtotals and such.
Then, name each textbox uniquely in each subtotal:TextboxSub1SalesUnits (for the first level of subtotals), so on and so forth. Then, on the expresion for the detail line, use this formula to calculate the percent to total:
=(Fields!SalesUnits.Value / ReportItems!TextBoxSub1SalesUnits.Value)*100
Then, on the subtotal 1 level type:
=(ReportItems!TextBoxSub1SalesUnits.Value/ReportItems!TextBoxSub2SalesUnits.Value)*100
If you want, you can make textboxes for the totals by warehouse (and hide them if you want to), and still make reference in any calculation.
It's a pain to create all those textboxes, name them and make the formulas, but it's the only way I know so far.
Hope it works!
-
This works!!! I knew there had to be something and it makes complete sense...but i just couldn't figure out what to do! I really appreicate your help. Thanks a bunch!!
howie
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
|