Monday, May 07, 2012 2:28 AM
I have a matrix report that aggregates data on a time range....
<7 >=7-<14 >=14<21 .......
LAB 12 28 21
RAD 6 8 4
I would like for the user to be able to click on the aggregate value for a range and have a subreport open displaying data on each of the rows that have been aggregated for that range and row value(rad or lab)
is this possible? any tutorials on how to do this? main report does not have parameters.
Monday, May 07, 2012 4:11 AMModerator
This could be done easily inside matrix report either by drill through report or by embedding sub report.
In order to have drill through report you just right click on the matrix column and go to the properties and go to navigation and in jump to report drop down box choose your detail report and map the parameter as I have shown in the figure.
Other way is you could add sub report inside your matrix by just adding a column to the matrix and add sub report inside and map your parameter to the sub report. I have laos shown that option in the screen
I hope this will help
Syed Qazafi Anjum
Please click "Mark as Answer" if this resolves your problem or "Vote as Helpful" if you find it helpful.
- Proposed As Answer by Syed Qazafi AnjumMicrosoft Community Contributor, Moderator Monday, May 07, 2012 4:11 AM
- Marked As Answer by ghw123 Friday, May 11, 2012 1:38 AM
Tuesday, May 08, 2012 11:02 PM
Thanks for the reply Syed Qazafi Anjum I will look at this as soon as I can and will get back to you.
Wednesday, May 09, 2012 6:59 AMModerator
Syed has posted an excellent answer above. As Syed said, you can achieve your goal by creating a drillthrough report. We usually create a drillthrough report when want a user to open the report by clicking a link within the original report. Drillthrough reports commonly contain details about an item that is contained in the original summary report.
Syed also provides another solution: add a subreport to the main report. We can add a subreport when we need to display another report inside the body of a main report. In this issue, if you add a subreport control to the main report, you may also need to add a toggle to the row group to show/hide the subreport. For more information, you can refer to the blog below:
Building Subreport in Reporting Services
In the screenshot Syed has posted above, the drillthrough action and the subreport control are add to the report at the same time. Based on your requirement, I think the drillthrough report is a better choice. When designing the drillthrough report, you need to add two parameters to filter the time range and value like LAB/RAD. Then, you can pass the values of row/column group to the corresponding report parameters of the drillthrough report and filter the report.
If you have any questions, please feel free to ask.
Friday, May 11, 2012 1:44 AM
Thanks for your reply Mike, have tried both options and the report processing time becomes double or more.
Unfortunately the query references different different db's and has lots of criteria so performance is an issue.
It maybe we will have a main report with user selected parameters.
Is there a way that the user can select different param values without the report having to requery the db something about report param rather than a query param?