Dynamic Grouping in SSRS Reports

  • AndyOwl

    Mr or Mrs. 500

    Points: 578

    Comments posted to this topic are about the item Dynamic Grouping in SSRS Reports

    Andy is a director of Wise Owl[/url], a UK company providing training courses (and occasional consultancy) in SQL, Reporting Services, Integration Services and Analysis Services, as well as in many other Microsoft software applications. You can see more about Wise Owl's SQL Server training courses here[/url].

  • aa.nichol

    SSC Enthusiast

    Points: 142

    Hi Andy

    Although I am not new to SSRS I still find things a bit puzzling sometimes, mainly the terminology. I felt your tutorial was very good, however, would it be possible to download the example file so I could follow the tutorial more closely.

    Regards

    AA

  • SQLPhil

    SSCarpal Tunnel

    Points: 4204

    Excellent article Andy! Very clearly laid out and makes a fairly advanced piece of report development appear straightforward.

  • jcrawf02

    SSC-Insane

    Points: 24198

    Interesting idea, have you seen a lot of use for this? Seems to me if the users wished to dig into the data and summarize on their own, they might be better served by exporting to Excel and using pivot tables? I suppose they might run into some data limitations given a large data set, but that would allow for multiple levels of grouping, etc that the report design does not.

    ---------------------------------------------------------
    How best to post your question[/url]
    How to post performance problems[/url]
    Tally Table:What it is and how it replaces a loop[/url]

    "stewsterl 80804 (10/16/2009)I guess when you stop and try to understand the solution provided you not only learn, but save yourself some headaches when you need to make any slight changes."

  • twgage

    SSC Journeyman

    Points: 82

    I see this as a way of bringing the pivot tables of Excel to an SSRS report - to a degree, anyway.

    I'll bet that a future version of SSRS will include a much easier way to accomplish this.

    Creating something like this with report parts, generic enough to use as a template, would be the next logical step.

  • mtassin

    SSC-Insane

    Points: 23096

    jcrawf02 (10/11/2012)


    Interesting idea, have you seen a lot of use for this? Seems to me if the users wished to dig into the data and summarize on their own, they might be better served by exporting to Excel and using pivot tables? I suppose they might run into some data limitations given a large data set, but that would allow for multiple levels of grouping, etc that the report design does not.

    I've written a few dynamic grouping reports in my past (mostly in crystal reports) and they've always been popular with management types who can't be bothered to fiddle around in Excel.

    This is a pretty good example of how to do this and well done.



    --Mark Tassin
    MCITP - SQL Server DBA
    Proud member of the Anti-RBAR alliance.
    For help with Performance click this link[/url]
    For tips on how to post your problems[/url]

  • whenriksen

    SSCarpal Tunnel

    Points: 4742

    I've used a slightly different approach for dynamic grouping. Instead of setting the grouping expression to a parameter, I define calculated fields in the dataset named simply Group#.

    I have used this for up to three exclusive levels of grouping. By exclusive I mean that the grouping options are filtered so that the item selected to group by in level 1 is not available in the parameter list for group level 2.

    I have also found this to be very maintanable for others as the logic is contained exclusively in the calculated field and grouping within the report is handled just like using a normal field. It is also very easy for others to understand what they are looking at when the cell values are set to use fields named Group1, Group2, Group3 rather then seeing <expr> in all of the cells. Just like in the article, I hide the headers/footers for a level when they select "No Group".

    Wes
    (A solid design is always preferable to a creative workaround)

  • mahen83

    SSC Enthusiast

    Points: 110

    Hi Andy,

    Excellent Article. Personally I never thought of GROUPING in this way in SSRS report.

    Keep it up.. !! your atricle really helped..

  • mtillman-921105

    SSCertifiable

    Points: 7049

    This is terrific, thank you. I'll have to try that.

    ______________________________________________________________________The greatest enemy of knowledge is not ignorance, it is the illusion of knowledge. - Stephen Hawking

  • mtillman-921105

    SSCertifiable

    Points: 7049

    Unfortunately, "Grouping" has two meanings. At first, I was thinking of "Grouping" as in a T-SQL "GROUP BY" clause. But what this really does is create a conditional stepped report, to use SSRS' terminology - or maybe it could be called a parameterized stepped report.

    In this case, you're not really changing the number of rows, but reordering them with headers. This is still terrific, but I just thought I'd clarify that.

    ______________________________________________________________________The greatest enemy of knowledge is not ignorance, it is the illusion of knowledge. - Stephen Hawking

  • twgage

    SSC Journeyman

    Points: 82

    mtillman-921105 (10/12/2012)


    Unfortunately, "Grouping" has two meanings. At first, I was thinking of "Grouping" as in a T-SQL "GROUP BY" clause. But what this really does is create a conditional stepped report, to use SSRS' terminology - or maybe it could be called a parameterized stepped report.

    In this case, you're not really changing the number of rows, but reordering them with headers. This is still terrific, but I just thought I'd clarify that.

    I'm trying to understand - SSRS does call it "Groups"; I thought stepping referred to the *format* of the report, not the organization of the data per se...

    T-SQL has the "Group By" clause in the select statement...

    I was under the impression that the only difference in the two "groupings" was in where the execution of the "grouping" is done...

    If there is a functional difference between grouping done by the SQL engine versus the grouping done by the SSRS engine, could you help me understand with a brief description or a link to something that describes it?

    Still trying to learn, thanks!

  • mtillman-921105

    SSCertifiable

    Points: 7049

    twgage (10/12/2012)


    mtillman-921105 (10/12/2012)


    Unfortunately, "Grouping" has two meanings. At first, I was thinking of "Grouping" as in a T-SQL "GROUP BY" clause. But what this really does is create a conditional stepped report, to use SSRS' terminology - or maybe it could be called a parameterized stepped report.

    In this case, you're not really changing the number of rows, but reordering them with headers. This is still terrific, but I just thought I'd clarify that.

    I'm trying to understand - SSRS does call it "Groups"; I thought stepping referred to the *format* of the report, not the organization of the data per se...

    T-SQL has the "Group By" clause in the select statement...

    I was under the impression that the only difference in the two "groupings" was in where the execution of the "grouping" is done...

    If there is a functional difference between grouping done by the SQL engine versus the grouping done by the SSRS engine, could you help me understand with a brief description or a link to something that describes it?

    Still trying to learn, thanks!

    If you use T_SQL's GROUP BY, then all of the rows collapse on the fields in the GROUP BY clause. For example, if you group by Country, then you will ony see one row per country in the results. But in "Grouping" in another sense, the way it is used in this article, keeps all of the rows, but orders them differently.

    Here is a page descrbing the "Stepped Report:" http://msdn.microsoft.com/en-us/library/dd220467(v=sql.100).aspx

    ______________________________________________________________________The greatest enemy of knowledge is not ignorance, it is the illusion of knowledge. - Stephen Hawking

  • bloger7791

    SSC Journeyman

    Points: 92

    Thanks. It's good article

  • alexdbabu

    Grasshopper

    Points: 15

    Hi,

    I am totally new to SSRS and I have SSRS 2005 installed in my system. How do I implement this dynamic grouping in SSRS 2005? I found that many things in the 2005 version are different from those explained in this article.Can I find a related article which explains the same for SSRS 2005?

    Thanks,

    Alex

  • ronald.charles.smith

    Newbie

    Points: 9

    I typically do this sort of thing in the dataset and them pass the grouped data to the report. Especially when aggregating large datasets, you want the heavy lifting to be done by SQL to minimize the data sent to the report.

Viewing 15 posts - 1 through 15 (of 29 total)

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