TechBrothersIT is the blog spot and a video (Youtube) Channel to learn and share Information, scenarios, real time examples about SQL Server, Transact-SQL (TSQL), SQL Server Database Administration (SQL DBA), Business Intelligence (BI), SQL Server Integration Services (SSIS), SQL Server Reporting Services (SSRS), Data Warehouse (DWH) Concepts, Microsoft Dynamics AX, Microsoft Dynamics Lifecycle Services and all other different Microsoft Technologies.
In this video of SSRS Tutorial series, we are going to learn How to Install Report Builder 3.0 and Report builder for SQL Server 2016 Reporting Services.
We will learn
How to download Report Builder directory from Microsoft Website and Install
How to download/install Report Builder from Report Manager
How to download and Install Report Builder - SQL Server Reporting Services(SSRS) Tutorial
In this video of SSRS Tutorial Series , we are going to learn How to deploy Data Source and Reports to Reporting Server by using Report Manager Web Interface.
While deploying report/s and Data Sources to Report Server by using Report Manager, we will learn
How to locate report file/s and Data Source Files in Solution on Local Drive
How to open Report Manager so we can upload Report and Data Source
Upload Report and Data Source, Configure Report to use Data Source after deployment
Preview Report in Report Manager
How to Upload Report/Data Source by using Report Manager - SQL Server Reporting Services(SSRS) Tutorial
In this video of SSRS Tutorial series, we are going to learn how to create different folders for Data Sources, Data Set and Business Types by using Report Manager.
We will learn below items in this video
How to find the Report URL which is installed on machine
How to open Report Server and Report Manager Web App in IE, Chrome and FireFox
How to create Different Folders by using Report Manager Web Interface
How to Delete Folders or Rename existing Once by using Report Manager Web Interface
How to move folder/s to Another Folder or Out of Folder
How to Hide folder/s by using Report Manager Web Interface
How to create difference folders by using Report Manager Web Interface- SQL Server Reporting Services(SSRS) Tutorial
In this video of SSRS Tutorial, we are going to learn How to use Filters in SSRS Report.
While performing this demo, we will learn following items
How to create DataSet for SSRS Report
How to use Table Item in SSRS Report to create Tabular report
How to get to Filter Property and Explore different options such as
Top N Row
Bottom N Row
In Operation in SSRS Filter
Like Operation in SSRS Report
Other Operators such as =, <>,>= etc.
How to use Filters in SSRS Report- SQL Server Reporting Services(SSRS) Tutorial
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.
CREATEPROCEDURE dbo.Sp_TotalSale
ASBEGINIF OBJECT_ID('tempdb..##Temp') ISNOTNULLDROPTABLE ##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
In this video of SSRS Series, we are going to work on Cascade Parameter. I got this question from one of the visitor.
here is the scenario
He would like to provide facility to his users to choose the Column name from the Drop down( Parameter). Once the Column name is chosen then Next parameter should show values available in the table for that column ( Cascaded Parameter). The user should be able to choose multiple values if he/she like. Once user choose the values for parameter, He/She should be able to see the results as per values provided in the parameters.
Let's create the Main Query with two Parameters
-- Main Query CREATEPROCEDURE dbo.MainQuery
@ColumnName VARCHAR(100),
@ValuesVARCHAR(MAX)
ASBEGINIF OBJECT_ID('tempdb..##Temp') ISNOTNULLDROPTABLE ##Temp
DECLARE @Val VARCHAR(MAX)
SET @Val=Replace(@Values,',',''',''')
DECLARE @SQL NVARCHAR(MAX)
SET @SQL='Select SalepersonFName,City,State,Country,Region into ##Temp from dbo.TotalSale where '+@ColumnName+'
in ('''+@Val+''')'PRINT @SQLEXECUTE (@SQL)
SELECT * FROM ##Temp
END--Test your SP for Main QueryEXEC dbo.MainQuery 'Country','Pakistan,India'
Stored Procedure for the Values Parameter ( Cascaded Parameter)
-- Multi Value Parameter CreatePROCEDURE dbo.ColValues
@ColumnName VARCHAR(100)
ASBEGINIF OBJECT_ID('tempdb..##TempValues') ISNOTNULLDROPTABLE ##TempValues
DECLARE @SQL NVARCHAR(100)
SET @SQL='Select '+@ColumnName+' As val into ##TempValues from dbo.TotalSale'EXECUTE (@SQL)
SELECTDISTINCT * FROM ##TempValues
END--Test your SP for Multiple valuesEXEC dbo.ColValues 'Country'
Query for ColumnName parameter
--Column Name Parameter QuerySELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_Name='TotalSale'
What we will learn while creating this video for Cascade Parameter report by using Stored Procedures
How to create Stored Procedures with Dynamic SQL
How to use Stored Procedure in SSRS for DataSet
How to use Stored Procedure for Parameters
How to create Cascading Parameter in SSRS Report
How to create Cascade Parameters in SSRS Report - SQL Server Reporting Services(SSRS) Tutorial
In this video of SSRS Tutorial Series, you will learn How to Sort the Results by using Parameters in SSRS Report
You will also learn
How to create static Parameters in SSRS Report for ColumnName and OrderBy
How to write expressions on Tablix to use the Parameters to Sort the Result set for Report
How to use IIF in SSRS Report
How to Sort the Data Ascending and Descending in SSRS Report
How to Sort Dynamically in SSRS Report by using Parameters - SQL Server Reporting Services(SSRS) Tutorial
