SSRS - No data message for report items

This article is about handling display of report items like chart , table or matrix when there is no data
returned from the dataset .

For example :

Consider below query is my dataset :

DECLARE @SalesbyMonth TABLE (Months VARCHAR(20),Sales INT,MonthNo INT)
INSERT @SalesbyMonth SELECT 'January',1000,1
INSERT @SalesbyMonth SELECT 'February',100,2
INSERT @SalesbyMonth SELECT 'March',100,3
INSERT @SalesbyMonth SELECT 'April',200,4
INSERT @SalesbyMonth SELECT 'May',500,5
INSERT @SalesbyMonth SELECT 'June',800,6
INSERT @SalesbyMonth SELECT 'July',500,7
INSERT @SalesbyMonth SELECT 'August',100,8
INSERT @SalesbyMonth SELECT 'September',900,9
INSERT @SalesbyMonth SELECT 'October',600,110
INSERT @SalesbyMonth SELECT 'November',100,11
INSERT @SalesbyMonth SELECT 'December',100,12
SELECT * FROM @SalesbyMonth
WHERE MonthNo = @MonthNo

I have table & column chart on the report body , when I preview the report and enter MonthNo =12 and click on View report

SSRS - How to display table after clicking on chart

If we have a requirement to display table only after clicking on chart then follow below steps :

Consider below query is my dataset :

DECLARE @SalesbyMonth TABLE (Months VARCHAR(20),Sales INT,MonthNo INT)
INSERT @SalesbyMonth SELECT 'January',1000,1
INSERT @SalesbyMonth SELECT 'February',100,2
INSERT @SalesbyMonth SELECT 'March',100,3
INSERT @SalesbyMonth SELECT 'April',200,4
INSERT @SalesbyMonth SELECT 'May',500,5
INSERT @SalesbyMonth SELECT 'June',800,6
INSERT @SalesbyMonth SELECT 'July',500,7
INSERT @SalesbyMonth SELECT 'August',100,8
INSERT @SalesbyMonth SELECT 'September',900,9
INSERT @SalesbyMonth SELECT 'October',600,110
INSERT @SalesbyMonth SELECT 'November',100,11
INSERT @SalesbyMonth SELECT 'December',100,12
SELECT * FROM @SalesbyMonth

I have Column chart and table on the report body as shown below :




To display all labels on the X-axis (Horizontal axis) 

To sort labels on X - axis (Horizontal axis) 

Parameter settings is as shown in below images :

SSRS - How to repeat headers on each page

Refer my post on Technet wiki - http://social.technet.microsoft.com/wiki/contents/articles/19398.ssrs-how-to-repeat-headers-on-each-page.aspx

T-SQL Script for inserting rows repeatedly based on particular count

Refer my post on Technet gallery - http://gallery.technet.microsoft.com/scriptcenter/T-SQL-Script-for-inserting-f36b3dc1

SSRS - How to display multiple columns horizontally stacked in one row cell

Refer my post on Technet Wiki - http://social.technet.microsoft.com/wiki/contents/articles/19363.ssrs-how-to-display-multiple-columns-header-and-value-horizontally-stacked-in-one-row-cell.aspx

SSRS - How to set Column Visibility property for many columns based on Parameter value

Refer my post on Technet Wiki -  http://social.technet.microsoft.com/wiki/contents/articles/19333.ssrs-how-to-set-column-visibility-property-for-many-columns-based-on-parameter-value.aspx

SSIS - Extract filename from file path using TOKEN and TOKENCOUNT

TOKEN and TOKENCOUNT are new string functions introduced from SSIS 2012 .


As mentioned in MSDN :

TOKEN - Returns a token (substring) from a string based on the specified delimiters that separate tokens in the string and the number of the token that denotes which token to be returned. 

Syntax : 

TOKEN(character_expression, delimiter_string, occurrence)

TOKENCOUNT - Returns the number of tokens in a string that contains tokens 

separated by the specified delimiters. 

Syntax : 

TOKENCOUNT(character_expression, delimiter_string)

Example : (To  extract filename from file path using TOKEN and TOKENCOUNT )

Below expression returns 3 , because delimiter_string "\\" splits the character_expression 
"G:\\GAMES\\fifa.exe" into three parts .

