Link to home
Start Free TrialLog in
Avatar of Michael Katz
Michael KatzFlag for United States of America

asked on

2 separate reports from 1 stored procedure...

2 separate reports from 1 stored procedure...

I have a Detail version of a query with roughly 8 columns and a 'D' qualifier Column to show its detailed
and a summary version of a query with roughly 4 columns and a 'S' qualifier Column to show its Summarized

What I am hoping is to use 1 single Stored Procedure to maybe call a #TempTable for either the Input of a 'S' or 'D'...is this even realistic?

I am not against creating multiple Stored Procedures that pass results to each other...

Kind of new at this
Avatar of ste5an
ste5an
Flag of Germany image

You can do it, but whether it is a good idea or not depends on the kind of calculation necessary for the details or summary. Cause this may prohibit a cached query plan.

From the logical standpoint: a qualifier for detail and summary columns is often a sign of bad design. It should not be necessary at all. Either all columns form the detail report or they form the summary report.

But for the implementation: whether you need a temporary table or not depends solely on the logic of your calculations.
Avatar of slightwv (䄆 Netminder)
slightwv (䄆 Netminder)

What database product?  Most databases these days allow you to ROLLUP summary records in the same query that generates the detail rows.

If you can post some sample data and the expected results we can try to come up with a complete test case for you.
Nope, SSRS doesn't support it..
Multiple results sets from a single query are not supported. Ragged hierarchies, which do not have a constant number of columns and can produce different number of data values for each row, are not supported.
https://docs.microsoft.com/en-us/sql/reporting-services/report-data/report-datasets-ssrs?view=sql-server-2017

In your case, you have first dataset with 8 columns and second dataset with 4 columns..
If you wish to work it around, then try the below approach:
1. Insert those 2 dataset into a single temp table at the procedure level with the corresponding column names well aware so that
2. When you execute the procedure, all records can be passed to single data set at SSRS level.
3. In SSRS Data Set, use Data set filters to identify whether the record is for Detail or Summary.

Please be noted that this will have some performance issues if you have lot of records present in the data set..
Avatar of Michael Katz

ASKER

Thank you for your post.. I will attempt this
This question needs an answer!
Become an EE member today
7 DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform.
View membership options
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.