While Creating SSRS reports for configuration manager ,I use lot of expressions through the report definition. These expressions (formula filed) are used frequently in reports to control content and report appearance. Expressions are written in Microsoft Visual Basic, and can use built-in functions, custom code, report and group variables, and user-defined variables. Expressions begin with an equal sign (=) syntax.
These expressions can be used in reports to present data like 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 etc.
SQL Expressions Optimizes the report performance,used to speed up the report from the complicated selections.You cannot use Expression to connect MORE THAN 1 DATA SET in your Report.
In this blog post,I would like to show you some of the expression (like syntax) examples which I use in most of the my Configmgr reports.
You can Use thee simple expression directly on the design surface, in a dialog box, or in the Properties pane, or you can edit it in the Expression dialog box, just as you would with any expression.
Print Date—>How do you print the report execution date
="Printed on " & Globals!ExecutionTime
Total Report Runtime (Sec)—>Calculate the total execution time of report
="Total Run time (in Sec): "& System.DateTime.Now.Subtract(Globals!ExecutionTime).Seconds
Report Executed by—Print the UserID who executed the report
="Report Run by: "& User!UserID
Altering the row colors (BackgroundColor property on Text Box )—>Change the background color for alternate lines instead of having single color across all rows
=IIF(Rownumber(nothing) mod 2, "Transparent", "Silver")
How to show parameters (prompts) in report—> You wanted to bring the parameter value into the report for Ex: collection Name to be printed on the report
=Parameters!CollID.Label
To show the values in UPPERCASE—> To print all the values in UpperCase
=UCASE(Fields!FieldName.Value)
Convert text to Proper case—> To print the first letter of the word to be in UpperCase
=StrConv(Fields!FieldName.Value, VbStrConv.ProperCase)
If you want to replace NULL with other value—>replace the NULL with other values
=iif(Fields!FieldName.Value = nothing, "No Value",Fields! FieldName.Value)
How to set colors in Pie Chart instead of Random Colors based on the output (Example taken is patch compliance , Color property on Text Box ):
=SWITCH(Fields!Patch_Status.Value = "Compliant", "Green",Fields!Patch_Status.Value= "Non-compliant", "Yellow",Fields!Patch_Status.Value = "Compliance state unknown", "Red")
you can also use IIF condition (Values greater than or equal to 10 display with a green background, between 1 and 9 display with a Yellow background, and less than 1 display with a red background.):
=IIF(Fields!status.Value >= 10, "Green", IIF(Fields!status.Value >= 1, "Yellow", "Red"))
Combining more than one filed using concatenation using (& vbCrLf & ):
=Fields!CollectionID.Value & vbCrLf & Fields!CollectionName.Value
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, "\"))
How to display text prior to the selected values (Ex:Collection ID or Name):
="Collection Name: "& Parameters!CollID.Label
How to use If condition to return the value if that matches (example to check if client is installed or not return True or False):
=IIF(Fields!Client.Value =1, True, False)
If the date value is more than week ,display red color ,else Green (This is needed when checking the hardware inventory date of client):
=IIF(DateDiff("d",Fields!LastHWScan.Value, Now())>7,"Red","Green")
How to display page number and total pages in report in footer:
=Globals.PageNumber & " of " & Globals.TotalPages
How to display name of the reprot in the footer or body in each page:
=Globals.ReportName & ", dated " & Format(Globals.ExecutionTime, "d")
Page breaks—>place a page break at the end of a specified number of rows instead of, or in addition to, on groups or report items.:
=Ceiling(RowNumber(Nothing)/25)
If you have 2 variables (prompts) for ex: days and date (start date & end date) and one is optional ,how to print the value in the report (Print the value that is not blank)
=IIF(isnothing(Parameters!StartDate.Value) and isnothing(Parameters!EndDate.Value),Parameters!days.Value &" Days",Parameters!StartDate.Value & " Till " &Parameters!EndDate.Value)
When running reports, you may get empty results .So instead of displaying columns with empty results, how do we replace it with no results found ?
Cretae a simple table with no results found and add the following formula in visibility section ,hidden .
=IIF(CountRows("DataSet1") = 0 , False, True)
What this do is, if the dataset1 has empty results then bring up custom no results found else display the results of dataset1.
Recommended Reading
https://msdn.microsoft.com/en-us/library/ms345237.aspx
https://msdn.microsoft.com/en-us/library/ms157328.aspx?f=255&MSPPError=-2147217396