TOKENCOUNT("G:\\GAMES\\fifa.exe", "\\")

  
Below expression returns fifa.exe , because  delimiter_string "\\" splits the character_expression "G:\\GAMES\\fifa.exe" into three parts, Occurrence is specified as 3, so the third token in the string is returned.

TOKEN("G:\\GAMES\\fifa.exe", "\\",3)




 To  extract filename from file path using TOKEN and TOKENCOUNT 

TOKEN("G:\\GAMES\\fifa.exe", "\\", TOKENCOUNT("G:\\GAMES\\fifa.exe", "\\"))


 

For more info & examples :

Token - http://technet.microsoft.com/en-us/library/hh213216.aspx 
TokenCount - http://technet.microsoft.com/en-us/library/hh213135.aspx

How to check the syntax of dynamic SQL before execution

Refer my post on Technet Gallery - http://gallery.technet.microsoft.com/scriptcenter/T-SQL-Script-to-check-the-935002e7

T-SQL Script to compare two tables definition / metadata in different databases

Refer my post on Technet Gallery - http://gallery.technet.microsoft.com/scriptcenter/T-SQL-Script-to-compare-aaae99a7

SSRS - To group same row data with one column having varying data

Refer my post on Technet Wiki - http://social.technet.microsoft.com/wiki/contents/articles/19044.ssrs-to-group-same-row-data-with-one-column-having-varying-data.aspx

SSRS - How to add variables

To add Variables in SSRS reports :

Goto Report - > Report Properties ... -> Variables

or right-click outside the body of the report - > Report Properties ...  -> Variables

 We can assign any value or write expression for the value of the variable and can use that
variable throughout the report

 

SSRS - Example for Conditional formatting

In this post - http://www.allaboutmssql.com/2013/08/ssrs-example-for-tablix-with-sparkline.html
Under section TablewithIndicators , Indicators color are displayed based on Indicator column value .
In similar way we can do conditional formatting on the report .

SSRS - How to add Custom Code and example for using Custom Code

To add Custom code in SSRS reports :

Goto Report - > Report Properties ... -> Code

or right-click outside the body of the report - > Report Properties ...  -> Code



Consider this is my Dataset for this example :

SSRS - Example for Tablix with Sparkline / Bar Chart / Indicator

This article is about an example for creating table with
i) Sparkline
ii) Bar Chart
iii) Indicator

Create Data source and then Datasets

Dataset name : TablixWithGraph

Dataset Query : 

DECLARE @FY TABLE (Products VARCHAR(20),Q1 INT,Q2 INT,Q3 INT)
INSERT @FY SELECT 'SQLServer2008',1000,200,100
INSERT @FY SELECT 'SQLServer2012',1000,1000,1500
INSERT @FY SELECT 'SQLServer2014',1000,3000,5000

SELECT * FROM (
SELECT Products,Q1,Q2,Q3,Q1 AS Q11,Q2 AS Q22,Q3 AS Q33 FROM @FY) tmp
UNPIVOT
(QData FOR Qs IN (Q11,Q22,Q33)
) AS Unpvt

Dataset name : TablixWithIndicators

Dataset Query :

SSRS - Example for Lookup , LookUpSet and MultiLookup functions

In this article, let us see examples for Lookup, LookUpSet and MultiLookup functions  .

As mentioned in MSDN:

Lookup  -
Returns the first matching value for the specified name from a dataset that contains name/value pairs.

LookUpSet   -
Returns the set of matching values for the specified name from a dataset that contains name/value pairs.

MultiLookup -
Returns the set of first-match values for the specified set of names from a dataset that contains name/value pairs.

Examples:

Create Data source and then Datasets

Dataset name : LookUp_Parameter

Dataset Query :

DECLARE  @Country_Master TABLE(Id INT,Country_Code VARCHAR(10),Country_Name VARCHAR(10))
INSERT INTO @Country_Master SELECT 1,'IND','INDIA'
INSERT INTO @Country_Master SELECT 2,'US','USA'
INSERT INTO @Country_Master SELECT 3,'CN','CHINA'
SELECT * FROM @Country_Master

SSRS - Stacked Column (bar) Chart

This article is about an example for creating  Stacked column chart .

