A Candid Look at Report Builder 3.0

Microsoft has been hard at work again, releasing SQL Server 2008 R2 Report Builder 3.0. Like its predecessors, Report Builder 3.0’s goal is to provide end users with an easy-to-use alternative to the report-authoring tools in Business Intelligence Development Studio (BIDS). This is a laudable goal, because the BIDS tools can be a bit daunting at first, especially for paradevelopers not used to dealing with sophisticated application development tools. (Paradeveloper is a term I coined to refer to those professionals who are tasked with creating reports but lack significant database, SQL, or report authoring training or experience, such as doctors, lawyers, and receptionists.)

Does Report Builder 3.0 meet its goal? The easiest way to answer this question is to walk you through the steps in Report Builder 3.0’s report creation process and discuss whether the tool’s features make it easy to accomplish these steps without involving the IT department for help. Along the way, I’ll point out some of the new features. If you’re new to Report Builder, this walkthrough will also give you a general idea of what the report creation process entails. Before I get started, though, I need to cover a few important details about Report Builder 3.0.

Details You Need to Know

Report Builder 3.0 is designed to work with (and only with) SQL Server 2008 R2 Reporting Services (SSRS 2008 R2). That’s because Report Builder 3.0 builds fourth-generation (2010 Report Definition Language—RDL) reports, which can be saved to or fetched from an SSRS 2008 R2 catalog. Report Builder 3.0 won’t open older SSRS catalogs, so if you have third-generation (2008 RDL) reports in an SSRS 2008 catalog, you have several options:

• Continue to use Report Builder 2.0 to access the reports in SSRS 2008 catalogs.
• Use the BIDS tools to import the reports into an SSRS 2008 R2 catalog and use Report Builder 3.0 to access them.
• Use a tool such as Reporting Services Scripter to create scripts that will import the reports into an SSRS 2008 R2 catalog and use Report Builder 3.0 to access them.

Report Builder 3.0 installs on the client system by default when you select it from the SQL Server 2008 R2 Report Manager. You can also download it for free (provided you have a SQL Server license) from the Microsoft Download Center.

Getting Started

After Report Builder 3.0 is installed, users need to open it. Typically, I wouldn’t even mention this step, but Report Builder 3.0’s opening page has changed significantly so I want to spend some time discussing it.

When Report Builder 2.0 is started, it opens with a blank report design surface that has two launcher icons—one launches the Table or Matrix Wizard and the other launches the Chart Wizard. Report Builder 3.0 opens with a new Getting Started page. However, this page isn’t displayed until Report Builder 3.0 establishes a connection to the last-used SSRS catalog. This can take 30 seconds or longer if SSRS hasn’t been accessed in the past 20 minutes. If you’re running the desktop version of Report Builder 3.0, it displays a Connecting to message box, in which you have the option of canceling the connection attempt.

The new Getting Started page has several new entry points:

• From the New Report tab, you can launch the Table or Matrix Wizard, the Chart Wizard, and the new Map Wizard for map-based reports, as Figure 1 shows. The Blank Report link closes the Getting Started page and opens the blank report design surface.
• The New Dataset tab opens the door to an entirely new and very important feature: shared datasets. Users are walked through the process of creating a dataset that can be shared by several reports at once. I’ll discuss the new shared datasets feature later.
• The Open tab takes you to a brutally simple SSRS catalog folder list. This catalog file finder looks like a dialog box leveraged from a very early version of Windows. Although it can navigate the selected SSRS catalog (or system files), it has no ability to manage the catalog despite doing double-duty as the dialog box used to save reports. You can’t create new folders or delete or rename existing folders, even in the My Reports directory in the catalog. In addition, you can’t delete or rename reports from here—or anywhere within Report Builder 3.0. All of these tasks are relegated to the report DBA.
• The Recent tab is torn right out of the Microsoft Office 2010 playbook. It displays a handy list of reports accessed since Report Builder was started. However, there’s no way to sort the list or filter it based on the SSRS catalog (or anything else). It shows which SSRS catalog was referenced, but it leaves off the most significant part of the name when the path is long—and there’s no horizontal scroll bar. Thankfully, you can hover over it and get the complete pathname. The list of recent reports is also displayed in the Home menu but in an even narrower format.

The Getting Started page is handy, but once it’s gone, it’s gone. You have to restart Report Builder to reopen the Getting Started page. However, most (but not all) of the functionality can be found through other menus. Copying Office 2007, you can access a significant amount of functionality by clicking the red icon in the upper left corner. You’ll find options for creating new reports (which opens a subset of the Getting Started page), saving reports, publishing report parts, checking for updates on shared datasets, and more. However, the functionality provided by Report Builder isn’t a substitute for the functionality provided by Report Manager. Report Builder isn’t a tool for report DBAs—it’s not intended to be.

