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.

Right-clicking the Data Sources folder enables you to add a new source, which is a connection to the database - here, you name the server and database and the security credentials for the connection. If you have separate development/test and production databases, first specify the development/test database and later switch the connection to point to the production database once you come to deploy the model. You can have multiple Data Sources if you need to bring disparate databases together.

Once you have the Data Source defined, create one or more Data Source Views - the first step in simplifying the database for the end user. A View enables you to add or edit relationships between tables as seen by the user, without affecting the actual database structure, while you can also create calculated values and set logical keys. Here, you get to pick the physical tables and views from the database - when you've picked a table, click the Add Related Tables button to automatically select all tables related to this one, based on the database schema. (Note that if you have many-to-many relationships defined, Add Related Tables will only pick the intermediate, or link, tables, and to get the full relationships select all these link tables and click Add Related Tables again.)

You can add as many or as few tables as you want to a Data Source View, but the more tables you add the more complicated it becomes for a user to navigate. It's best to divide up the database into smaller chunks. There may be overlaps between Data Source Views, with some tables appearing in more than one view. Aim to include all the tables required for reporting on this aspect of the data, but no irrelevant ones that would just be "noise" and confuse the user.

When you open a Data Source View, you'll see a diagram of the tables and their columns laid out with relationship lines drawn between them. If there are any relationships missing, add them by dragging the mouse from a column in one table to a column in another - drag in the direction of the relationship, from the foreign key to the primary key, which is towards the parent table. A dialog box pops up to let you confirm the relationship. You can delete any relationships that aren't necessary or which may confuse the user: there may be triangular relationships in a database, or "pig's ear" relationships whereby a table is related to itself, which you don't want your users to be bothered with, so just click on the relationship line and press Delete.

In a Data Source View, you can also specify friendlier names and descriptions for tables and columns, expanding any abbreviations and making names consistent where there may have been historical variations. For instance, columns that had been called AckNo, Ack No, Ack_No, AcknowledgementNo, and AckID in various different tables might all be standardised to one common spelling as their "friendly name" - remember, you're not changing the underlying structure of the database, just a view for reporting purposes.

Having created a Data Source View, you can create a Report Model. In Solution Explorer, right-click on the Report Models folder and choose Add New Report Model, which starts the wizard that will do most of the work for you by further simplifying the data creating entities and attributes from the tables and columns. It collapses related tables into much simpler structures, so the user doesn't have to know about lookup tables; adds aggregates such as Sum, Average, Min and Max to numeric values; and automatically expands dates to expose Day, Month, Quarter and Year separately. One of the steps in the wizard lets you control which parts of this processing you want done, but the defaults are probably adequate for most instances and any extra aggregates you don't need can be easily deleted later.