Difficulty With Drill-Down Reports
-
Monday, February 18, 2013 5:28 PM
Greetings, All -
I'm tasked with designing a report to replace one that uses a SQL query in SQL Server to gather the data with the result C&P'd into a premade Excel sheet using a pivot table to create the final product.
Well, not so much tasked as endeavoring to create something less clunky using SSRS.
I have the query made and it returns what I need. I've made that into a stored procedure called in a dataset in SSRS. It all works fine and produces the exact same content - although, I'm still working on a way to sort where the alpha entries appear before the numeric - as the Excel version.
Now, it needs to be turned into a drill-down report. I've tried a couple of tutorials I've found online, but they seem to add additional columns - headers - that make the report burdensomely wide. The Excel copy simply has a small '+' next to each category that can be drilled, like so;
The SSRS Tablix looks like this;
I guess the question is, how can I get the Tablix to drill-down on each of the Period, Date, Desc, Proc, and the first two Diagnoses without having to add a new header column for each one?
Probably, if someone could just let me know how to do one of them, I can probably just apply what I learn from that to the others on my own.
As always, thanx very much in advance for any help crumbs tossed my way!!
All Replies
-
Monday, February 18, 2013 6:05 PM
Hi,
You can do this in SSRS. It's a bit different than excel. This tutorial will show you. Skip until maybe 4:15 on the video.
Basically, it's just toggling the visibility of the row based on a grouping column. But since you have multiple groupings, it maybe a challenge.
http://www.youtube.com/watch?v=t2nYDHIVXqY
Below is a two tier grouping i made which shows that it is possible to have multiple. What the video shows is setting the entire row's visibility. To do the nested, for example in your design layout, to create a toggle/drilldown based on ClaimTypeDesc, you need to click on the Proc_code and and drag your mouse to paid_amt. Then on the Visibility Toggle property on the right bottom, select ClaimTypeDesc as the toggle field.
Please mark as answered or vote helpful if this post help resolved your issue. Thanks!
k r o o t z- Marked As Answer by Adam Quark Friday, February 22, 2013 3:42 PM
-
Monday, February 18, 2013 6:36 PM
Thanx for the response, krootz, but your solution is what I've already found via web searches.
Your answer adds two columns to the report; CLIENTNAME and TOTALDURATION. At the moment, real estate on the report is important, since this will be printed out, and I need to minimize the number of columns. I don't really have the room to add in 5+ extra columns.
The result needs to be like the Excel sheet; where a column simply expands, rather than adding another column as a header for the expansion.
Is this even possible in SSRS?
-
Monday, February 18, 2013 7:15 PM
Well, an idea hit me.
Since the visibility of a column is based upon a toggled actuated by clicking on the group name object, I thought I could, rather, base the visibility on the name of the object prior to the one targeted. For example, the Claim Type Desc text box's visibility is controlled by toggling the Admit Date like so;
But, what happens is, though the data for the columns are all hidden, the row space for each row is still allocated, giving me;
Maybe I'm part-way there, I dunno. If I could get the row space to be hidden along with the data, I'd have what I want.
I think...
-
Monday, February 18, 2013 8:00 PM
Adam, what you're trying to do is completely possible. I'm going to briefly outline how I have done this on some of my own reports, but perhaps someone else has a better way.
Basically, you want to create a group for each level of drill down and display a row header for that row. If a column is automatically generated when you make the group, just delete it or hide it. On the row header for each drill down level, you're going to display all of the detail aggregate data for each level of drill, so you will need to repeat the aggregates you have in each of your columns in these rows - they should automatically scope to the applicable levels.
You should just keep a single column (call it GroupingColum for our example )to display the values (ClientName, Duration, etc.) that you are grouping your rows on. Next, you'll want to toggle visibility of each row group based on someone clicking the cell in the GroupingColumn on the row above the row you are setting visibility for. Finally, add a bit of padding on the left side of each cell in the GroupingColumn - increasing the amt of padding as you go down.
You should end up with a grouping column that behaves something like this:
--------GroupColumn--------
+201202
*Clicking on 20120202 cell will produce:
--------GroupColumn--------
-20120202
+2/2/2012
+2/29/2012
*Clicking on 2/29/2012 cell will produce:
--------GroupColumn--------
-20120202
+2/2/2012
-2/29/2012
+Primary Srvc Referral
+Lab/Path Pro. FeesSo that's the general idea, hope this helps if that's what you're after. If you have specific questions for implementing this, shoot.
- Edited by PeterDNCO Monday, February 18, 2013 8:01 PM
- Marked As Answer by Adam Quark Friday, February 22, 2013 3:42 PM
-
Monday, February 18, 2013 8:10 PM
Thanx for the response, krootz, but your solution is what I've already found via web searches.
Your answer adds two columns to the report; CLIENTNAME and TOTALDURATION. At the moment, real estate on the report is important, since this will be printed out, and I need to minimize the number of columns. I don't really have the room to add in 5+ extra columns.
The result needs to be like the Excel sheet; where a column simply expands, rather than adding another column as a header for the expansion.
Is this even possible in SSRS?
Hi Adam,
That was just an example. WHen you add a group, it automatically creates the extra column. YOu can delete that column without deleting the group and still use your existing columns as the toggle.
Please mark as answered or vote helpful if this post help resolved your issue. Thanks!
k r o o t z -
Monday, February 18, 2013 8:19 PMLooks close, PeterDNCO, but this old guy is entirely self-taught on this new-fangled do-hickey. I'll see if I can work my way through what you posted, but I may come back for a bit more basic detail...
-
Monday, February 18, 2013 8:59 PM
Another interesting aspect of the Excel verison is that if a single field is clicked in order to roll it up, then all fields of that name are rolled up - or, expanded, as the case may be.
For example, in the following image;
The top one is prior to clicking the '+' sign adjacent to the first "Primary Srvc Referral." The bottom image is after having clicked that first '+.' As can be seen, BOTH "Primary Srvc Referral"s expanded - actually, all of them in the pivot table did.
As I understand SSRS at the moment, if one "Primary Srvc Referral" is clicked, only that one entry is expanded, not all of them in the report. This may not be an issue, but I was told to duplicate the Excel funtionality, so, if I can, I'd like to be able to expand all of them by clicking one of them. However, I have a feeling that expanding one at a time will be acceptable, especially if that's the functionality of SSRS. Problem is, there are those in higher levels of the business world who will say, "Well, Excel can do it, why can't this fancy SSRS do the same? It must be able to - make it so!," even though they have absolutely no idea of WHY they want it that way, except that Excel did. Had they seen SSRS first, it would never occur to them. I've dealt with way too many of that type...
Anyway, long story short, if I can expand all by clicking one, that'd be good, but if I can't, that's fine, too.
-
Monday, February 18, 2013 9:01 PM
Hi Adam,
That was just an example. WHen you add a group, it automatically creates the extra column. YOu can delete that column without deleting the group and still use your existing columns as the toggle.
Please mark as answered or vote helpful if this post help resolved your issue. Thanks!
k r o o t zAh!
Maybe that's what PeterDNCO was talking about.
OK, I'll check that out.
- Edited by Adam Quark Monday, February 18, 2013 9:01 PM
-
Friday, February 22, 2013 3:42 PM
OK, got it to do what I wanted. It was maybe a sort of combination of Peter's and krootz' suggestions. The idea I had was essentially it, but rather than selecting the TEXTBOX to assign visibility, I should have selected the GROUP - the yelow bars, down at the lower left of the screen.
So, it now copllapses on the groups I want, no columns are added and there's no extra row space.
All is good in the world...
- Marked As Answer by Adam Quark Friday, February 22, 2013 3:42 PM
.png)
