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.
