SSRS Report: Cannot see the field from dynamic stored procedure

I have to create a cross-tab report

1. Where the spreading columns have to created dynamically
2. Add two calculated column - GrandTotal row, and Total Column

Required layout:

Applications by school and status code             App Year: 2015                   Ref Date: 22/01/2015

School	Total	A00	A01	A02	As many columns as needed (Dynamic)…
Acton High School	3	2	1
Barton School	8	4	2	2
Calvin Academy	2		1	1
As many rows as there is schools…
Totals	13	6	4	3

Parameters:

1. Application Year – Defaults to the application year of the current year (presently 2015) – option to go back to previous years
2. Reference Date – Defaults to current date, but will need to be changed if the user changes the Year parameter
3. Threshold, Application status total threshold - Please include a filter option to only show schools where the total applications is above a certain number.  Suggested values: 1 (Default),2,5,10,15,20,30,40,50

My Approach towards creating this dynamic SQL report :

1. Create a stored procedure with dynamic SQL which will have the follow properties
1. It will accept three parameters. Parameters will be initialized with default values.
2. Declare a variable which will store the value of the spreading column
3. Declare a variable which will add the sum() on every spreading column generated by dynamic SQL in step 2 which will calculate the GrandTotal row.
4. Create a dynamic PIVOT query and inside the PIVOT query
1. To calculate the TotalColumn, Use a SQL window aggregate function OVER with aggregate function COUNT
2. SELECT INTO a temporary table
3. Terminate the PIVOT query with a semicolon inside the same scope
4. Now issue the SELECT query against the temporary table created in here step 2. Add a RANK column in this SELECT statement.
5. Union the result set from here in step 4 with the dynamic GrandTotal row
5. Execute the dynamic pivot query
2. Create a SSRS report
1. Create a DATASET
1. Give a dataset name
2. Choose a data source
3. Choose Query Type: Text, do not choose stored procedure. If you choose stored procedure it will not return any columns or fields in report. In the query designer type the stored procedure name with parameter name each separated by comma. For example: exec xProcApplicationsBySchool @RefDate, @ApplicationYear
4. Check the parameter tab. In case parameters are not automatically created then add the parameters manually
5. Create a filter in case you have not pass some query parameter. The main difference between the query parameter and filter is, query parameter will filter the data in the dataset where as if you use filter the data will be in the dataset but when you will run the query then it will use the query parameter. The benefit of filter is you can decide later if you want to filter the data or not where as query parameter already filter the data.

Some of the problems I have experienced while I was creating this report:

1. Stored procedure was executing successfully but it was not returning any columns. To solve this
1. SET FMTONLY OFF;
2. Since it was dynamic sql stored procedure, all the sql statement was written inside a single inverted quotation mark, so when I needed to use a single inverted single quotation mark to show it is a string, it was giving an string conversion error message. 
1. For example to write - WHERE city='London' in dynamic sql you have write - WHERE city='''London'''. Here one for set @variable=' ', one for string ' ', one for escape charater ''. 
2. Cannot convert datetime to nvarchar. Same thing was happing when I tried to compare a date - WHERE application_date<=@RefDate.
1. I had to convert the date to nvarchar 
2. and write statement like this - WHERE application_date<='''+@RefDate+'''
3. In SSRS report, 
1. SSRS report was not showing any data set fields. 
1. SSRS - Choose Query Type: Text, do not choose stored procedure. If you choose stored procedure it will not return any columns or fields in report. In the query designer type the stored procedure name with parameter name each separated by comma. For example: exec xProcApplicationsBySchool @RefDate, @ApplicationYear 
2. In the “Stored procedure” itself –After this it was showing the fields but another problem had arisen. It was not using the query parameter. At first initialize the parameter in stored procedure then in SSRS-add the parameter manually from the parameter tab
2. Same size all the columns
1. Select both the cells (not the column), go to properties and enter the value in width. The width can be found under "Postion->Size" in properties. If for some reason you cannot see the Width/height under the Size, enter the values as "Width, height" in inches, example, "1.5in, 0.25in"
3. Set the first row, and Last row different color
1. For the first row =IIF(RowNumber("DataSet1") = 1, firstRowColor, OtherRowColor), 
2. For the last row=IIF(RowNumber("DataSet1") = CountRows("DataSet1"), lastRowColor, OtherRowColor). 
3. To combine the two=IIF(RowNumber("DataSet1") = 1, firstRowColor, IIF(RowNumber("DataSet1") = CountRows("DataSet1"), lastRowColor, OtherRowColor)). To see the properties, Click on View->Properties
4. Add the Report header page for SSRS report, not the page header page
1. Insert the header information just above the Tablix where you show your table fields value
5. Push the Tablix into the second page.
1. Go to Tablix properties-->General tab-->Check option add a page break before 
6. Add a chart, Color code the highest value
1. In order to color code the highest sum value or second highest value, adding a RANK column in the stored procedure
2. How to add a Ranking column in SSRS
1. Do it in the sql query for example stored procedure not in the SSRS. Use window aggregate function.For example ROW_NUMBER. 
2. ROW_NUMBER was not working in according order by clause 
1. Terminate the previous statement with semicolon worked
3. ROW_NUMBER tie breaker was not working
1. Both the tie breaker column has to mention – ascending or descending, for example ORDER BY SchoolTotal DESC, #PivotTable.CMPN_Company_Name ASC
3. In SSRS expression - =IIF((Fields!Rank.Value)=1,"Yellow","Silver")
4. In the chart, filter the chart data. For example show only schools with more than 10 TotalStatus
1. Select Chart (Not any chart parts)-->Chart Properties-->Filter-->Add-->Choose a field, >=, 10, 
7. After adding the RANK, GrandTotal is appearing in the middle because RANK sort the result set already by SchoolTotal, CMPN_Compmany_Name. 
1. Add the GrandTotal in the SSRS report
1. Insert Row-->Outside Group - Below
8. Add a Tool tip for the Header column
1. Select the textbox-->Properties-->General--Tool tip

