MSSQL Server Reporting Services: The Authoring Phase: Overview Part II - Page 4
March 29, 2004
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.
1. Select the Data tab.
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.
2. Click the Generic Query Designer button atop the Data tab.
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.
3. Click the Add Table button atop the Data tab.
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).
4. Click the Sales Order Detail table in the dialog to select it.
The Add Table dialog, with our selection, appears as shown in Illustration 15.
5. Click Add to add the selected table.
6. Click Close to close the dialog and return to the Generic Query Designer.
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.
7. Scroll down the length of the Sales Order Detail table, and click the checkbox to the left of the LineTotal column, as depicted in Illustration 17.
This marks the column for inclusion in our dataset. We note that it appears immediately in the Grid pane underneath the Diagram pane.
8. Click the Run button, shown in Illustration 18, atop the Data tab.
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.