Need help converting excel pivot table to SSRS

• nicenroad

  SSC Veteran

  Points: 255

  More actions

  June 12, 2015 at 2:04 pm

  #318198

  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?

• Luis Cazares

  SSC Guru

  Points: 183706

  More actions

  June 12, 2015 at 2:20 pm

  #1805233

  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;

  Luis C.
  General Disclaimer:
  Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

  How to post data/code on a forum to get the best help: Option 1 / Option 2

• Luis Cazares

  SSC Guru

  Points: 183706

  More actions

  June 15, 2015 at 8:51 am

  #1805470

  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.

  Luis C.
  General Disclaimer:
  Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

  How to post data/code on a forum to get the best help: Option 1 / Option 2