Irfan's World

January 27, 2011

Custom Date Format in Universe Designer for WebI reports

Date is an important attribute in doing business, capturing events etc.   As you know there are lot of different date formats used in different countries.  For example, In USA we use MM/DD/YYYY format, in UK they use DD/MM/YYYY etc.  A request came to me from one of my client to format a date that displays month name, something  similar  as "7th of February 2005"  or "23rd of February 2004" format, as they need to convey some information through a business letter to their customers living around the world.  The client’s reporting system mostly uses Web Intelligence reports.   My first idea was to train the users on how to create variable in the report and use it, Later I have decided that I should create an object in the Universe and came up with the following solution.  It is not that difficult once you understand what the code does to create a Universe object to support this requirement.

We have the following data in the SQL Server 2008 database, which shows the date format from the database.

SQL Code for the Universe Object (SQL Server) :

CASE
  
WHEN DATENAME(DAY, [Date]) IN (1,21,31) THEN
       
DATENAME(DAY, [Date]) + 'st ' + 'of ' +
       
DATENAME(MM, [Date]) + ' ' +
       
DATENAME(YYYY, [Date])
  
WHEN DATENAME(DAY, [Date]) IN (2,22) THEN
       
DATENAME(DAY, [Date]) + 'nd ' + 'of ' +
       
DATENAME(MM, [Date]) + ' ' +
       
DATENAME(YYYY, [Date])
  
WHEN DATENAME(DAY, [Date]) IN (3,23) THEN
       
DATENAME(DAY, [Date]) + 'rd ' + 'of ' +
       
DATENAME(MM, [Date]) + ' ' +
       
DATENAME(YYYY, [Date])
  
ELSE
       
DATENAME(DAY, [Date]) + 'th ' + 'of ' +
       
DATENAME(MM, [Date]) + ' ' +
       
DATENAME(YYYY, [Date])
END

Parse the Universe Object to make sure that the syntax and the code is correct, test the object on few reports before it goes to production.

Let’s test what data is returned by this object.

Hope this tip helps. See you later !!!.

Advertisements

Leave a Comment »

No comments yet.

RSS feed for comments on this post. TrackBack URI

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

Create a free website or blog at WordPress.com.

%d bloggers like this: