How do I format numbers in string to display with two decimal places

Question

0.00/5 (No votes)

See more:

SSRS-2008

I have a field that is a varchar datatype that passes both text and numbers into my RDL.

I need to display the numbers (often entered as 00.00000) with two decimal places while displaying any text in the field as text.

I used an IIF statement to format the field but it either displays D, 00.00, ##.##, or error in the fields.

Here are examples of my attempts:
IIF(Fields.MyField.Value="Number", Format(Fields!MyValue.Value, "D", cstr(Fields!MyValue.Value)) **This displayed Ds in the field when I attempted to format the field as a Decimal. Using a lower case d did not work.

IIF(Fields.MyField.Value"Number", Format(Fields!MyValue.Value, "00.00", cstr(Fields!MyValue.Value)) **This displayed 00.00. Removing the double quotes displayed zeros instead of the actual string values.

How can I get numbers that are in this nvarchar field to display with two decimal placed while also displaying actual text as text? This should be very simple to do!

What I have tried:

My attempts are described in my question.

Posted 9-Sep-16 6:18am

Member 10379103

Updated 9-Sep-16 7:18am

Maciej Los

v2

Add a Solution

Comments

Richard Deeming 9-Sep-16 13:05pm

If the field MyField contains the literal string "Number", how do you think that string is going to be converted to a number?

Member 10379103 9-Sep-16 13:11pm

Richard,

I accidentally typed the wrong field! The actual field that contains the word "Number" is Category so the statement should read "IIF(Fields.Category.Value="Number", Format(Fields!MyValue.Value),"00.00"), Fields!MyValue.Value)

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

David_Wimbley · Answer 1 · 2016-09-09T06:41:00

Solution 1

If you are able to, i would alter the data feed into your report and handle it that way.

See example t-sql below.

SQL

DECLARE @MoneyValue NVARCHAR(200) = '40.403285923';
DECLARE @MoneyValue2 NVARCHAR(200) = '49';
DECLARE @TextValue NVARCHAR(200) ='im not a number';

--2 decimal places
SELECT CAST(ROUND(CAST(@MoneyValue AS FLOAT),2) AS MONEY)

--Check If number, convert text to have 2 decimal places and then to nvarchar, if not a number, then display as is
SELECT CASE WHEN ISNUMERIC(@TextValue ) = 1 THEN CAST(CAST(ROUND(CAST(@TextValue AS FLOAT), 2) AS MONEY) AS NVARCHAR(200)) ELSE @TextValue END

Posted 9-Sep-16 6:41am

David_Wimbley

Comments

Member 10379103 9-Sep-16 13:00pm

Dave,

Unfortunately, I don't have that option. I am almost there, though - this works for the number formatting but is displaying #Error for the string text or blank:

=IIF(Fields!MyValue.Value="Number", Format(CDbl(Fields!MyValue.Value),"00.00"), Fields!MyValue.Value)

If I can just resolve the #Error that displays in the last part of m condition, I may finally have this resolved.

Any ideas?

David_Wimbley 9-Sep-16 13:12pm

My guess is that your error stems from the field thinking its a double but your trying to treat it as a double and a nvarchar. Basically, trying to take on two types when it can only have one.

If you convert your text to a double, add turn it to money, then convert back to text, that would work

Basically same idea in the query i posted. See how it convert text to float, then money (To get 2 decimals), then back to text. Thats what you need to do here, I think you converted the CDbl back to text it would not print #Error in situations where your value is not a number.

Maciej Los · Answer 2 · 2016-09-09T07:18:00

Solution 2

Seems, you need to check if value in MyField field is numeric value. If it's, you need to format it:
=IIF(IsNumeric(MyField), Format(MyField, "##0.00"), MyField)

For further details, please see:
Expression Examples (Report Builder and SSRS)[^]
IsNumeric Function (Visual Basic)[^]

Posted 9-Sep-16 7:18am

Maciej Los