Business Intelligence with Microsoft SQL Server Reporting Services – Part 2

By Adnan Masood

• Part 1
  • Introduction
  • Reporting Services Installation
  • Programming Reporting Services
  • Report Manager
• Part 2
  • Parameterized Reports
  • Calling Stored Procedures from Reports
  • The Report Viewer Control
• Part 3
  • Using Reporting Server Web Services
  • Using Custom Code in Reports
  • Subscription Scheduling in Reporting Server
  • Caching and Rendering Preference
  • Conclusion
  • References and Further Readings
  • About The Author

Parameterized Reports

Not all reports are static, most of the enterprise reports are data driven or parameterized. In the following section I’m
adding two variables, startdate and enddate in the Orders Report. These arguments will be used to filter the data being queried from orders table.

The parameters are regarded as query parameters and as you can see in the query window below, these parameters are passed in the SQL query string.

Upon execution, the Report Designer preview asks for parameters values. When executing from a browser, it shows the parameters toolbar with default values in it. You can change the values in the parameters toolbar. Parameters can be set to null, defaulted through report manager and can also be data driven.

Calling Stored Procedures from Reports

If your enterprise reporting system policy dictates business logic to be encapsulated in stored procedures, reporting services are there to rescue you again. SQL server stored procedure provides an efficient way of processing compiled SQL statements and can be used by reporting services to build reports on top of their result set.

In the following two examples, I’ll be using stored procedures from northwind database and display their result in reports. The dataset creation process is similar to one specified above, however this time command type is selected as stored procedure.

This first example is simpler; it uses the stored procedure "Ten Most Expensive Products" and use it as data source for its tabular data binding. The process of using stored procedure as data source can be seen below.

Figure: Creating a data source from stored procedure via data tab in reporting services IDE for visual studio.NET

This binding exposes collection of two fields (Ten Most Expensive Products, UnitPrice) which can be used as fields in the report table. After adding these fields in the recurring row, I’ve set the headings in header and we are all set to preview the report.

Figure: Adding the fields in report table.

Upon execution, the report will look like the following.

Figure: Report preview which uses Ten Most Expensive Products as data source.

Second example is a bit complex as it uses stored procedure as well as SQL to retrieve the parameters for combo box. The intent of this report is to provide sale by the category of products which includes beverages, produce, seafood, etc. Every product is associated with a category and the stored procedure "SalesByCategory" takes category as input and provide the sales breakdown of products in that category. However, to achieve this, we would need to display the list of categories in the report. Here is the step by step diagrammatic flow of how to achieve this goal.

Figure: Creating a stored procedure based data set.

First, as its name depicts the sales by category stored procedure provides the sales history on the basis of product category. To provide interactivity in the report, we need to acquire the data driven parameters i.e. list of categories from database. This can easily be achieved from report parameters window as shown in this figure below. The report is reading category name, order year and their corresponding values from database as parameters.

Figure: Report Parameters being retrieved from database (step 1)

Figure: Assigning default Report Parameters. (step 2)

Figure: Assigning the corresponding value fields for report parameters. (step 3)

Finally the report could be seen as follows. The Category name and order are drop down lists for selection and the report takes these parameters and pass them on the salesbycategory stored procedure for the required result set.

Figure: Report preview; combo boxes for category details

This whole procedure as demonstrated with GUI above can be represented in RDL as follows.

<DataSources>

  <DataSource Name="Northwind">
    <rd:DataSourceID>1a755f4d-006e-42ce-804b-852dc13c6840</rd:DataSourceID>
    <ConnectionProperties>
      <DataProvider>SQL</DataProvider>

      <ConnectString>initial catalog=Northwind</ConnectString>
    </ConnectionProperties>
  </DataSource>
</DataSources>

Listing: The RDL segment for data source. Notice the data source id as guid to uniquely identify the data source

<DataSets>
  <DataSet Name="Northwind">
    <Fields>
      <Field Name="ProductName">
        <DataField>ProductName</DataField>

        <rd:TypeName>System.String</rd:TypeName>
      </Field>
      <Field Name="TotalPurchase">
        <DataField>TotalPurchase</DataField>

        <rd:TypeName>System.Decimal</rd:TypeName>
      </Field>
    </Fields>
    <Query>
      <DataSourceName>Northwind</DataSourceName>

      <CommandType>StoredProcedure</CommandType>
      <CommandText>SalesByCategory</CommandText>
      <QueryParameters>
        <QueryParameter Name="@CategoryName">

          <Value>=Parameters!CategoryName.Value</Value>
        </QueryParameter>
        <QueryParameter Name="@OrdYear">
          <Value>=Parameters!OrdYear.Value</Value>

        </QueryParameter>
      </QueryParameters>
      <rd:UseGenericDesigner>true</rd:UseGenericDesigner>
    </Query>
  </DataSet>

