CHAPTER 3 INTRODUCTION TO REPORTING SERVICES DESIGN WITH SQL SERVER DATA TOOLS
Filters: Defines filter values for the dataset that you can use when the report is
executed.Creating Other Data Sources
One exciting aspect of SSRS is its ability to query multiple data source types as well as SQL Server. As
mentioned previously, any ODBC or OLE DB provider can be a data source for SSRS, as can XML, SSIS,
and SAP. For a simple example of using a data source other than a SQL Server database, let’s look at the
OLE DB Provider for Microsoft Directory Services. Creating the data source to Directory Services is
similar to procedure you used to create the SQL Server data source, except that you select OLE DB as the
data source and OLE DB Provider for Microsoft Directory Services for the OLE DB provider in the data
source properties.
By using a direct LDAP query, you can generate field information for use in SSRS like so:SELECT en,sn,objectcategory,department
FROM 'LDAP://DirectoryServerName/OU=OuName,DC=Company,DC=Com'The query uses a standard SQL dialect that returns the common name, surname, object category
(computer or person), and department from Active Directory. The field names are automatically created
and can be used like any other data field for a report.
You must consider a couple of caveats when querying Active Directory or any other data source that
doesn’t support the graphical query designer in SSRS:- Query parameters aren’t supported directly in the query. However, you can define
and use report parameters in the query—referred to as a dynamic query—and to
filter data. - Because a graphical query designer isn’t available, you need to develop the query
in the generic query designer by typing the query directly and testing. This
requires knowledge of Active Directory objects and names.
Tip Several tools are available to assist in managing Active Directory, such as Active Directory Application
Mode (ADAM); LDP, an Active Directory tool; and ADSIEdit, a graphical Active Directory browser. Both are included
with the Windows Support Tools.Configuring Parameters
Parameters in SSRS come in two flavors, query parameters and report parameters, and the two are often
tied together closely.
You use a parameter that’s based on a SQL query or stored procedure to limit the record set
returned to the report, typically in the WHERE clause of a query. In the source query, you define
parameters by prefacing the parameter’s name with an @ symbol, such as @MyParameter. Within SSRS’s
query design tools, this does two things: it forces the query to prompt for the value of the parameter
when it’s executed. Secondly, it automatically creates the other parameter, the report parameter, using
the same name. With stored procedures, such as Emp_Svc_Cost, which you created in the previous
chapter and have used here, any parameters that have been defined in the stored procedure are also
automatically created for the report.