Sunday, March 20, 2016

SSRS – Format() Function

The most common fact for the data representation is that this is information being pulled from a stored procedure and we don't really know what the query was and which kind of data formatting is using in the script. This data formatting can be easily done with some very common expression formatting in SSRS. If we are developing an SSRS report to show gross sales which are broken down in order date, cost, gross profit, gross profit %, order count, total sales etc.

A standard principle is to separate data from display, so use the Value property to store the data in its native data type and use the Format property to display it how we want to display the data. This data formatting become very urgent when exporting our report to Excel because if we have the right data type for our data then it will export to Excel as the right data type as we defined in the report.
Most Common Data Representation Formats - As we know that Reporting Services already introduced enhanced rich text functionality to permit mixed formatting, or even mixed tooltips & actions, within a single textbox and different formats could include mixing font sizes, colors, and other things like bold and italics within one textbox. Some very common data formatting are given below:
  1. Various date formats
  2. Currency with 2 decimal places
  3. Number with 4 decimal places
  4. Percentage with decimal places
  5. Negative value format
To understand the report data formatting, we can use the following data to generate the report –
SELECT CONVERT(VARCHAR(8), GETDATE(), 12) AS ORD_NO,
CAST(GETDATE() AS DATE) AS ORD_DATE,
121 AS QTY,
121 * 1.5 AS SALES,
121 * 1.2 AS COST,
121 * 1.5 - 121 * 1.2 AS MARGIN
UNION
SELECT CONVERT(VARCHAR(8), GETDATE() + 1, 12) AS ORD_NO,
CAST(GETDATE() + 1 AS DATE) AS ORD_DATE,
120 AS QTY,
120 * 1.75 AS SALES,
120 * 1.12 AS COST,
120 * 1.75 - 120 * 1.12 AS MARGIN
UNION
SELECT CONVERT(VARCHAR(8), GETDATE() + 2, 12) AS ORD_NO,
CAST(GETDATE() + 2 AS DATE) AS ORD_DATE,
32 AS QTY, 32 * 21.75 AS SALES,
32 * 21.12 AS COST,
32 * 21.75 - 32 * 21.12 AS MARGIN 

Report will be look like as given below after using the above script –

To apply the format settings in the existing report column, we need to right click on the column and choose the text box properties as given below –
In the Textbox properties window, choose number to see all the available formats where we can choose the pre-define formats. If you want to add your own formats then we can choose the custom format as given below- 
Or we can use the column/Textbox properties window and fill the Format as given below-

Various date formats - Date formatting in SQL Server Reporting Services (SSRS) is a very common requirement where customers look for a very specific format and it is become very important to understand the basic components of date formats.
Date Format Expression
Result
=FORMAT(Fields!ORD_DATE.Value,"d")
M/d/yyyy
=FORMAT(Fields!ORD_DATE.Value,"d/M/yyyy")
d/M/yyyy
=FORMAT(Fields!ORD_DATE.Value,"MM-dd-yyyy")
MM-dd-yyyy
=FORMAT(Fields!ORD_DATE.Value,"dd-MM-yyyy")
dd-MM-yyyy

Currency with 2 decimal places – Currency formatting is very important for the report because every country has its own currency.
Currency Format Expression
Result
=FORMAT(Fields!SALES.Value,"'$'0.00;('$'0.00)")
$158.00
=FORMAT(Fields!SALES.Value,"'$'#,0.00;('$'#,0.00)")
$1,158.00
=FORMAT(Fields!SALES.Value,"'$'#,0.00)
-$58.00
Number with 4 decimal places –To display the number with n number decimal points, we can use the following format() settings -
Number Format Expression
Result
=FORMAT(Fields!QTY.Value,"0.000;(0.000)'')
158.000
=FORMAT(Fields!QTY.Value,"0.00;(0.00)'')
158.00
=FORMAT(Fields!QTY.Value,"0.0;(0.0)'')
158.0
=FORMAT(Fields!QTY.Value,"0;(0)'')
158
Percentage with decimal places –To display the percentage number with n number decimal points, we can use the following format() settings -
Percentage number Format Expression
Result
=FORMAT(Fields!MARGIN.Value,"0.00%;(0.00%)'')
158.00%
=FORMAT(Fields!MARGIN.Value,"0.000%;(0.000%)'')
158.00o%
=FORMAT(Fields!QTY.Value,"0.0%;(0.0%)'')
158.0%
=FORMAT(Fields!QTY.Value,"0%;(0%)'')
158%


12 comments:

  1. What happens to the data format when you export the data to excel sheet?

    ReplyDelete
  2. What happens to the data format when you export the data to excel sheet?

    ReplyDelete
  3. hi, help me, I modified in design of a sales Report, and want format number $1.158,00 but ever print $1,158.00

    ReplyDelete
  4. “Interesting  information, thanks for making these contributions.
    Great  information Glad to find your article.!This blog is really very informative.
    Thanks for sharing it with us
    bangalore escorts service 
     bangalore call girls 
    bangalore russian escorts 
    bangalore cheap escorts 
    bangalore escorts 

    ReplyDelete



  5. A relationship starts off with kissing first. If you are a good kisser the other person judges you there for your other intimacy skills. This is the situation of a hit or a miss, if you kissed her right then you can make or break the relationship you are going to start with her. I am the best kisser you will ever going to meet and if you do not know how to kiss properly then worry not I will teach you and at the same time will you will have the perfect kiss moment with me.


    call girls in Zirakpur

    ReplyDelete
  6. I am a sex goddess of everyone you know of and I will give my best to ride you like a sugar daddy. My sex drive is powerful than other average women you have had sex with. Once you sleep with me I promise there is no going back and you will plead me to stay for a little longer. I am the one and only for you and you can have the bestest of the bestest time in my company. Let's meet soon and love each other like crazy lovers who are unstoppable.


    Hyderabad escort service

    ReplyDelete
  7. If you are alone at your home and feel bored. You should come to our site and book a meeting with stunning call girls in Chandigarh. We do endorse you and we help you to book your meeting with charming escorts in Chandigarh.

    Call Girls in Chandigarh
    Call Girls in Zirakpur
    Call Girls in Mohali
    Call Girls in Panchkula

    ReplyDelete
  8. Ludhiana Escorts Service Enjoy your stay in Ludhiana with our bubbly and stunning independent escort in Ludhiana. We are known for providing the best service for escorts. We are also one of the most reputed and old escort agency in Ludhiana. Our escorts are highly professional and trained in the art of seduction to give you the most pleasurable night of your life.

    ReplyDelete

Popular Posts