Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Tips Database Forum Rss Feed

» Database Journal Home
» Database Articles
» Database Tutorials
MS Access
SQL Scripts & Samples
» Database Forum
» Slideshows
Free Newsletters:

News Via RSS Feed

Rss Feed

Database Journal |DBA Support |SQLCourse |SQLCourse2

Featured Database Articles


Posted Mar 29, 2004

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

By William Pearson

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.

Illustration 12: The Generic Query Designer Button

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

Illustration 13: Generic Query Designer - Initial View

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.

Illustration 14: The Add Table Button

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.

Illustration 15: Select the Sales Order Detail Table

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.

Illustration 16: Generic Query Designer - with New Table in Place

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.

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.

8.  Click the Run button, shown in Illustration 18, atop the Data tab.

Illustration 18: The Run Button

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.

MS SQL Archives

Latest Forum Threads
MS SQL Forum
Topic By Replies Updated
SQL 2005: SSIS: Error using SQL Server credentials poverty 3 August 17th, 07:43 AM
Need help changing table contents nkawtg 1 August 17th, 03:02 AM
SQL Server Memory confifuration bhosalenarayan 2 August 14th, 05:33 AM
SQL Server Primary Key and a Unique Key katty.jonh 2 July 25th, 10:36 AM