Report Design Advanced Features - SQL Server 2008

As you’ve seen, it is fairly easy to create a basic report. But by no means should this lead you to believe that very complex reporting solutions can’t be built with Reporting Services. It is possible to have multiple master detail reports, subreports, charts, gauges, and advanced formatting—all within a single report. Reporting Services also provides the ability to generate totals and aggregates on the fly when needed (although if you need this type of functionality on a larger scale, Analysis Services is probably the proper tool for the job). Here, we’ll review some of the more advanced Reporting Services features. The Tablix, Gauge, and Chart data regions are discussed in the “Data Regions” section later in this chapter.

Multivalued Parameters

Reporting Services supports the concept of multivalued parameters for report generation. Multivaluedparameters are very useful in reporting applications. Imagine the scenario where you want to be able to select sales based on the state in which they exist. However, you may have sales reps who cover multiple states, so they want to access their customers from multiple states at once. With multivalued parameters, you can provide this capability.

To create amultivalued parameter, you create your parameters as you normally would, except you also check the Allow Multiple Values check box in the Report Parameter Properties dialog box, as shown in Figure.

Creating multivalued parameters

Creating multivalued parameters

Once you have specified that your parameter will be multivalued, the Reporting Services runtime takes care of displaying the parameters as check box options in the UI for users to select the different values, as shown in the example in Figure. There is even a Select All option that will select and deselect all parameter values.

Runtime UI for multivalued parameters

Runtime UI for multivalued parameters

When a user selects amultivalued parameter, Reporting Services returns the selected values as a comma-delimited list. If you want to use that list in an expression, you will need to parse that list into its different values. You can also pass values for parameters within a URL to Reporting Services using a comma-delimited list.

Multivalued parameters have the following restrictions:

  • They cannot accept null values.
  • They are always returned as strings, so you need to make sure your stored procedures or logic can understand and parse the strings.
  • You cannot use them in filters since they are not deterministic.
  • You cannot use them with stored procedures.
  • Your query must use an IN clause to specify the parameter, such as SELECT * FROM table WHERE name IN (@NameMVParam).

If you want to use multivalued parameters in expressions, you can use the Join and Split functions to join together or split apart the values for your multivalued parameter. With multivalued parameters, you can use the Label, Value, and Count functions to return the names of the parameter values. The Value function returns the values for the selected parameters that might be different from the label. Count returns the count of the values for the parameter. For example, the following expression returns the values for amultivalued parameter named Cities:

=Join(Parameters!Cities.Value, ", ")).

Finally, as with any parameters, make sure to not overdo the number of options you allow in your parameter. Limit it to the list that is required for the user to make the right selections; otherwise, performance will suffer.

DatePicker for Date Values

To make it easier for end users to select date values used in parameters, Reporting Services includes a DatePicker runtime control. But note that the parameter must use a datetime type. Also, it cannot be selected for amultivalued parameter. If you specify amultivalued parameter, you will get a dropdown list for your parameter value selection, not a DatePicker control. Figure shows selecting a parameter that uses the DatePicker control.

Runtime UI for date parameters

Runtime UI for date parameters

Interactive Sorting

Interactive sorting is done through text box properties. This means that you do not select an entire column to view the interactive sort properties, but instead click the text box that is the column heading in a table. Interactive sorting works with tables, lists, and matrices, as well as grouped or nested data in those containers. In addition, you can sort different multiple columns using this technology. Figure shows the Properties dialog box for interactive sorting.

Setting interactive sort settings at design time

Setting interactive sort settings at design time

You can specify the data region or grouping to sort, or where to evaluate the scope of the sort expression. This is useful if you want independent sorting based on grouping. For example, you could have a sales report grouped by country and then by city. By default, changing the sort order of countries does not affect the sort order of the cities in those countries. However, you could make resorting the countries affect the sorting of the cities as well.

The UI element for the sort is an up arrow or a down arrow, depending on the sort order. When you do the sort inside the UI, a request is actually sent back to the server to redo the sort. It is not a client-side sort only, so you must be careful about performance implications of users re-sorting on many columns in your report.

Analysis Services Integration

Reporting Services provides tight integration with Analysis Services via the BIDS environment. By having Reporting Services integrate with BIDS, you now have one place where you can create andmanage your Analysis Services technologies, such as cubes, key performance indicators (KPIs), anddata-mining settings. Finally, you can create and deploy your reports from BIDS. Figure shows the interface for the Analysis Services in BIDS.

