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

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:

WordPress.com Logo

You are commenting using your WordPress.com 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 )

Google+ photo

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

Connecting to %s

%d bloggers like this: