Search code examples
reporting-servicesssrs-expression

how do I change a lookup value to whole a number in ssrs?

How do I format a number with 2 decimal places to a whole number?

I used the Lookup function to get a result, but formatting decimal to whole number does not work for this value. I did Text box properties -> number -> whole number doesn't work for one of my value. Also customize number to #,### but its not changing anything.

How do I make this value display as a whole number?

Solution

  • You may be confusing how the number is displayed (which can be controlled using text box properties) and it's actual value. It sounds like the value returned from the lookup is not a number, it might be a string/text value instead which would explain why it was not affected by number formatting.

    One option is to convert the value to an integer (whole number) in the lookup expression itself, using a function to convert the value: CInt()

    For example if your expression currently looks something like this:

    =Lookup(Fields!SomeField.Value, Fields!SomeDatasetField1.Value, Fields!SomeDatasetField2.Value, "SomeDataset")

    then you can change it to:

    =CInt(Lookup(Fields!SomeField.Value, Fields!SomeDatasetField1.Value, Fields!SomeDatasetField2.Value, "SomeDataset"))

    Or if you want to keep the original value, but just change how it is displayed then convert to a decimal value instead:

    =CDec(Lookup(Fields!SomeField.Value, Fields!SomeDatasetField1.Value, Fields!SomeDatasetField2.Value, "SomeDataset"))

    and then use the text box formatting options to control the displayed format.