Creating Reports

To create a new report in Report Builder 3.0, users need to take the following steps:

Step 1. Users need to decide what information they want to show in their reports. Report Builder isn’t much help here, because the conceptual design is up to the users and their managers, or the reports’ consumers. If users want to leverage an existing cataloged report, all they need to do is browse the catalog and choose a report to clone. (Users won’t be able to browse catalogs or save reports to folders to which they haven’t been granted rights by the report DBA.) After that, it’s just a matter of making appropriate changes and saving the report back to the catalog.

Step 2. Users need to choose or enter an SSRS catalog URL, which tells Report Builder where the reports can be downloaded for editing and saved. The catalog URL needs to be entered by hand or copied from those documented by the report DBA unless Report Builder is launched from Report Manager. Although Report Builder can remember past SSRS ReportServer virtual directory connections, it has no mechanism for browsing for new SSRS catalogs on the network. This means users must know how to enter a proper URL, including how to reference the correct ReportServer instance name, and possibly know how to access a local instance of SQL Server Express hosting a ReportServer virtual directory. To make this more difficult, the examples given don’t show how to reference a named instance. For example, to reference the ReportServer instance named George on a system named Fred, you need to use the URL http://fred/ReportServer_George.

Step 3. Users need to determine where the data for the report is located. For example, if they want a report about widget sales, they would look for places where sales data is kept. Typically, this is a spreadsheet or database. Again, Report Builder isn’t much help here, so the report DBA needs to provide documentation on the data repositories that a user can access. Typically, the report DBA would create and document a set of shared data sources with role-appropriate credentials. That is, these credentials would grant the domain group to which a user belongs sufficient rights to access (but not update) the needed data. Note that starting with SSRS 2008 R2, report DBAs can publish shared datasets as well—even through SharePoint data connection libraries.

Step 4. Users must select the data source for the report. In Report Builder, the data source is simply a cataloged connection string or a pointer to a shared dataset. As a result, Report Builder can leverage data from almost anywhere, except that pile of papers on the user’s desk. In disciplined shops, cataloged data sources expose database views, stored procedures, shared datasets, and canned report models. Although it’s possible to expose base tables, most diligent report DBAs don’t do so, especially those concerned with data security. If necessary, users can create new data sources, assuming they know how to create a connection string and have sufficient rights to do so (most don’t). However, it’s best if they choose an existing data source or shared dataset.

Step 5. Users need to build a query against the selected data source or shared dataset. In this case, Report Builder’s new graphical query designer can make it easier for users to build a query because the designer constructs the SQL query behind the scenes. The graphical query designer exposes a list of available database objects available to a user based on the rights assigned by the report DBA. These objects are organized by schema. As Figure 2 shows, the user drags and drops one or more of these objects to create a query.

Although the graphical query designer is easy to use, it has a few drawbacks:

• It’s too constrained for even moderately complex queries. It’s impossible to rename columns, create composite columns, or easily establish relationships when the column names don’t match. I suspect that users might quickly outgrow Report Builder’s graphical query designer.
• Too many users might simply select the table object in the designer, thus selecting all of the table’s columns, instead of building a focused query for their report. Thankfully, this might be a moot point, as most shops don’t expose base tables.

Alternatively, users can enter a SQL query (assuming they know how to write one) directly in the text-based query designer, which they can access by clicking the Edit as Text button. Again, permissions play a critical role here. Users must be restricted to read-only access to the database so that they don’t run a query that accidentally or intentionally destroys data in it. In addition, users need know enough about SQL so that they don’t run inefficient queries that slow down the production server. Users also need to know that if they use the text-based query designer, they can’t return to the graphical query designer without starting over.

Step 6. After users run the query they created in step 5, they need to link the columns (fields) extracted from the data source to the column, row, grouping, and details areas in the report. Report Builder combines the tools for designing a report’s table or matrix into what’s called the Tablix layout tool. Report Builder 3.0’s Tablix tool is the same as that in Report Builder 2.0. However, Report Builder’s Tablix tool is missing some functionality compared with the equivalent tool used in BIDS. The Tablix tool doesn’t include the concept of a page-break field, which makes it more difficult to know which fields have been added. It also assumes that every numeric value placed in the details section is to be summed—even identity columns and numerical part numbers. These unwanted aggregations should be corrected manually at this point so that there aren’t needless Sum expressions in the report. Thankfully, all the user has to do is reset the aggregate operation on each detail field that doesn’t need it.

Step 7. After the fields are laid out, the users need to choose the overall report layout and colors. They also need to perform several other tasks, including:

