Gallery

Introducing SQL reports

Posted on 6 Nov 2008 at 15:48

End users' reporting requirements are ever-changing. Simon Jones takes you through the process of building a report model to answer every demand.

Step through the wizard and at the end you get to run the Model Generation routine that creates all the entities, attributes date variations and aggregates on all the tables in the Data Source View. If you have a lot of tables and columns this may take a minute or more, but eventually you'll be presented with the Report Model in a simple editor pane with a list of entities down the left-hand side. All the attributes of each entity appear in the middle, and the properties of a selected object appear in the Properties tool, usually docked on the right.

So far it's been wizards all the way with not much skill involved, but now you're on your own and it can look daunting. But actually it's fairly easy once you get to know your way around. First, set the Culture property for your Model, from the default of "English (United States)", which means that money amounts print in dollars and all dates print arse backwards. Next, visit each of the entities (tables) in turn and check their properties. Each has a Name property (singular) and a CollectionName property (plural). You should check that these singular and plurals are correct and add spaces and capitalisation where the wizard didn't get them quite right. Next, add a description of the entity if it isn't 100% obvious from its name, and adjust the other properties to match: of particular interest are DefaultAggregateAttributes, DefaultDetailAttributes and SortAttributes.

The SortAttributes property lets you define the sort order for this entity, and if you want the data sorted in a format other than alphabetically or numerically by first attribute, you'll need to set this accordingly. Many lookup tables contain a DisplayOrder column so that the system supervisor, who maintains the list of valid lookup values, can control the order in which they're displayed: for example, you might want regions shown in the order: Scotland, Northern Ireland, Northern England, Midlands, East Anglia, Western England, Southern England. Main data tables, such as Purchase Orders, you might want sorted by creation date.

The DefaultDetailAttributes property lets you specify which attributes are included in a report if the user just drags this entity to the report design surface. For simple lookup tables, this may just be one attribute - the name or description column - whereas for main data tables it may be several attributes, so for a purchase order you might select ID, date, supplier's name and total amount. The DefaultAggregateAttributes property does the same thing, but is used when a user drags the entity onto a report to give summaries based on a one-to-many relationship with it - you could choose a count of the rows, or a total of a monetary amount. For purchase orders, you'd probably choose both a count of rows and the sum of the purchase order values.

The IdentifyingAttributes property shows which attributes should be shown to the user in a list or dropdown list to be able to identify rows, and is used for setting report parameters based on this entity. The InstanceSelection property says whether to use a list, a dropdown or a filtered list for such a selection. Finally, the IsLookup property should be set for any entity that's a simple lookup table, which reduces the complexity of the data model seen by the end user by hiding all those choices that apply to main data tables, but which are irrelevant to lookup tables.

Once you've looked at the properties of an entity take a look at its attributes, which are derived from the columns of data in the underlying table: hide columns such as ID numbers or GUIDs where the user doesn't need to be concerned about them; delete unwanted aggregate attributes such as Total, Average, Min or Max where they don't make any sense; rename attributes by supplying different singular and plural names if necessary using the Linguistics.SingularName and Linguistics.PluralName properties. You can specify default numeric formats, such as "c02" for currency with two decimal places or "p04" for a percentage with four decimal places, and format Boolean values by specifying their true and false names, separated by a semicolon. For example, a Boolean attribute called "Complete" might have the format "Complete;" which would print the word "Complete" when true and nothing at all when false. Finally, you can drag the entities and attributes up and down to re-order them into a more logical arrangement.