Sunday, December 21, 2014
   
Text Size
Login

SSRS Zero Padding

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.       ) 
Add Comment

Name:

Email:

Website:

Message:


Latest Posts

  • Basic Android App using Google Maps and Current Location

    • Wed 17-Dec-14
      Is that how it's spelt? setContentVie( "View". If not try going to build and "clean project". Also ...
      Joel L.  
    • Tue 16-Dec-14
      I have problem in "cannot resolved R" " setContentVie(R .layout.activit y_maps);" :cry: How to solve ...
      LucasR
  • Migrate Joomla! 1.5.x to 2.5.x+

    • Thu 18-Dec-14
      Interesting report LLiSEIL! I'll definitely have to review it when Joomla 3.5 comes out. Error 1: is ...
      Joel L.  
    • Tue 16-Dec-14
      * Categories: - duplicates, I suppose from the multiple times I launched the script. - love the notes ...
      LLiSEIL  
    • Tue 16-Dec-14
      SQL ERror 3 > -- Step 2) Import articles from J15 and insert as articles in J30 INSERT INTO `newbase_j25`.d ...
      LLiSEIL