Search

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


T-SQL - Example for SUBSTRING with CASE statement

This post is just to show an example for SUBSTRING with CASE statement

Sample Code:

DECLARE @tmp TABLE (FullFileName VARCHAR(100))
INSERT @tmp SELECT 'File_2013-04-21.txt'
INSERT @tmp SELECT 'File_2013-04-21.txt -1'
INSERT @tmp SELECT 'File_2013-04-21.txt -2'

SELECT FullFileName,
SUBSTRING(FullFileName,1,
CASE WHEN (CHARINDEX(' -',FullFileName)) > 0
THEN CHARINDEX(' -',FullFileName)
ELSE LEN(FullFileName) END ) [FileName]
,CASE WHEN CHARINDEX(' -',SUBSTRING(FullFileName,CHARINDEX(' -',FullFileName,0),LEN(FullFileName))) > 0
THEN SUBSTRING(FullFileName,CHARINDEX(' -',FullFileName,0),LEN(FullFileName))
ELSE NULL END Filesuffix
FROM @tmp


SQL Server 2012 - PARSENAME function - To split delimited data

PARSENAME  -  Returns the specified part of an object name.

Syntax :
PARSENAME ( 'object_name' , object_piece ) 

Examples:

DECLARE @ObjectDetail VARCHAR(100) = 'SQLServer.AdventureWorks2012.Sales.Customer'
SELECT @ObjectDetail ObjectDetail

SELECT
PARSENAME(@ObjectDetail,4) [Server],
PARSENAME(@ObjectDetail,3) [Database],
PARSENAME(@ObjectDetail,2) [Schema],
PARSENAME(@ObjectDetail,1) [Table]


--usually Dot(.) is considered as the delimiter by PARSENAME function

DECLARE @pipedelimited VARCHAR(50) = 'Chennai|TamilNadu|India'

SELECT
PARSENAME(REPLACE(@pipedelimited,'|','.'),3)City,
PARSENAME(REPLACE(@pipedelimited,'|','.'),2) State,
PARSENAME(REPLACE(@pipedelimited,'|','.'),1) Country


DECLARE @DateTime DATETIME = GETDATE()
SELECT @DateTime [DATE]
SELECT PARSENAME(@DateTime,1) [DATE]


Reference - http://msdn.microsoft.com/en-in/library/ms188006.aspx

SSIS - Execute SQL Task - Result Set and Parameter Mapping

In this post,let us see an example for capturing query result (single valued)  into SSIS variable and insert the captured data in the variable into another table.

First create a variable in the scope of package with Data Type as INT32.

Drag & drop two Execute SQL Task on the Control Flow tab.

Follow as shown in below screen shots :