ptg
181
Types of Data Sources
Report-Specific Data Sources
The report-specific data source should be used only 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. You can use the Report Manager web interface to change the reference from
an embedded to a shared data source.
Shared Data Sources
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. To override a shared data
source, you have to set
OverwriteDataSources
project property to
True
(see Figure 11.1).
11
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 develop-
ment to production.
Data Source Expressions
An expression can be used to define the connection at runtime. A classic case is the differ-
ence between an active online transaction processing (OLTP) database and historical data
or a data warehouse. Many companies store historical data that is more than six months
FIGURE 11.1

. Windows authentication (integrated security). It is the easiest to use, and the creden-
tials are handled separately from the data sources. Using this option, you are also
leveraging time-tested Windows security. Because each Windows user has individual
credentials, you can assign security restrictions down to a level of an individual user.
You can also leverage Windows security groups to handle several users as a group
with the same level of permissions. For example, you can set permissions in such a
way that an employee’s personal information is available only to the HR group and
the employee herself.
. Hard-code the credentials. In this case, you just enter login credentials. Report
Designer stores encrypted credentials separately from the data source, locally and in
the file
ProjectName.rptproj.user
and then in the Reporting Services database, also
in the encrypted form.
. Prompt a user for login credentials.
CHAPTER 11 Accessing Data
From the Library of STEPHEN EISEMAN
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
ptg
183
FIGURE 11.2
Shared Data Source Properties dialog box, Credentials tab.
Connection Strings
11
. Use the No Credentials option. This is used for data sources that do not require
credentials.
. Hard-code credentials in the connection string (not recommended). This approach is
not recommended because of the security implications associated with this method.
Because the credentials are not encrypted, this approach can easily lead to unautho-
rized information access.

184
. SQL Server RDBMS. You can connect the .NET Framework data provider, OLE DB
data provider for SQL Server, and ODBC. In general, we do not recommend using
ODBC because in many cases ODBC providers are several times slower than .NET or
OLE DB providers.
. SQL Server 2000 and later, where 1433 is a port number. The SQL Server admin-
istrator sets a port for an instance of SQL Server during configuration process:
Data source=SQLServer\InstanceName,1433;Initial Catalog=DatabaseName
. Microsoft OLE DB provider for SQL Server:
Provider=SQLOLEDB.1;Data Source=(local);Integrated Security=SSPI;Initial
Catalog=AdventureWorks
. Analysis Services. Much like with the SQL server, you can connect using several
different providers:
. The .NET Framework data provider for Analysis Services:
data source= OLAPServer;initial catalog=AdventureWorksDW
. The Microsoft OLE DB provider for OLAP 8.0. You can use it to connect
to SQL Server 2000 and later:
provider=MSOLAP.2;data source=OLAPServer;initial
catalog=AdventureWorksDW
. The Microsoft OLE DB provider for Analysis Services 10.0. You can use it
to connect to SQL Server 2008:
provider=MSOLAP.4;data source= OLAPServer;initial
catalog=AdventureWorksDW
. Oracle. We recommend Oracle using network configuration tools and that you set
an alias for an Oracle server. For example, where the actual server is at
OracleServerAddress:Port/Instance
, you can set an alias as
OracleServer
.
. The .NET Framework data provider for Oracle. Oracle is running on Windows,

. SAP NetWeaver BI data source:
DataSource=http://SAPServer:8000/sap/bw/xml/soap/xmla
From the Library of STEPHEN EISEMAN
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
ptg
186
TABLE 11.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.
. Hyperion Essbase:
Data Source=http://HyperionServer:13080/aps/XMLA;Initial Catalog=SalesDB
. Teradata:
Data Source=TeradataServer;User ID=myUsername;Password=myPassword;
Microsoft supplies additional drivers that are not installed by default with SSRS. For
example, the Microsoft SQL Server 2008 feature pack (www.microsoft.com/downloads/
details.aspx?FamilyId=C6C3E9EF-BA29-4A43-8D69-A2BED18FE73C&displaylang=en)
comes with an IBM DB2 OLE DB driver install. Once you install it, the report driver shows
Microsoft OLE DB Provider for DB2
in the OLE DB provider list.
Practically all the DBMS vendors supply .NET, OLE DB, or ODBC providers for their data-
bases and supply setup and connection string information. In addition,
Connectionstrings.com provides connection information for a variety of data sources.
Querying Data
After a connection is established, your next step is to query the data source. For most rela-
tional 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

