Post reply

Totaling a group total instead of the details inside the group

  • October 26, 2011 at 2:21 pm

    #250725

    So my question is, i have a new column that is going to be added to a report. This column within each group as it is returned from the query, is all the same value on every row, but its really the Total of that grouping for a particuar field. (don't ask why they want it this way, they just do lol)

    So what i want to do is on the first grouping of that, i'm going to just take the last value on each group (doesn't matter because they are all the same value for every row within the group) and display that as the total. Then what i want to do on the NEXT group total is just take the total of those totals. So far the only thing i've been able to do is to take the total of every row in the detail row, which is not what i want.

    So for example. There are 10 rows in group1 and the column has 15 repeated for every row. There are 5 rows in group2 and the column has 43 repeated for every row. I want the total underneath that column to show 15 and 43 on those respectively(this part i can already do), then then total for those two groups combined i want to see 58 (15 + 43). The only thing i've gotten to show so far has been 365 --> ((15 * 10 rows) + (43 * 5 rows))

    So if anyone knows of an expression that would help me get this done. That would be GREAT!!!

    Also i need this to work on one more grouping outside of that as well!

    EDIT: I'm using ssrs 2008, not R2, and i've been trying to read up on using custom code but i can't find anything similar to my situation.

  • October 27, 2011 at 12:36 pm

    #1400361

    Are you showing the repeating values in the detail as well, or just on the group level?

  • October 27, 2011 at 12:40 pm

    #1400362

    Daniel Bowlin (10/27/2011)

    Are you showing the repeating values in the detail as well, or just on the group level?

    No They are just wanting the one value per group to show up where the rest of the Total's would. So the column would be completely blank for the details, and show up in the total. Then i need to sum that group total on each group.

    Here is a thread i made on another forum with some visuals. Maybe this will help a bit: http://social.msdn.microsoft.com/Forums/en-US/sqlreportingservices/thread/7e7a6633-247d-4491-b23b-648b4a407fde/#7e7a6633-247d-4491-b23b-648b4a407fde

  • October 27, 2011 at 1:33 pm

    #1400376

    If you can modify the dataset query I wrote a blog that may help.

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

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

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