SSRS Report: Cannot find stored procedure in the list

When I created the dataset, I could not see the newly created stored procedure.

When I started the stored procedure Run as a administrator then I could see all the stored procedure.

SQL Query: Pass DATETIME parameter to Dynamic SQL in Stored Procedure [Solved]

I could not pass datetime parameter to a stored procedure, where datatime parameter was being used in dynamic sql query, the lesson I have learned:

1. convert the parameter to nvarchar rather than to datetime. You should use this conversion for even integer value as well.
2. use extra single inverted comma before and after conversion of the datetime parameter to nvarchar
3. The above two steps, our target is to achieve the date like this, APPL_ApplicationDate <='27-jan-2015'. 

WHERE APPL_ApplicationDate <=  '''+CONVERT(nvarchar, @RefDate)+'''


Solution:
WHERE APPL_ApplicationDate <=  '''+CONVERT(nvarchar, @RefDate)+'''

set @SqlCommand=N'

WITH ctePivotData AS

(

       select CMPN_Company_Name, APPL_Student_Status, STUD_Student_ID       from ng.dbo.STUDstudent

       INNER JOIN ng.dbo.CMPN_Company_main

              ON STUDstudent.STUD_School_ISN=CMPN_Company_main.CMPN_ISN

       INNER JOIN ng.dbo.APPLications

              ON STUDstudent.STUD_Student_ID=APPLications.APPL_Student_ID

       WHERE APPL_ApplicationDate <=  '''+CONVERT(nvarchar, @RefDate)+'''

)

SELECT CMPN_Company_Name,' + @SpreadingList + ' INTO #PivotTable

FROM ctePivotData

PIVOT(count(STUD_Student_ID) FOR APPL_Student_Status IN (' + @SpreadingList + ') ) AS P

order by CMPN_Company_Name;

select * from #PivotTable;';

--print @SqlCommand

execute sp_executesql @SqlCommand;

SQL Query: Insert Dynamic Query Results into Temporary Table

I wanted to insert the result of the dynamic PIVOT query into a temporary table and then issue a select clause. I have faced few issues

1. If I have created #Table then I was not able to issue the select statement outside of that statement because #table is not available outside the scope of that statement
2. If I have created ##table then two user could not use that query or stored procedure at the same time.

Benefit of using Temporary table

1. You can use where condition, which you cannot use in PIVOT query

1. Create the temporary table inside the PIVOT scope
2. Issue the select statement  inside the PIVOT scope

set @SqlCommand=N'

WITH ctePivotData AS

