Solved

SSRS Matrix report with Drilldown/Subreport

Posted on 2010-11-12
9
2,630 Views
Last Modified: 2012-05-10
I have a matrix report that contains a part number, partname and job number in the rows.  The columns are the months/year for the next 12 months.  The detail is a sum of parts due for each of those months.

I now want to add either a subreport or drilldown capability to the report.  When you click the Job number it should show details about the job (amount of parts ordered, how many were shipped and on what date, how many are in process, etc.).  I can get it to work with a drilldown report by setting the toggle item for all of the details to the job number.  The issue I have is that the job number column looks like it is about 6" wide because of the details columns.  I want it to be normal width until you click the toggle switch, then open up the details below the job number.  

I have tried inserting a subreport into the matrix but have not had any luck.  It doesn't seem to link the 2 reports correctly.

Any help on how to accomplish this would be appreciated.  Please provide details, I am a novice with SSRS reports.
0
Comment
Question by:PsychoDazey
  • 4
  • 3
9 Comments
 
LVL 21

Accepted Solution

by:
Yurich earned 500 total points
ID: 34131922
Hello,

The sub-report will work for you - I tested and if it's set to invisible, then it will not be expanded.
To add a sub-report to your matrix (presuming it's 2005, slightly different for 2008):
 - Click a detail field (where you have your sums) and select "Add Row"
 - Drag and drop a sub-report control in the new field
 - Right-click your sub-report and set the sub-report name and its parameters (two different tabs)
 - Select your sub-report field again and go to the properties (F4)
 - Set in the Visibility "Hidden" to true and select a toggle item. That can be a bit tricky as it will allow only fields that are in the same group or in the containing group.

That should be all.
Good luck :)
0
 
LVL 6

Author Comment

by:PsychoDazey
ID: 34133189
Hi Yurich -

Its SQL 2008 R2.  How would I add the subreport to that version?  And as for your last bullet point "That can be a bit tricky as it will allow only fields that are in the same group or in the containing group."  Does that men I have to have the fields from the subreport in the main report also?  Or just in the dataset?
0
 
LVL 21

Expert Comment

by:Yurich
ID: 34133205
Hello,

You just open a control panel with your controls and drop your sub-report control in the extra field under your sum-field you created. The wording for "Add Row" might be different somewhat for 2008. I don't have 2008 installed handy, so can't tell you exactly to a letter what it will say.

As for the fields, no, you don't need to have any extra fields anywhere. The structure of the matrix is based on groups and even a detail row is a group (i.e. your sum field). So your sub-report will be a part of a group as soon as you drop it on your matrix. You shouldn't worry about this too much - when you get to the point of setting visibility for your sub-report, you'll have only allowed controls in your Toggle Item drop-down box. So if you pick one, it will surely work.

Cheers
0
Space-Age Communications Transitions to DevOps

ViaSat, a global provider of satellite and wireless communications, securely connects businesses, governments, and organizations to the Internet. Learn how ViaSat’s Network Solutions Engineer, drove the transition from a traditional network support to a DevOps-centric model.

 
LVL 10

Expert Comment

by:itcouple
ID: 34139938
Hi

Recently I had a request to display limited number of info and I have decided to use a tooltip :) The user reaction was very positive.

also just something to be aware (not necessarily in this case).... If you use subreports and they contain shared dataset it will not display them in preview but they will work correctly when you deploy everything

Hope that helps
Emil
0
 
LVL 6

Author Comment

by:PsychoDazey
ID: 34198690
I still haven't gotten this to work.  I want the main matrix report to show the part number, part name and overdue in the grouping columns.  In the details will be my quantities due with the dates as headers.  My sub report will show the job number(s) and details of that job for each part number.  I want to toggle the display of the sub report by part number.

When I try to expand the subreport I get an error and it closes BIDS.  Emil, is that what you meant when you said it wouldn't display correctly in preview mode?
0
 
LVL 21

Expert Comment

by:Yurich
ID: 34213850
I never had any problems with sub-reports not displaying. To eliminate possibility that it's your report viewer, you can try deploying your report and sub-report to the server and see if you're getting the same error. There is a chance that you'll see more details about the error on the server.

To see if you have some subtle problem with your query or report/sub-report design, delete the sub-report that you have now and create a new one. In this sub-report show some literal text (e.g. "testing sub-report"). See if it's working. Than you can add a parameter to your sub-report and pass it from your main report. Set your sub-report to display your parameter and see if it's working. If you manage to get that far, you can try re-creating your original sub-report to display your job details. Sometimes, re-creating a new report is easier and faster than trying to fix the existing one. Especially if you experimented a lot with it. Removing and deleting things (i.e. controls, data objects, etc.) from report not always work neatly - there can be still traces and dependencies left in the XML file which you will not see or know on from the design view.

Good luck
0
 
LVL 6

Author Comment

by:PsychoDazey
ID: 34275440
Thanks for all the suggestions so far.  I had to put this aside for right now, I am dealing with a server migration but will return to this hopefully Monday.
0
 
LVL 6

Author Comment

by:PsychoDazey
ID: 34740012
Yurich actually gave me all of the information I needed on this, I neglected to come back and close.  please allow me to award the points and I will close the question properly.
0

Featured Post

MIM Survival Guide for Service Desk Managers

Major incidents can send mastered service desk processes into disorder. Systems and tools produce the data needed to resolve these incidents, but your challenge is getting that information to the right people fast. Check out the Survival Guide and begin bringing order to chaos.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Time Corrections for Reports Working with a report, we made some interesting discoveries about the time corrections/updates We are using the following Parameters: Starting Entered Date (Date) formatted as Data type: "Date/Time" Ending Entered …
Introduction In the following article I’ll be discussing and demonstrating several different ways of how images can be put on a report. I’m using SQL Server Reporting Services 2008 R2 CTP, more precisely version 10.50.1352.12, but the methods ex…
This video shows how to quickly and easily add an email signature for all users on Exchange 2016. The resulting signature is applied on a server level by Exchange Online. The email signature template has been downloaded from: www.mail-signatures…
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used below. https://filedb.experts-exchange.com/incoming/2017/03_w12/1151775/Permutations.txt https://filedb.experts-exchange.com/incoming/201…

828 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question