Some DBAs would cringe at what I’m about to tell you, but I often use T-SQL to format my data prior to presenting it in a report. Many experts feel that data formatting should always be done in the presentation layer. There are a couple of reasons why I do this.
- I re-use as much code as I can. I create base templates for most of the main modules in M2M (Sales, Purchasing, Accts Payable, Etc) and these templates include the formatting code. This results in a professional, standardized image to my reports.
- I report using many different programs as my presentation layer and I want to re-use my scripts for all of them.
Consider the following script:
Select fsono, fenumber, frelease, fpartno from m2mdata50..sorels |
The first few rows of data from the M2M educational database return:
000001 001 000 CP2080 000001 002 000 FF204W 000002 001 000 WF201R 000002 001 001 WF201R 000002 001 002 WF201R 000002 001 003 WF201R 000002 001 004 WF201R |
Do you notice the distraction of the leading zeros? When searching for a sales order number, does anyone really enter “000003”? These are a problem in most of the modules of M2M. As I mentioned before, many DBAs feel that you should remove them via formatting in the presentation program of your choice. However, I regularly create reports in SQL Reporting Services, Crystal Reports, Visual FoxPro, and Excel. This means that if I don’t pre-format with T-SQL, I will have to adapt the presentation later manually, which and the technique will be different for each reporting media. This is time consuming and error prone.
As always, please read my standard disclaimer before implementing any code from my site. Using T-SQL, you could strip the preceding zeros like this:
select substring(fsono, patindex('%[^0]%',fsono), 10) SONo, substring(fenumber, patindex('%[^0]%',fenumber), 10) SOItemNo, substring(frelease, patindex('%[^0]%',frelease), 10) Release, fpartno PartNo from m2mdata50..sorels |
Which yields this:
1 1 000 CP2080 1 2 000 FF204W 2 1 000 WF201R 2 1 1 WF201R 2 1 2 WF201R 2 1 3 WF201R 2 1 4 WF201R |
That works, but it’s too wordy for me. A better option is to use a User Defined Function (UDF) to clean up the data. I’ve demonstrated how to write these in the past.
Consider the following:
CREATE FUNCTION RemoveZeros(@ZeroField VARCHAR(25)) RETURNS VARCHAR(12) AS BEGIN DECLARE @ZerosRemoved VARCHAR(12) DECLARE @Length INT SET @length = len(@ZeroField) SELECT @ZerosRemoved = CASE When @ZeroField = '000' then 'M' WHEN @Length > 0 THEN substring( @ZeroField, patindex( '%[^0]%', @ZeroField), @Length) ELSE '' END RETURN @ZerosRemoved END |
Which produces output like this:
1 1 M CP2080 1 2 M FF204W 2 1 M WF201R 2 1 1 WF201R 2 1 2 WF201R 2 1 3 WF201R 2 1 4 WF201R |
Can you see the possibilities? You can use this UDF in other modules such as shipping and invoicing as well. It works particularly well in shipping because the item numbers are 6 digits. Have any of you ever had a shipper with a million line items on it?
Select fshipno, fitemno, fpartno from m2mdata50..shitem 000001 000001 CP2080 000002 000001 FF204W 000003 000001 L20100 000003 000002 L20200 000004 000001 EF120100P |
Becomes:
Select dbo.removezeros(fshipno) ShipNo, dbo.removezeros(fitemno) ItemNo, fpartno PartNo from m2mdata50..shitem 1 1 CP2080 2 1 FF204W 3 1 L20100 3 2 L20200 4 1 EF120100P |
The performance impact is negligible, and the both the script and the output are well formatted.
What other UDF’s do you think would be useful for M2M reporting?
Nice tip. I’m just wondering how many of your users actually write sql code any more complext than select * from.
Great Idea, taking your advise and from now on I’m going to use it!!
Thank you,
Hi David. Actually, I work the other way around; I like the preceeding zero’s as it looks like what is displayed in M2M screens and if you are data mining, then you’d want to see all the charachters in the field.
Which poses a problem when I copy the data results to Excel and loose the preceding zero’s and when date fields show “00:00” in Excel, I have to go around formatting the columns.
Thanks for the tip though!
Raj