Analysis Services interface in BIDS

Analysis Services interface in BIDS

With Reporting Services, you can easily add your Analysis Services cubes to a report. Let’s walk through the creation of a new report that uses Online Analytical Processing (OLAP) technologies in SQL Server to give you a better understanding of how the Analysis Services integration works. The walk-through assumes you already have an existing Analysis Services cube. For the purposes of the example, we’ll use the AdventureWorks sample database and cubes.

First, create a new Business Intelligence project and select Report Server Project as the template. Once you select the report project and give it a name, you’re dropped into Report Designer within BIDS. In Solution Explorer, right-click the Shared Data Sources node and select Add New Data Source. For the provider, you will use the .NET Framework Provider for Microsoft Analysis Services. Select Microsoft SQL Server Analysis Services as the data source, and enter the path to the AdventureWorks Analysis Services environment.

If you double-click your data source after you create it, you will see that Analysis Services appears as a top-level data source, just like SQL Server, Oracle, OLE DB, and ODBC. The Properties dialog box for the data source.

Analysis Services as a top-level data source

Analysis Services as a top-level data source

Next, add a new report by right-clicking Reports and selecting Add ➤Add New Item ➤Report, which creates a blank report. Type in your report name, such as Report1.rdl, and click Add. Once the report is created, you can add a new dataset to the report by clicking the link on the report surface. This new dataset will be based off your Analysis Services data source. After you select the shared data source you created earlier and click Next, you are dropped into the Analysis Services Query Designer, as shown in Figure. Instead of having to write Multidimensional Expressions (MDX) by hand, you can drag and drop measures in the Query Designer. The MDX is automatically generated for you based on your interaction with the Query Designer. If you are a power MDX user, you can still go into the generic Query Designer and write your own MDX.

The Analysis Services Query Designer in Reporting Services

The Analysis Services Query Designer in Reporting Services

Since you want to create a quick sales report that shows reseller sales by geography and fiscal year, you need to drag and drop items from your measures, KPIs, or Analysis Services hierarchy over to the right side of the Query Designer window. The first level in the hierarchy that you’ll add to the report is Geography. When you drag and drop, the Query Designer executes the query to return a preview of your result set, as shown in Figure, once you complete the steps that follow.

Previewing the result set in the Query Designer

Previewing the result set in the Query Designer

First, drag and drop the dimension called Geography onto the grid on the right. You can find this dimension under the AdventureWorks cube. This will create the Country, State-Province, City, and Postal Code columns. You can remove the Postal Code column if you want, since it is a level of detail that we don’t need for this report. Next drag and drop just the Reseller name from the Reseller dimension onto the grid. Then drag and drop the Date.Fiscal Year and Date.Fiscal Quarter of Year dimensions from the Date dimension under the Fiscal grouping.

Next, you need to drag and drop some measures. The first one is the Reseller Sales Amount measure. You can find this under the Measures hierarchy, then under Reseller Sales. Drag and drop the Reseller Sales Amount measure onto the grid. Then drag and drop the Reseller Order Quantity measure from the same hierarchy.

Right now, this returns all fiscal years. Let’s refine the query a bit to return only two fiscal years. To make this change, you just need to select the dimension on which you wish to filter in the top filter dialog box, the hierarchy in that dimension that you want to use, the operator, and finally the value. Here, select the Date dimension, the Date.Fiscal Year hierarchy, and check to see whether the Fiscal Year is equal to FY 2004 or FY 2005, as shown in Figure.

Creating filters in the Query Designer

Creating filters in the Query Designer

If you look at the MDX that the designer generates for the entire query, as shown in Figure, you can see that you would not want to do this by hand!

MDX generated by the Query Designer

MDX generated by the Query Designer

Now that you have your dataset, switch to the design view of Report Designer by clicking the Design tab. You will notice that the fields from your cube are available in the field list to the left of the design surface. You can drag and drop your fields onto controls that you add to your report. You’ll use the Table and Chart controls to display your data.

