SSRS : Frequently used expressions

I was used the below expressions to full fill the client requirements on day to day business requirements.

1. How to display the alternate color to the Row ?

Select Entire Row 

Go to Properties, Find Background property and click on ‘Expression...

=IIF((RowNumber(nothing) MOD 2)=1,"Gainsboro","Transparent")

2.  How to display or high light single color based on the value of a field ?

=IIF(Fields!Column.Value = "Approved", "Green", "No Color")

Ex:  =IIF(Fields!Service.Value = "Office", "Green", "No Color")

3. What is IIF ? 

IIF expression which is also know and IF THEN ELSE statement that allows to provide a condition that can be evaluated to either TRUE or FALSE and provide a value for TRUE part and for FALSE part.

&: is the concatenate operator in SSRS

To Know the execution time of a report.

="Execution Time: " &

IIf(System.DateTime.Now.Subtract(Globals!ExecutionTime).TotalSeconds < 1, "0 seconds",

(

IIf(System.DateTime.Now.Subtract(Globals!ExecutionTime).Hours > 0, System.DateTime.Now.Subtract(Globals!ExecutionTime).Hours & " hour(s), ", "") +

IIf(System.DateTime.Now.Subtract(Globals!ExecutionTime).Minutes > 0, System.DateTime.Now.Subtract(Globals!ExecutionTime).Minutes & " minute(s), ", "") +

IIf(System.DateTime.Now.Subtract(Globals!ExecutionTime).Seconds > 0, System.DateTime.Now.Subtract(Globals!ExecutionTime).Seconds & " second(s)", ""))

)

4. What is SQL case equivalent in SSRS ?

= switch(Fields ! PartyLoc.Value = "HYD", 

"http://sql-pc/ReportServer/Pages/ReportViewer.aspx?%2fTable_Matrix%2fReport_Table_Matrix&rs:Command=Render", 

Fields ! PartyLoc.Value = "MUM", "http://www.facebook.com", 

isnothing(Fields ! PartyLoc.Value), "http://www.samples.com")

5. How to get colors for the cells based on the expression ?

=iif(Sum(Fields!NetIncome.Value)< 0 , "Red",

 iif( Sum(IIF(Fields!NetIncome.Value<0, -1,0))
< 0 ,"Pink", "Transparent" ))



6.How to hide a column while exporting to specified format in SSRS?


Go to Particular Text box à F4 à Expand Data Only à  Set Data Element Out Put: No Out Put

7. How to highlight the maximum row with colors?


Go to Text Box Properties à Fill à Background Color = Expression and paste it.


=iif(Fields!ID__Unit.Value=max(Fields!ID__Unit.Value,"DataSet1"),"Yellow","None")

8. How to display page numbers with total pages i.e Page 1 of 4


 Take Text Box on Page Header or Footer à RC Expression à Use the below Code


="Page" & Globals!PageNumber & "Of"&Globals!TotalPages