Formatting Minutes as Hours and Minutes in SQL Server Reporting Services (SSRS)

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.

About Dattatrey Sindol (Datta)

Datta is a Microsoft BI Enthusiast, passionate developer, and a blogger. View Full Profile

Posted on January 23, 2010, in Code Snippets, SQL Server, SSRS, SSRS Snippets, SSRS Tips, Tips 'N' Tricks and tagged , , , , . Bookmark the permalink. 2 Comments.

  1. Thanks! I tweaked yours a bit to solve a similar problem I was running into, worked great on SSRS 2014.

  1. Pingback: Handling Large Number of Categories in a SSRS Pie Chart « Datta's Ramblings on Business Intelligence 'N' Life

What are your thoughts?

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: