Database Reference
In-Depth Information
UNIQUE_PRODUCT.BASE_PRODUCT_NUMBER
and UNIQUE_MONTH.MONTH_KEY IN (200504, 200505)
group by UNIQUE_MONTH.CURRENT_YEAR,
UNIQUE_MONTH.MONTH_EN,
COUNTRY.COUNTRY_EN,
UNIQUE_PRODUCT.PRODUCT_NAME
order by 1 asc , 2 asc , 3 asc , 4 asc
This SQL command is available from the topic's website (
www.ibmpressbooks.com/
title/9780132656757)
in the accompanying supplements.zip file as Product Forecast.sql.
NOTE
To m a k e s u r e t h a t t h e r e p o r t w i l l a c t u a l l y r e t u r n d i f f e r e n t d a t a , w e w i l l q u e r y o n
months that are similar (May 2005) and ones that are in only one query (April 2005). At the
end of the testing of the report, you will remove the following line:
and UNIQUE_MONTH
.MONTH_KEY IN (200504, 200505)
.
7.
Click
Va l i d a t e
to check the syntax on the database server. Click
OK
if no messages appear.
8.
Double-click
Legacy_Query
.
Now we want to make sure that the legacy query uses the same names as the query using
the new package..
9.
From the
Properties
pane, rename the following data items:
•
CURRENT_YEAR
to
Ye a r
•
MONTH_EN
to
Month
•
COUNTRY_EN
to
Country
•
PRODUCT_NAME
to
Product
•
EXPECTED_VOLUME
to
Expected volume
•
FORECAST_REVENUE
to
Forecast revenue
10.
Click
Ye a r
and Shift-click
Forecast revenue
. Change the
Aggregate Function
prop-
erty to
None
.
This is designed to turn off the automatic aggregation for the query in IBM Cognos
Report Studio. This is done because the legacy query handled all the aggregation.
11.
Click
Ye a r
.
This is done to select a single data item in order to test the query. Any one data item can
be selected in this instance. If you do not click on a data item in the Data Items pane, you
might get an error message in the next step.
12.
From the
Run
menu, select
View Tabular Data
to test your query. Verify that your
results match what's shown in Figure 4.15.
