Today I was working on an SSRS report for one of our clients and came across a specific formatting requirement around formatting Minutes as HH:MM. Browsed a little bit but could not get it working. Then sat for a while & tried few tricks by playing around with the expressions and finally got it working 🙂
Here is how I solved the problem.
The data available in the database is numeric and represents Total Minutes. When I initially wrote some expressions to derive Hours and Minutes from the Total Minutes I was able to get the data something like this:
10 Minutes -> 0:10
65 Minutes -> 1:5 etc.
However the need was to display the data something like below:
10 Minutes -> 00:10
65 Minutes -> 01:05 etc.
Now to get this formatting, first split the Minutes into Hours & Remaining Minutes as follows:
Hours part of TotalMinutes = Floor(Fields!Minutes.Value/60)
Remaining Minutes part of TotalMinutes = Fields!Minutes.Value Mod 60
Now to display the data in the intended format put the following expression in the textbox of the detailed row of the Table/Matrix of SSRS:
=Format(Floor(Fields!Minutes.Value/60),”00″) + “:” + Format((Fields!Minutes.Value Mod 60),”00″)
Below is a sample report with formatted minutes.
Hope you will find this useful. If yes feel free to leave a comment below.
Note: This article/demonstration is based on SSRS 2008. The options should be almost the same in SSRS 2005 as well.