Step 1: Create Data source and then create Dataset with below query :

--Sample data
DECLARE @SalaryByGrade TABLE (Grade VARCHAR(20),Quarters CHAR(2),Salary INT)
INSERT @SalaryByGrade SELECT 'Senior Manager','Q1',1000
INSERT @SalaryByGrade SELECT 'Senior Manager','Q2',1000
INSERT @SalaryByGrade SELECT 'Senior Manager','Q3',2000
INSERT @SalaryByGrade SELECT 'Manager','Q1',500
INSERT @SalaryByGrade SELECT 'Manager','Q2',500
INSERT @SalaryByGrade SELECT 'Manager','Q3',1000
INSERT @SalaryByGrade SELECT 'Programmer','Q1',200
INSERT @SalaryByGrade SELECT 'Programmer','Q2',200
INSERT @SalaryByGrade SELECT 'Programmer','Q3',200
SELECT * FROM @SalaryByGrade

Step 2 :
Drag&drop Chart from Toolbox or right-click on report body - > Insert - > Chart .
Add a Stacked Column chart :

 Step 3 :  Double - click on the chart area , you will notice Chart Data ,
 Under Chart Data for summation Values add Salary column from Dataset and
then for Category Groups add Quarters column from the Dataset and
for Series Groups add Grade column from the Dataset as shown in below image :

Click on Preview Pane :

To adjust the size of bars in the chart - http://www.allaboutmssql.com/2013/06/ssrs-in-bar-charts-how-to-adjust-size.html

SSRS - Chart with two Vertical (Y) axes - Primary and secondary Vertical axes

This article is about an example for creating Chart with two Vertical (Y) axes - Primary and secondary Vertical axes .

Step 1: Create Data source and then create Dataset with below query :

--Sample data
DECLARE @Tmp TABLE (Projects VARCHAR(20),SQLServer2008 INT,SQLServer2012 INT)
INSERT @Tmp SELECT 'P101',100,1000
INSERT @Tmp SELECT 'P102',500,1500
INSERT @Tmp SELECT 'P103',1000,200
SELECT * FROM @Tmp

Step 2 :
Drag&drop Chart from Toolbox or right-click on report body - > Insert - > Chart .
Add a Line chart :

Step 3 :  Double - click on the chart area , you will notice Chart Data , Under Chart Data
for summation Values add SQLServer2008 and SQLServer2012 columns from Dataset and then for Category Groups add Projects column from the Dataset .

To add Secondary vertical axis :

Step 4 : Under Chart Data , right-click on SQLServer2012 (second summation value) - >  Series Properties ... - > Axes and Chart Area - > Vertical axis - > Secondary , as shown in below images :

 Click on Preview Pane :

SSRS - Multiple Sparklines chart

This article is about an example for creating multiple sparklines chart .

Step 1: Create Data source and then create Dataset with below query :

--Sample data
DECLARE @FY TABLE (Products VARCHAR(20),Q1 INT,Q2 INT,Q3 INT)
INSERT @FY SELECT 'SQLServer2008',1000,200,100
INSERT @FY SELECT 'SQLServer2012',1000,1000,1500
INSERT @FY SELECT 'SQLServer2014',1000,3000,5000
SELECT * FROM @FY


Step 2 :
Drag&drop Chart from Toolbox or right-click on report body - > Insert - > Chart .
Add a Line with Markers chart :

Step 3 :  Double - click on the chart area , you will notice Chart Data , Under Chart Data
for summation Values add Q1 , Q2 and Q3 columns from Dataset and then for Category Groups add Products column from the Dataset .

Click on Preview Pane :

SSRS - Bar chart with line

In this article , I will shown an example on how to create Bar chart with line .

Step 1: Create Data source and then create Dataset with below query :

DECLARE @SalesByMonth TABLE (Sort INT,Months VARCHAR(20),Sales INT)
INSERT @SalesByMonth SELECT 1,'Jan',100
INSERT @SalesByMonth SELECT 2,'Feb',1000
INSERT @SalesByMonth SELECT 3,'Mar',200
INSERT @SalesByMonth SELECT 4,'Apr',500
INSERT @SalesByMonth SELECT 5,'May',300
INSERT @SalesByMonth SELECT 6,'Jun',900
INSERT @SalesByMonth SELECT 7,'Jul',100
INSERT @SalesByMonth SELECT 8,'Aug',400
INSERT @SalesByMonth SELECT 9,'Sep',400
INSERT @SalesByMonth SELECT 10,'Oct',600
INSERT @SalesByMonth SELECT 11,'Nov',900
INSERT @SalesByMonth SELECT 12,'Dec',900
SELECT * FROM @SalesByMonth

