ValueFormat function – SSRS Format Mask

This function really comes in handy when you’re working on a report that is able to display numbers/currencies and percentages.

ValueFormat-Code

ValueFormat-Results

Code:

CREATE FUNCTION [dbo].[ValueFormat]
(
   @Value FLOAT --You might want to replace this by DECIMAL(19,4) if it needs to be accurate!
   ,@Character VARCHAR(1)
)
RETURNS VARCHAR(20)
AS
BEGIN

   DECLARE @ValueFormat VARCHAR(20)
   SET @ValueFormat =
   (
   SELECT CASE
      WHEN (@Value) < -100000000000 and @Character = '#' THEN '#,##0.00M'
      WHEN (@Value) < -1000000000 and @Character = '#' THEN '#,##0.00M'
      WHEN (@Value) < -10000000 and @Character = '#' THEN '#,##0.00M'
      WHEN (@Value) < -1000000 and @Character = '#' THEN '#,##K'
      WHEN (@Value) < -10000 and @Character = '#' THEN '#,##K'
      WHEN (@Value) < -1000 and @Character = '#' THEN '#,##K'
      WHEN (@Value) < -1 and @Character = '#' THEN '#,##0.00'
   
      WHEN (@Value) < 1 and @Character = '#' THEN '#,##0.00'
      WHEN (@Value) < 10000 and @Character = '#' THEN '#,##K'
      WHEN (@Value) < 1000000 and @Character = '#' THEN '#,##K'
      WHEN (@Value) < 10000000 and @Character = '#' THEN '#,##0.00M'
      WHEN (@Value) < 1000000000 and @Character = '#' THEN '#,##0.00M'
      WHEN (@Value) < 100000000000 and @Character = '#' THEN '#,##0.00M'
   
      WHEN (@Value) < 0 and @Character = '%' THEN '0.##%'
      WHEN (@Value) < 1 and @Character = '%' THEN '0.##%'
   END
   )
   RETURN @ValueFormat

END

Advertisements

Leave a Reply

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