Database Reference
In-Depth Information
3.
In the
Properties
pane, double-click the
Data Source
property.
This shows that reports do not need to be related directly to a package. As long as the
data source is available in the IBM Cognos Connection environment, certain report
authors can create their own SQL and query the database directly.
This feature may be limited by your IBM Cognos Administrator.
4.
Select
great_outdoors_sales
and click
OK
.
5.
Double-click the
SQL
property. The SQL dialog box is displayed.
6.
Enter the following code:
select UNIQUE_MONTH.CURRENT_YEAR,
UNIQUE_MONTH.MONTH_EN,
COUNTRY.COUNTRY_EN,
UNIQUE_PRODUCT.PRODUCT_NAME,
sum(PRODUCT_FORECAST.EXPECTED_VOLUME) as EXPECTED_VOLUME,
sum(PRODUCT_FORECAST.UNIT_PRICE *
PRODUCT_FORECAST.EXPECTED_VOLUME) as FORECAST_REVENUE
from
(select distinct MONTH_KEY,
min(CURRENT_YEAR) as CURRENT_YEAR,
min(CURRENT_MONTH)as CURRENT_MONTH,
min(MONTH_EN) as MONTH_EN
from GOSALES.TIME_DIMENSION
group by MONTH_KEY) UNIQUE_MONTH,
GOSALES.COUNTRY COUNTRY,
(select PRODUCT.BASE_PRODUCT_NUMBER AS
BASE_PRODUCT_NUMBER,
PRODUCT_NAME_LOOKUP.PRODUCT_NAME AS PRODUCT_NAME
from GOSALES.PRODUCT PRODUCT,
GOSALES.PRODUCT_NAME_LOOKUP PRODUCT_NAME_LOOKUP
where PRODUCT.PRODUCT_NUMBER =
PRODUCT_NAME_LOOKUP.PRODUCT_NUMBER
and PRODUCT_NAME_LOOKUP.PRODUCT_LANGUAGE = N'EN')
UNIQUE_PRODUCT,
GOSALES.PRODUCT_FORECAST PRODUCT_FORECAST,
GOSALES.BRANCH BRANCH
where PRODUCT_FORECAST.SALES_YEAR = UNIQUE_MONTH.CURRENT_YEAR
and PRODUCT_FORECAST.SALES_MONTH = UNIQUE_MONTH.CURRENT_MONTH
and PRODUCT_FORECAST.BRANCH_CODE = BRANCH.BRANCH_CODE
and COUNTRY.COUNTRY_CODE = BRANCH.COUNTRY_CODE
and PRODUCT_FORECAST.BASE_PRODUCT_NUMBER =