• Inspecting each Tablix column for any unnecessary Sum expressions, removing them if found.
• Making sure the groupings are correct.
• Setting the correct format for numerical (especially currency) columns.
• Adjusting the column widths after the report is rendered for the first time. Unlike DataGrid controls, column widths aren’t set by data inspection.
• Titling the report. Unlike the BIDS wizard, the Report Builder wizards don’t prompt for a report “name” (its title), so users have to manually enter it and set its typeface, size, background color, and text color.

Step 8. After the report is fully tuned, polished, and tested, the final step is to save the report in the SSRS catalog or file system, assuming the users have sufficient rights to do so. This might not be the case if users’ security, roles, and rights aren’t configured correctly by the report DBA.

Note that the report wizards aren’t re-entrant. That is, after you finished using a wizard, there’s no way to restart it for an existing report. This means that if a report’s groupings need to be changed, for example, the changes need to be made using Report Builder’s grouping UI—yet another challenge for the novice user.

Creating Shared Datasets

One of the most important new features exposed in SSRS 2008 R2 is shared datasets. Consider that in a production system, SQL Server already knows how to optimize queries based on the fact that most of the time the same set of queries is executed many times a second. Optimization in this case is accomplished through use of the procedure cache. Similarly, frequently used data pages are cached to eliminate the need to refetch data from the disk. In SSRS, many reports share the same (or similar) queries. The procedure cache can help, but if you include queries that are nearly the same (e.g., two identical queries, except one fetches more columns), you’ll find that the bulk of your reports could potentially share the same rowset product. What if these datasets were persisted in the SSRS catalog so they could be reused by several reports? That’s what the idea of shared datasets is all about.

Report Builder 3.0 has added functionality that lets you create reusable datasets that persist in the SSRS catalog and select shared datasets from the SSRS catalog. BIDS supports most of this functionality as well. However, you aren’t able to browse the catalog for a shared dataset to add to your BIDS project, which in my opinion is a serious shortcoming. In BIDS, you have to manually import the shared dataset from the catalog and add it to your report project.

When it comes time to choose a data source in the report creation process (step 4), you can choose to create a new dataset from the results of a query or use a cataloged shared dataset. You can imagine the performance difference when choosing a shared dataset. Consider that some datasets can take hours or days to create; having the results persisted in the catalog for reuse can dramatically improve performance.

When building a shared dataset, you have to use a cataloged data source. You can’t use one that’s built into the dataset. Good candidates for shared datasets include the following:

• Pick lists of valid countries, part numbers, part colors, or other data that doesn’t change frequently
• Rowsets used by more than one report
• Datasets constructed with a proprietary mechanism

Shared datasets can be customized by report DBAs to take advantage of evolving table statistics, additional indexes, data availability, and query performance tuning.

SSRS 2008 R2 supports a number of mechanisms to refresh cataloged shared datasets so reports can work with the latest data. In Report Builder, you make sure a report uses the latest version of a shared dataset by clicking the Check for Updates button. Report Builder will then inspect the SSRS catalog for changes to any published shared datasets or shared report parts.

Like shared datasets, shared report parts are new to SSRS 2008 R2 and supported by Report Builder 3.0. Shared report parts provide a way to preconfigure a report to contain a preconfigured table, matrix, map, or chart. Although interesting, their addition isn’t nearly as important as shared datasets.

Report Builder 3.0 vs. BIDS

Which tool should your organization use for building reports: Report Builder 3.0 or BIDS? It’s a matter of personal preference and who will be using the tool. BIDS is a bit more difficult to grasp initially, but once users get the hang of it, they become more proficient report developers far more quickly. And for users who often write reports, BIDS has features that make them more efficient at it. For example, unlike Report Builder, BIDS lets you create a report solution in which you can visually see dozens or hundreds of reports at a glance and manage the folders where they’ll be deployed. Plus, BIDS is a project-based design tool, whereas Report Builder is a single-report design tool. This means that, in Report Builder, you have to close one report before you can begin working on another. This makes working on subreports difficult because you constantly have to step back and forth between report designers.

With that said, there are situations in which using Report Builder makes sense (assuming the report DBAs do their job). For example, paradevelopers who occasionally need to create a simple report or clone an existing report are better off with Report Builder, especially if they’re working with SharePoint-hosted reports, because SSRS 2008 R2 supports SharePoint lists as a data source. Another good fit might be analysts who occasionally need to build reports based on SQL Server Analysis Services (SSAS) cubes because SSAS data sources expose a user-friendly semantic layer.

No matter whether your company uses Report Builder, BIDS, or another reporting tool, you need to keep the following in mind:

• Is your database locked down to prevent users from accessing (and possibly broadcasting) proprietary or confidential data?
• Even if the report DBA creates a bank of debugged and documented views and stored procedures, what prevents a user from adding filters that (intentionally or otherwise) alter what the reports portray?

Any company that cares about its data and livelihood needs to carefully consider how that data is exposed to the people who generate the reports and how those people, in turn, expose that data in their reports.