Post reply

Summing a subset of a Group

  • May 27, 2010 at 4:17 am

    #225076

    Hi,

    I'm a newbie to SSRS so i apologise in advance if i'm talking complete nonesense here.

    I have set up a report to return a summed sales total for each branch.

    I would now like to break down the sales total by period end but i want these figures to sit alongside (in the same row as) the summed sales total.

    I basically want to sum the sales total within the group but only include records with a specific period number but i have no idea how to acomplish this.

    I have seen that it is possible to simply filter the entire dataset but this will obviously restrict the data returned for the entire report and skew my totals.

    Does anyone have any suggestions about how i should go about this?

    Thanks in advance.

  • May 27, 2010 at 8:01 am

    #1173325

    I can't off the top of my head come up with a way for you to get just one reporting period without the others and without screwing with your total.

    If showing all of the periods would fit the requirements, you could investigate a matrix report and add a column group based on your reporting period.

    IF however that will not meet your needs another way I've been able to accomplish such things is to actually do all of the data manipulation in the SQL query or stored procedure. Then just output that results in a table.

    -Luke.

    To help us help you read this[/url]For better help with performance problems please read this[/url]

  • May 27, 2010 at 8:06 am

    #1173331

    Thanks for the advice Luke, i'll have a look at using a matrix instead; i have come across this suggestion during my research into the issue already so it would seem like a sound suggestion.

  • May 27, 2010 at 8:14 am

    #1173338

    Basically a matrix is a CrossTab that gets auto generated on the fly. Seems like you are doing a sales report so it'd be something like the following, please excuse the ascii art...

    NAme | Qtr1 | Qtr2 | Qtr3 | Qtr4 | Total

    Bob | 1000 | 1500 | 500 | 0 | 3000

    Jane

    Bill

    Mary

    etc...

    It dynamically adds rows as the names change and also dynamically will add columns based on your column groups.

    -Luke.

    To help us help you read this[/url]For better help with performance problems please read this[/url]

  • May 27, 2010 at 8:27 am

    #1173351

    Thanks again Luke.

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

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