Data formatting in SSRS RDL Reports with Field Expressions…

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…

Backstory…

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… 😀

– Lower case and Upper case…

=UCase(Fields!mytestfield.Value))

or for lower case,

=LCase(Fields!mytestfield.Value))

– Multiple fields into a Single field…

= 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.

– Check for null…

ISNOTHING(Fields!mytestfield.Value)

This will return a True or False boolean value based the value in the field.

– If conditions…

=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”…

– Switch statements…

=Switch(
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.

– Text Trimming…

=RTrim(UCase(Fields!mytestfield.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).

– To Date object…

=CDate(Fields!mytestfield.Value)

This will return a date object from the value present in “mytestfield”…

– Formatting a value…

=Format(CDate(Fields!mytestfield.Value), "dd/MM/yyyy")

This formats the value accordingly to the given date format, similarly this applies to any type..

– Format and Concat multiple fields into a single value…

= 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…

– Yesterday, Today, or Tomorrow…

=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,

=Format((DateAdd("d",-1,Today())), "dd/MM/yyyy")

Since it returns a date object no need to convert the value before formatting…

– Convert UTC date time object to local time…

=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…

– Extract Date object segments…

=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…

– Build a 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… 😀

Conclusion

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!