Listing: The RDL segment for dataset. Query parameters are specified in this segment.

  <DataSet Name="Categories">
    <Fields>
      <Field Name="CategoryID">

        <DataField>CategoryID</DataField>
        <rd:TypeName>System.Int32</rd:TypeName>
      </Field>
      <Field Name="CategoryName">

        <DataField>CategoryName</DataField>
        <rd:TypeName>System.String</rd:TypeName>
      </Field>
      <Field Name="Description">

        <DataField>Description</DataField>
        <rd:TypeName>System.String</rd:TypeName>
      </Field>
      <Field Name="Picture">

        <DataField>Picture</DataField>
        <rd:TypeName>System.Byte[]</rd:TypeName>
      </Field>
    </Fields>
    <Query>

      <DataSourceName>Northwind</DataSourceName>
      <CommandText>SELECT * FROM CATEGORIES</CommandText>
      <rd:UseGenericDesigner>true</rd:UseGenericDesigner>
    </Query>

  </DataSet>
</DataSets>

Listing: The RDL segment for retrieving parameters. It includes the Command text and field definitions.

<ReportParameters>
  <ReportParameter Name="CategoryName">

    <DataType>String</DataType>
    <DefaultValue>
      <DataSetReference>
        <DataSetName>Categories</DataSetName>
        <ValueField>CategoryName</ValueField>

      </DataSetReference>
    </DefaultValue>
    <Prompt>CategoryName</Prompt>
    <ValidValues>
      <DataSetReference>

        <DataSetName>Categories</DataSetName>
        <ValueField>CategoryName</ValueField>
        <LabelField>CategoryName</LabelField>
      </DataSetReference>

    </ValidValues>
  </ReportParameter>
  <ReportParameter Name="OrdYear">
    <DataType>String</DataType>
    <DefaultValue>

      <Values>
        <Value>1998</Value>
      </Values>
    </DefaultValue>
    <Prompt>OrdYear</Prompt>

    <ValidValues>
      <ParameterValues>
        <ParameterValue>
          <Value>1996</Value>
        </ParameterValue>

        <ParameterValue>
          <Value>1997</Value>
        </ParameterValue>
        <ParameterValue>
          <Value>1998</Value>

        </ParameterValue>
      </ParameterValues>
    </ValidValues>
  </ReportParameter>
</ReportParameters>

Listing: The RDL segment for fixed Parameters. It specifies the label, data value data set reference along with parameter (Ordyear) values and default values.

Reporting services opens up a wide arena for you for data binding. The above examples demonstrate how can we use stored procedures in reports; however its your choice to use dynamic SQL, business objects (custom assemblies), stored procedures or any other custom data source for your report.

The Report Viewer Control

The report viewer control is an excellent intrinsic tool provided to facilitate report viewing in hosted client web or desktop application.
This control makes URL based access a breeze and reporting integration to any existing .NET app a charm. This control comes with code both
in VB.NET and C#. This control is available
%System Root%\Program Files\Microsoft SQL Server\MSSQL\Reporting Services\Samples\Applications\ReportViewer\vb.
You may want to build the solution to incorporate the assembly in your web / desktop application project.

In rest of this section, I’ll demonstrate how you can use this control to create a sample web application which supports reporting via URL access. The source code of this project is available as ParametricReporting.csproj in the source code zip file. First of all, in order to add this control in your web application, go to tools -> Add/Remove Toolbox Items. In the Customize Toolbar dialog box click the .NET Framework Components tab, browse to the location specified above and select ReportViewer.dll. Report Viewer component would be added to VS.NET toolbox and you can add it into your web form. This step is shown in the figure below.

Figure: Adding the report viewer control in the web form.

This is a simple web application which uses two calendar controls to select starting and ending date for orders report. The purpose of this example is to demonstrate how easily reports can be embedded in a web application. It also shows that reporting services functionality can easily be extended by traditional applications and their controls.

Figure: Report viewer control added in web form. VS.NET IDE

With couple of lines of code to pass the orders details parameters to the Report viewer control, the following report can be achieved.

Figure: Report rendered by using report viewer control.

With just modifying the rendering format to PDF, I can get the report in PDF within the report viewer control. This makes it a very handy choice for showing supporting reports and business intelligence associations within interactive web and desktop applications.

Figure: Report rendered as PDF by using the URL access and report viewer control.