Results 1 to 5 of 5

Thread: % of Total in reporting services

  1. #1
    Join Date
    Feb 2005
    Posts
    3

    % 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

  2. #2
    Join Date
    Mar 2005
    Location
    Tampa FL USA
    Posts
    2
    Did you figure it out already?.

  3. #3
    Join Date
    Feb 2005
    Posts
    3
    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.

  4. #4
    Join Date
    Mar 2005
    Location
    Tampa FL USA
    Posts
    2
    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!

  5. #5
    Join Date
    Feb 2005
    Posts
    3
    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
  •