June 12, 2015 at 2:04 pm
Hello,
I was hoping someone could help me out. I have a client who manually creates a report every month. They run an SQL stored procedure that returns 2 result sets(using 2 select statements) from 2 tables. The client then manually copied the results from each result set and pasted it into a tab in excel. They then created a third tab for a pivot table using the other 2 tabs data.
I am tasked with converting this into an automated SSRS report someone can just enter the date range for and have the same pivot table created. The first issue I have run into is that SSRS doesn't like multiple data sets. It takes the first result set and ignores the second.
I tried doing a union between the select statements in the stored procedure and input both result sets into a temp table, that I could then use for the report, however the select fields lists in each select statement are not the same, and I get an error about them having to be equal.
My plan was to dump both result sets into one temp table, then create a matrix in SSRS to mirror the pivot table in excel, but I can't even get the stored procedure working right.
Any ideas?
June 12, 2015 at 2:20 pm
There's a problem here. Even if you explained exactly what you need to do, it's too generic. We can't help with the stored procedure because we can't see it. We're unable to see how the 2 data sets relate.
If you just want to append one data set to the other and most columns are similar, try adding columns to the queries. e.g.
CREATE TABLE #Table1(
colA int,
colB date,
colC varchar(20));
CREATE TABLE #Table2(
colA int,
colB date,
colD decimal(18,4));
SELECT t1.colA, t1.colB, t1.colC, CAST( NULL AS decimal(18,4)) AS colD
FROM #Table1 t1
UNION ALL
SELECT t2.colA, t2.colB, CAST( NULL AS varchar(20)) AS colC, t2.colD
FROM #Table2 t2
DROP TABLE #Table1;
DROP TABLE #Table2;
June 15, 2015 at 8:51 am
From your private message, I could understand that your 2 recordsets have the same information but one has details and the other one has totals. SSRS is able to group and present totals as needed without needing 2 recordsets.
You also have several problems with your code, such as a possible abuse of code division, use of nolock hints, incorrect cross tabs syntax and a bad placement of UNION ALL.
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy