CHAPTER 3 INTRODUCTION TO REPORTING SERVICES DESIGN WITH SQL SERVER DATA TOOLS Note In Reporting Services 2012, you can choose from many types of data source. SQL Server happens to be
the default, but you can grab data from sources such as SQL Azure, Analysis Services, Oracle, SAP, and
TERADATA. You can use generic OLE DB and ODBC data sources as well, of course.
You now have a shared data source. Notice that its name has defaulted to DataSource1. You can
rename it by right-clicking it, selecting Rename, and entering Pro_SSRS.rds as the new name. It is good
practice to give each data source a meaningful name, such as the name of the database it will connect to.
In a real-world environment, you will be deploying your reports and/or databases on different servers,
so it’s not advisable to prefix the data source name with the name of the server containing the database.
In other words, it is not recommended to name your data source DEV_Pro_SSRS to represent a
development data source, would you want to name your data source something like Server1_Pro_SSRS.
In practice, we’ve developed all our reports using an identical data source name. However, because
each of our online customers had a database name that uniquely identified them, we designed an
application that reset the database properties in the data source after it was published. In this way, we
could use the same reports against the same database schema, but we could deploy them to multiple
customers on the same report server.
In this example, the data source file you created has an .rds extension and is stored and published
separately from the report. You can open an .rds file in a text editor, because it’s an XML file that defines
the connection properties you just created graphically. Listing 3-1 shows the Pro_SSRS.rds file.
Listing 3-1. Pro_SSRS.rds File
<?xml version="1.0" encoding="utf-8"?>
<RptDataSource xmlns:xsi=http://www.w3.org/2001/XMLSchema-instance
xmlns:xsd="http://www.w3.org/2001/XMLSchema">
Creating a Dataset
Your next step is to proceed to the Report Data area to create your first dataset. This is true regardless of
whether you’ve developed a query or stored procedure in an application other than BIDS or whether
you’re beginning it now within the report. In this example, you are going to use a stored procedure that
is already complete and tested, so half the battle is done.
Using BIDS to develop SSRS 2008 R2 and SSRS 2011 reports, you create a dataset in two steps:
creating a link to a data source and getting the data for the report.
- In the Report Data box, select <New ~Dataset...>, which opens the Dataset
Properties dialog box. Each dataset defaults to a name of the form DataSet##,
where ## is the next unused number in sequence.