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 18-13 shows the data rows that the data set returns.

Figure 18-13

Sales By Territory data set preview

7	Choose OK.

Copyright Actuate Corporation 2012