Querying Data

After a connection is established, your next step is to query the data source for the data intended. For most relational databases, this involves executing some type of SQL query against the catalog. In the case of Analysis Services, you use Multidimensional Expressions (MDX) queries, and for Data Mining, you use Data Mining Extensions (DMX) queries.

The Graphical Query Designer that comes with Report Designer aids developers in developing queries in any of the preceding languages. For more advanced queries, or in cases when the data source is not an RDBMS, the Generic Query Designer can be used.

Graphical Query Designer

The Graphical Query Designer is a tool to aid in the development of the query. Behind the scenes, it connects to the data store to pull tables and views. All you have to do is right-click on the top pane to add the table you want and select the columns. If the database has referential integrity, the Graphical Query Designer picks that up as well, and makes the necessary joins automatically. You can also join the tables by dragging columns from one table to the other.

Table 9.1 outlines the four panes in the Graphical Query Designer.

Table 9.1. Panes of Graphical Query Designer

Pane	Function
Diagram	Displays graphic representations of the tables in the query. Use this pane to select fields and define relationships between tables.
Grid	Displays a list of fields returned by the query. Use this pane to define aliases, sorting, filtering, grouping, and parameters.
SQL	Displays the Transact-SQL query represented by the Diagram and Grid panes. Use this pane to write or update a query using Transact-SQL query language.
Result	Displays the results of the query. To run the query, right-click in any pane, and then click Run.

Changing the diagram or grid affects the SQL and Results panes. For example, when you add a table to the diagram, it actually adds the table to the SQL query as it is being generated. This is a good way for users to actually learn SQL. Figure 9.1 shows the Graphical Query Designer.

Figure 9.1. Graphical Query Designer.

Generic Query Designer

The Generic Query Designer is open ended. It is for times when you need more flexibility than the Graphical Query Designer allows. This is especially good for running multiple SQL statements to perform some preprocessing, or dynamic statements based on parameters or custom code. The Generic Query Designer is shown in Figure 9.2.

Figure 9.2. Generic Query Designer.

The data set contains a couple of properties of which developers should be mindful. They are as follows :

The Name of the data set
The Data Source or a pointer to a shared data source
The Query String, which represents a query that retrieves data from the data source
The Fields collections, which includes fields retrieved by the query and calculated fields
The Query parameters (a parameter in a query string, such as SELECT * FROM Address WHERE City = @City ) and Dataset/Parameters, which are used to limit selected data and must have matching parameters in each for proper report processing
The Filters collection, which further filters result of the query inside of a Report Server after a data set returns data

Command Type

Command type is similar to the ADO.NET command type. It indicates the type of query that is contained in the query string and corresponding CommandText element of RDL. There are three values: TableDirect , Text , and Stored Procedure . Text provides for execution of a free-form (but, of course, valid) dynamic query. Stored Procedure corresponds to a stored procedure call. Finally TableDirect indicates that the value is the name of a table from which to retrieve data. All data from the table is returned.

Note

Not all the providers support all three values. For example, whereas OLEDB supports the TableDirect command type, the Microsoft SQL Server (SqlClient) provider does not. Thus, TableDirect is not shown as one of the choices for the Microsoft SQL Server (SqlClient) provider. Of course, SELECT * FROM <Table> would work just the same in the case of either provider.

Queries and Data Parameters

Most queries and stored procedures require inputting some type of parameter to return data. Take the following example:

 Select * from Test_Table where Id = @Id

This is an example of a parameterized SQL on a fictitious table. Input parameters to stored procedures are another good example. So how does the data set give us this functionality?

The answer is in the Parameters collection. To be clear, this is separate from report parameters. Query parameters are used during the processing of the query, or select statement. If a query parameter is specified, a value must be given to the parameter for the query to process. A report parameter is used during report processing to show different aspects of the data that can include, but are not limited to, query processing. If a T-SQL query includes query parameters, the parameters are created in a report automatically, and the values specified in the report parameters are passed along to the query parameter.