(

       select CMPN_Company_Name, APPL_Student_Status, STUD_Student_ID, COUNT(APPL_Student_Status) OVER(PARTITION BY CMPN_Company_Name) AS SchoolTotal

       from STUDstudent

       INNER JOIN CMPN_Company_main

              ON STUDstudent.STUD_School_ISN=CMPN_Company_main.CMPN_ISN

       INNER JOIN APPLications

              ON STUDstudent.STUD_Student_ID=APPLications.APPL_Student_ID

       WHERE CMPN_Company_Name NOT LIKE ' + @Phrase1 + '

       AND APPL_Student_Status NOT IN ('''',''1'',''2'',''97'')

      

)

SELECT CMPN_Company_Name,' + @SpreadingList + ', SchoolTotal INTO #PivotTable

FROM ctePivotData

PIVOT(count(STUD_Student_ID) FOR APPL_Student_Status IN (' + @SpreadingList + ') ) AS P

select * from #PivotTable

;';

execute sp_executesql @SqlCommand;

SQL Query: Pivot with Grand Total Column and Row

SQL - Pivot with Grand Total Column and Row

Narapareddy Shyamprasad, 27 Jul 2011 CPOL

4.75 (15 votes)

Rate this: vote 1vote 2vote 3vote 4vote 5

SQL Dynamic Pivots

Introduction

Microsoft SQL Server has introduced the PIVOT and UNPIVOT commands as enhancements to T-SQL with the release of Microsoft SQL Server 2005. Pivot Table in SQL has the ability to display data in custom aggregation.PIVOT rotates a table-valued expression by turning the unique values from one column in the expression into multiple columns in the output, and performs aggregations where they are required on any remaining column values that are wanted in the final output. UNPIVOT performs the opposite operation to PIVOT by rotating columns of a table-valued expression into column values.
More information on Pivot.
In this article, I’m concentrating on how to display Grand Total row and column for the pivot table as shown in the below grid view:
Here in the grid, I’m showing the number of matches played by a team in each month. At last, I need to show the grand total for each team (Last column) - this total gives the year count for the teams. In the same way, I need a grand total row as last row in grid, which will give the number of matches played by all the teams for that particular month.

Background

Usually as a .NET developer, first I will get the pivot table from the stored procedure to dataset and grand total row and column. I will manipulate in the C#/ VB code using Datatable (Datatable compute method for better performance). But in this article, I want to show how to get the grand total row and column from the stored procedure, so that we can directly display data in the grid without any manipulation in the C#/VB.

How It Works

Here I’m using pivot with dynamic columns, most questions were about the column list in the PIVOT statement. This list is fixed, but many times the new columns are determined by the report at a later stage. This problem is easily solved when we mix pivots with dynamic SQL, so here is a very simple script about how to dynamically generate the pivot statement:

 Collapse | Copy Code

DECLARE @cols NVARCHAR (MAX)
SELECT @cols = COALESCE (@cols + ',[' + colName + ']', '[' + colName + ']')
FROM    Table1
WHERE Conditions
ORDER BY colName

PRINT @cols

The above script gives you the list of columns in string format with commas. Example: [Jan], [Feb], [Mar], [Apr], [May], [Jun], [Jul], [Aug], [Sept], [Oct], [Nov], [Dec]. If you pass this dynamic query to your pivot, then your pivot columns will display dynamically.
The below SQL script creates the stored procedure which returns pivot table as output.

 Collapse | Copy Code

CREATE PROCEDURE pivot_TeamVsMatches
AS

/*First get the dynamic columns query*/

DECLARE @columnHeaders NVARCHAR (MAX)
SELECT @columnHeaders  = _
 COALESCE (@columnHeaders  + ',[' + month + ']', '[' + month + ']')
FROM    tbl_Matches
ORDER BY month

DECLARE @FinalQuery NVARCHAR(MAX)

SET @FinalQuery =  ‘SELECT *
   FROM
    (SELECT Team,
        Month
    FROM tbl_Matches
    ) A
   PIVOT
    (
     COUNT(*)
     FOR ColName
     IN (‘+@columnHeaders +’)
    ) B
ORDER BY Team’
PRINT ‘Pivot Queuery :’+ @FinalQuery
EXECUTE (@FinalQuery)
GO

This stored procedure returns pivot table as below:
Now to get the grand total and row, we need to form the COALESCE query. As shown below…

 Collapse | Copy Code

/* GRAND TOTAL COLUMN */
DECLARE @GrandTotalCol NVARCHAR (MAX)
SELECT @GrandTotalCol = COALESCE (@GrandTotalCol + ‘ISNULL _
([' + CAST (Month AS VARCHAR) +'],0) + ', 'ISNULL([' + CAST(Month AS VARCHAR)+ '],0) + ')
FROM tbl_Matches ORDER BY  Month
SET @GrandTotalCol = LEFT (@GrandTotalCol, LEN (@GrandTotalCol)-1)

The above query returns as below…

 Collapse | Copy Code

@GrandTotalCol = ISNULL ([' + CAST (Jan AS VARCHAR) +'],0) + ISNULL _
([' + CAST (Feb AS VARCHAR) +'],0) + ISNULL ([' + CAST (March AS VARCHAR) +'],0) + _
…………. + ISNULL ([' + CAST (Dec AS VARCHAR) +'],0).
/* GRAND TOTAL ROW */
DECLARE @GrandTotalRow NVARCHAR(MAX)
SELECT @GrandTotalRow = COALESCE(@GrandTotalRow + ',ISNULL(SUM([' + _
CAST(Month AS VARCHAR)+']),0)', 'ISNULL(SUM([' + CAST(Month AS VARCHAR)+']),0)')
FROM tbl_Matches ORDER BY  Month

The above query returns as below…

 Collapse | Copy Code

@GrandTotalRow = ISNULL(SUM([' + CAST(Jan AS VARCHAR)+']),0) + _
ISNULL(SUM([' + CAST(Feb AS VARCHAR)+']),0) + ……… + _
ISNULL(SUM([' + CAST(Dec AS VARCHAR)+']),0).

The above COALESCE strings need to be used in dynamic pivot query…
Below is the stored procedure which will give the total output of our requirement.

 Collapse | Copy Code

CREATE PROCEDURE pivot_TeamVsMatches
AS
/* COLUMNS HEADERS */
DECLARE @columnHeaders NVARCHAR (MAX)
SELECT @columnHeaders  = COALESCE (@columnHeaders  _
+ ',[' + month + ']', '[' + month + ']')
FROM    tbl_Matches
ORDER BY month

/* GRAND TOTAL COLUMN */
DECLARE @GrandTotalCol NVARCHAR (MAX)
SELECT @GrandTotalCol = COALESCE (@GrandTotalCol + ‘ISNULL ([' + _
CAST (Month AS VARCHAR) +'],0) + ', 'ISNULL([' + CAST(Month AS VARCHAR)+ '],0) + ')
FROM tbl_Matches ORDER BY Month
SET @GrandTotalCol = LEFT (@GrandTotalCol, LEN (@GrandTotalCol)-1)

/* GRAND TOTAL ROW */
DECLARE @GrandTotalRow NVARCHAR(MAX)
SELECT @GrandTotalRow = COALESCE(@GrandTotalRow + ',ISNULL(SUM([' + _
CAST(Month AS VARCHAR)+']),0)', 'ISNULL(SUM([' + CAST(Month AS VARCHAR)+']),0)')
FROM tbl_Matches ORDER BY  Month

/* MAIN QUERY */
DECLARE @FinalQuery NVARCHAR (MAX)
SET @FinalQuery =  ‘SELECT *, ('+ @GrandTotalCol + ') _
AS [Grand Total] INTO #temp_MatchesTotal
   FROM
    (SELECT Team,
        Month
    FROM tbl_Matches
    ) A
   PIVOT
    (
     COUNT (*)
     FOR ColName
     IN (‘+@columnHeaders +’)
    ) B
ORDER BY Team
SELECT * FROM #temp_MatchesTotal UNION ALL
SELECT ''Grand Total'','''','+@GrandTotalRow +', _
ISNULL (SUM([Grand Total]),0) FROM #temp_MatchesTotal
DROP TABLE #temp_MatchesTotal'
-- PRINT 'Pivot Query '+@FinalQuery
EXECUTE(@PivotQuery)
GO

Result as below…
Here in this stored procedure, I have used temporary table to get the grand total row. I did the UNION ALL with temporary table; don’t forget to drop the temporary table.
For any queries & suggestions, mail me @ narapareddy.shyam@gmail.com. By using some third party controls also we can achieve this, but here I concentrated only on typical/ normal SQL query. If anybody has any efficient and different ways, kindly share with me.
Thanks again. Bye.

Once again thanks a lot to Mr.shyamprasad for this wonderful article.It helped me lot. I have seen lot of peoples asked for db script and details .Here is the sample I have done with help of this article. DB SCRIPT ------------------ CREATE TABLE [dbo].[TestPivot]( [Team] [nvarchar](50) NULL, [month] [nvarchar](50) NULL, [rank] [int] NULL ) ON [PRIMARY]   GO INSERT [dbo].[TestPivot] ([Team], [month], [rank]) VALUES (N'INDIA', N'JAN', 10) INSERT [dbo].[TestPivot] ([Team], [month], [rank]) VALUES (N'INDIA', N'FEB', 20) INSERT [dbo].[TestPivot] ([Team], [month], [rank]) VALUES (N'INDIA', N'MAR', 30) INSERT [dbo].[TestPivot] ([Team], [month], [rank]) VALUES (N'INDIA', N'APR', 20) INSERT [dbo].[TestPivot] ([Team], [month], [rank]) VALUES (N'INDIA', N'MAY', 10) INSERT [dbo].[TestPivot] ([Team], [month], [rank]) VALUES (N'INDIA', N'JUN', 20) INSERT [dbo].[TestPivot] ([Team], [month], [rank]) VALUES (N'JAPAN', N'JAN', 10) INSERT [dbo].[TestPivot] ([Team], [month], [rank]) VALUES (N'JAPAN', N'MAY', 52) INSERT [dbo].[TestPivot] ([Team], [month], [rank]) VALUES (N'JAPAN', N'JUN', 52) INSERT [dbo].[TestPivot] ([Team], [month], [rank]) VALUES (N'JAPAN', N'APR', 10) INSERT [dbo].[TestPivot] ([Team], [month], [rank]) VALUES (N'AMERICA', N'JAN', 50) INSERT [dbo].[TestPivot] ([Team], [month], [rank]) VALUES (N'AMERICA', N'FEB', 20) INSERT [dbo].[TestPivot] ([Team], [month], [rank]) VALUES (N'AMERICA', N'MAR', 12) INSERT [dbo].[TestPivot] ([Team], [month], [rank]) VALUES (N'AMERICA', N'MAY', 5) INSERT [dbo].[TestPivot] ([Team], [month], [rank]) VALUES (N'AMERICA', N'JUN', 34) INSERT [dbo].[TestPivot] ([Team], [month], [rank]) VALUES (N'JERMANY', N'JUN', 23) INSERT [dbo].[TestPivot] ([Team], [month], [rank]) VALUES (N'JERMANY', N'JAN', 10) And SP ------------ ALTER PROCEDURE pivot_TeamVsMatches3 AS /* COLUMN HEADERS*/ DECLARE @columnHeaders NVARCHAR (MAX) SELECT @columnHeaders = COALESCE ( (@columnHeaders) + ',[' + month + ']', '[' + month + ']') FROM (Select Distinct month from TestPivot) as PIVT Print @columnHeaders /* GRAND TOTAL COLUMN */ DECLARE @GrandTotalCol NVARCHAR (MAX) SELECT @GrandTotalCol = COALESCE (@GrandTotalCol + 'ISNULL ([' + CAST (month AS VARCHAR) +'],0) + ', 'ISNULL([' + CAST(month AS VARCHAR)+ '],0) + ') FROM (Select Distinct month from TestPivot) as PIVT ORDER BY month SET @GrandTotalCol = LEFT (@GrandTotalCol, LEN (@GrandTotalCol)-1) /* GRAND TOTAL ROW */ DECLARE @GrandTotalRow NVARCHAR(MAX) SELECT @GrandTotalRow = COALESCE(@GrandTotalRow + ',ISNULL(SUM([' + CAST(month AS VARCHAR)+']),0)', 'ISNULL(SUM([' + CAST(month AS VARCHAR)+']),0)') FROM (Select Distinct month from TestPivot) as PIVT ORDER BY month /* MAIN QUERY */ DECLARE @FinalQuery NVARCHAR (MAX) SET @FinalQuery = 'SELECT *, ('+ @GrandTotalCol + ') AS [GRANT TOTAL] INTO #temp_MatchesTotal FROM (SELECT Team,month,rank FROM TestPivot) A   PIVOT ( SUM(rank) FOR month IN (' +@columnHeaders +') ) B ORDER BY Team   SELECT * FROM #temp_MatchesTotal UNION ALL SELECT ''GRANT TOTAL'', '+@GrandTotalRow +',ISNULL (SUM([GRANT TOTAL]),0) FROM #temp_MatchesTotal DROP TABLE #temp_MatchesTotal' PRINT 'Pivot Query '+@FinalQuery EXECUTE(@FinalQuery) GO EXEC pivot_TeamVsMatches3