Welcome to Joel Lipman .Com

Preparing Content...


Loading...

Our Website Development Notes

We hope this helps!

Articles // Microsoft // MS Reporting Services

SSRS Zero Padding

Thursday, 3rd September 2015
191,361 Reads
Quick Reminder
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.

The Situation
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.

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/.

The Padding
Found this in a forum so thought I'd better make a note:
  1. Right("00000" & Fields!ERROR_CODE.Value.ToString, 5)  
  2.  
  3. -- "Right()" to say extract text from the right  
  4. -- ".ToString" because it's likely you're doing this to a number and numbers just get rounded up without the prefixing zeros  
  5. -- "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.
  1. =IIF(  
  2. Fields!ERROR_CODE.Value="SUCCESS",  
  3. "Success",  
  4. Right(  
  5. "00000" & Replace(  
  6. Fields!ERROR_CODE.Value.ToString,  
  7. "-",  
  8. ""),  
  9. 5)  
  10. )



Recent Comments

Gravatar for Art
Oracle: order by subquery missing right parenthesis
Hello, what if I would like to add rownum to that code, but in my case it's all is subquery? How can I do this ?

20 Jan


Gravatar for Robert
CharIndex Reverse - find occurrence starting from end of string in TSQL
Very helpful thanks! This worked for me as well, and is a little shorter. Not sure if there are drawbacks. SELECT RIGHT(@Haystack,CHARINDEX(@Delimiter,REVERSE(@Haystack))-LEN(@Delimiter))

29 Dec


Gravatar for Translation

27 Dec


Gravatar for Tibbe
JComments 2.3.0 with ReCaptcha in Joomla 2.5.x
Hi there, This looks like a great solution to get rid of spam comments. How to integrate this in Joomla 3.x? Step 2 I did in settings.xml, but no result in frontend. Kind Regards, Tibbe

16 Dec


Gravatar for sach|n
SSRS Repeat Headers in PDF Report
Yes its working for me..thnx Joel.

7 Oct