Our next step is to create the shared dataset for the project. Before doing that, let's create a stored procedure named dbo.uspSalesDetails. This is going to be the query for our dataset.
Download the T-SQL codes included in this book if you haven't done so already. We're going to use the T-SQL file named uspSalesDetails_Ch01.sql for this chapter.
We will use the same stored procedure for this whole chapter, unless otherwise indicated.
- Right-click on the
Shared Datasetsfolder in Solution Explorer, just like we did when we created the data source. That displays the Shared Datasets Properties dialog. - Let's name our dataset
ds_SalesDetailReport. We use the query type stored procedure, and select or typeuspSalesDetailson the Select or enter stored procedure name drop-down combo box. Click on OK when you're done:
Before we work on the report itself, let's examine our dataset.
In the Solution Explorer window, double-click on the dataset ds_SalesDetailReport.rsd, which displays the Shared Dataset Properties dialog box.
Notice that the fields returned by our stored procedure have been automatically detected by the report designer. You can rename the field as shown:
Note
Ad-hoc Query (Text Query Type) versus Stored Procedure: as a good practice, always use a stored procedure where a query is used. The primary reason for this is that a stored procedure is compiled into a single execution plan. Using stored procedures will also allow you to modify certain elements of your reports without modifying the actual report.