CHAPTER 13 CREATING REPORTS USING REPORT BUILDER 1.0, 2.0, AND 3.0
Creating Reports with Report Builder 3.0
When we heard that SQL Server 2008 R2 was coming to the market with all of the new visual
enhancements, there was talk about another Report Builder. After all of the great additions made with
Report Builder 2.0, we could not wait to see what ad hoc goodies the next release would bring to our end
users.
When SQL Server 2008 R2 came to the market, along came Report Builder 3.0 and with it came some
additional data sources, data visualization report items, and cached data sets when previewing reports
to name a few. High-level details about each of the enhancements are:- Report Parts: Report Parts allow report authors to create and deploy reports that
can be re-used by other reports. For example, if a report developer created a chart
for Employee Service Cost by Branch report with a total cost chart, another report
developer or end user could use that report as a Part of their report. - Data Sources: With the coming of age of SQL Azure, Microsoft has created a
Microsoft SQL Azure data source. They have also added the SharePoint List Data
Extension to connect to SharePoint lists and Microsoft SQL Server Parallel Data
Warehouse. - Report Items: As discussed in Chapter 5 using the Report Designer in SQL Server
Data Tools, new report visualization items have been added to the Report Builder.
The new report items are Map, Data Bar, Sparkline, and the Indicator. You can
find these on the Insert tab of Report Builder. - Dataset Caching: With end users creating ad hoc reports, the potential for many
long running report requests is pretty high. As such, to prevent multiple round
trips when no dataset related changes are made, Microsoft has added dataset
caching to the matrix of new features. This makes the end users experience better
when creating ad hoc reports, which could equate to more acceptance by end
users.
Now that you’ve been introduced to Report Builder 3.0 and some of the new features and
enhancements, let’s get started on some examples. In this example, we are going to build a report that
has the number of visits and the estimated cost of service for each patients State and by the State in
which the patient was seen at one our branches. It will be very similar to the report that we have created
with the exception that we will also be using a Map control. We will also have a drill through report from
our Map report to a more detailed tabular report. We have learned about drill through reports earlier in
the text, but we never performed a drill through using Report Builder. The drill through report
demonstrated in this section is available in the Pro_SSRS project in the Source Code/Download area for
the book on the Apress Web site (www.apress.com). This report is called
Emp_Svc_Cost_By_Patient_State_RB3_Drill.
Before we get started on Map report, open up Report Builder 3.0 from the Report Manager. Once
Report Builder is loaded, click Open in the Getting Started window. Navigate to the location where you
have the Pro_SSRS solution stored on your hard drive and select the report named
Emp_Svc_Cost_By_Patient_State_RB3_Drill. Click Open to open the report in Report Builder 3.0. If you
click Run, you should have similar results to what is shown in Figure 13-61.