August 24, 2011 at 6:01 am
Hello,
I have a requirement to create a report where a user can click on a link and that link would expand to another report related to that particular link.
What example- if there is a column which displays count and one of the row is selected by the user then that link should display all the data based on that count. If there is say a count of 100, the user should be able to expand that link and view the details for the 100 records.
Do someone know if drill through report is a solution for this requirement and how can I get further info on how to create a drill through report ? Also, the report in question has two date parameters- from date and to date which also need to be taken into consideration.
Thanks,.
Paul
August 24, 2011 at 8:05 am
Yes, this sounds like the exact use for a drill through report. Here is a how-to article from the microsoft website. Passing multiple parameters to the drill through report is supported so no worries with your date fields:
http://technet.microsoft.com/en-us/library/dd207031.aspx
Good luck, Steve
August 25, 2011 at 5:49 am
sdvoranchik (8/24/2011)
Yes, this sounds like the exact use for a drill through report. Here is a how-to article from the microsoft website. Passing multiple parameters to the drill through report is supported so no worries with your date fields:http://technet.microsoft.com/en-us/library/dd207031.aspx
Good luck, Steve
Thanks Steve but your link points to SSRS 2008 and I am using 2005. do you know of any other link for 2005 ?
August 25, 2011 at 6:24 am
Sorry about that, here is the equivilant article for SQL 2005 (and there are many addtional links you can get to from here):
http://technet.microsoft.com/en-us/library/ms159847(SQL.90).aspx
August 25, 2011 at 9:10 am
Hi,
I have created a drill through report but I am not getting the desired data in the target report. I dont know what is it that I am doing wrong.
I have created exactly the same parameters in the navigation tab of the source report as the target report. The problem I think are the fromdate and todate parameters in the source report. How do I link the parameters in the main report to the ones in the target report ?
If not can someone please explain or lead to a a link which explains the creation of drill through report in detail ? I am simply lost.
Thanks,
Paul
August 25, 2011 at 9:36 am
So it sounds like you are on your way and getting closer. Here is a quick recap. To create the drill through report, you would create it just as you would the main report.
To link to the drill through report you have selected the report item which will be contain the action. Right click the report item and go to properties. Select the navigation tab and in the Hyperlink Actions section select Jump To Report. The drop down box there should contain all the reports in your solution and you can select your desired drill-through report.
Now to add the parameters click the Parameters button. In the first column you can select the drop down and see the Parameters that have been defined in your drill through report. The second column is the value that needs to be passed into that parameter from the main report. Here are a few examples:
If you wanted to hardcode something you could pass:
="01/01/2010" or ="test string"
If you want to select one of the predefined fields from your dataset you can just select from the drop down
If you need to pass the paramters from the main report to the drill through you could select expression and you have the power of all the functions or type something like:
=Parameters!Date_From.Value - for Parms OR
=Fields!Date_To.Vale - for a field from your dataset
Hopefully this helps, if you need more information be as specific as possible with the error or issue and we will continue to try to get you uip and running.
August 26, 2011 at 2:43 am
sdvoranchik (8/25/2011)
So it sounds like you are on your way and getting closer. Here is a quick recap. To create the drill through report, you would create it just as you would the main report.To link to the drill through report you have selected the report item which will be contain the action. Right click the report item and go to properties. Select the navigation tab and in the Hyperlink Actions section select Jump To Report. The drop down box there should contain all the reports in your solution and you can select your desired drill-through report.
Now to add the parameters click the Parameters button. In the first column you can select the drop down and see the Parameters that have been defined in your drill through report. The second column is the value that needs to be passed into that parameter from the main report. Here are a few examples:
If you wanted to hardcode something you could pass:
="01/01/2010" or ="test string"
If you want to select one of the predefined fields from your dataset you can just select from the drop down
If you need to pass the paramters from the main report to the drill through you could select expression and you have the power of all the functions or type something like:
=Parameters!Date_From.Value - for Parms OR
=Fields!Date_To.Vale - for a field from your dataset
Hopefully this helps, if you need more information be as specific as possible with the error or issue and we will continue to try to get you uip and running.
Thanks for your detailed reply.
August 26, 2011 at 5:46 am
This is when I usually try to take a step back and break things down to try to figure out where the problem is occuring. First step is to verify the data type of the fromdate and todate parameters in the parent report, child report, and stored proc. Also, what is the data type of the database field you are comparing them to. Make sure they have all been defined consistantly, you may be getting issues with dates being converted to strings and vice versa.
Next follow the path of the parameters from the beginning to the end.
In the parent report create two text boxes in the body or header and print Parameters!fromdate.Value and Parameters!todate.Value back into the report so you can physically see the value that is being used. This is the exact value that will be passed to the child report.
Next in the child report, do the same thing. Create two textboxes to verify the dates being passed into the report. I would also unhide the parameters and verify the values here as well.
Finally, in the stored proc, cut and paste the values you are seeing in the child report into the stored proc values you are trying to run. Does this return any data? Hopefully this will help you narrow down where the disconnect is happening. Please let us know.
August 30, 2011 at 3:23 am
sdvoranchik (8/26/2011)
This is when I usually try to take a step back and break things down to try to figure out where the problem is occuring. First step is to verify the data type of the fromdate and todate parameters in the parent report, child report, and stored proc. Also, what is the data type of the database field you are comparing them to. Make sure they have all been defined consistantly, you may be getting issues with dates being converted to strings and vice versa.Next follow the path of the parameters from the beginning to the end.
In the parent report create two text boxes in the body or header and print Parameters!fromdate.Value and Parameters!todate.Value back into the report so you can physically see the value that is being used. This is the exact value that will be passed to the child report.
Next in the child report, do the same thing. Create two textboxes to verify the dates being passed into the report. I would also unhide the parameters and verify the values here as well.
Finally, in the stored proc, cut and paste the values you are seeing in the child report into the stored proc values you are trying to run. Does this return any data? Hopefully this will help you narrow down where the disconnect is happening. Please let us know.
Hi thanks for your informative reply. The report is running fine now but the challenge now is to drill through the totals row. I need to be able to drill through to a totals row which uses the expression:
=SUM(Fields!Late_c.Value)
Now this becomes slightly complicated as I dont know how to create a parameter for the sum. Would you have some idea for this ?
Thanks,
Paul
August 30, 2011 at 7:14 am
I am not sure I fully understand the challenge. If your expression is in the master report, setting up the drill through is relatively similar. Your question specifically asks, how do you set up a paramter for a sum. You should set this up the same way as you did for your other parameters. In your child report create the parameter and define it as an integer (or float) depending on your need for decimal places. Then in your master report go back to your the report item where you defined the drill-through. For the paramter name select this new paramtere and for the parameter value select expression and use the expression you provided.
=SUM(Fields!Late_c.Value)
Does that make sense?
August 30, 2011 at 8:03 am
sdvoranchik (8/30/2011)
I am not sure I fully understand the challenge. If your expression is in the master report, setting up the drill through is relatively similar. Your question specifically asks, how do you set up a paramter for a sum. You should set this up the same way as you did for your other parameters. In your child report create the parameter and define it as an integer (or float) depending on your need for decimal places. Then in your master report go back to your the report item where you defined the drill-through. For the paramter name select this new paramtere and for the parameter value select expression and use the expression you provided.=SUM(Fields!Late_c.Value)
Does that make sense?
Thanks again, actually I assigned the value as "Late" for the other corresponding parameter and it works and theoritically should be the same for this parameter as well. However, when I run the report, the child report returns a blank. I think I am unable to link the report item because it is a sum instead of a field.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply