In this article we will create a table report, reflecting the overall sales data of particular city in the state of Washington, US. The source data will use publicly available database AdventureWorks 2012.
Download source code
Prerequisites
To get started you’ll need:
- Microsoft Visual Studio 2010
- Microsoft SQL Server 2012 with SQL Server Reporting Services installed
- AdventureWorks 2012 database
Creating a project
Start the Microsoft Visual Studio. Create a blank solution with the name A01.sln. In the context menu, select the solutions Add-> New Project … In the Business Intelligence-> Reporting Services project type, select Report Server Project. Give the project a name A01.Reports
Adding a Data Source
Now you need to add a new data source for our report. To do this, in the Solution Explorer, open the context menu of Shared Data Sources folder and select Add New Data Source. A window opens Shared Data Source Properties. Specify the data source name and assign A01_AdventureWorks connection string to the database AdventureWorks. You can test the connection by clicking the Edit …, and then in the dropdown box Connection Properties by clicking the Test Connection.
Create stored procedures for reporting data
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 the code
CREATE SCHEMA [A01] AUTHORIZATION [dbo]
CREATE PROCEDURE [A01].[uspGetCitiesByStateProvince]
@StateProvinceID int
AS
BEGIN
SET NOCOUNT ON;
select distinct
City,
StateProvinceID
from
Person.Address
where
StateProvinceID = @StateProvinceID
order by
City
END
GO
CREATE PROCEDURE [A01].[uspGetSalesByCity]
@City nvarchar(30),
@StateProvinceID int
AS
BEGIN
SET NOCOUNT ON;
select
soh.SalesOrderID,
soh.SalesOrderNumber,
soh.OrderDate,
soh.SubTotal,
soh.TaxAmt,
soh.Freight,
soh.TotalDue
from
Sales.SalesOrderHeader soh
inner join
Person.Address a
on
soh.BillToAddressID = a.AddressID
where
a.City = @City and
a.StateProvinceID = @StateProvinceID
order by
soh.OrderDate,
soh.SalesOrderNumber
END
GO
This SQL script creates the two stored procedures. The first procedure [A01]. [UspGetCitiesByStateProvince] will return a list of cities to obtain data on the volume of sales. The second procedure [A01]. [UspGetSalesByCity] returns the sales data for a particular city.
Adding a blank report
Add a new report in our report server. In Microsoft Visual Studio Solution Explorer find the Reports folder. In the context menu choose Add-> New Item… Select the Report template and give a name to a new report Report.SalesByCity.rdl
Double click on the file name Report.SalesByCity.rdl. Pay attention to Toolbox panel, which contains the basic elements, from which the report is going to be constructed. And also pay attention to the Report Data panel, which you need to create and manage data sources, data sets and report parameters. If you do not see the Report Data panel, or you accidentally close it, press Ctrl + Alt + D.
Creating a Report Data Source
Now we need to create a report data source. Go to the Report Data window and click on the shortcut menu, click Data Sources folder, Add Data Source. We have created a shared data source Report Server (Shared Data Source), so the window that opens Data Source Properties just type the name of SalesByCity_AdventureWorks, specify the type of source Use shared data source reference, and the drop-down list, select a shared data source A01_AdventureWorks.
Creating Report Datasets
Now create the data sets for our report. In the same window, select Report Data Datasets folder and in the context menu select Add Dataset … Set a new data set name to Cities. Select the option Use a dataset embedded in my report. Assign the data source SalesByCity_AdventureWorks. Specify the type of query and select Stored Procedure drop-down list the name A01.uspGetCitiesByStateProvince.
Click OK. Dataset Properties window will close and will appear in the Parameters folder ID parameter state / province with the name StateProvinceID. Configure it so that the data set returned the list of cities Cities of Washington state. Double click on this parameter. In the Report Parameter Properties window, set the option in the Select parameter visibility to Hidden.
In the Set Default Values option Specify values, then click the Add button in the Value field, enter the value 79 (the ID of Washington).
Thus, we have identified a hidden parameter ID, state / province for a dataset Cities with an explicit ID value of Washington.
Then create a data set for the stored procedure A01.uspGetSalesByCity. In the Dataset Properties asking the dataset name SalesByCity, specify the stored procedure A01.uspGetSalesByCity. Switch to the section Parameters. For the parameter @ StateProvinceID drop-down list, select Parameter Value [@ StateProvinceID]. For the parameter @ City did not choose anything. Click the OK button.
Dataset Properties window will close and the City parameter will appear in the Parameters folder. Double-click on it. Switch to the Available Values section and select Get values from a query. In the Dataset drop-down list, select a dataset Cities. In the Value field, select the City, in the Label field – City. This way we defined an input parameter – City, which will be displayed on the sales data.
Report Design
So, we have 2 parameters of the report – a hidden parameter identifier state / province (WA) and user-defined parameter – the city, which the report is based on. Let’s add results table. Open Toolbox toolbar and click on the tool “Table”, and then click in the working area. Thus, we created an empty Table report.
Open the context menu for the table and select Tablix Properties… In the Properties window, enter the name of the table Tablix_SalesByCity. In the drop-down list, select the Dataset name value to “SalesByCity”. Click OK.
Click on an empty table cell to the left of the cell labeled Data. Icon will appear when you click on that will pop-up list column data set SalesByCity. Select the column SalesOrderNumber.
The cell value will be equal to [SalesOrderNumber], that is, it will be displayed column data SalesOrderNumber. The value of the cell header table, located above the cell data will be equal to the Sales Order Number.
Similarly, assign different table cells remaining columns data set, except SalesOrderID (we need it in the future). To increase the number of columns in a table using the context menu of the table and click Insert Column. Customize the style of header cells and data to your liking with the help of the window Properties.
View the report
It’s time to see the results of our labors. In the Workspace tab of the report Preview. You will see our parameter City, containing a list of cities in Washington State. Select a city and click View Report.
Conclusion
The generated report provides information about the order number, date and order price for a certain city. In next article, we will add the ability to view order details, as well as receive aggregated sales data by state / province.
Dmitrii Komin Blog 