In this article we will discuss the creation of composite report on the example of adding to the level of detail built in the last article, a report of sales for a given city.
First, we create a high-level view of sales, making a report on the state / province. Then we add the details of the order of accountability, so the user can get more information by simply clicking on the row order. So, let’s start.
Download source code
Creating stored procedures for data sets
Start the Microsoft SQL Server Management Studio.
In the Connect To Server window, specify the server name, authentication type, if necessary, and then click Connect.
Click New Query and verify that the request for a new drop-down list of Available Databases specified database AdventureWorks.
Run following code
CREATE PROCEDURE [A01].[uspGetStatesProvinces]
@CountryRegionCode nvarchar(3)
AS
BEGIN
SET NOCOUNT ON;
select
sp.StateProvinceID,
sp.StateProvinceCode,
sp.Name
from
Person.StateProvince sp
where
sp.CountryRegionCode = @CountryRegionCode
order by
sp.Name
END
GO
CREATE PROCEDURE [A01].[uspGetSalesByStateProvince]
@StateProvinceID int
AS
BEGIN
SET NOCOUNT ON;
select
@StateProvinceID StateProvinceID,
*
from
(
select
a.City,
sum(soh.SubTotal),
sum(soh.TaxAmt),
sum(soh.Freight),
sum(soh.TotalDue)
from
Sales.SalesOrderHeader soh
inner join
Person.Address a
on
soh.BillToAddressID = a.AddressID
where
a.StateProvinceID = @StateProvinceID
group by
a.City
) s(City, SubTotal, TaxAmt, Freight, TotalDue)
order by
s.City
END
GO
CREATE PROCEDURE [A01].[uspGetSalesOrderDetails]
@SalesOrderID int
AS
BEGIN
SET NOCOUNT ON;
select
p.ProductNumber,
p.Name,
sod.UnitPrice,
sod.UnitPriceDiscount,
sod.OrderQty,
sod.LineTotal
from
Sales.SalesOrderHeader soh,
Sales.SalesOrderDetail sod,
Production.Product p
where
soh.SalesOrderID = sod.SalesOrderID and
p.ProductID = sod.ProductID and
sod.SalesOrderID = @SalesOrderID
order by
p.ProductNumber
END
GO
The stored procedure [A01]. [UspGetStatesProvinces] returns a list of states/provinces for a given country code. The procedure [A01]. [UspGetSalesByStateProvince] returns the aggregated sales data by certain state/province. The procedure [A01]. [UspGetSalesOrderDetails] is needed for “order details” subreport.
Add a report of sales by state/province
Start the Microsoft Visual Studio. Open the solution A01.sln.
The project A01.Reports Reports and locate the folder in its context menu select Add-> New Item …
In the Add New Item dialog box, select the Report template and specify the report name Report.SalesByStateProvince.rdl.
Adding to the report data source SalesByStateProvince_AdventureWorks, referring to the shared data source A01_AdventureWorks.
Creating report datasets
Create a new dataset is a list of states/provinces. Specify the dataset name StatesProvinces. Specify the dataset name of a stored procedure A01.uspGetStatesProvinces. Go to Parameters and for @CountryRegionCode parameter type “US” in “Parameter Value” field . Click OK.
Thus, the data set StatesProvinces will return to the United States list of states where sales were made.
Next, create a set of data for aggregate data on sales by state / province. Ask a new data set name SalesByStateProvince. Specify the name of a stored procedure A01.uspGetSalesByStateProvince. Click OK.
Note that in the Report Data window in your Parameters, a new option StateProvinceID. Double-click on it. In the Prompt, set the State. In the Available Values section, select Get values from a query, specify the data set StatesProvinces, in the Value field, select StateProvinceID, in the Label field, select Name. Click OK.
Designing “Sales by state/province” report
Add the item in the report table. In the Properties window, enter the name of the table Tablix_SalesByStateProvince. In the Dataset name, select a data set SalesByStateProvince. Click OK.
Fill in the following columns of a table cell data set: City, SubTotal, TaxAmt, Freight, Total Due. Customize the style of header cells and data as you like using the Properties window.
Click on the Preview. Choose the state from the list and click View Report.
“Sales by state/province” report is ready!
Customizing a transition between the reports
Now, let’s allow the user to switch between the level of sales reports by state reports on the level of sales in the city and back.
In the working area of the report, select cell data in column [City]. In the context menu of the cell select Text Box Properties… In the Properties window go to Action. Select the type of action Go to report. Further, the drop-down list, select the Specify a report a report Report.SalesByCity. We need to define the parameters for the transfer of accountability. Note, that Report.SalesByCity subreport has 2 parameters: the identifier of the state / province (StateProvinceID) and the name of the city (City).
We define a value for the transferred StateProvinceID. Click Add. In the Name field, select StateProvinceID, in the Value column, select the report data [StateProvinceID]. Similarly, determine the value for the parameter passed to City.
Now we have to add the formatting of the cell City, so it looks like a hyperlink. Open the Properties window for the City in the cell section, select the Font Color Blue, and in the field TextDecoration select Underline. Switch to Report Preview. Voila!
Now you can choose the city of interest and view the list of orders and sales for each order.
Navigating back to the parent report by pressing the Back to Parent Report button on the toolbar of the report.
Adding “Order Details” subreport
Let’s go back to our Report.SalesByCity report, created in the previous article, and add the subreport, displaying the order contents and any additional information about the order.
In A01.Reports project locate the Reports folder and in context menu select Add-> New Item …
In the Add New Item dialog box, select the Report template and specify the report name Report.SalesOrderDetails.rdl.
Add to a report data source SalesOrderDetails_AdventureWorks2012, referring to the shared data source A01_AdventureWorks2012.
Creating report data sets
Create a new data set. Specify the dataset name SalesOrderDetails. Specify the dataset name of a stored procedure A01.uspGetSalesOrderDetails. Click OK.
Notice that SalesOrderID option appeared in Parameters folder. Double-click on it. In the Properties dialog, set the “Select parameter visibility” option to “Hidden”. Thus, we will make this option is not visible to the user. Click OK.
Designing “Order Details” report
Add Table item to report work area. Set the table name to Tablix_SalesOrderDetails and dataset name to SalesOrderDetails. Click OK.
Fill in the table header with all available columns in the data set, namely: ProductNumber, Name, UnitPrice, UnitPriceDiscount, OrderQty, Line Total.
Customizing transition between the reports
Set up the transition to a report of sales in the city accountable for order details.
Open the report Report.SalesByCity. Go to the workspace and select cell data in column [SalesOrderNumber]. In the context menu, select the cell Text Box Properties… In the Properties window, go to Action. Select the type of action Go to report. Further, the drop-down list, select the Specify a report a report Report.SalesOrderDetails.
We need to determine the value of the order identifier (SalesOrderID) for transmission to the responsible. Click Add. In the Name field, select SalesOrderID, in the Value column, select the report data [SalesOrderID]. Thus, the column [SalesOrderID] Report.SalesByCity report will be used as a parameter for the report SalesOrderID Report.SalesOrderDetails. Click OK.
Conclusion
Actually the work is completed. Now you can open Report.SalesByStateProvince report, switch to Preview and analyze sales data for each of 3 levels: state-city-order identifying data anomalies.
Dmitrii Komin Blog 