Data Points
Designing Reports with SQL Server Reporting Services 2005
John Papa
Code download available at:
DataPoints2006_06.exe
(206 KB)
Browse the Code Online
Contents
Many applications require some degree of integration with a reporting tool. A good solution, SQL Server™ Reporting Services 2005, provides Web-based reports and can be integrated into both Windows® Forms and Web-based applications. Plus, it introduces several enhancements over its predecessor, Reporting Services 2000, that make designing effective reports simpler than ever.
In this month's column I will show you how to design reports effectively using Reporting Services 2005, which can manipulate, sort, and group rowsets of SQL data. However, there are times when it is beneficial to prepare the data first using SQL Server, so I'll look at what to consider when that's necessary. I will also show how multivalued parameters (new to Reporting Services 2005) work and how you can easily parse the multivalued parameters using a SQL Server function. In addition, I'll discuss Report Definition Language (RDL), designer tools, and more.
Report Definition Language
The XML-based RDL defines how all aspects of a report should behave, including (but not limited to) the data source, query, report parameters, report layout, external code, and color schemes (read the RDL spec at
microsoft.com/sql/technologies/reporting/rdlspec.mspx). The schema for RDL is well defined and is openly available, allowing third-party applications to interact with and even build reports to work with Reporting Services.
Microsoft offers a few tools that can build reports including Visual Studio® 2005 and Business Intelligence Studio, which comes with SQL Server 2005.
Since RDL defines all aspects of a report, you can also open the RDL file using an XML editor or a basic text editor such as Notepad. I have often found it very useful to set some properties of a report in one of the designer tools like Business Intelligence Studio and then look at what changes were made to the RDL. For example, I recently wrote several reports that all had the same set of report parameters. The reports were very different in almost every way except that a subset of the report parameters were exactly the same. The parameters had a predefined list of acceptable values, captions, and default value settings. I was able to copy and paste this small portion of functionality from one report to the others within the RDL. Figure 1 shows what I mean. In this particular case I was able to save time by editing the RDL directly. In other situations, viewing the RDL can be helpful to see what code has been injected into a report.
Figure 1 Report Parameters Shown in RDL
|
<ReportParameters>
<ReportParameter Name="startDate">
<DataType>DateTime</DataType>
<DefaultValue>
<Values>
<Value>=Today</Value>
</Values>
</DefaultValue>
<Prompt>Start Date</Prompt>
</ReportParameter>
<ReportParameter Name="endDate">
<DataType>DateTime</DataType>
<DefaultValue>
<Values>
<Value>=DateAdd("d", 7, Today())</Value>
</Values>
</DefaultValue>
<Prompt>End Date</Prompt>
</ReportParameter>
<ReportParameter Name="status">
<DataType>Integer</DataType>
<DefaultValue>
<Values>
<Value>=1</Value>
</Values>
</DefaultValue>
<Prompt>Status</Prompt>
<ValidValues>
<ParameterValues>
<ParameterValue>
<Value>1</Value>
<Label>Online</Label>
</ParameterValue>
<ParameterValue>
<Value>2</Value>
<Label>Offline</Label>
</ParameterValue>
<ParameterValue>
<Value>3</Value>
<Label>Archived</Label>
</ParameterValue>
</ParameterValues>
</ValidValues>
</ReportParameter>
</ReportParameters>
|
Data Sources and Deployment
Once you create a reporting solution and add your first report to it using either Visual Studio or Business Intelligence Studio, the usual next step is to prepare the data for the report. To get the data into a report you must create a report data source and link it to the report. The data source tells your report what server, database, and credentials to use in connecting to the database. You can add a new data source by right-clicking on the Shared Data Sources node in the Solutions Explorer window and choosing Add New Data Source. The data source will ask you to provide the connection string information to use and a name (I named mine SampleDataSource). Later, when you create a DataSet for your report to use, you will specify the name of the data source.
The data source you use in your development environment will likely differ from the one you use in production. If so, you would not want your local data source to be deployed to the production server and thus have production reports pointing to your development database server. Fortunately, report designer tools allow you to specify whether or not you want your data sources to be deployed to the target server and overwrite the data sources on the target server.
Figure 2 shows the properties dialog of the sample project, which indicates that I have chosen not to overwrite the target server's data sources. (Also note that in my sample project, I am using the debug configuration and have specified that my target server is my local installation of Reporting Services 2005. You can create multiple configurations, which is what I do when I want to be able to deploy to different servers based on need.)
.gif)
Figure 2 Deployment Options
Data Preparation Considerations
When designing reports you can use either embedded SQL queries within the report or you can invoke a stored procedure. The decision is often predicated on your development department's standards; however, my preference is to use stored procedures, if for nothing else, for the ability to make minor modifications to the SQL without having to open a report.
Reports generally gather data from a relational database to be displayed in a more user-friendly manner, such as tables, hierarchical drill downs, matrices, and charts. In most cases the data stored in the database is not stored the way that users want to view it. This means that the transformation of the data into the desired format to be shown on a report must be done somewhere. One way is to let the report format the data the way the user wants it. Another method is to massage the data in a stored procedure before it gets to the report. There are pros and cons to each approach and sometimes a combination is best.
In general, grouping and other aggregation features are performed faster in a database management system than in a report. On the other hand, providing the data in a raw format to the report offers the flexibility of changing the grouping within the same report.
The upside to filtering in the report is that you can grab a lot of data once from the database and then apply various filters in the report without hitting the database again. The problem with this technique is that if you grab too much data you are really slowing down the initial load of the report only to show a fraction of the data you return. A general rule of thumb I use with report filters is to avoid them and instead first try filtering in the stored procedure (or in the SQL). Only if the performance tests prove this to be too slow do I opt to use report filters.
DataSets and Parameters
The Data tab is where you set up the DataSet(s) that a report will use. My sample report, which is named BasicProcParametersReport.rdl, is going to get a list of customers and their orders, so I created the stored procedure shown in Figure 3.
Figure 3 Gathering the Data
|
CREATE PROCEDURE prCustomerOrders
@startDate DATETIME, @endDate DATETIME
AS
SELECT
c.CustomerID, c.CompanyName,
c.City, c.ContactName,
o.OrderID, o.OrderDate,
od.UnitPrice, od.Quantity,
od.UnitPrice * od.Quantity AS ExtendedPrice
FROM
Customers c
INNER JOIN
Orders o ON c.CustomerID = o.CustomerID
INNER JOIN
[Order Details] od ON o.OrderID = od.OrderID
WHERE
o.OrderDate BETWEEN @startDate AND @endDate
ORDER BY
c.CompanyName, o.OrderDate
|
I create a DataSet on the Data tab and tell it to use the data source I already created (SampleDataSource) and the stored procedure prCustomerOrders. When you enter this information on the query tab of the Dataset dialog window and click OK, the report designer grabs the fields that the stored procedure returns as well as a list of the parameters required by the stored procedure. It automatically creates report parameters to match them and associates the stored procedure's parameters with the report parameters, as shown in the Parameters tab of the Dataset dialog window (see Figure 4).
.gif)
Figure 4 Dataset Parameters
There are many ways you can set the values of parameters. There is no rule that says you have to have a report parameter that matches the parameter in your SQL. This comes in handy if you want to use an existing stored procedure but you do not want the report users to be able to choose the values for all of the parameters. If you want to predefine the value of a parameter to a stored procedure, you can go to the parameters tab of the Dataset dialog window and enter the value manually.
The technique I like to use when I want to set a value of a parameter to a stored procedure without allowing the user of the report to change it is to keep the matching report parameter but set it to hidden. You can do this from the Layout tab by right-clicking and choosing Report Parameters from the menu. From the Report Parameters dialog window you will see the two parameters that were previously created to match the stored procedure's parameters (see
Figure 5). From here you can set the default value for a parameter using an expression such as the following, which calculates the date one year from today:
|
=DateAdd("yyyy", 1, Today())
|
.gif)
Figure 5 Report Parameters
You can also specify a list of values users can choose from. This can be a hardcoded list (non-queried) or a queried list derived from a stored procedure. I could create a Country report parameter that is populated from a query. To do this, I'd create a second DataSet called CountryDataSet that gets a list of countries to populate the parameter's dropdown list. For a working example, see SimpleReport.rdl included in the download on the MSDN®Magazine Web site. SimpleReport.rdl gets a list of customers from the specified country dropdown list parameter. The list of countries is retrieved from a second DataSet defined in the report.
Multivalued Parameters
The Report Parameters dialog window lets you set basic properties like the name of the parameter, its data type, and the caption. You can also set whether or not the parameter is hidden from the user and if the parameter allows multiple values. This latter feature is new to Reporting Services 2005 and is very useful for letting a user select more than one value from a list.
For example, I could modify prCustomerOrders from Figure 3 to accept a list of customers (from a dropdown list with checkboxes) as a parameter and then set up the corresponding report parameter to be a multivalued parameter (MVP). The MVP lets the user select more than one value and then passes those values to the stored procedure as a comma-delimited string. Of course, if you use the MVPs, you need to account for parsing out the parameter values in your stored procedure.
The example report for MVPs in the downloadable code is named MVPReport.rdl. This report uses the stored procedure prCustomerOrders_SelectCustomers which, in addition to accepting date parameters, also accepts a list of CustomerID fields. To parse out the list of CustomerIDs I created a SQL Server function named Split (also included in the code download). The Split function accepts a delimited list of values and a delimiter. It parses the list of values and returns a table with a single column called Item. In my example, each row of the resulting table will contain a CustomerID from the comma-delimited list. Another option, if using SQL Server 2005, would be to use the common language runtime (CLR) and a managed stored procedure. However, this Split function will work in either SQL Server 2000 or SQL Server 2005. I added the following line of code to the stored procedure's WHERE clause to match any of the customer IDs that are passed in the list:
|
AND c.CustomerID IN
(SELECT Item FROM dbo.Split(@customerIDs, ','))
|
Figure 6 shows the results of the multivalued parameter report (MVPReport.rdl) after selecting several customers from its MVP. Notice that you can select one or more customers from the list or you can choose the Select All option.
.gif)
Figure 6 Multivalued Parameters
Expressions
The expression builder in Reporting Services 2000 left a lot to be desired. While you could type in a slew of Visual Basic® .NET-compliant methods and operations to create expressions, there was no palette of functions and operations to choose from. Reporting Services 2005 addresses this issue as it has more features built into its expression builder, including a categorized list of available functions and operators that can be used to build expressions. The expression builder also provides IntelliSense® in its code window to assist with building expressions.
Figure 7 shows the expression builder with an expression that calculates the date one year from today. Basic logic, including if statements, can be performed through its code window. However, if the logic gets more complicated than a line or two, consider encapsulating the code into either the report's code window or in an external assembly.
.gif)
Figure 7 Expression Builder
Expressions are quite valuable and offer a great deal of flexibility to reports. For example, they can be used to set report properties such as the values of textboxes, tooltip text, visibility of controls, toggle appearance, fonts, and countless other properties. They can also be used to set a parameter's default value, the color of the bars on a chart control, or even a value to group the data in a report. In fact, most places inside a report can accept an expression to set its value. Anywhere you see the function button (circled in Figure 5) you can set an expression. In addition, you can enter an expression just about anywhere you can type in a value even when there is no explicit button to open the expression builder.
Wrapping Up
Before designing the report using the Visual Studio or Business Intelligence tools, it pays to do a little design preparation. The tools available in Reporting Services 2005 expose a wealth of functionality that can help you build your applications faster and with more flexibility. In the next installment of Data Points, I will demonstrate how to use report links, how to embed code within a report, and how to access custom external .NET assemblies from a report. I will also explore how to make more advanced reports using the chart control and some of the other controls available for Reporting Services 2005.
Send your questions and comments for John to mmdata@microsoft.com.
John Papa is a Senior .NET Consultant with
ASPSOFT and a baseball fanatic who spends most of his summer nights rooting for the Yankees with his family and his faithful dog, Kadi. John, a C# MVP, has authored books on ADO, XML, and SQL Server, and can often be found speaking at industry conferences such as VSLive or blogging at
codebetter.com/blogs/john.papa.