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
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:
What if there are many columns? Any easy way to get column name?
what if there are 100 columns?
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
Post a Comment