SSRS Expressions

Expressions are used throughout the report definition to specify or calculate values for parameters, queries, filters, report item properties, group and sort definitions, text box properties, bookmarks, document maps, dynamic page header and footer content, images, and dynamic data source definitions.
Here are some of the reporting services expressions:
1.To get Today’s date 
=Today
2.The following code will get Today's date -3 days
=DateAdd("d", -3, Today)
3.Get the Name of the day - like sunday
=WeekdayName(DatePart("w", Today))
4.SWITCH statement - An alternative to IIF/CASE 
=SWITCH(WeekdayName(Fields!Date.Value) = "Monday","Blue",
WeekdayName(Fields!Date.Value) = "Tuesday","Green",
WeekdayName(Fields!Date.Value) = "Wednesday","Red")

5.Format Numbers as Currency 
=FormatCurrency(1000)
6. Convert integer values to string
= CStr(123123)
7.The Iif function returns one of two values depending on whether the expression is true or not.

=IIF(Fields!LineTotal.Value > 100, True, False) 

8.Use multiple IIF functions (also known as "nested IIFs") to return one of three values depending on the value of PctComplete. The following expression can be placed in the fill color of a text box to change the background color depending on the value in the text box.

=IIF(Fields!PctComplete.Value >= 10, "Green", IIF(Fields!PctComplete.Value >= 1, "Blue", "Red"))

9.Test the value of the ImportantDate field and return "Red" if it is more than a week old, and "Blue" otherwise. This expression can be used to control the Color property of a text box in a report item:

=IIF(DateDiff("d",Fields!ImportantDate.Value,Now())>7,"Red","Blue")

10.Test the value of the Department field and return either a subreport name or a null (Nothing in Visual Basic). This expression can be used for conditional drillthrough subreports.

=IIF(Fields!Department.Value = "Development", "EmployeeReport", Nothing)

11.The Sum function can total the values in a group or data region. This function can be useful in the header or footer of a group.

=Sum(Fields!LineTotal.Value, "Order") 

12.You can also use the Sum function for conditional aggregate calculations.

=Sum(IIF(Fields!State.Value = "Finished", 1, 0)) 

13.The RowNumber function, when used in a text box within a data region, displays the row number for each instance of the text box in which the expression appears. This function can be useful to number rows in a table. It can also be useful for more complex tasks, such as providing page breaks based on number of rows
The scope you specify for RowNumber controls when renumbering begins. The Nothing keyword indicates that the function will start counting at the first row in the outermost data region. To start counting within nested data regions, use the name of the data region. To start counting within a group, use the name of the group.

=RowNumber(Nothing)

14.Color alternate rows in table with a different color - This is very useful when there are a lot of rows with similar data and its hard to differentiate the rows. You have to set the table background color property with the following code.

=iif(RowNumber(Nothing) Mod 2, "WhiteSmoke", "White")
15. Dynamic DataSource - If you have a requirement for a report to be run against multiple database server you can change the datasource connection string property to be dynamic. You can write your own expression in the connection string option in the data source properties. The following will create a dynamic connection to the server depending on the report parameter (ServerName) selected. Note: You have to make sure that the login has permission on all the servers you would like to connect to.

="Data Source=" & Parameters!ServerName.Value & ";Initial Catalog=WSS_Content"
16.Combine more than one field by using concatenation operators and Visual Basic constants. The following expression returns two fields, each on a separate line in the same text box:

=Fields!FirstName.Value & vbCrLf & Fields!LastName.Value

17.Format dates and numbers in a string with the Format function.

=Format(Parameters!StartDate.Value, "M/D") & " through " & Format(Parameters!EndDate.Value, "M/D")

18.The Right, Len, and InStr functions are useful for returning a substring, for example, trimming DOMAIN\username to just the user name. The following expression returns the part of the string to the right of a backslash (\) character from a parameter named User:

=Right(Parameters!User.Value, Len(Parameters!User.Value) - InStr(Parameters!User.Value, "\"))

19.The following expression results in the same value as the previous one, using members of the .NET Framework System.String class instead of Visual Basic functions:
=User!UserID.Substring(User!UserID.IndexOf("\")+1, User!UserID.Length-User!UserID.IndexOf("\")-1)

20.Join - Display the selected values from a multivalue  parameter
=Join(Parameters!MyParameter.Value,",")
21.The Regex functions from the .NET Framework System.Text.RegularExpressions are useful for changing the format of existing strings, for example, formatting a telephone number. The following expression uses the Replace function to change the format of a ten-digit telephone number in a field from "nnn-nnn-nnnn" to "(nnn) nnn-nnnn": 

=System.Text.RegularExpressions.Regex.Replace(Fields!Phone.Value, "(\d{3})[ -.]*(\d{3})[ -.]*(\d{4})", "($1) $2-$3")

SQL_MERGE

USE tempdb;
GO
CREATE TABLE dbo.TargetData(EmployeeID int, EmployeeName varchar(10),
     CONSTRAINT Target_PK PRIMARY KEY(EmployeeID));
CREATE TABLE dbo.SourceData(EmployeeID int, EmployeeName varchar(10),
     CONSTRAINT Source_PK PRIMARY KEY(EmployeeID));
GO
INSERT dbo.TargetData(EmployeeID, EmployeeName) VALUES(100, 'Mary');
INSERT dbo.TargetData(EmployeeID, EmployeeName) VALUES(101, 'Sara');
INSERT dbo.TargetData(EmployeeID, EmployeeName) VALUES(102, 'Stefano');

GO
INSERT dbo.SourceData(EmployeeID, EmployeeName) Values(101, 'Bob');
INSERT dbo.SourceData(EmployeeID, EmployeeName) Values(104, 'Steve');
GO

--BEGIN TRAN
--
MERGE TargetData T
USING SourceData S
ON T.EmployeeID = S.EmployeeID

 --Not Exist in Target
WHEN NOT MATCHED /* AND AND S.EmployeeName LIKE 'S%' */ BY Target
THEN INSERT  VALUES(S.EmployeeID, S.EmployeeName)  -- Inserts to Target
 --Not Exist in Target
WHEN NOT MATCHED /* AND AND S.EmployeeName LIKE 'S%' */ BY Source
THEN DELETE  -- Inserts to Target
WHEN MATCHED THEN UPDATE SET T.EmployeeName = S.EmployeeName

OUTPUT $action, inserted.*,deleted.*;
--updated.*;


select * from dbo.TargetData
select * from dbo.SourceData
/*
DROP TABLE dbo.TargetData
DROP TABLE dbo.SourceData
*/

--COMMIT TRAN
--ROLLBACK TRAN

SQL

  

-----------------------User Degine Function---------------

----------------------------------------------------------

-- Type 1 > Scaler Functions :  Returns single value.

--Ex: Factorial of Given No.

CREATE FUNCTION UDF_Factorial( @num int)

RETURNS int

AS

BEGIN

DECLARE @fact int

SET @fact=1

WHILE @num != 0

BEGIN

      SET @fact=@fact * @num

      SET @num=@num-1

END

RETURN @fact

END

-----------------------------------------------------------------

--Execute User Define Function

SELECT dbo.UDF_Factorial(5) as Factorial

------------------------------------------------------------------

--Ex: Function to Find Employee MAX Salary of Given Dept

CREATE FUNCTION UDF_getMaxSal(@Deptid int)

RETURNS NUMERIC(10,2)

AS

BEGIN

DECLARE @MaxSal NUMERIC(10,5)

 SET @MaxSal=(SELECT TOP 1 Salary FROM tblEmployee WHERE DeptId=@Deptid ORDER BY Salary DESC)

RETURN @MaxSal

END

GO

--Execution of UDF in SELECT Statment

SELECT EmpId,EmpName,Address,Deptid,Salary FROM tblEmployee WHERE Salary=dbo.UDF_getMaxSal(2)

-- Type 2 >  Inline Table Valued Function : Returns Table Object

--Ex: Function to Return Record of Emplyees whose Salary > given salry

CREATE FUNCTION UDF_getEmpRecordsAboveGivenSal(@Salary int)

RETURNS Table

AS

return (SELECT EmpId,EmpName,Address,Gender,Salary FROM tblEmployee WHERE Salary > @Salary)

GO

-----------------------------------------------------------------------------

-- Execution

SELECT * FROM dbo.UDF_getEmpRecordsAboveGivenSal(25000)

-- Type 3 >  Multi Statement Table Valued Function :

--                Explicitly defines the structure of the table to return.

--                Defines column names and datatypes in the RETURNS clause.

--Ex: Fuction to get Employee Records with Dept Name

CREATE FUNCTION getEmpByDeptName()

RETURNS @EmpWithDept Table

(

      EmpId int,

      EmpName varchar(50),

      DeptName varchar(50)   

)

AS

BEGIN

 INSERT INTO @EmpWithDept SELECT e.EmpId,e.Empname,d.DeptName FROM tblDepartment d INNER JOIN tblEmployee e ON d.DeptId=e.DeptId

 return

END

GO

-----------------------------------------------------------------------------

-- Execute Multi Statement Table Valued Function

SELECT * FROM dbo.getEmpByDeptName()

--Modify or Alter UDF like Follows

Alter FUNCTION getEmpByDeptName(@DeptName varchar(50))

RETURNS @EmpWithDept Table

(

      EmpId int,

      EmpName varchar(50),

      DeptName varchar(50)   

)

AS

BEGIN

 INSERT INTO @EmpWithDept SELECT e.EmpId,e.Empname,d.DeptName FROM tblDepartment d INNER JOIN tblEmployee e ON d.DeptId=e.DeptId WHERE DeptName=@Deptname

UPDATE @EmpWithDept SET DeptName='DOT NET' WHERE DeptName='.NET'

 return

END

GO

--------------------------------------------------------------------------------------------------------------------------------

-- Execute Multi Statement Table Valued Function

SELECT * FROM dbo.getEmpByDeptName('.NET')

----------------------------------Triggers-----------------------------------

-----------------------------------------------------------------------------

--Defination : A trigger is an action that is performed behind-the-scenes when an event occurs on a table.

-- Types of Trigger: 1) Instead of/Before  2) After/For

-- There are Two Tables with Field and Diffrent name one is tblPersone and another is tblPersonUpdate

SELECT *  FROM tblPerson

SELECT * FROM tblPersonUpdate

-----------------------------------------------------------------------------

--Inserting Records into tblPerson

INSERT INTO tblPerson VALUES('Vinay','Sayaji,Indore',Convert(Varchar,GETDATE(),114))

INSERT INTO tblPerson VALUES('Rahul','Vijay Nagar,Indore',Convert(Varchar,GETDATE(),114))

INSERT INTO tblPerson VALUES('Hitesh','Khargone',Convert(Varchar,GETDATE(),114))

SELECT *  FROM tblPerson

SELECT * FROM tblPersonUpdate

--Creating Trigger on Table tblPerson After Update will Insert Old Record into tblPersonUpdate

CREATE TRIGGER UDT_PersonUpdate

ON tblPerson

After UPDATE

AS

      DECLARE @id int;

      DECLARE @name varchar(50);

      DECLARE @address varchar(50);

      DECLARE @time varchar(50);

     

      select @id=U.id from deleted U;

      select @name=U.name from deleted U;

      select @address=U.address from deleted U;

      select @time=U.time from deleted U;

     

BEGIN

      INSERT INTO tblPersonUpdate(id,name,address,time)VALUES(@id,@name,@address,@time)

      PRINT 'Record Has been Inserted into tblPersonUpdate'

     

END

--Now Updating in Table tblPersone

UPDATE tblPerson SET address='Bhopal' WHERE id=3

-----------------------------------------------------------------------------

--Now Updated Records in tblPerson and tblPersonUpdate

SELECT *  FROM tblPerson

SELECT * FROM tblPersonUpdate

Diff ISNULL & NULLIF

ISNull :

Requires 2 arguments, check if first argument is NULL then replace NULL value with second argument , exactly works as :

Select ISNULL (FirstArgument, SecondArgumanet)

NULLIF :

Returns a null value if the two specified expressions are equal.

NULLIF ( expression1 , expression2 )

Like, When expression1 = expression2 Then Null Else FirstAgument End

Difference between NULLIF and ISNULL

Sample data

 CREATE TABLE dbo.TestTable

 (

     Id int IDENTITY (1,1),

     Name varchar(20),

     OldAddress varchar(50),

     NewAddress varchar(50)

 );

 INSERT INTO TestTable (Name, OldAddress, NewAddress)

 VALUES ('Emp1', NULL, NULL)

 INSERT INTO TestTable (Name, OldAddress, NewAddress)

 VALUES ('Emp2', '123 Street', '456 Street')

 INSERT INTO TestTable (Name, OldAddress, NewAddress)

 VALUES ('Emp3', NULL, NULL)

 INSERT INTO TestTable (Name, OldAddress, NewAddress)

 VALUES ('Emp3', '890 Street', '890 Street')

NULLIF

NULLIF returns the first expression if the two expressions are not equal. If the expressions are equal, NULLIF returns a null value of the type of the first expression.

SELECT *, NULLIF(OldAddress,NewAddress) AS 'No change in address' FROM TestTable

Results and explanation for NULLIF