I have got two fact tables: FACT_TABLE_1 and FACT_TABLE_2
FACT_TABLE_1
----------------
REFERENCE_NUMBER
MONTH_ID
REGION_ID
UNIT_COST
FACT_TABLE_2
----------------
REFERENCE_NUMBER
PRODUCT_ID
UNIT_VALUE
I need to create the following analysis.
From one report that uses the first fact table (FACT_TABLE_1) the user will make a drill to template (a report that is based on FACT_TABLE_2).
The requirements are:
1. The month will be prompted in the first report
2. The region attribute will be in the column or row (doesn't matter) and one of its element will be choosen by the user to make the drill to template.
3. In the template (second report), only the FACT_TABLE_1 should be filtered by REGION_ID (the user filtering condition) and MONTH_ID (prompted only in the first report). The FACT_TABLE_2 shouldn't be filtered. Ok, this can be done by metric dimensionalization ("ignore" MONTH AND REGION Level).
4. The only link between FACT_TABLE_1 and FACT_TABLE_2 is the REFERENCE_NUMBER, therefore there must be a join like FACT_TABLE_1.REFERENCE_NUMBER = FACT_TABLE_2.REFERENCE_NUMBER.
Summing up, I need the following query:
In the first report:
SELECT A12.REGION_ID, SUM(A11.UNIT_COST)
FROM FACT_TABLE_1 A11,
REGION_TABLE A12,
MONTH_TABLE 13
WHERE MONTH_ID = '200110'
GROUP BY A12.REGION_ID
And the second report (the template):
SELECT A12.PRODUCT_ID, SUM(A11.UNIT_VALUE)
FROM FACT_TABLE_2 A11, PRODUCT_TABLE A12
WHERE A11.REFERENCE_NUMBER IN (SELECT R11.REFERENCE_NUMBER
FROM FACT_TABLE_2 R11,
MONTH_TABLE R12,
REGION_TABLE R13
WHERE MONTH_ID = '200210'
AND REGION_ID = 'NORTH')
GROUP BY A12.PRODUCT_ID
FINAL WORDS:
The month will be prompted only in the first report and the prompt object can be an "element prompt" or a "value prompt text type".
The REGION_ID will be choosen by the user from the first report result previous (or at moment) to the drill to template process.
How could I do it?
Tks in advance.
Humberto
FACT_TABLE_1
----------------
REFERENCE_NUMBER
MONTH_ID
REGION_ID
UNIT_COST
FACT_TABLE_2
----------------
REFERENCE_NUMBER
PRODUCT_ID
UNIT_VALUE
I need to create the following analysis.
From one report that uses the first fact table (FACT_TABLE_1) the user will make a drill to template (a report that is based on FACT_TABLE_2).
The requirements are:
1. The month will be prompted in the first report
2. The region attribute will be in the column or row (doesn't matter) and one of its element will be choosen by the user to make the drill to template.
3. In the template (second report), only the FACT_TABLE_1 should be filtered by REGION_ID (the user filtering condition) and MONTH_ID (prompted only in the first report). The FACT_TABLE_2 shouldn't be filtered. Ok, this can be done by metric dimensionalization ("ignore" MONTH AND REGION Level).
4. The only link between FACT_TABLE_1 and FACT_TABLE_2 is the REFERENCE_NUMBER, therefore there must be a join like FACT_TABLE_1.REFERENCE_NUMBER = FACT_TABLE_2.REFERENCE_NUMBER.
Summing up, I need the following query:
In the first report:
SELECT A12.REGION_ID, SUM(A11.UNIT_COST)
FROM FACT_TABLE_1 A11,
REGION_TABLE A12,
MONTH_TABLE 13
WHERE MONTH_ID = '200110'
GROUP BY A12.REGION_ID
And the second report (the template):
SELECT A12.PRODUCT_ID, SUM(A11.UNIT_VALUE)
FROM FACT_TABLE_2 A11, PRODUCT_TABLE A12
WHERE A11.REFERENCE_NUMBER IN (SELECT R11.REFERENCE_NUMBER
FROM FACT_TABLE_2 R11,
MONTH_TABLE R12,
REGION_TABLE R13
WHERE MONTH_ID = '200210'
AND REGION_ID = 'NORTH')
GROUP BY A12.PRODUCT_ID
FINAL WORDS:
The month will be prompted only in the first report and the prompt object can be an "element prompt" or a "value prompt text type".
The REGION_ID will be choosen by the user from the first report result previous (or at moment) to the drill to template process.
How could I do it?
Tks in advance.
Humberto