Drag a Table control from the Toolbox onto the design surface. You can use this control to add your information to the report. (You could also use the Matrix control if you want to have expanding columns as well as expanding rows.) In the table, you’ll just drop some fields from the Dataset tab into the details: Country, State_Province, City, Reseller, Fiscal_Year, Fiscal_Quarter_of_Year, and Reseller_Sales_Amount. Right-click the Reseller_Sales_Amount column and select the text box properties. Within the Number section of the Text Box Properties dialog box, set the Category value to Currency. Also select the Use 1000 Separator (,) check box and choose to Show Zero as - . By default, Reporting Services will render two decimal places in a currency value. Figure 19-25 shows the form design with just the table added.

A table layout with OLAP information

A table layout with OLAP information

Next, you may want to chart the information to see how each reseller is doing. The charting technology has been significantly updated in SQL Server 2008 and will be covered in detail in the “Updated Chart Data Region” section later in this chapter. Adding OLAP fields to a chart is another drag-and-drop operation. Drag and drop a Chart control from the Toolbox onto your report. Select 3D-Pie as the chart type. From the Report Data tab, drag and drop the Fiscal_Year field onto the category field drop target on the chart. Drag and drop the Reseller_Sales_Amount field onto the data field drop target on your chart. Figure shows a simple chart that will show yearly sales.

A chart with OLAP information

A chart with OLAP information

You can then preview or deploy your new report using the built-in previewing or deployment support of BIDS.

In this walk-through, you’ve seen how you can leverage your OLAP information as easily as your relational information in SQL Server 2008 Reporting Services.

Reporting Services Data Sources

Reporting Services provides, out of the box, connectivity to a vast array of data sources, such as XML/web services, Hyperion, SAP, Oracle, ODBC drivers, and OLE DB drivers. The SQL Server Integration Services (SSIS) data source connects to and runs an SSIS package and returns the results so you can report on them. There is also the option to derive the values used within a data source using expressions. We’ll cover some of the more interesting data sources here.

Data Source Expressions

To use a data source expression, you should create your data source as you normally would using a static connection string. Expression-based connection strings work only with nonshared data sources.

Lay out the report the way you want, and then add a parameter that will specify your data source. You can make this parameter use static values or pull from a data source to get its values. Once you have set the parameter, you can go back to your data source definition and replace the static value with a parameter. An example of a connection string using a data source expression is shown here:

="data source=" & Parameters!Datasource.Value & ";initial catalog=AdventureWorks"

Figure shows setting a data source using an expression.

A data source that uses an expression

A data source that uses an expression

XML/Web Services Data Source

The XML/web services data source (called XML in the data source drop-down list) allows you to connect to an XML document, a web service, or a web application that returns XML data. You simply specify the endpoint URL to which you wish to connect. For example, if you wanted to connect to a web service, you would input the URL to the ASMX or WSDL file for the web service. Reporting Services supports connecting to web services like SQL web services using the new native web services technology in SQL Server, as well as the Google, Amazon, and eBay web services. You can also use web services that you’ve created using Visual Studio or other IDEs, You can also connect to just pure XML data sources. This means that if you have a web application that you can address via URL and that returns an XML document, you can use that application to retrieve information to your report.

SAP Data Source

The SAP data source lets you report directly from an SAP data source so that you can provide rich reporting for SAP applications, without needing to extract the data into another intermediary data source.

SQL Server provides an SAP ADO.NET provider in the Enterprise Edition. You can download the provider from http://msdn.microsoft.com/downloads. Once you have downloaded the provider, you can start executing queries against your SAP system from any environment that uses ADO.NET, including Reporting Services. There are two query commands you can send to this data source. The first executes RFCs/BAPI, which is the API for SAP. The second executes SQL queries against SAP tables. Refer to SQL Server Books Online for details on using the SAP data source.

Custom Report Items

Custom report items are primarily targeted at partners who want to extend Reporting Services with custom controls that are processed when the report is processed, and then output an image onto the report. These controls allow the enterprise developer to build custom controls in any .NET language and deploy them.

For example, if you wanted to add a new chart type that Reporting Services does not support, such as one that shows advanced financial or statistical analysis, you can write a custom report item (sometimes called a custom ReportViewer control) that integrates with the design-time and runtime Reporting Services experience. With the image you produce on the report, you can create an image map so that you can provide interactivity with your control. Most likely, you will not create custom report items, but you will probably consume them in your reports from third-party providers.


All rights reserved © 2018 Wisdom IT Services India Pvt. Ltd DMCA.com Protection Status

SQL Server 2008 Topics