Let me share some exciting ways of formatting data with Field Expressions in SSRS RDL based Reports that I discovered during my Microsoft Dynamics 365 adventures! 😉
Using the Expression Builder in Visual Studio Report Designer, you can add all kinds of interesting ways of formatting your data for RDL Report projects. You can set custom expressions to these data fields to format and manipulate them easily.
This is where the magic happens, as you can see there’s a whole collection of library for formatting data with Expressions…
So I happened to be embarked on a Microsoft Dynamics 365 CRM data Reporting project, where I was tasked built a bunch of SSRS RDL based reports, that pulls data from CRM and format it based on the custom business logic before displaying to the user.
This involved writing a whole bunch of custom expressions on each field in those reports, where I came across all these interesting ways of formatting and manipulating data.
So here I am sharing some interesting field expression formatting that I discovered during that adventure… 😉
Formatting Fields with Expressions!
Opening up the Expression editor dialog you can see the long list of available syntaxes with their respective functionality description. It gives a good head start for whatever the function you’re looking to implement to format your data.
Alright then let me share some awesome stuff you can do with these data formatting functions available through expressions… 😀
or for lower case,
= Fields!mytestfield1.Value + " - " + Fields!mytestfield2.Value
You can pull data from multiple fields into a single field, and concatenate those field values using “+” operator, even add string chars in the middle as you wish.
This will return a True or False boolean value based the value in the field.
=IIf(ISNOTHING(Fields!mytestfield.Value), " - ", "DC-" + UCase(Fields!mytestfield.Value)))
As shown above this will check if “mytestfield” no value, then return ” – “, otherwise return the Upper-cased, custom value of “mytestfield” as “DC-XXXXX”…
Fields!mytestfield.Value = "1", "Monkey",
Fields!mytestfield.Value = "2", "Rabbit",
Fields!mytestfield.Value = "3", "Penguin")
Based on the value present in “mytestfield”, we return the response value.
No trailing spaces (RTrim) at the end or similarly no leading spaces (LTrim) in the beginning of the value, or for both function in the same time use (Trim).
This will return a date object from the value present in “mytestfield”…
This formats the value accordingly to the given date format, similarly this applies to any type..
= Format(Fields!mytestfield1.Value, "yy") + Format(Fields!mytestfield2.Value, "MM") + Format(Fields!mytestfield3.Value, "dd")
This format those different field values and merge them into a single field…
=DateAdd("d",-1,Today()) =Today() =DateAdd("d",1,Today())
First expression, gets the Yesterday’s date, second get the Today’s date, third expression gets the Tomorrow’s date…
You can also format is similarly,
Since it returns a date object no need to convert the value before formatting…
=Format( System.TimeZone.CurrentTimeZone. ToLocalTime(Fields!mytesttimefield.Value), "hh:mm tt")
Here “mytesttimefield” carries a date time object with UTC time, and we’re now converting that to the local system time, whichever the timezone set up in the client environment…
=Year(CDate(Fields!mytestfield.Value)) =Month(CDate(Fields!mytestfield.Value)) =Day(CDate(Fields!mytestfield.Value))
Three options, extract Year, Month and Date values from a given Date object…
=DateSerial("2020", "06", "18")
Now that builds a date object by year, month, and date segments. If I’m to spice it up…
=Format( DateSerial( Year(CDate(Fields!mytestfield.Value)), Month(CDate(Fields!mytestfield.Value)), "1").AddMonths(1).AddDays(-1), "dd/MM/yyyy")
You can also add extra values after build the date object as shown there.
Well that’s it for now… 😀
It’s actually quite fun to work with formatting or manipulating data using Expressions in SSRS RDL Reports, while the Expression editor window follows a great editing tool with descriptions of each function available.
There’s many awesome functions available in the Expression editor to format our data fields before presentation to the user during execution. You could build all kinds of custom formatted data values even by merging multiple fields. All this is quite straight forward and easy to use.
Share the love! 😀 Cheers!