Problem solve Get help with specific problems with your technologies, process and projects.

Reporting Services 2008 R2 eases SQL Server report building

It’s true, Microsoft has put out a number of new BI technologies for SQL Server 2008 R2, but changes to existing tools like Reporting Services may hold the most value initially.

It’s been said many times before, but SQL Server 2008 R2 is really a business intelligence (BI) release. After delivering a host of new features for developers with SQL Server 2005 and for DBAs with 2008, the time had come to satisfy the needs and cravings of the BI crowd.

So it’s not surprising that SQL Server Reporting Services (SSRS) -- one of Microsoft’s key BI offerings -- comes with numerous new and improved features in R2. Describing all of them is beyond the scope of this article, so I will instead focus on the most useful and enticing features designed to improve your report building experience, increase productivity, and provide easier collaboration.

Shared datasets

If you build a lot of reports that are related or similar in nature, it’s likely that many of them use the same datasets, especially when they are used as data sources for report parameters. I recall many times when I’ve opened the Report Definition Language (RDL), looked up the dataset definition and carefully pasted it to a new report. Short of recreating the reports from scratch, this was the best course of action since the Business Intelligence Development Studio (BIDS) didn’t allow copy and paste for datasets.

SQL Server 2008 R2 addresses this issue with the introduction of shared datasets, which have a separate node in Microsoft's Solution Explorer just like shared data sources. When you add a dataset to the project, the dialog allows you to select a shared dataset instead of creating a new one:

Figure 1. Selecting a shared dataset for SSRS reports (click to enlarge)
Selecting a shared dataset for SSRS reports

When you deploy your solution, shared datasets get deployed to a common folder where you can further modify their configurations.  Another plus here is that shared datasets also allow you to define data caching, so you now have greater control over caching the underlying data.

In previous versions of SQL Server Reporting Services, you could only control caching at the report level, meaning all datasets were cached at the same time and frequency. With SQL Server 2008 R2, you can configure caching in Report Manager at the dataset level and refresh certain parts of the report more or less frequently based on the nature of the data (Figure 2).

Figure 2. Cache configuration improvements in Report Manager (click to enlarge)
Cache configuration improvements in Report Manager

Report parts

Previous versions of SQL Server Reporting Services lacked tools to create reusable report components to help generate parts like common charts or graphs that could be easily plugged into new reports. In the past, folks have used sub-reports to create this functionality, and though it worked to some extent, it was not the same as having a true component.

SQL Server 2008 R2 lets you select individual components from a report and upload them to a report library on the server. The following snapshot shows the Publish Report Parts dialog box in Business Intelligence Development Studio:

Figure 3. The Publish Report Parts dialog box in BIDS (click to enlarge)
The Publish Report Parts dialog box in BIDS

After the selected parts have been deployed to the server, you can browse, search and select them to be included in your reports. Figure 4 shows the Logo report part created in the previous dialog as used in a report in the Report Builder application:

Figure 4. Selecting parts in Microsoft Report Builder (click to enlarge)
Selecting parts in Microsoft Report Builder

You can publish the following components as report parts: charts, gauges, images, maps, parameters, rectangles, tables, matrixes and lists. Based on my experience, many of these objects are usually not explicitly named in reports; a generated name such as “Chart1” is used instead. If you are going to publish report parts, it goes without saying that you should develop and follow consistent naming conventions to make the job easier for report designers when they are browsing and selecting report parts on the server.

 There is a limitation to be aware of, however. As you know, reports can be authored in BIDS or in Report Builder. Previous versions of Report Builder were limited in functionality and hard to use, but the new Report Builder 3.0 version in R2 provides a much better experience.

When it comes to working with report parts, only Report Builder 3.0 allows full report part authoring and sharing. This means that you can upload report parts so that later you or other users can browse the report part library and select the parts to be used. On the other hand, Business Intelligence Development Studio is limited in how it allows you to work with report parts; you can publish selected report parts but you cannot browse or reuse the existing parts. This is yet another reason to check out and possibly start using Report Builder 3.0 as your main authoring tool.

New data sources

With SQL Server 2008 R2, you can now natively use three additional data sources:
Microsoft SQL Azure -- No surprise here, as Azure has gotten a lot of publicity recently.
Microsoft SQL Server Parallel Data Warehouse -- This is a new scalable type of data storage where large database tables are stored across multiple physical nodes.

Microsoft SharePoint List -- This data source allows you to utilize existing lists in your reports.

Reports as data sources

 There is a new report rendering option in SQL Server 2008 R2 that lets you export report data as an Atom document, which is essentially an RSS feed.  You can export directly from Report Manager or SharePoint. Other Atom applications can also receive report data in this format. One example is the new PowerPivot BI tool that allows you to import data from existing reports. That functionality is implemented using an Atom document feed.  

One of the advantages of using Atom feeds is that other applications can utilize both your existing reports and the work you’ve done in creating and securing datasets. So rather than granting applications access to SQL Server and configuring database security, you can just configure the application to use the Atom export from an existing report.

Find more information on what IT professionals can expect from SQL Server 2008 R2.

Roman Rehak is a senior database architect at in Colchester, Vt. He specializes in SQL Server development, database performance tuning, ADO.NET and writing database tools. Roman also serves as the technical chair for the SQL Server track at the annual DevTeach conferences in Canada and is the president of the Vermont SQL Server User Group.

Dig Deeper on Microsoft SQL Server Reporting Services (SSRS)

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.