SSRS - Examples for using report functions in expressions

In this post,let us see examples for report functions in SSRS - RowNumber,Previous,RunningValue,CountDistinct,Count,CountRows

--Sample Data
USE AdventureWorks2012
GO
CREATE
TABLE SSRS_TestExp(EmployeeId INT,Name VARCHAR(50),Designation VARCHAR(50),Salary INT,DOJ DATE)

INSERT SSRS_TestExp SELECT 1,'Sathya','Senior Programmer',500,'2013-1-1'
INSERT SSRS_TestExp SELECT 4,'Deepak','Programmer',100,'2013-1-4'
INSERT SSRS_TestExp SELECT 3,'Sunny','Programmer',150,'2013-1-3'
INSERT SSRS_TestExp SELECT 2,'Ashlee','Senior Programmer',400,'2013-1-2'

SELECT * FROM SSRS_TestExp ORDER BY EmployeeId

--DROP TABLE SSRS_TestExp

Create a report,drag-drop Table from Toolbox and then configure datasource and dataset as shown in below screenshots :




Select below columns,right-click - > Expression.. -> add below expressions

PreviousDOJ   =IIF(IsNothing(Previous(Fields!DOJ.Value)),"Joined First",Previous(Fields!DOJ.Value))

GroupByDesignation  =Runningvalue(Fields!Designation.Value,countdistinct,nothing)

RowNumber  =RowNumber("DataSet1")

RunningValue  =RunningValue(Fields!Salary.Value, Sum, Nothing)

Count(EmployeeID)  =Count(Fields!EmployeeId.Value)

TotalRowsCount  =CountRows("DataSet1")

CountDistinct(Designation)  =CountDistinct(Fields!Designation.Value,"DataSet1")




For Reference :

RowNumber    -  http://msdn.microsoft.com/en-IN/library/ms159225%28v=sql.100%29.aspx
Previous          -   http://msdn.microsoft.com/en-us/library/ms156372%28v=sql.100%29.aspx
RunningValue   -  http://msdn.microsoft.com/en-us/library/ms159136%28v=sql.100%29.aspx
CountDistinct    - http://msdn.microsoft.com/en-IN/library/ms155923%28v=sql.90%29.aspx
Count               - http://msdn.microsoft.com/en-US/library/ms157163%28v=sql.90%29.aspx
CountRows       - http://msdn.microsoft.com/en-US/library/ms156330%28v=sql.90%29.aspx


2 comments:

Anonymous said...

Clear Implementaions... Good...

Anonymous said...

Really useful post summarizing those functions! Saved me a lot of time - thanks!