Task 3: Build a data set
In this procedure, build a data set to indicate what data to retrieve from the OrderDetails and Products table.
1 In Data Explorer, right-click Data Sets, and choose New Data Set.
2 In New Data Set, in Data Set Name, type the following text:
SalesTotals
Use the default values for the other fields.
Data Source Selection shows the name of the data source that you created earlier.
Data Set Type specifies that the data set uses a SQL SELECT query to retrieve the data.
3 Choose Next.
4 In New Data Set—Query, type the following SQL SELECT statement to indicate what data to retrieve:
select Products.ProductLine,
sum(OrderDetails.QuantityOrdered * OrderDetails.PriceEach) as TotalPrice
from OrderDetails, Products
where products.productcode = orderdetails.productcode
group by products.productline
order by products.productline
This statement calculates the total sales amount for each product line.
5 Choose Finish to save the data set. Edit Data Set displays the columns specified in the query, and provides options for editing the data set.
6 Choose Preview Results.
Figure 14‑34 shows the data rows that the data set returns.
Figure 14‑34 SalesTotals data set preview
7 Choose OK.
