SQL Server 2008's Report Designer Preview

Do information workers need an enterprise-class product such as Microsoft SQL Server? They don't write code, maintain systems, or develop cubes, so the answer is no; information workers have no need to directly work in SQL Server. Reporting is a different issue, though; report authoring has a long history (long before the arrival of SQL Server Reporting Services—SSRS) of being developed by both IT and non-IT professionals. In fact, non-IT personnel author most of the reports in most organizations.

Related: Unlock the Secrets of SQL Server 2008 Reporting Services

When SSRS was released in 2004 as an add-on for SQL Server 2000, it had a lot going for it. Assuming the client had already purchased a licensed copy of SQL Server, SSRS was free. It integrated very well with the other Microsoft software, and had a report wizard that enabled quicker initial report development. The version of SSRS released with SQL Server 2005 included several enhancements, most notably the Report Models and Report Builder (technologies that were at least partially bought during Microsoft's acquisition of ActiveViews).  Report Models and Report Builder let information workers author SSRS reports without having to use the Visual Studio shell. An IT professional could pre-configure these report models to meet all the report-authoring needs of the business folks. The nice thing about the Report Models solution was that it required action by someone with IT knowledge before a typical business user could author and consume reports.  

SQL Server 2008's Report Designer Preview

With the upcoming release of SQL Server 2008, SSRS will once again be enhanced, this time with the addition of an end-user report authoring tool that doesn't require  an IT professional to take action first (not counting security matters)! Report Designer Preview is a standalone, end-to-end report authoring tool that lets information workers create both traditional reports (meaning non-report-model based) and report-model-based reports in a user-friendly environment. The new tool is based on the Office ribbon interface that users are familiar with. The tool is very easy to navigate, commands are easy to locate, and the new Office interface it is based on is quite nice for those who use the Office 2007 suite of products. In addition, the tool facilitates local report preview functionality as well as a publishing capability. The publishing capability lets you publish reports to a designated SSRS 2008 Report Server. All data regions in Report Designer Preview are treated as tablix regions. Tablix is a new data region that combines attributes of both  tabular and matrix report types.

Exploring Report Designer Preview

Now that you understand why the tool exists, it's time to start learning it. First, launch Report Designer Preview by clicking Start,Programs,Microsoft SQL Server 2008,Reporting Services, then Report Designer Preview. Once the tool has launched it will display a new, empty report as shown in Figure 1, below.

The first thing you should notice is that the tool (as mentioned) is based on the familiar Office 2007 ribbon interface including the jewel icon. Click the icon and you will see the following operations:

The New operation simply creates a new report. Open opens a previously created Report Definition Language (RDL) file. I've personally tested this operation in conjunction with Visual Studio's Report Designer. I have created reports in Visual Studio and then opened them in Report Designer Preview—the only requirement is that you recreate the report's data sources. To save a copy of the report to the file system in RDL format (XML), use the Save or Save As operations. Publish lets you deploy a report to an intended 2008 Report Server.

The Save, Undo, and Redo commands sit beside the jewel icon. They let you save a report, undo changes, and redo changes, respectively. Next come the familiar ribbon tabs. In Report Designer Preview they are Home, Insert, and View.

The Home tab contains basic report-modification options, including Clipboard, Font, Alignment, Border, Arrange, and Preview. Insert contains Data Regions, Report Items, Subreports, and Header & Footer options. The View tab contains the Report Views and Show/Hide options. Report Views contains the Design and Preview options, and Show/Hide contains the following four panes, which you can optionally show:

These panes contain the remainder of the designer's content we still need to explore.

Data Pane

The Data Pane contains the actual data sets that provide the data for your report. From here you can access both your report's custom data and built-in fields. This section is also where you define your report's custom parameters and images. The basic premise of defining your custom data is that you first create a new data source and then define individual data sets based on that data source. The common right-click context options apply to the Data Pane's various members, including folders, parameters, data sources, and data sets. Figure 2, below, shows the Data Pane with a data source, two data sets, and an image created.

Properties Pane

In the Properties pane, you assign properties that pertain to the various report objects. Most properties have an expression option that lets you assign the property a value at runtime. You can sort the properties by alphabetical order or by categorization. If you have worked with Visual Studio you will be quite comfortable with the Properties pane.

Grouping Pane

If you elect to group your report's rows or columns they will show in the Grouping pane. The Grouping pane contains two main sections: Rows and Columns. Once you designate a group in the designer, a corresponding entry will show under the Rows or Columns sections. Once you create the group, you can edit, delete, or add parent/child groups.

Ruler

When you turn on the Ruler pane you will see two rules displayed. One ruler is above the report and another to the left of the report, divided by inches, indicating the length of the various items on reports. The rulers are nice when you are trying to get items within a certain physical length or when you prepare your reports for printing. At this time, there's not a print layout feature in Report Designer Preview, though I wouldn't be too surprised to see it by the time SQL Server 2008's released to manufacturing.

Demo

Launching Report Designer Preview

1.       Click Start, Microsoft SQL Server 2008,Reporting Services, Report Designer Preview

Creating a Data Source and Data Set

1.       Click the "Click here to create a data source and data set for your report" button. This will launch the Data Source Properties dialog box.

2.       Type "AWDW" in the Name text box.

3.       Click the Edit button to create a new data source. Be sure that you use a valid SQL Server 2008 instance that contains the AdventureWorks DW database.

4.       Click OK to complete the Data Source creation.

5.       Click Next to advance to the Query Editor.

6.       In the Query dialog box, type the following code:

--AW Fact Finance Query

SELECT

               DT.DayNumberOfWeek

               ,DO.OrganizationName

               ,DDG.DepartmentGroupName

               ,DS.ScenarioName

               ,DA.AccountDescription

               ,SUM(FF.Amount) AS [Amount]

FROM

               dbo.FactFinance FF

INNER JOIN

               dbo.DimTime DT ON (FF.TimeKey = DT.TimeKey)

INNER JOIN

               dbo.DimOrganization DO ON (FF.OrganizationKey = DO.OrganizationKey)

INNER JOIN

               dbo.DimDepartmentGroup DDG ON (FF.DepartmentGroupKey = DDG.DepartmentGroupKey)

INNER JOIN

               dbo.DimScenario DS ON (FF.ScenarioKey = DS.ScenarioKey)

INNER JOIN

               dbo.DimAccount DA ON (FF.AccountKey = DA.AccountKey)

GROUP BY CUBE(

               DT.DayNumberOfWeek

               ,DO.OrganizationName

               ,DDG.DepartmentGroupName

               ,DS.ScenarioName

               ,DA.AccountDescription)

7.       Click Finish to complete the Data Set creation process.

Creating and Publishing the Report

1.       Click the Insert tab in the main menu.

2.       Then click the Table icon and a table data region should appear in the report's detail section.

3.       Right-click in any cell and select Insert Column to create three additional columns.

4.       Now, drag and drop all six fields under the DataSet in the Data Pane to the Report's Detail Section.

5.       Click the View main menu button.

6.       Click the Preview button in the menu to preview the report's content.

7.       Click the jewel icon and then select Publish.

8.       Confirm that the report server's URL is correct and click OK.

*Report Server URL for my SQL Server 2008 machine is http://se-sql2000-comp/ReportServer_SQL2008.

Keep An Eye On Queries and Reports

Report Designer Preview is a next-generation tool that facilitates information workers authoring traditional SSRS reports. You can leverage the new tool with Report Models as well, but this capability already exists within the Report Builder tool. End users might also need to learn SQL or MDX (the product doesn't currently include query builders). Given the tool's intended audience, I suspect that query builders will be included in the final version of the tool.

Administrators need to be cognizant of the reports and queries that end users are submitting to the organization's various data sources. In reality, several different dimensions drive who is authoring reports, and although I don't agree with information workers authoring traditional reports (mainly because of the performance implications), the trend is likely to continue. With that fact in mind, this new tool will make it nice for business users to escape the Visual Studio shell. Given the proliferation of such tools in our industry, you should take appropriate action as an administrator to prohibit business users from causing system problems. One such tool we see in the SQL Server 2008 to help on this front is the Resource Governor.

See also: Navigating Microsoft's Report Designer Maze