CODEWITHSUNDEEP
reporting-services
RotatingWheel
RotatingWheel

Reputation: 1041

Number Conversion in SSRS

I have a requirement where I convert an ID (decimal example, 536879311820 ) and display in SSRS table after conversion using expression builder as shown in the figure.enter image description here

The rule for conversion is like this:

So 536879311820 should be displayed in report as 99820. I tried up to the following expression in the expression builder, 

=Right(HEX(Fields!PersonId.Value), 8)

Since the result of this expression is hexadecimal number as string, I can't find any function in common functions category in expression builder.

Upvotes: 1

Views: 3685

Answers (2)

StevenWhite
StevenWhite

Reputation: 6034

You have to format the string as hex after you get the last 8 characters:

=CDec("&H" + Right(Hex(536879311820), 8))

Upvotes: 2

BIDeveloper
BIDeveloper

Reputation: 2638

This is easier done in SQL. Here is some code which will do it for you. I've included two additional columns so you can see how the final column was computed:

SELECT  CAST(123456789 AS VARBINARY(8)) HexValue, CONVERT(VARCHAR(MAX), CAST(123456789 AS VARBINARY(8)),2) AS VARCHARHEX, RIGHT(CONVERT(VARCHAR(MAX), CAST(123456789 AS VARBINARY(8)),2),5) AS Last5Chars

Upvotes: 0

Related Questions