Welcome To TechBrothersIT: SSRS Tutorial 90- How to Create SSRS Report When Number of Columns Can change Anytime in Source Object

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.

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;

How to create SSRS Report with Dynamic Columns- SQL Server Reporting Services(SSRS) Tutorial

Check out related Posts / Videos in this Chapter

2 comments:

UnknownFebruary 13, 2019 at 7:55 AM
Hello brother. Your video is really helpful. Thanks for the knowledge you are sharing with us.
I 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
UnknownApril 9, 2019 at 8:11 AM
Thanks It does work. However my data do have Dates and cannot format those values as dates on the SSRS report.

So the end client cannot use filter on dates fields which is vital for my report. any advise on formatting?

Note: Only a member of this blog may post a comment.

Label

SSRS Tutorial 90- How to Create SSRS Report When Number of Columns Can change Anytime in Source Object

2 comments: