Common functions used in SSRS

While working on a Technology/Product, there always are some common functions/methods that we come across. Though, we don't understand the importance of those small piece of codes but it can help a lot of other guys working on.
I have also seen a lot of forum queries with such type questions, so I will post some of those functions here.

• Data of Multiple-select parameters in a text-box.

We all make use of multi-value parameters in our report but how to show all the selected values in a textbox.

Let us assume, we have a Country parameter named as Parameters!Country.value. Parameters!Country.value(0) gives you first selected value, but to show all use Join()

=Join(Parameters!Country.value, "," )

• Splitting a string and picking specific sub-string

Let us assume we have a directory path of a file present in C:\SSRS folder named "File1.txt". We want to retrieve only the filename, in this case we can use Choose() with Split() .

Here, Fields!dirpath.value contains the value "C:\SSRS\File1.txt"

= Choose( 3, Split( Fields!dirpath.value ,"\"))

• Newline inside a text-box

We have a requirement to show following in a single text-box,

Total

-------

1000

Don't worry, use special characters "chr(10)" or "vbcrlf" for that.

= "Total" & chr(10) & "---------" & chr(10) &  "1000"

• Date-time Manipulation

If I ask you, please give me a date that is10days from Today either ways.

What kind of question is this, go search the calendar...  :)

Well, these kind of requirements constitutes your daily reports. So, SSRS gives us a function for that 

DateAdd() and DateDiff()

I generally use DateAdd() only for both adding and subtracting.

= DateAdd(DateInterval.Day , -2, Today)  
= DateAdd(DateInterval.Day , 2, Today)

Here,
DateInterval.Day - Manipulation for days

-2  - Subtract 2 days (depends on the interval selected. Here, Day is selected)        
2 - Adding 2 days (depends on the interval selected)

Today - Base value for adding or subtracting

• Lookup()

I must say, Lookup() is one of the great addition to SSRS 2008 R2 kitty.

It lets you retrieve a field value using a join on two datasets. So, this way you are performing joins not in the query but inside your report.

Assume, you have 2 datasets: DataSet1 and DataSet2

DataSet1 got EmpId, Name, DOB, Dept and comes from Employee table

DataSet2 got EmpId, Balances, Salary and comes from HR table

Now, you require to show Balances for each employee in a single tablix along with Employee details and you know both of them are in different datasets.Use lookup,

=Lookup(Fields!EmpId.Value, Fields!EmpId.Value,  Fields!Balances.Value, "DataSet2")

• Searching within a string

To check whether your string contains any specific lettters or words, InStr() comes handy

=InStr(1, Fields!Stringval.value, "me")

returns a non null value if search string is present and basically returns the position of that.

• Formatting integers, date-time

SSRS gives a function, Format(), to format the values according to your need.

=Format(Fields!Calls.value , "formatstring")

formatstring : Can be "#,##0", "$#,##0.00", "MM/dd/yyyy", "yyy-MM-dd HH:mm:ss" etc.

• Getting the row number

To find out the row number of any record inside a Tablix, Matrix control, use RowNumber().

It can be very helpful in assign alternate row background color to any tablix.

=IIF( RowNumber(Nothing) mod 2 = 0 , "Lightgrey" ,"white" )

• Month name, Week Name in different format

You want month name in full or abbreviated from. Use,

=MonthName(1) will give January.

=MonthName(1,true) will give Jan.

Similarly, for week name

=WeekDayName(1) will give Sunday.

=WeekDayName(1,true) will give Sun.

Also, Sunday got value 1 and Saturday as 7 by default

• Replace-with in SSRS

You can replace specific occurrence of string with another one. 

And the function is Replace(string, find, replacewith)

=Replace( Fields!Categ.value , "/", ",") 

These were some of the functions that I feel comes handy in our day-to-day reporting activities.

Though, the list is not over but needs to be ended here.