Step 2 :
Drag&drop Chart from Toolbox or right-click on report body - > Insert - > Chart .
Add a column chart :

SSRS - How to repeat headers for each group

In this article , I am going to share about the information on
i) how to repeat headers for each group
ii.)how to overcome the error  :

"An error occurred during local report processing .
The definition of the report '/Report ' is invalid .
The tablix 'Tablix' has a detailed member with inner members . Detail members can only contain static inner members ."

For example , consider below sample data :

DECLARE @Tmp TABLE (Country VARCHAR(30),City VARCHAR(30),Population INT)
INSERT @Tmp SELECT 'India','CHN',100
INSERT @Tmp SELECT 'India','MUM',400
INSERT @Tmp SELECT 'India','KOL',300
INSERT @Tmp SELECT 'US','NY',100
INSERT @Tmp SELECT 'US','CAL',300
SELECT * FROM @Tmp

SSIS - Shred data from XML file in folder into columns of a table

This article is about shredding data from XML file in a folder into columns of a table using SQL Server Integration services (SSIS) .

Example :

Step 1 : Drag-drop Data Flow Task on to the Control Flow tab

Step 2 : Double-click on Data Flow Task , We will be directed to the Control Flow tab

Step 3 : In the Control Flow tab  , drag - drop
XML Source , Data Conversion and OLE DB Destination tasks .

Consider I have XML file  named Stud.xml in the path D:\

<StudentInfo>
  <Student>
    <Id>1111</Id>
    <Name>Sathya</Name>
  </Student>
  <Student>
    <Id>1112</Id>
    <Name>Deepak</Name>
  </Student>
</StudentInfo>


Step 4 : Double-click XML Source task and do the settings as shown in below image :

SQL Server XML Bulk load examples

This article is about bulk loading XML file into tables (shredding XML data into columns of table)  :
i) Loading single table
ii)Loading multiple tables (With Foreign key relationship)
iii)Handling identity column insert while loading XML file
iv)Handling table creation before loading XML file

Prerequisites :  (Install SQLXML 4.0 SP1)

As mentioned in MSDN , after SQL Server 2008, the latest version of SQLXML (SQLXML 4.0 SP1) is no longer included in SQL Server. To install SQLXML 4.0 SP1 when it is available, download it from  - http://www.microsoft.com/en-gb/download/details.aspx?id=30403

Step by step Installation guide for SQLXML 4.0 SP1 - 

i) Example : Loading single table

Step 1 : Create table

USE [AdventureWorks2012]
GO
CREATE TABLE Student (Id INT , Name VARCHAR(100))
 
Step 2 : Open notepad and place the XML and save it as Studxml.xml

<StudentInfo>
  <Student>
    <Id>1111</Id>
    <Name>Sathya</Name>
  </Student>
  <Student>
    <Id>1112</Id>
    <Name>Deepak</Name>
  </Student>
</StudentInfo>

How to Install SQLXML 4.0 SP1

As mentioned in MSDN , after SQL Server 2008, the latest version of SQLXML (SQLXML 4.0 SP1) is no longer included in SQL Server. To install SQLXML 4.0 SP1 when it is available, download it from  - http://www.microsoft.com/en-gb/download/details.aspx?id=30403

After downloading SQLXML 4.0 , based on the OS (32 / 64 bit) choose the file and click on it

T-SQL - Using Order By clause to return resultset with exact matches first and then partial matches

This article is about using Order By clause with NULLIF to return resultset with exact matches first and then partial matches .

For Example , consider below sample data :

DECLARE @SearchProduct VARCHAR(50) = 'SQL Server'

