Modifying the Underlying Dataset
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.
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.
Click the Generic
Query Designer button atop the Data tab.
Query Designer button is shown in Illustration 12.
Illustration 12: The
Generic Query Designer Button
Query Designer loads, and appears as depicted in Illustration 13.
Illustration 13: Generic
Query Designer - Initial View
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.
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.
Click the Add
Table button atop the Data tab.
Table button is shown in Illustration 14.
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).
Click the Sales Order Detail table in the dialog to select it.
Table dialog, with our selection, appears as shown in Illustration 15.
Illustration 15: Select
the Sales Order Detail Table
Click Add to
add the selected table.
to close the dialog and return to the Generic Query Designer.
Order Detail table appears, complete with an automatic join in place, as
shown in Illustration 16.
Illustration 16: Generic
Query Designer - with New Table in Place
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.
the length of the Sales Order Detail table, and click the checkbox to
the left of the LineTotal column, as depicted in Illustration 17.
Illustration 17: Selecting
a Table Column for Inclusion in the Dataset
This marks the column
for inclusion in our dataset. We note that it appears immediately in the Grid
pane underneath the Diagram pane.
Click the Run
button, shown in Illustration 18, atop the Data tab.
Illustration 18: The Run
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.
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