October 10, 2012 at 10:59 pm
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].
October 11, 2012 at 1:35 am
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
October 11, 2012 at 1:55 am
Excellent article Andy! Very clearly laid out and makes a fairly advanced piece of report development appear straightforward.
October 11, 2012 at 6:32 am
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."
October 11, 2012 at 7:37 am
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.
October 11, 2012 at 7:41 am
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.
October 11, 2012 at 9:31 am
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)
October 11, 2012 at 9:31 am
Hi Andy,
Excellent Article. Personally I never thought of GROUPING in this way in SSRS report.
Keep it up.. !! your atricle really helped..
October 11, 2012 at 4:29 pm
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
October 12, 2012 at 8:08 am
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
October 12, 2012 at 9:09 am
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!
October 12, 2012 at 10:04 am
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
October 13, 2012 at 8:17 am
Thanks. It's good article
October 15, 2012 at 4:35 am
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
October 15, 2012 at 1:32 pm
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 28 total)
You must be logged in to reply to this topic. Login to reply