Sql Server Reporting Services

Method

Microsoft SQL Server 2008 Reporting Services was evaluated by utilizing the Reporting Services built in to Microsoft Visual Studio 2008 as well as the standalone Reporting Services of Microsoft Report Builder coupled with various tutorial documents from Microsoft Developers Network.

Review

The first thing that comes to mind when thinking about this evaluation of SQL Server Reporting Services (SSRS) is the old saying “look before you leap”. Perhaps it was because I did not fully know what I was getting into or that I was not aware of the many side tasks I would have to take to complete this evaluation, but there were more than a few times when I felt like I had bitten off more than I could chew. At this point I’m just happy to say that the evaluation is complete. The result is a new found respect for the complexity and power of SSRS, especially for those who have mastered its many features and functions. The following white paper documents my evaluation of SSRS and provides feedback based on the experience.

The second option is to download and install Microsoft Report Builder. Report Builder can be installed on any Windows machine running XP or higher and is considerably easier to setup than the reporting services on an SQL Server. Report Builder proves to be more of a lite version of the reporting services integrated into Visual Studio. While the two platforms are undoubtedly running the same engine, Microsoft seems to have excluded some of the more advanced functionality found in Visual Studio in favor of presenting a more user friendly interface in Report Builder. In fact Report Builder features a user interface based on the layout of Office 2007 applications, a clear attempt to cater to business users. Visual Studio is a much more complex environment for designing reports but proves to be more powerful in the long run than its standalone counterpart. Both platforms save reports in Microsoft’s Report Definition Language (.rdl) format which means a report created in one platform can be opened in the other granted you can connect to the same datasource and design the same query. Provided I have a more technical background I prefer to design reports in Visual Studio, but I fully understand why non-technical and business users would find Report Builder more comfortable and easier to use.

