Task 3: Build a data set
In this procedure, build a data set to specify what data to retrieve and combine from various tables in the database.
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:
Sales By Territory
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 retrieve the sales total for each territory:
SELECT CLASSICMODELS.OFFICES.TERRITORY,
SUM(CLASSICMODELS.ORDERDETAILS.QUANTITYORDERED *
CLASSICMODELS.ORDERDETAILS.PRICEEACH) as SALES
FROM CLASSICMODELS.CUSTOMERS,
CLASSICMODELS.ORDERS,
CLASSICMODELS.ORDERDETAILS,
CLASSICMODELS.OFFICES,
CLASSICMODELS.EMPLOYEES
WHERE CLASSICMODELS.ORDERS.ORDERNUMBER = CLASSICMODELS.ORDERDETAILS.ORDERNUMBER
AND CLASSICMODELS.CUSTOMERS.SALESREPEMPLOYEENUMBER = CLASSICMODELS.EMPLOYEES.EMPLOYEENUMBER
AND CLASSICMODELS.EMPLOYEES.OFFICECODE = CLASSICMODELS.OFFICES.OFFICECODE
AND CLASSICMODELS.CUSTOMERS.CUSTOMERNUMBER = CLASSICMODELS.ORDERS.CUSTOMERNUMBER
GROUP BY CLASSICMODELS.OFFICES.TERRITORY
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 15‑13 shows the data rows that the data set returns.
Figure 15‑13 Sales By Territory data set preview
7 Choose OK.
