CODEWITHSUNDEEP
reporting-services
DaBeau96
DaBeau96

Reputation: 548

SSRS Report Shows #Error When Converting Number to Currency

I am trying to get the report to show the purchase price which the user enters in a parameter. If the user doesn't enter anything it will say "Undisclosed" otherwise I want it to show the purchase price as currency. I have tried the following:

=IIF(Parameters!PurchasePrice.Value = "", "Undisclosed", Cstr(Format(Parameters!PurchasePrice.Value, "C")))

=IIF(Parameters!PurchasePrice.Value = "", "Undisclosed", Format(Parameters!PurchasePrice.Value, "C"))

=IIF(Parameters!PurchasePrice.Value = "", "Undiscloded", FormatCurrency(Parameters!PurchasePrice.Value,0))

=IIF(Parameters!PurchasePrice.Value = "", "Undiscloded", FormatNumber(Parameters!PurchasePrice.Value,0))

I can get "Undisclosed" to appear but every time I enter a number it shows #Error

Upvotes: 0

Views: 461

Answers (2)

Matthew John Ang
Matthew John Ang

Reputation: 71

You can try the following solution:

 =IIF(IsNumeric(Parameters!PurchasePrice.Value), Format(Val(Parameters!PurchasePrice.Value), "C"), "Undisclosed")

Upvotes: 1

Alan Schofield
Alan Schofield

Reputation: 21703

I would approach this slightly differently. This will avoid SSRS trying to format a non numeric to a currency. IIF will evaluate both the true and false outcomes even though only one result can be true for each instance.

I would set the textbox that shows the purchase price up as follow...

Set the Value expression to be

=VAL(Parameters!PurchasePrice.Value)

This will return a zero if the parameter value is left as blank/empty string

Then set the Format property of the textbox to

$#.00;-$#.00;Undisclosed

Assuming you want $ as the currency symbol. This format string will format the number to two decimals for positive and negative numbers and prefix the $ symbol but for zero values, it will show the work "Undisclosed"

Upvotes: 0

Related Questions