Chapter 9. Accessing Data

Chapters 9 and 10 look more closely at data sets, and takes a look at report parameters. The combination of these two items helps to add incredible flexibility to SSRS.

Parameters are values you can pass into the report to help make rendering decisions at runtime. Users can get prompted to input parameters at runtime. In combination with data sets, you can draw a list of valid values from your data sources. You can even make parameters dependent on each other in such a way that the lists of values for Parameter B are derived as a function of Parameter A.

As you have already seen, data sets contain the data that reports use, and they are critical to report processing. Data sets contain data sources that are pointers to the actual data store. They also contain the query that the data source will process. The end result of this is a list of fields that result from the processing of the query. To this, you can add your own calculated fields and apply filters. The result of this is a single set of rows and columns that is used in processing the report, or as a list of parameter values.

Data-Processing Extensions

The first thing created during the report development process is, usually, a data set. With that, a developer would select a source of data, create a query, and evaluate the quality of the data returned by the data set.

Data quality is something you, as the developer, must evaluate; however, the open-ended nature of SSRS helps tremendously by not putting any hard limits on the type of data you can use.

SSRS comes with the ability to connect to SQL Server, Analysis Services, Integration Services, Oracle, ODBC, OLE DB, and XML.

If these choices are not enough, it is possible to extend SSRS by writing a custom data-processing extension to be used within a data source. Extensions are covered in Chapter 26, “Writing Custom Reporting Services Extensions.”

Types of Data Sources

There are three types of data sources. The first type of data source is the embedded data source. This type is kept within the report, and cannot be accessed by other reports. In the SSRS documentation, this is referred to as a report-specific data source.

The second type of data source is the shared data source. The largest difference between the two data sources is location. A shared data source lives on the Report Server as a separate entity from the reports, whereas the definition for a report-specific data source is stored within the report itself.

This allows other reports to use them for their data sources.

The third type of data source is an expression that is used to dynamically choose the data source at runtime. This is called a data source expression. Remember that just about every property can be modified by an expression. Data sources are no different.

Report-Specific Data Source

The report-specific data source should only be used when the data needed to process a report should be restricted to that report. If multiple reports need to access the same data source with the same credentials, you should use a shared data source. This is because maintaining lots of embedded data sources can be cumbersome. After the report has been published to the Report Server, the embedded data source has to be maintained as part of that report. Then, you can change it to reference a shared data source.

Shared Data Source

A shared data source exists on the Report Server as a separate entity. Report developers can define a shared data source in Visual Studio, but it does not overwrite an existing data source by default in the same manner that it overwrites reports.

A shared data source is useful when

• Many reports use data from the same location with the same credentials.

• An administrator needs to have an easy way to change the location of a data source for several reports. This is the case, for example, when moving reports from development to production.

Data Source Expressions

An expression can be used to define the connection at runtime. A classic case is the difference between an active OLTP database and historical data or a data warehouse. Many companies store historical data that is more than six months old in a data warehouse. You would have to determine an appropriate connection from some report-level parameter.

Like all expressions, a data source expression would have to be written in Visual Basic.NET and preceded by an “=” sign.

To define data source expressions, consider the following guidelines:

• Do not use a shared data source. You cannot use a data source expression in a shared data source. You must define a report-specific data source for the report instead.

• Design the reports using a static connection string.

• Use a report parameter to specify the values to be passed to the expression. The parameter can pull from a list of valid values from a query using a separate data source. Later in this chapter, you will see how to set up parameter dependencies that allow you to do this.

• Make sure all the data sources implement the same schema.

• Before publishing the report, replace the static connection string with an expression.

It is easiest to use Windows authentication. This is because the Report Server stores data source credentials separately from the data sources themselves. Another option is to hard-code the credentials, or prompt a user for login credentials.

The following is an example of an expression-based data source for SQL Server:

="Data Source=" &Parameters!DBServer.Value & “;Initial Catalog=NorthWind

The preceding example assumes that there is a parameter called DBServer.

Connection Strings

Connection strings vary wildly by the type of processing extensions used in the data set. For example, if you use the OLEDB or ODBC process, you must specify the driver. For SQL Server, you should specify a database name, whereas for Oracle the database name is not required. For XML, just point it to the source by entering a URL in the connection string. In all cases, you should not specify the credentials used in accessing the data source inside the connection string. SSRS stores data source credentials separately.

The following are some common connection strings:

• SQL Server 2000 and above

        Data source=MyServerMyInstance,1433;Initial Catalog=Pubs

• Analysis Services (SQL 2005)

        Data Source= MyServerMyInstance,1433;initial catalog=AdventureWorksDW

• Analysis Services

        provider=MSOLAP.2;data source=MyOLAP;initial catalog=AdventureWorksDW

• Oracle

        Data Source=OracleSID

• XML via URL

        URL="http://MyWebServer.com/Queryresults.aspx"

• XML via Web Service

        URL=<url>; SOAPAction=<method-uri>[#|/]<method-name>

        URL=http://MyReportServer/reportserver/reportservice.asmx;
        SOAPAction="http://schemas.microsoft.com/sqlserver/2004/05/reporting/reportservices/ListChildren"

• SSIS Package

        -f c:packagename.dtsx

• SAP Connector

        ashost=SAPAppServer client=000 snc_mode=1 sysnr=00 type=3 user=SAPDOTNET snc_partnername="p:[email protected]";

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

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.

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

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.

Adding a Data Source

If you have closed the AdventureWorksReport solution, reopen it. To create a data source, complete the following steps:

1. From Solution Explorer, click on the AdventureWorksReports project.

2. From the Project menu, select Add New Item.

3. In the Project Items list box that appears, select Data Source.

4. For the name, type “MyDS”.

5. Select Microsoft SQL Server. By now, your screen should resemble Figure 9.6.

Figure 9.6. Creating a shared data source.

6. Enter the following connection string:

Data Source=(localhost);Initial Catalog=AdventureWorks

7. On the Credentials tab, select the Use Windows Authentication (Integrated Security) option, if necessary.

8. Click OK.

Summary

Data sources provide the report with a connection to the data. Data sets use the data source along with a query of some kind to produce a resultset that the rendering engine takes and uses to process the report.

Data sources can be either specific to a report or shared among many reports. SSRS can natively hook into SQL Server, Analysis Services, Integration Services, and Oracle. A number of other providers are supported through ADO.NET, and if a provider is not available, one can be custom developed. It is helpful from an administration point of view to use a shared data source whenever possible. An exception to this is when a data source needs specific credentials or elevated security.

Visual Studio offers two query designers—the Graphical Query Designer and the Generic Query Designer. SSRS leaves the processing of the query to the data source. After the data source is finished processing the data, it generates a data set, which is a collection of fields inside of rows. Calculated fields can be added to the data set at design time to augment the returned results. Filters can also be applied to the resulting data sets. Both filters and fields are applied on a row-by-row basis and, if not used carefully, can lead to performance problems.

Parameters can either be static or bound to data sets. The value of certain parameters can also be passed in as input to a query. The output of that query can be used as the list of values for a parameter, in effect creating a dependency between parameters and data sets. This dependency can be used in many different ways to affect the data used in the final data set processed.