The day before yesterday, one of my internal customers asked for a listing of an external customer’s orders. Orders had to be selected based on dates, various subsidiary customer accounts, order statuses, etc. and totalled along specific lines. Our external customer wanted us to provide this to them because their own systems professionals couldn’t, or wouldn’t. My internal customer said he needed it quick and that meant he really, really needed it the next day. We handed it to him within a few minutes. My customer beamed how happy he was that we could promise these reports and deliver. It’s a competitive advantage. When our external customers evaluate who to procure services from, this level of customer service may be the convincing factor that tips their business our way.
Last post I talked about how to take our reporting to the next level using Excel Tables. Today, I’m introducing that table – the “Fields” table. At right is a thumbnail of the table. If you click it, you can see it in its entirety (It’s too wide for this blog’s format). It contains most of the parameters necessary to describe data elements for a data extracts. Here are descriptions of the various columns:
|Table||Name of the database table that contains the field (see “Field” column)|
|Alias||An abbreviation of the table name. Usually this is a one character tag like O for ORDERS, or C for CUSTOMERS. This shortens the select statement (improving readability) while identifying which field comes from which table.|
|Field||Name of the database field/column in the table (see “Table” column)|
|Key||Identifies fields/columns as part of the table’s unique key|
|Heading||Column heading for the field displayed in the worksheet.|
|S.Ord.||(Sort Order) Identifies which fields are sorted on first, second, third, etc. There is no limit to the number of sort fields.|
|S.Seq.||(Sort Sequence) Identifies “Ascending” or “Descending” sort sequence for the column.|
|Freeze||Identifies which field to freeze Excel’s window pane on. This is usefull for keeping column heading and key values displayed at all times, no matter which cell you’re in.|
|Sel.Func.||(Selection Clause Function) Identifies an SQL formula to use on the field. Use a “?” to identify where the Field appears in the formula.|
|Hide||Use “H” to hide the column completely. Use “C” to “conditionally” grey out repetitive values. This is useful for extracts that contain many records for the same key value. Duplicate key values can be greyed out leaving the first value in the list to stand out.|
|Width||Restricts a column to a specific width. This is useful for improving readibility when long text fields are used.|
|Format||For numeric fields you can specify any numeric format understood by Excel. For character fields you can specify alignment: L = Left, R=Right, C=Centered, W=Wrap Text.|
|Default||For reports, this would be better labeled as “Excel Formula”. Use this to embed calculated columns into your result.|
|Input||Not used for reports. For update spreadsheets this identifies the conditions under which this field may be changed: A=Adding records, C=Changing existing records or adding records, P=By program control only (users are locked from making any changes)|
|Required||Not used for reports. Identifies fields that cannot be left blank. A common synonym is “mandatory.”|
|V.Type||(Validation Type) Not used for reports. Identifies rules to apply to insure values entered are valid.|
|V.Tbl||(Validation Table) Not used for reports. Identifies an Excel or database table to use to validate values.|
|Upd. Func.||(Update Function) Not used for reports. Identifies an SQL function to use when updating values in tables|
|Notes||Use this the same way you would use program comments.|
I put this table in a worksheet called “Tables”. This table is a powerful RAD (Rapid Application Development) type tool. Once I get the basics of the report up, I can then bring in my customer who will almost always want to change the output based on either something they didn’t communicate, or based on a realization they had when they saw the results of what they asked for. I no longer dread those moments. I actually look forward to them. Because while we’re talking, I see the excitement on their faces as within seconds their desires are realized – simply by adding or adjusting a row in this table. I can easily add columns to their results by adding rows to this table – without changing my code. I can change the initial sort to anything they want – without changing my code. I can change column headings, add formats, and even add calculated columns – all without changing any of my code – and all within a matter of seconds. My customers are thrilled.
In the next post we will start adding code to take advantage of this table in order to generate much of our SQL Select statement for us.