Home / Business Intelligence / Leading Zeros in Xcelsius – Tech Tip

Leading Zeros in Xcelsius – Tech Tip

What can I do if Xcelsius is chopping off the leading zero’s in my order numbers?

This question comes up often when I’m teaching an Xcelsius training class.  Often enough that I’ve decided to discuss the solution here in a tech tip on our blog.

This scenario can happen with different data sources (XML, Live Office, Qaaws) when a number string is interpreted by Xcelsius to be numbers rather than string data.  As many people have tried, it is not enough to format the cells as text in the Excel spreadsheet of Xcelsius.  We need to use a trick to add those leading zeros back in. 

For example, say my Order numbers are all 3 digit numbers (for simplicity sake).  My order numbers are loaded into Column D, but then converted to their proper 3 digit format in Column F with the use of the Length formula in Excel, LEN.  By simply combining an if statement and the Length function to determine the length of a value, we can add the appropriate number of zero’s in front of a value, and voila, properly formatted Order numbers! 

See example below:

using the LEN function in Excel

About Roxanne

Roxanne Pittman is a Senior Business Intelligence Technical Consultant for InfoSol, providing consulting and training for BusinessObjects. She is a certified Business Objects Instructor for Business Objects, specializing in Web Intelligence, Desktop Intelligence, Dashboard Manager, Universe Design, Xcelsius Dashboards, Xcelsius Data Connectivity, and Knowledge Accelerator Customization. Roxanne has specialized in Xcelsius connected models, with extensive experience in using XML, Web Services, Live Office and InfoBurst-XDS and XDM to create dynamic dashboards. In addition to being a multi-certified instructor, Roxanne has experience working with Fortune 500 companies as a consultant in the deployment of business intelligence solutions.

Check Also

InfoSol Blog Tech Tip

Using an Input Control to Sort in Web Intelligence

In Design mode of Web Intelligence, you can change the sort order of columns in ...