Designing Data Access - Professional SQL Server 2005 Reporting Services - page 180

There are some known minor data type incompatibilities with report parameters. In particular, you may
find it easier to use String-type parameters for dates rather than the native Date type. If you use date
or numeric parameters, you may need to convert them in the query expression. Since SQL queries and
stored procedure calls are assembled as a string expression, parameters need not be converted to explicit
types. Type conversion will be performed by the database engine.

The report server and development computer will need to have the Sybase ASE OLEDB Provider installed
and configured correctly. This will enable you to create connections using the Microsoft Simple OLEDB
Provider with the installed Sybase client components.

Best Practices
Use shared data sources to reuse the connection information. Data sources are not redeployed by default.
Remove the report and data source file from the report server and redeploy the data source to update
connection information and certain report metadata.

When using complex query expressions, keep a copy of the last working query script in a separate query
tool window or in NotePad.

When using an expression for a data set (i.e., =”SELECT...”), if changes are made to the query expression,
you may need to remove the string encapsulation characters from the text to run the query. Make a point
to execute the query and click the Refresh toolbar button to update the report fields definition.

Filter in the data set or stored procedure to reduce network traffic and reduce report server processing
overhead. Filter data in the report to reuse the same result set and improve response time for longer,
interactive report sessions.

Plan ahead and filter data consistently in the data set, report item, or group.

In MDX queries, add and configure parameters before making any manual changes to MDX script. You
cannot modify or view the query using the graphical MDX Query Designer after making manual changes.

Summary
This chapter covered the following:

❑ Each report typically has at least one data set, which defines a data query. Each data set will

have a data source that may be stored in the report or the report project as a shared data source.
Additional data sets may be used for different report items or to populate parameter lists.

❑ A data set may be designed using either the Generic Query Designer or the Graphical Query

Designer. Most queries built on SQL Server databases are most easily designed using the
Graphical Designer.

❑ Data may be filtered in the data set query, which is typically processed on the database server,

or within the report. Report filters may be defined at the data set, at the report item, or within