MSSQL Server Reporting Services: The Authoring Phase: Overview Part II - Page 4

Modifying the Underlying Dataset

We want to quickly modify our underlying dataset. Some of us are probably not anxious to return to the SQL query we inserted in Part I. Let's take a look at a simpler way to add a table - at least a more visual way - and at the same time, gain a little exposure to another way to design our underlying datasets.

The SQL Query we inserted early in Part I appears. We will add a table in the same way that we might have written the query from the start - and as we will do in many subsequent articles, within which we are looking at specific reporting techniques.

The Generic Query Designer button is shown in Illustration 12.

The Generic Query Designer loads, and appears as depicted in Illustration 13.

The Generic Query Designer is slightly reminiscent of the design feature in MS Access. This tool will likely become familiar to those of us who do not readily write direct SQL. I like to use it even when I could easily write the query, as it provides a good view of the layout of a data source, and an easy-to-understand visual idea of the relationships between the tables involved.

We will add the Sales Order Detail table, which contains data about transactions that have transpired with our Products. Although we could make this very sophisticated, for purposes of our overview we are really only looking at how to create subtotals and totals, and so we will not belabor the myriad other factors that would surely be involved in generating a production-quality report here, such as date ranges, filters and parameters, and other considerations. For purposes of our overview, we seek only to add a value with which we can practice summarization in the report.

The Add Table button is shown in Illustration 14.

The Add Table dialog appears, from whence we can select from any tables appearing in our Data Source (specified in the Data Connection definition we performed in Part I).

The Add Table dialog, with our selection, appears as shown in Illustration 15.

The Sales Order Detail table appears, complete with an automatic join in place, as shown in Illustration 16.

We might, of course, not want the joins to be placed for us when we add tables, but in this case, there is no need to make any adjustments.

This marks the column for inclusion in our dataset. We note that it appears immediately in the Grid pane underneath the Diagram pane.

The modified query executes, and we see the new results dataset appear in the Result pane below the Query pane. We can also easily discern the change in the SQL query, as I used "T's" to name the tables in the original query (a convention that many use within SQL), and left the standard name in place for the column that we added.

The Generic Query Designer, with all the various panes we are seeing, adds a dimensionality and utility that I have yet to see in the enterprise reporting tools that Reporting Services will soon replace in many organizations. The ease with which we can crystallize ideas in this little "lab" makes it highly useful - I sometimes work within it to test ideas when a standard "report" is not even in the cards. The functionality of MSSQL Server 2000's Query Analyzer appears to have been in the mind of the designers of this excellent tool; we will see far more of it in later articles.