Post reply

Summing group field

  • April 3, 2012 at 7:48 pm

    #271485

    I'm currently having issues writing a table report that shows the sum of the value in a group header in an enclosing group's header.

    I'm trying to display a table that shows time spent assisting customers at different companies, with the company's budget, and budget for all companies in each region.

    This is what I'm aiming at

    This is what I have currently (note the Region budget line is wrong - it should sum the company budget header, but is summing it for each customer)

    Here's the SQL query

    select

    client_customer.hours

    , client_customer.name

    , client_company.name as com_name

    , client_company.budget

    , region.name

    from

    client_customer

    inner join client_company

    on client_customer.company_id = client_company.company_id

    inner join region

    on client_company.region_id = region.region_id

    Here's the SQL result

    hours | name | com_name | budget | region

    12 | J. Doe | Company 1 | 5000 | Region 1

    43.5 | S. Neil | Company 1 | 5000 | Region 1

    12 | A. Smith | Company 2 | 2000 | Region 1

    4 | C. Rodgers | Company 2 | 2000 | Region 1

    54 | K. Smith | Company 2 | 2000 | Region 1

    I'm creating the table currently by using =First(Fields!budget.value) in the company header, then =Sum(Fields!budget.value) in the region header.

    I'm sure the issue is that it's summing the budget for each company as many times as customers at each company, is there a best practice way of summing a group header that I could use above? I don't know what to put in the region budget field (I've thought of =Sum(First(Fields!budget.value)) and =Sum(ReportItems!CompanyHeaderText.Value))

    It would be great to get advice on how to properly go about the above.

  • April 4, 2012 at 7:26 am

    #1468827

    I wrote a blog about this exact problem. I hope this helps:

    http://www.bidn.com/blogs/Daniel/ssas/1766/ssrs-use-row_number-to-resolve-datasets-with-different-levels-of-aggregation

  • April 5, 2012 at 10:23 am

    #1469686

    I think this should be okay...

    =Sum(First(Fields!budget.value, "CompanyLevelGroupName"))

  • April 10, 2012 at 4:08 pm

    #1471584

    Thanks for this Daniel - it did exactly what I was after! I'll save this blog post for later 🙂

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply