how to get 00.00 format for percent column

Question

how to get 00.00 format for percent column

KoldCoffee

SSCoach

Points: 18971
More actions
August 13, 2014 at 10:38 am

#298092

I have a percent column that displays like this: 0.51%, 0.36%
Desired format: 51.00%, 36.00%
I am using this formula =Format(Fields!CUL1.Value,"F2")
what do I need to change to get the desired format?

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 1

KoldCoffee (8/13/2014)
I have a percent column that displays like this: 0.51%, 0.36%
Desired format: 51.00%, 36.00%
I am using this formula =Format(Fields!CUL1.Value,"F2")
what do I need to change to get the desired format?

Multiply the underlying display formula by 100.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

KoldCoffee SSCoach Points: 18971 More actions · Answer 2

i did not think of that. but I tried

=Format((Fields!CUL1.Value) * 100,"F2")

and it didn't work.

doug.brown SSCertifiable Points: 5607 More actions · Answer 3

Instead of using the expression, what happens if you just change the field's Format to P (in field properties)? That's what I've always used for percentage fields. It automatically changes 0.45 to 45%.

SQLRNNR SSC Guru Points: 281334 More actions · Answer 4

KoldCoffee (8/13/2014)
i did not think of that. but I tried
=Format((Fields!CUL1.Value) * 100,"F2")
and it didn't work.

Multiply by 100 in the source data instead of the format.

It also helps to understand what your data looks like. F2 should automatically format as a percent.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

KoldCoffee SSCoach Points: 18971 More actions · Answer 5

if by P you mean Percentage, as in right click the cell in design mode, select Text Properties, Number,and select Percentage, it's already done. (one of the first things I did). I indicated 2 decimal places and the sample is 12.35% in the dialogue.

I can't multiply by 100 in the source (or do the calculation in the source) because I found that doing a calculation on this raw data in the dataset prevents me from retrieving it. So, I bring in the data raw, and have SSRS do the calculation. It's actually like this:

=Format(countdistinct(Fields!property_conversion_lead_id.Value, "UniqueLeadsQuery") / (countdistinct(Fields!property_conversion_lead_id.Value, "UniqueLeadsQuery") + (CountDistinct(Fields!move_lead_id.Value, "UniqueLeadsQuery") - CountDistinct(Fields!property_conversion_lead_id.Value, "UniqueLeadsQuery"))),"F2") & " %"

SQLRNNR SSC Guru Points: 281334 More actions · Answer 6

KoldCoffee (8/14/2014)
I can't multiply by 100 in the source (or do the calculation in the source) because I found that doing a calculation on this raw data in the dataset prevents me from retrieving it.

The problem basically boils down to your dataset. The query producing your dataset is affecting your formatting somehow. The best approach is to fix the dataset. If performing a calculation on the dataset prevents you from returning any data - then there is an error in how that was done. In that case, as well as the current, it boils down to fixing the dataset.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events