In the case of stored procedures, they are usually the inputs to them; see Figure 9.3. For plain SQL, they could be any variable.

Figure 9.3. An example of how the designer prompts you for the parameters.

The Report Designer automatically creates a report parameter with the same name as the query parameter. If there is already a parameter with the same name, it associates the two parameters. Figure 9.4 shows the association and where it is located in the UI.

Figure 9.4. SQL and Report Parameter Association.

Stored procedures can be executed by changing the command type to Stored Procedure and entering the SQL statement. There is no reason for the exec clause. If a stored procedure has default values, that value can be passed through to the procedure by passing the query parameter the keyword DEFAULT .

The Timeout property sets a limit as to the amount of time the query can run. If left empty, the query can run indefinitely.

Querying XML ^{NEW IN 2005}

The ability to directly query XML as a data source is a new feature for SSRS 2005. Because querying XML is a little different than querying, it is worth noting some special requirements that are unique to using XML as a data source. They are as follows:

Set XML as the data source type.
Use a connection string that points to either the URL of a web services, web-based application, or XML document. XML documents from inside SQL Server cannot be used. Instead use xquery or xpath as a part of the query with SQL Server as the data source type.
Use either Windows integrated security or no credentials. No other type of credentials are supported.
Define the XML query using either element path , query element, or leave it empty.

The Generic Query Designer is the only way to create queries against XML. The Graphical Query Designer will not work. The good news is that developers can specify one of three types, as shown in Table 9.2.

Table 9.2. XML Query Types

XML Query Type

Description and Syntax

Element path

The element path specifies the path to the data to return without including namespaces.

Syntax:

ElementPath = XMLElementName [/ElementPath]

XMLElementName = [NamespacePrefix:]XMLLocalName

Query element

The query element is similar to the element path, but it helps to define namespaces for the element path.

Syntax:

<Query xmlns:es="http://schemas.microsoft.com/StandardSchemas/ExtendedSales">

<ElementPath>/Customers/Customer/Orders/Order/es:LineItems/es:LineItem</ElementPath>

</Query>

Empty

No query. It takes the first element path to a leaf node and applies it to the whole document.

In the following document an empty query will default to:

 /Custs/Cust/Orders/Order: <Custs>    <Cust ID=1>       <Name>Bob</Name>       <Orders>             <Order ID=1 Qty=6>Chair</Order>             <Order ID=2 Qty=1>Table</Order>       </Orders>       <Returns>             <Return ID=1 Qty=2>Chair</Order>       </Returns>    </Cust>    <Cust ID=2>       Name>Aaron</Name>    </Cust> </Custs>

Fields

The result of processing the query is the Fields collection. As you run statements, you can click on the Refresh Fields button to update the fields in the data set.

There are two types of fields. The first and most obvious is the database fields. Database fields are the direct result of running the query. As you might have noticed, the field name automatically gets set to the field name as expressed by the query.

The second type of field is a calculated field. This is the result of using expressions or custom code to derive a value based on one of the database fields. For example, suppose you want to determine the percent of a quota a salesperson has met.

You can add a field to the data set and plug in this expression (see Figure 9.5):

 =SalesYTD/SalesQuota * 100

Figure 9.5. Adding a calculated field to a data set.

A word of caution on calculated fields. The calculation is performed for every row brought back from the data set. If it is a large data set, this can be rather time consuming.

Fields and XML

In dealing with XML, every element along the element path and every attribute is returned as a field. All the fields are String data types. Some fields even include embedded XML.

Filters

At first, it might seem strange that you would need a filter at the data set level. After all, why would you need them, if you can simply modify the WHERE clause in the SQL? The dilemma comes when you need to run canned queries, such as stored procedures, or if you cannot pass in the appropriate value to filter inside the SQL.

A word of caution comes with this as well. It is much easier to filter at the database level than at the client level. Returning large data sets simply to filter it down to one or two rows on the Report Server is possible, but it might be an inefficient use of system resources.