Getting started in Visual Studio is not as easy as one would hope. In order to create a blank report template, you first must create a new project and then add a report to that project. To help streamline the reporting process Microsoft provides a Wizard that walks users through the entire report creation path and produces a fully designed report. The report creation path can be broken down into four steps: datasource connection, data selection, report design, and report deployment. This Wizard is useful for learning the reporting path but ultimately fails to teach users how to produce truly unique and customized reports on their own. One major flaw with the Wizard is that if your report does not turn out the way you desired there is no way to go back into the Wizard and make changes, which means you are forced to either go through the entire Wizard again and generate a new report or stick to altering the report on your own. Additionally, the Wizard only supports the creation of either a table or matrix report. While these two data regions are the most commonly used, at some point users will undoubtedly need to implement other data regions and thus will be forced to design from scratch regardless. The Wizard proved useful in the initial stages of the evaluation and learning SSRS would have been much more challenging without, but in the end the Wizard was generally disregarded in favor of designing reports by hand once of the underlying structure of the software was better understood.

            Once you have enough experience using the Wizard and you feel comfortable with the environment of Visual Studio, it is time to move on to designing reports from the ground up. There is a surprising lack of support for first time users trying to design reports on their own. The best source of help comes from Microsoft Developers Network (MSDN). Unfortunately it appears as though the majority of the tutorial documents were created by MSDN community members and only one of the five recommended articles has a rating above one star out of five. Considering Google searches failed to turn up anything useful (i.e. tutorial videos in Spanish), the only option is to put your head down and crank through the tutorial documents as best you can. All the training documents make use of the Microsoft sample database AdventureWorks which contains mock data of an outdoor and sporting goods retailer. Installing and setting up AdventureWorks turns out to be a complex task all in itself that consumed a few days time. The MSDN article on how to setup and install AdventureWorks proved useless and in the end the task was passed off to a network administrator, who proceeded to spend a few days of his own valuable time setting up and installing the database. As soon as the AdventureWorks detour was complete, the time came to move on and actually make use of the MSDN tutorial documents.

            The first tutorial focuses on how to design tables. Tables in SSRS are members of the Tablix family, which also includes lists and matrices. These data regions grow dynamically down or across the page during report generation. Once you’ve established a connection to AdventureWorks the next step is to design a query. Queries are one part of SSRS that users will love to hate. The query is the backbone of your report and learning how to design them is a quintessential part of SSRS, so you can imagine the frustration when the tutorial provided code to copy and paste. Rather than teach users how to design their own queries, the MSDN solution is to copy and paste a query that in practice produces an error and fails to select the necessary data. With enough patience and practice you can decipher the SQL code in the query and use the graphical query designer to recreate a working version of the query, but this is something business users would not desire or have the knowledge to do. Armed with the mimicked query it came time to create the table. A key element of designing both tables and matrices is understanding how the Row and Column Groups sections work. With tables, adding a variable to the Row Group will base the rest of the variables off of that variable. In this example we’re creating a sales history for AdventureWorks so it makes sense that the first variable in the Row Group is Order Date and the second is Sales Order Number. This effectively breaks the table down first into separate days and then further organizes the data by each individual order on a given day. Additional variables added to the table will display information relative to the order numbers, such as Product, Quantity, and Retail Value. Using one of SSRS’s handy features, you can right click on any numerical values and Add Total which creates a new row in the table and automatically places a SUM() function of the variables there. Thanks to the established Row Grouping you can easily create a total for every order, a total for each month, and then finally a grand total.

            A key element of SSRS introduced along with the table tutorial is the Expression Editor. The Expression Editor gives users the power to create their own custom variables based off of the other variables in their datasource. For example, in creating the query for the sales history table you can select the total selling value of each product as well as the commission percent each sales associate is compensated with. You can then use the Expression Editor to create a variable that multiplies the total value of each product by the commission percent to find the sales commission associated with each individual item. You can take things even further by creating another expression that is a SUM() of the sales commission from each item and give a total sales commission for each order. Based on the Row Grouping, the Add Total feature can then be used to create an expression that reports the total monthly sales commission and finally the grand total sales commission. The Expression Editor features mathematical operators for numerical values as well as comparison and text operators for alpha numeric values. You can use the Expression Editor to report data between a date range or only report data if it falls below or above a certain quantity.

            After completing the table tutorial and also learning how to use the Expression Editor, it is time to move on to the matrix tutorial. This tutorial stays on the same track as the previous by providing users with another faulty query. The query makes use of a predefined SQL function that the graphical query designer has no means to reproduce, so in the end anyone trying to recreate this query will be forced to write SQL code. Once a working query has been constructed you can move on to designing the matrix. In addition the Row Groups section used for tables, matrices also make use of the Column Groups section. In this example the Product Category and Product Subcategory variables are used as Row Groups and the Territory and Year variables are used as Column Groups. This breaks your matrix down by Product Category and Product Subcategory and then reports the sales numbers for each Product Subcategory in each Territory and alternatively during each Year. The Add Total feature again proves useful by automatically aggregating your data and reporting the total sales figures. In addition to the total row added in the table example, matrices give you the option of adding a total column. This column gets added to the far right-hand side of your matrix and sums the sales of every Product Subcategory to report the grand total sales number across every Territory and Year.

            The next data region on the tutorial list is charts. Charting is one of the most powerful features of SSRS, and their brilliance lies in their ease of use. To add a chart to your template either select Chart from the toolbox or right-click and insert Chart and the Select Chart Type window will open. This window gives you access to eight different chart types with several variations of each. After you select the chart type, the template for that chart type will be added to your report. If you then drag any data variable onto the chart three subwindows will appear on the top, bottom, and right-hand side of your chart. The bottom subwindow is for Category Fields and placing a variable here will define it as the dominant variable of your chart. For example, if you add a Name variable to a pie chart or bar graph Category Field, each Name will be represented by a different color on the pie chart or as individual variables on the y-axis. If you then add any variables to the top subwindow, Data Fields, they will be the data reported for each category of your chart. So if you’re creating a sales contributions pie chart and define the Name variable as your Category, adding 2002 Sales as the Data will show each sales teams member’s contributions to the total sales in 2002. The final subwindow, Series Fields, is used to display different layers on a chart. For example, if you were plotting each sales team member’s statistics over a period of time you would place a year or date variable in the Category Fields, sales numbers in the Data Fields, and finally a name variable in the Series Fields.

            One of the few benefits of the MSDN tutorials is that they became easier as they progressed, and if creating charts was easy than implementing dashboards is a cinch. Dashboards are used as key performance indicators (KPIs) and compare numerical values against a certain range. So for example if you’re a retailer you could use dashboards to track daily sales numbers against a target goal or use them to alert you when inventory levels are becoming low. To add a dashboard to your report, either select Gauge from the Toolbox or right-click and Insert a Gauge. The Select Gauge Type window and here you choose to implement either a linear or radial gauge. Next your gauge will be added to your template and you then drag the item you wish to report on to the gauge. The final step is to click on the gauge and select the scale. You then must right-click and go to Scale Properties. This is where you enter the minimum and maximum values of your scale as well as choose between linear or logarithmic scales. Thanks to Row and Column Grouping, dashboards can be added to tables and matrices giving you a graphical representation of any important numerical values.

Massive Learning Curve