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:
- Various date formats
- Currency with 2 decimal places
- Number with 4 decimal places
- Percentage with decimal places
- 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%
|
good post
ReplyDeleteWhat happens to the data format when you export the data to excel sheet?
ReplyDeleteWhat happens to the data format when you export the data to excel sheet?
ReplyDeletehi, help me, I modified in design of a sales Report, and want format number $1.158,00 but ever print $1,158.00
ReplyDeleteThank You for providing us with such an insightful information through this blog.
ReplyDeleteTop 10 java training institute in coimbatore |Java Training institute Coimbatore | software Testing Course in Coimbatore | Selenium Training Coimbatore | Automation Testing | Selenium Training institute Coimbatore | Best Selenium Training institute Coimbatore | Online selenium Training institute Coimbatore | software Testing Course in Coimbatore | Best Online software testing training institutes in coimbatore | Best software Testing Training Course in Coimbatore | Online software testing course in coimbatore | Manual and Automation Testing Training in saravanampatti