DECLARE @Tmp TABLE (Id INT , Products VARCHAR(50))
INSERT @Tmp SELECT 2,'SQL Server 2008'
INSERT @Tmp SELECT 1,'SQL Server 2005'
INSERT @Tmp SELECT 3,'SQL Server 2008R2'
INSERT @Tmp SELECT 5,'SQL Server'
INSERT @Tmp SELECT 4,'SQL Server 2012'
INSERT @Tmp SELECT 6,'SSIS'
INSERT @Tmp SELECT 7,'SSRS'
INSERT @Tmp SELECT 8,'SSAS'
SELECT * FROM @Tmp

So to return resultset with Products Like ''SQL Server'' , below query can be used :

SELECT * FROM @Tmp
WHERE Products LIKE '%'+@SearchProduct+'%'

To Order By resultset with exact matches first and then partial matches ,
below query can be used :

SELECT * FROM @Tmp
WHERE Products LIKE '%'+@SearchProduct+'%'
ORDER BY NULLIF(Products,@SearchProduct)

Reference - MSDN Forumn - Transact - SQL

T-SQL Group By with CASE statement

This article is about the usage of CASE statement within Group By clause .

For example : Consider below sample data

DECLARE @Tmp TABLE (Products VARCHAR(50),Count INT)
INSERT @Tmp SELECT 'SQL Server 2008',100
INSERT @Tmp SELECT 'SQL Server 2008',100
INSERT @Tmp SELECT 'SQL Server 2012',200
INSERT @Tmp SELECT 'SQL Server 2012',200
INSERT @Tmp SELECT 'ORACLE',600

Consider if we are required to calculate the overall count of the products like "SQL" and "Oracle" ,
then below query containing Group By clause with CASE statement can be used :

SELECT
CASE WHEN Products LIKE '%SQL%' THEN 'MSSQL'
              WHEN Products LIKE '%ORACLE%' THEN 'Oracle'
              ELSE NULL END Products ,
SUM(Count) OverallCount
FROM @Tmp
GROUP BY CASE WHEN Products LIKE '%SQL%' THEN 'MSSQL'
              WHEN Products LIKE '%ORACLE%' THEN 'Oracle'
              ELSE NULL END

Search for a string in XML column of a table

This article has the examples on how to search for a string in XML column  of a table .

Consider below sample data :

DECLARE @StudentInfo TABLE (Id INT, Name VARCHAR(20),Subjects XML)
INSERT @StudentInfo SELECT 1,'Sathya',
'<Subjects>
    <Subject>
      <Name>English</Name>
      <Marks>70</Marks>
    </Subject>
    <Subject>
     <Name>Maths</Name>
     <Marks>60</Marks>
    </Subject>
</Subjects>'
INSERT @StudentInfo SELECT 2,'Deepak',
'<Subjects>
    <Subject>
      <Name>English</Name>
      <Marks>70</Marks>
    </Subject>
    <Subject>
     <Name>Science</Name>
     <Marks>60</Marks>
    </Subject>
</Subjects>'
SELECT * FROM @StudentInfo






 --To search for subject named science in the XML column
SELECT *
FROM @StudentInfo
WHERE Subjects.exist('Subjects/Subject[Name="Science"]') = 1
--To search for subject named science and marks >= 50 in the XML column
SELECT *
FROM @StudentInfo
WHERE Subjects.exist('Subjects/Subject[Name="Science"][Marks >= "50"]') = 1
--Using Contains function to search for subject named science
SELECT *
FROM @StudentInfo
WHERE Subjects.exist('(Subjects/Subject/Name[contains(.,"Science")])')=1

Related reference links :

http://technet.microsoft.com/en-us/library/ms189869.aspx
http://msdn.microsoft.com/en-us/library/ms178026.aspx
http://social.msdn.microsoft.com/Forums/sqlserver/en-US/601f6859-3567-435b-b764-859fb08a1044/search-for-xml-string

 

T-SQL Script to Split a column with delimited string into multiple columns

Refer my post on Technet gallery - http://gallery.technet.microsoft.com/scriptcenter/T-SQL-Script-to-Split-a-308206f3

T-SQL Script to find the names of the StoredProcedure that has used dynamic SQL

Refer my post on sql-articles -  http://sql-articles.com/scripts/t-sql-script-to-find-the-names-of-the-storedprocedure-that-has-used-dynamic-sql/