SSRS - IsMissing - Visibility Function

SCENARIO :

In SSRS,based on certain condtions mentioned in the dataset  (query/stored procedure),if we are required to display only particular columns which holds data and to eliminate/hide columns,which are not required to be displayed in the report.

If we want to handle the above scenario in SSRS,IsMissing function can be used.

Example 1:

Sample data :

CREATE TABLE Famous_Personalities
(Profession VARCHAR(30),
Person_Name VARCHAR(30),
Game VARCHAR(30),
Talented_In VARCHAR(30))

INSERT Famous_Personalities SELECT 'sports','sachin','Cricket',NULL
INSERT Famous_Personalities SELECT 'sports','messi','Football',NULL
INSERT Famous_Personalities SELECT 'entertainers','AR Rehman',NULL,'Music composer'
INSERT Famous_Personalities SELECT 'entertainers','shakira',NULL,'Dancing&Singing'


--stored procedure - report dataset


CREATE PROCEDURE SSRS_Columns_Ismissing
@Profession VARCHAR(30)
AS

BEGIN

IF
@Profession = 'sports'

SELECT Person_Name,Game,Profession FROM Famous_Personalities
WHERE Game IS NOT NULL

 ELSE

SELECT
Person_Name,Talented_In,Profession FROM Famous_Personalities
WHERE Talented_In IS NOT NULL

END


Example :


Step 1:  create report with above sample data.




Step 2: Dataset will have columns that is mentioned inside the IF block in the stored procedure,
we have to manually add the column - Talented_In as shown below






     





Step 3 : Form the report as shown below & click View Report





Below image shows IF block of the stored procedure .Here,column  Talented_In is not necessarily   required to be displayed.



Below image shows ELSE block of the stored procedure .Here,column  Game is not necessarily   required to be displayed



Step 4:  To hide columns -we have to use IsMissing function in the expression, try as shown below





Now the report shows the column only if the field exists in the report dataset after the data is retrieved from the data source:



Example 2:

Consider below is my Dataset 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 * INTO #temp
FROM (
SELECT Months,Sales
FROM @SalesByMonth) up
PIVOT (SUM(Sales) FOR Months IN (Jan,Feb,Mar,Apr,May,Jun,Jul,Aug,Sep,Oct,Nov,Dec)) AS pvt

DECLARE @Query NVARCHAR(MAX) ,
        @Columns NVARCHAR(MAX)


SELECT @Columns =  LEFT(DATENAME(MONTH,DATEADD(mm, DATEDIFF(mm,0,GETDATE()), 0)),3)
+','
+ LEFT(DATENAME(MONTH,DATEADD(mm, DATEDIFF(mm,0,GETDATE())-1, 0)),3)
+','
+ LEFT(DATENAME(MONTH,DATEADD(mm, DATEDIFF(mm,0,GETDATE())-2, 0)),3)

SET @Query = 'SELECT '+ @Columns + ' FROM #temp'

EXEC(@Query)


Dataset query returns only last 3 months data and our requirement is to show only latest 3 months data on the report .

On Jan month column Set Column Visibility - > Show or hide based on expression - >
=IIF(Fields!Jan.IsMissing,True,False) 

similarly set the Column Visibility property for remaining month columns .



Click on Preview Pane :

 
 

3 comments:

Pavithra said...

What if there are many columns? Any easy way to get column name?

Pavithra said...

what if there are 100 columns?

Sathya said...

Pavithra ,
I guess this will help -
http://social.technet.microsoft.com/wiki/contents/articles/19333.ssrs-how-to-set-column-visibility-property-for-many-columns-based-on-parameter-s-value.aspx