I didn't want to do this at the database level, mainly because it meant modifying the SQL query. The zero padding would need to be applicable within an MDX query.
We have a database using Oracle 10g, and a SQL Server Reporting Services v2008 R2 environment. My use for this was when displaying an audit log displaying the oracle errors.
An Oracle error usually returns in the format of -12345. If we want to look them up the error is ORA-12345. Unfortunately Oracle also returns errors of less than 10000 so ORA-00201 would actually be returned as "-201". As I wanted a link so that the user can just click on this link and it would take them to http://ora-00201.ora-code.com/.
Found this in a forum so thought I'd better make a note:
- Right("00000" & Fields!ERROR_CODE.Value.ToString, 5)
- -- "Right()" to say extract text from the right
- -- ".ToString" because it's likely you're doing this to a number and numbers just get rounded up without the prefixing zeros
- -- "00000" because we don't expect strings to exceed 5 characters.
The Oracle Error Value
Returning just the 5 digits of the oracle error code without the minus/hyphen in front.
- "00000" & Replace(