Result pane
SQL pane
Grid pane
Diagram pane
Add table
Sort by selected column:
ascending/descending
Show/hide panes
Verify SQL
Run query
Toggle between graphical
and generic query designers
Generate GROUP BY
Remove filter/selected column
from a WHERE clause
FIGURE 11.3
Graphical Query Designer.
TABLE 11.1
Continued
Pane Function
Query Displays the Transact-SQL query represented by the Diagram and Grid panes. Use
this pane to write or update a query using T-SQL query language.
Result Displays the results of the query. To run the query, right-click in any pane, and then
click Run.
From the Library of STEPHEN EISEMAN
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
ptg
188
CHAPTER 11 Accessing Data
FIGURE 11.4

Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
ptg
189
Querying Data
11
FIGURE 11.5
Dataset Properties dialog box.
. The
Fields
collections, which includes fields retrieved by the query and calculated
fields
. The
Parameters
(a parameter in a query string, such as
SELECT * FROM
Person.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
. The
Options
, which provides instructions on how to execute a query (

TableDirect
feature of SQL
From the Library of STEPHEN EISEMAN
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
ptg
190
CHAPTER 11 Accessing Data
Server, where the query 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 OLE DB supports
the
Table
command type, the .NET provider Microsoft SQL Server (SqlClient) does not.
Therefore, Table is grayed out for the Microsoft SQL Server (SqlClient) provider. Of
course,
SELECT * FROM <Table>
would work just the same in the case of either
provider.
Parameters
Most queries and stored procedures require inputting some type of parameter to return
data. Consider the following example:
SELECT *
FROM Person.Address
WHERE (City = @City)
This is an example of a parameterized SQL on an
Address
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

ptg
191
Querying Data
11
FIGURE 11.6
An example of how a designer prompts you for the parameters.
FIGURE 11.7
SQL and report parameter association.
From the Library of STEPHEN EISEMAN
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
ptg
192
CHAPTER 11 Accessing Data
from a query. However, the Report Designer will remove the association if the parameter is
no longer present in the query.
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 proce-
dure 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 or set to
0
, the query can run indefinitely.

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 name-
spaces for the element path.
Syntax:
<Query
xmlns:es=” />Sales”>
<ElementPath>/Customers/Customer/Orders/Order/es:LineItems/es:Line
Item</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 the following:
/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>

one of the database fields. The value could just be a result of the function
Now()
.
Let’s examine a calculated field. Suppose you want to determine the percent of a quota a
salesperson has met.
You can add a calculated field to the data set and plug in this expression (see Figure 11.8):
=SalesYTD/SalesQuota * 100
Note that calculated expressions have
<<Expr>>
in the Field Source column and an fx
button after it.
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 return as
a field. All the fields are
String
data types. Some fields even include embedded XML.
From the Library of STEPHEN EISEMAN
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
ptg
195
Summary
11
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 just modify the
WHERE
clause in the SQL? The
dilemma comes when you need to run canned queries, such as stored procedures, or if

takes and uses to process the report.
Data sources can be either specific to a report or shared among many reports. A number of
data providers are installed with SQL Server and include SQL Server, SSAS, Oracle, XML,
Teradata, SAP, and Essbase.
From the Library of STEPHEN EISEMAN
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
ptg
196
CHAPTER 11 Accessing Data
Many other data sources are supported through .NET, OLE DB, and ODBC. If a provider is
not available, it 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.
From the Library of STEPHEN EISEMAN
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
ptg
CHAPTER
12

default values, SSRS would present a parameter selec-
tion control (see Table 12.1) with default values pre-
selected from a list of available values. For example, if
you specify Value1 and Value2 as available and
specify Value2 as default, SSRS would display
Multiselect list as the parameter selection control and
Value2 will be selected.
. Otherwise, default values are ignored.
When you specify the default values but not available
values, SSRS would present a corresponding control with
default values entered. The default values can be overrid-
From the Library of STEPHEN EISEMAN
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
ptg
198
CHAPTER 12 Report Parameters
TABLE 12.1
Parameter Presentation Options
Control Number of
Values
Report Designer Has
Control Over Entered
Values
Presented When
Text box 1 No. Single-value parameter,
excluding
Boolean
and
DateTime
types.

Single-value
Boolean
parameters only.
den. For example, if you specify Value2 as default and do not specify any available values,
SSRS would display a Text box (or Multiline text box if it is a Multivalue parameter) with
Value2 entered. You would be able to override Value2 with another value.
Parameters can also be hidden from the user. Although this might sound strange at first,
hidden parameters can be used to alter report processing based on the input from other
parameters, or based on the result from a data set.
Setting Up Parameters
You can set up parameters from the Report Designer through the following steps:
1. Select View, Report Data from the BIDS main menu. This will open Report Data
dockable window.
2. In the Report Data dockable window, right-click the Parameters folder and select Add
Parameter from the drop-down menu. The initial screen looks similar to Figure 12.1.
From the Library of STEPHEN EISEMAN
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
ptg
199
Parameter Properties
FIGURE 12.1
Report parameters.
12
Note the tabs General (shown on the figure), Available Values (allows you to enter a list of
available values), Default Values (allows you to enter a list of default values), and
Advanced. The Advanced tab controls how SSRS refreshes default values for dependent
parameters:
. Automatically Determine When to Refresh (default): Asks SSRS to analyze
dependencies and refresh when dependencies exist
. Always Refresh: Asks SSRS to always refresh, regardless of dependencies

,
Date/Time
,
Boolean
,
Float
The data type in which to expect the parameter. It
defaults to
String
, but you can choose a different
data type from the drop-down list. If the report
parameter values come from a query, the return
type for the query must match the type specified
here. Because .NET CLR is a strongly typed
system, SSRS is too. It returns an error if a string
is passed in for what is supposed to be a number
value. For a
Boolean
value, the Report Server
simply creates a radio button list with
true
/
false
as the only options.
Prompt String
A friendly message to pass to the user who enters
the parameter data. If it is left empty, and a default
value is specified for the parameter, the user is not
asked to input the value because the report will
use the defaulted value. If the prompt is empty,

From the Library of STEPHEN EISEMAN
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.