Funny Videos: FB Fun4you
Below are listed most useful SSRS epressions which are used at the time of designing complex reports and dashboards in SSRS:
Below are listed most useful SSRS epressions which are used at the time of designing complex reports and dashboards in SSRS:
Ø
To add comments with Field Value in SSRS
expressions
="Report
Executed By: " + User!UserID
Ø
To get
total number of rows in the tablix or dataset’s result set
=CountRows("DataSet1")
Ø
Display
Report Execution Time in a textbox:
="Report Execution Time: " &
Globals!ExecutionTim
Ø
To Join all selected values of multi valued
parameter in a single string:
=InStr(Join(Parameters!CityName.Value,
", ")
Ø
To display Page number of Total pages in the
footer or header:
="Page
No. " & Globals!PageNumber & "
of Total pages: " & Globals!TotalPages
Ø
To display or print report name with user name
and execution time of the report in a textbox as: Report (reportname) generated
by DOMAIN/User on 10/23/2015 3:25:08 AM:
="Report
(" & Globals!ReportName & ")
generated by " & User!UserID & "
on " & Globals!ExecutionTime
Ø
If we want to hide or show columns based on the
selection of columns name in parameter on the SSRS report , suppose we have 10
columns on the report which are having name as:
Column1, Column2, Column3, …… , Column10
And these columns name are listed in a
mulit value parameter so if user selects Column1, column5 and Column8 from the
parameter list then only these three columns should be shown on the report so
we can use below expression in the column visibility property, under the
Show/Hide based on expressions:
=IIF(InStr(Join(Parameters!ColumnName.Value,","),"ColumnName")=0,True,False)
Decision Functions in SSRS expressions:
Ø
IIF()
– IIf function is used to check conditions whether the
expression is true or false, to perform next task.
=IIF(Fields!SalesAmount.Value
> 100, "LimeGreen","Red")
Use multiple IIF functions (also known as
"nested IIFs") to return one of three values depending on multiple
condition based on the the value of SalesAmount field as:
=IIF(Fields!SalesAmount.Value
>= 10, "Green"
,IIF(Fields!SalesAmount.Value
>= 1, "Blue",
"Red"))
Ø
Switch() -
A different way to get the same functionality uses the Switch function. The Switch function is useful when
you have three or more conditions to test. The Switch
function returns the value associated with the first expression in a series
that evaluates to true:
=Switch(Fields!PctComplete.Value >= 10, "Green",
Fields!PctComplete.Value >= 1, "Blue",
Fields!PctComplete.Value = 1, "Yellow",
Fields!PctComplete.Value <=
0, "Red",)
Ø A
third way to get the same functionality uses the Choose function. The Choose
function uses the first parameter as an index to one of the remaining function
parameters. The first parameter must be an integer. If the background color of
a text box in a table is set to this expression, the value of TotalSalesOrder
controls the color.
=Choose(Fields!TotalSalesOrder.Value,"Red","LimeGreen","Yellow")
Ø Page Breaks: In some reports, you may want to
place a page break at the end of a specified number of rows instead of, or in
addition to, on groups or report items. To do this, create a group that
contains the groups or detail records you want, add a page break to the group,
and then add a group expression to group by a specified number of rows.
·
The following expression, when placed in the
group expression, assigns a number to each set of 25 rows. When a page break is
defined for the group, this expression results in a page break every 25 rows.
·
=Ceiling(RowNumber(Nothing)/25)
Ø
To allow the user to set a value for the number
of rows per page, create a parameter named RowsPerPage
and base the group expression on the parameter, as shown in the following
expression:
=Ceiling(RowNumber(Nothing)/Parameters!RowsPerPage.Value)
Ø Conversion Functions: Conversion
functions can be used to convert the default data type for a field to the data
type needed for calculations or to combine text.
=CDec(500)
=CStr(Parameters!MySelection.Count)
Ø
To show field’s value in UpperCase:
=UCASE(Fields!ItemName.Value)
Ø
To show field’s value in proper case, Ist letter
in each word must be uppercase and rest of characters should be in lowercase:
=StrConv(Fields!ItemName.Value,
VbStrConv.ProperCase)
Ø
Replace Null vlaue with a string or number:
=iif(Fields!SalesAmount.Value
= nothing, 0.00,Fields!SalesAmount.Value) =iif(Fields!ItemName.Value
= nothing, "Unknown"
,Fields!ItemName.Value)
More on SSRS expressions:
For more on SSRS expressions go through these other web
links:
If you have any thoughts or suggestion, feel free to post
in the below comment section.
Thanks!
FB Fun4You: New Funny Prank Compilation
