Formatting integer in SSRS with leading zeros

Question

I would like to format as 10 digits in total regardless of the length of actual data, filling with leading zeros. Example actual data is 123456, Then my result would be 0000123456. But the actual data has the dynamic length, means we can't guess all the time. Can I achieve in SSRS? Instead of formatting in SQL?

Answer 1

In the report, set fields formating to as many zeros as the digits you want to appear. For example for 10 digits the format code should be "0000000000"

Answer 2

Doing a dynamic padding of leading zeroes can be obtained in multiple ways. Either you can directly do in the database itself then display the same in RS or get the raw data from DB and then do the padding in RS. For your reference I have shown both the ways.

Sample data to be used in RS -

declare @t table(i varchar(10))
insert into @t values (1),(12),(123),(1234),(12345),(123456),(1234567),(12345678),(123456789),(1234567890)
select i,RIGHT('0000000000'+ISNULL(i,''),10) 'ipadded' from @t

Expression used in RS for padding -

=Right("0000000000" & Fields!i.Value.ToString, 10)

Images -