Task 3: Build a data set
In this procedure, build a data set to indicate what data to retrieve 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:
Revenue
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 revenue and total of items sold for each country:
SELECT CLASSICMODELS.CUSTOMERS.COUNTRY,
SUM(CLASSICMODELS.ORDERDETAILS.QUANTITYORDERED * CLASSICMODELS.ORDERDETAILS.PRICEEACH) as SALES,
SUM(CLASSICMODELS.ORDERDETAILS.QUANTITYORDERED) as QUANTITY
FROM CLASSICMODELS.CUSTOMERS,
CLASSICMODELS.ORDERS,
CLASSICMODELS.ORDERDETAILS
WHERE CLASSICMODELS.CUSTOMERS.CUSTOMERNUMBER = CLASSICMODELS.ORDERS.CUSTOMERNUMBER
AND CLASSICMODELS.ORDERS.ORDERNUMBER = CLASSICMODELS.ORDERDETAILS.ORDERNUMBER
GROUP BY CLASSICMODELS.CUSTOMERS.COUNTRY
HAVING SUM(CLASSICMODELS.ORDERDETAILS.QUANTITYORDERED * CLASSICMODELS.ORDERDETAILS.PRICEEACH) > 200000
ORDER BY 2 DESC
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‑21 shows the data rows that the data set returns.
Figure 15‑21 Revenue data set preview
7 Choose OK.
