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

As you can see, almost anything is possible in terms of combining data sources and data sets. Data sources
can be practically any database product or any data source you can query by means of standard connec-
tion libraries or drivers. Reporting Services consumes data using the .NET data providers, which include
support for SQL Server, Oracle, and all OLE DB providers. These include almost any database product
that supports ODBC access or a capable ISAM driver. Data sets in Reporting Services are always read-
only, so there is no need to specify cursor types or locking options.
Reporting for Relational Data
In the previous chapter, you briefly looked at using the Query Builder. Now you'll take a closer look at how
queries are created and how data is provided for a report. At this point, it's important to understand the
basic building blocks for reports. The discussion will begin with some of these fundamentals. You will go
through several short walk-through exercises, so you can see and experience how it works. I am assuming
that you have used Visual Studio 2005 and you have created a report using the Report Wizard.
If you are a .NET programmer, as I am, you may have seen the term “data set” and thought, “I know
what a data set is, and I use them all the time in .NET data access program code, so I should have a leg
up on doing data access in Reporting Services.” If you are not a .NET programmer, you're already a step
ahead of those programmers who have to relearn the application of this term. By the way, if you are not
a .NET application developer, you don't need to concern yourself with this at all.
Why does the term “data set” mean two completely different things? We ran out of new words in the
English language a long time ago. Everyone knows that to be environmentally responsible we need to
recycle, so this is what we're doing — recycling words and phrases. This one is a classic example. In
Reporting Services, you have the concept of a query in the report definition that provides data values for
the report output; our good friends at Microsoft decided to call this a Dataset . If you have worked at all
with programmatic data access in the .NET Framework, you should know that a Dataset is also an object
that stores a cache of data (perhaps from a query) as an XML structure in memory. Although these two
items may both handle data and deal with queries, result sets, and binding values displayed in a report,
they are two very different concepts. Now, since we have that straightened out, try this: If you were to
create a custom data source extension in program code, you might use an ADO.NET Dataset that would
serve as the Dataset for a report!
Query Basics
Reporting Services has the ability to obtain data from a variety of data sources. Nearly all relational
database products are queried using a form of Structured Query Language (SQL) , which means that a
query created for one database product (say, Microsoft Access) may be portable to a different data source
(perhaps Oracle or SQL Server). Most database products implement a form of SQL conforming to the
ANSI SQL standard. SQL Server, for example, conforms to the ANSI 92 SQL standard, and other prod-
ucts may conform to other revisions (like ANSI 89 SQL or ANSI 99 SQL). Beyond the most fundamental
SQL statements, most dialects of SQL are not completely interchangeable and will require some under-
standing of their individual idiosyncrasies.
Other specialized database products may use a different query language. Microsoft SQL Server Analysis
Services is a data storage and retrieval product that uses multidimensional cube structures to organize
complex data for decision-support systems.