EMPLOYEES.EMPLOYEENUMBER,
CLASSICMODELS.EMPLOYEES.LASTNAME || ', ' ||
CLASSICMODELS.EMPLOYEES.FIRSTNAME name,
sum(CLASSICMODELS.ORDERDETAILS.PRICEEACH) sales,
CLASSICMODELS.ORDERS.ORDERDATE
from
CLASSICMODELS.EMPLOYEES,
CLASSICMODELS.CUSTOMERS,
CLASSICMODELS.ORDERS,
CLASSICMODELS.ORDERDETAILS
where
CLASSICMODELS.ORDERS.ORDERNUMBER =
CLASSICMODELS.ORDERDETAILS.ORDERNUMBER
and CLASSICMODELS.EMPLOYEES.EMPLOYEENUMBER =
CLASSICMODELS.CUSTOMERS.SALESREPEMPLOYEENUMBER
and CLASSICMODELS.ORDERS.CUSTOMERNUMBER =
CLASSICMODELS.CUSTOMERS.CUSTOMERNUMBER
and CLASSICMODELS.ORDERS.ORDERDATE between ? and ?
group by
CLASSICMODELS.ORDERS.ORDERDATE,
CLASSICMODELS.EMPLOYEES.EMPLOYEENUMBER,
CLASSICMODELS.EMPLOYEES.LASTNAME,
CLASSICMODELS.EMPLOYEES.FIRSTNAME
Chapter 9
[ 217 ]
4. Link the two Data Set Parameters to two Report Parameters, called startDate
and endDate respectively.
5. We need to create a new Computed Column to have the OrderDate column
with just the year and month. Click on the Computed Columns. Create a new
Computed Column called orderDateShort, and use the following Expression:
var combinedDate = row["ORDERDATE"].getFullYear() + "-";
if ((row["ORDERDATE"].getMonth() + 1) < 10)
{
combinedDate += "0" + (row["ORDERDATE"].getMonth() + 1);
}
else
{
combinedDate += (row["ORDERDATE"].getMonth() + 1);
}
combinedDate;
This expression will take the OrderDate column, and create a string
containing the 4-digit year, and append the month (leaving off the day).
Pages:
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177