In this post,let us see examples for report functions in SSRS - RowNumber,Previous,RunningValue,CountDistinct,Count,CountRows
--Sample Data
USE AdventureWorks2012
GO--Sample Data
USE AdventureWorks2012
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