In need of some Tessitura friends who have spent WAY more time in SSRS reports than I...
I've copied and altered the canned Performance Seating Book report to fit some additional needs and I have it 97% working in TEST the way we want...BUT for whatever reason, it isn't running the subreport that is supposed to show the parameters used. What am I missing?
Here's the error:
Here are my subreport details:
Thanks in advance!
Madeline Dummerth (Past Member) said:What am I missing?
I would find the sproc associated with the subreport, and run it in its own with Log Viewer running. Save the output, and compare to a run of the whole report. Tedious work, but differences spotted there can be the key to digging below the meaningless surface of "subreport could not be shown".
(I pretty much gave up on copying-and-modifying standard reports years ago, FYI. I usually crib some code from the original and make my own SSRS report for the front end. E.g. you may not even need a composite report.)
Thanks! I've never written my own SSRS report (somehow I find it intimidating??). I started over again and somehow now it's working? *shrug*
Madeline Dummerth (Past Member) said:I started over again and somehow now it's working? *shrug*
Wouldn't be the first time. SSRS is weird. Take the win! :-)
Agreed. There are so many hidden quirks. But at least you can google solutions to some of then unlike infomaker. TLCC ssrs workshops changed my life
If you choose to start writing your own SSRS reports in most cases sub reports really are not needed and better performance can be achieved by a combination of optimizing the underlying query to return different levels of detail and using expressions to impact formatting based on the level of detail to display as shown below. I've written well over 200 SSRS reports and there have only been 2 or 3 times where I've been forced into utilizing a sub report.
Here is a sample where a single function is handing all the different levels of data needed and the formatting is managed in the report.
Do sub reports affect what data that shows up when you save a report as a csv file? If so, then that might be a reason to include them. I know that before I had SSMS access I would often run a report and save it as a csv to see what other data I could get out of the report. Sometimes it had what I needed although it doesn't show up ion the screen within the report.
For the most part the results are exactly the same, the difference is going to be in the columns especially with the way ID and description are used in the Tessitura data schema. Here are the results from a quick test using season and season type.
Sub Report result:
No Sub Report result
The big saving are going to be the design and maintenance. With the sub report I had to build two reports and then will need to make sure the link Here are the parts for the sub report version:
The Sub report:
The Parent report:
The version with no SubReport:
The biggest difference is going to be on the database side where the report without the sub report will run much faster. Using SQL server profiler we can see what the reports are doing on the database side:
No sub report:
With a sub report:
As you can see the one with the sub report does a lot more data reads and execute more SQL statements against the database.