Task 3:  
In this procedure, build a data set to indicate what data to retrieve from various tables in the database.
1  
2  
Revenue
Use the default values for the other fields.
*
*
3  
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 18-21 shows the data rows that the data set returns.
Figure 18-21  
7  

Additional Links:

Copyright Actuate Corporation 2012