Retaining Leading Zeros When Publishing Chris21 Reports
This is a common problem and will happen a lot, especially if you have employee numbers with leading zeros. For instance, if your employee number is 001234 and you run a report and publish to Excel, Excel will assume the number is 1234 and will drop the two leading zeros. In most cases you will want to retain these leading zeros so that the correct employee numbers appear in your reports. To do this you must choose the correct option when publishing Chris21 reports to Excel.
Run a report from Chris21 that has Employee Number as one of the report fields. From the main menu in the Report Designer, click Publish -> Spreadsheet. The following dialog box will appear:
By default the As text option will be selected. If you choose OK at this point your leading zeros will be removed. Instead, choose the second option Using the columns wizard and then click OK.
Leave the file type as Delimited and click Next.
Notice that the report columns have now been defined. You don’t have to do anything here, just click Next.
The dialog box shown above is where we set the report to retain leading zeros. Click the column that stores the Employee Number. In this case it is the first column and is already highlighted. Now in the Column data format section click Text. This will format the employee number columns to be text rather than numeric. This effectively ensures that Excel recognises all the data, including leading zeros. If there are any other columns where there may be leading zeros, click those and set them as Text also. Now click Finish.
Your report has now been published to Excel and you will notice that the leading zeros on the Employee Number have been retained.