In this video of SSRS Tutorial, we are going to create a report in which the number of columns can change anytime. This report that we want to create should be Tabular report ( Detail Report).
The problem with the scenario is , we are using a table/view in which we can add or drop columns anytime. If we create simple tabular report , the problem is that every time our Table/View Definition change, we have to alter our report definition and redeploy.
In this video, we are going to learn some tricks, How to create report with Dynamic Columns. Here are the steps involved
We will create our SQL queries with Unpivot, Convert the columns to the Rows
Use Matrix Item in SSRS Report, so the dynamic columns can be handle dynamically
Use the Primary Key or ID as part of Rows so we have detail level data
Delete the Row level Column to hide the Primary Key
Change the definition of Table/View and check if everything working for our Dynamic Column SSRS Report
here are the queries, those we will use in our report.
How to create SSRS Report with Dynamic Columns- SQL Server Reporting Services(SSRS) Tutorial
Check out related Posts / Videos in this Chapter
The problem with the scenario is , we are using a table/view in which we can add or drop columns anytime. If we create simple tabular report , the problem is that every time our Table/View Definition change, we have to alter our report definition and redeploy.
In this video, we are going to learn some tricks, How to create report with Dynamic Columns. Here are the steps involved
We will create our SQL queries with Unpivot, Convert the columns to the Rows
Use Matrix Item in SSRS Report, so the dynamic columns can be handle dynamically
Use the Primary Key or ID as part of Rows so we have detail level data
Delete the Row level Column to hide the Primary Key
Change the definition of Table/View and check if everything working for our Dynamic Column SSRS Report
here are the queries, those we will use in our report.
CREATE PROCEDURE dbo.Sp_TotalSale AS BEGIN IF OBJECT_ID('tempdb..##Temp') IS NOT NULL DROP TABLE ##Temp; DECLARE @ObjectName VARCHAR(100) = 'vw_TotalSale' , @KeyColumn VARCHAR(100) = 'id'; DECLARE @ColumnNames NVARCHAR(MAX)= '' , @Values NVARCHAR(MAX)= '' , @SQL NVARCHAR(MAX)= ''; SELECT @ColumnNames += ', ' + QUOTENAME(COLUMN_NAME) , @Values += ', ' + QUOTENAME(COLUMN_NAME) + ' = CONVERT(VARCHAR(100), ' + QUOTENAME(COLUMN_NAME) + ')' FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = @ObjectName AND COLUMN_NAME <> @KeyColumn; SET @SQL = N'Select * into ##Temp FROM ( SELECT ' + @KeyColumn + @Values + ' FROM ' + @ObjectName + ' ) AS DRV UNPIVOT ( Value FOR ColumnName IN (' + STUFF(@ColumnNames, 1, 1, '') + ') ) AS UnPVT;'; EXEC sp_executesql @SQL; SELECT * FROM ##Temp; END;
Check out related Posts / Videos in this Chapter
- How to create Matrix Report in SSRS
- How to use Sub Report Report Item
- How to Create Column Chart in SSRS Report
- Highlight Columns in Chart for Max and Min Values in SSRS Report
- How to Create Stacked Chart Report in SSRS
- How to Display Total on top of Stacked Chart in SSRS Report
- How to Combine Column Chart with Line Chart in SSRS Report
- How to Plot multiple lines on Same Graph from Same Dataset in SSRS Report
- How to Custom Color Columns in Column Chart in SSRS Report
- How to Create Pie Chart in SSRS Report
- How to Display Percentage in Pie Chart in SSRS Report
- How to Change Data Labels Positions in SSRS Report
- How to Show Data Labels in Tooltip on hover in SSRS Report
- How to Change the Width of Columns/Bars in SSRS Report
- How to Create Bar Chart in SSRS Report
- How to Create Bar Stacked Chart in SSRS Report
- How to Create Area Chart in SSRS Report
- How to Create Scatter Chart in SSRS Report
- How to Create 100% Stacked Column / Bar Chart in SSRS Report
- How to Create Line Chart in SSRS Report
- How to use Data Bar in SSRS Report
- How to use Spark-line in SSRS Report
- How to make use of Indicator in SSRS Report
Hello brother. Your video is really helpful. Thanks for the knowledge you are sharing with us.
ReplyDeleteI have a query brother if you can help me out here.
I have a web project. ASP.NET Webforms.
I have a table called employees. Now i want to add a functionality where a user select employee from dropdownlist and then that employee record should loads from database. and then the user select columnof their need and print only that columns. e.g if a user select employee name 'AFFAQ' then the whole record of affaq loads from data which has 20 columns i-e name, fatherName,CNIC,Contact,Email,address and so on..but the user only want to select the name,contact and cnic and want to print it on rdlc report. i hope you understand my question. please help me brother
Thanks It does work. However my data do have Dates and cannot format those values as dates on the SSRS report.
ReplyDeleteSo the end client cannot use filter on dates fields which is vital for my report. any advise on formatting?
i didn't get detaset value
ReplyDeleteI created a dynamically scripted query that I then put into a temp table and used the above to display it in SSRS. It worked great, except I don't seem to have any control over the sorting of the columns (they're alphabical instead of the order they're in the temp table) or the rows. Is there a way to control this?
ReplyDeleteHi I got a problem with dynamic query that the columns are dynamic I dont know how to give the values to the ssrs table.
ReplyDeleteMy stored procedure is about the count of exceptions from past 10 days from the given date and the same date last year.
here is my code which gives the current data what I required but the problem is how can i convert that into report.
Exceptions 3/10/2021 3/10/2022 3/9/2022 3/8/2022 3/7/2022 3/4/2022 3/3/2022 3/2/2022 3/1/2022 2/28/2022 2/25/2022
053 - INVALID PERIOD END DATE 0 11 11 11 11 11 11 11 11 11 11
ENTITY TYPE MISMATCH 0 0 0 0 0 0 0 0 0 0 0
INVALID CRITICAL DETAIL RECORD 0 0 0 0 0 0 0 0 0 0 0
UNKNOWN DOCUMENT TRANSACTION TYPE CODE 0 0 0 0 0 0 0 0 0 0 0
REFUND REQUESTED ON AMENDED RETURN 0 0 0 0 0 0 0 0 0 0 0
DeleteDECLARE @date DATE=null
set @Date = coalesce( @Date , getdate() )
DECLARE @Date0 DATE =[its].[custom_fnGetNextWorkdayByCalendar](@Date, 0, default, default, default)
DECLARE @Date1 DATE =[its].[custom_fnGetNextWorkdayByCalendar](@Date, -1, default, default, default)
DECLARE @Date2 DATE =[its].[custom_fnGetNextWorkdayByCalendar](@Date, -2, default, default, default)
DECLARE @Date3 DATE =[its].[custom_fnGetNextWorkdayByCalendar](@Date, -3, default, default, default)
DECLARE @Date4 DATE =[its].[custom_fnGetNextWorkdayByCalendar](@Date, -4, default, default, default)
DECLARE @Date5 DATE =[its].[custom_fnGetNextWorkdayByCalendar](@Date, -5, default, default, default)
DECLARE @Date6 DATE =[its].[custom_fnGetNextWorkdayByCalendar](@Date, -6, default, default, default)
DECLARE @Date7 DATE =[its].[custom_fnGetNextWorkdayByCalendar](@Date, -7, default, default, default)
DECLARE @Date8 DATE =[its].[custom_fnGetNextWorkdayByCalendar](@Date, -8, default, default, default)
DECLARE @Date9 DATE =[its].[custom_fnGetNextWorkdayByCalendar](@Date, -9, default, default, default)
DECLARE @Date10 DATE =[its].[custom_fnGetNextWorkdayByCalendar](DATEADD(yy,-1,@Date), 0, default, default, default)
DECLARE @SQLString NVARCHAR(MAX);
ReplyDeleteSET @SQLString = N'SELECT r.desc_short as Exceptions,
count(case when cast(e.created_dttm as date) < ''' + Convert(Varchar(10), @Date10, 101) +''' and (closed_dt is null or cast(closed_dt as date) >=''' + Convert(Varchar(10), @Date10, 101) +'''
) then e.exception_type_key end) as [' + CONVERT(VARCHAR(10),@Date10,101) + '],
count(case when e.exception_status_key = 1 then e.exception_type_key end) as [' + CONVERT(VARCHAR(10),@Date0,101) + '],
count(case when cast(e.created_dttm as date) < ''' + Convert(Varchar(10), @Date1, 101) +''' and (closed_dt is null or cast(closed_dt as date) >=''' + Convert(Varchar(10), @Date1, 101) +'''
) then e.exception_type_key end) as [' + CONVERT(VARCHAR(10),@Date1,101) + '],
count(case when cast(e.created_dttm as date) < ''' + Convert(Varchar(10), @Date2, 101) +''' and (closed_dt is null or cast(closed_dt as date) >=''' + Convert(Varchar(10), @Date2, 101) +'''
) then e.exception_type_key end) as [' + CONVERT(VARCHAR(10),@Date2,101) + '],
count(case when cast(e.created_dttm as date) < ''' + Convert(Varchar(10), @Date3, 101) +''' and (closed_dt is null or cast(closed_dt as date) >=''' + Convert(Varchar(10), @Date3, 101) +'''
) then e.exception_type_key end) as [' + CONVERT(VARCHAR(10),@Date3,101) + '],
count(case when cast(e.created_dttm as date) < ''' + Convert(Varchar(10), @Date4, 101) +''' and (closed_dt is null or cast(closed_dt as date) >=''' + Convert(Varchar(10), @Date4, 101) +'''
) then e.exception_type_key end) as [' + CONVERT(VARCHAR(10),@Date4,101) + '],
count(case when cast(e.created_dttm as date) < ''' + Convert(Varchar(10), @Date5, 101) +''' and (closed_dt is null or cast(closed_dt as date) >=''' + Convert(Varchar(10), @Date5, 101) +'''
) then e.exception_type_key end) as [' + CONVERT(VARCHAR(10),@Date5,101) + '],
count(case when cast(e.created_dttm as date) < ''' + Convert(Varchar(10), @Date6, 101) +''' and (closed_dt is null or cast(closed_dt as date) >=''' + Convert(Varchar(10), @Date6, 101) +'''
) then e.exception_type_key end) as [' + CONVERT(VARCHAR(10),@Date6,101) + '],
count(case when cast(e.created_dttm as date) < ''' + Convert(Varchar(10), @Date7, 101) +''' and (closed_dt is null or cast(closed_dt as date) >=''' + Convert(Varchar(10), @Date7, 101) +'''
) then e.exception_type_key end) as [' + CONVERT(VARCHAR(10),@Date7,101) + '],
count(case when cast(e.created_dttm as date) < ''' + Convert(Varchar(10), @Date8, 101) +''' and (closed_dt is null or cast(closed_dt as date) >=''' + Convert(Varchar(10), @Date8, 101) +'''
) then e.exception_type_key end) as [' + CONVERT(VARCHAR(10),@Date8,101) + '],
count(case when cast(e.created_dttm as date) < ''' + Convert(Varchar(10), @Date9, 101) +''' and (closed_dt is null or cast(closed_dt as date) >=''' + Convert(Varchar(10), @Date9, 101) +'''
) then e.exception_type_key end) as [' + CONVERT(VARCHAR(10),@Date9,101) + ']
from r_exception_type r
left join its_exception e on e.exception_type_key = r.exception_type_key
where active_flag =1
group by desc_short';