Hi All,

I have a Product dimension table in the following structure below.

Productkey ProductName ParentKey
1 A
2 B 1
3 C 1
4 D 2
5 E 3

Likewise, the data goes on. Now, I am populating the Productkey field in my Fact table. Only the lowest level in the Product will be available in the Fact table (here productkeys 4 & 5). Now, I have to generate a report like whatever the Product I chose and bring it into the report, all the sub levels of that product should be joined with Fact table and yield the result.

Let's say, Productkey 4 has generated revenue of 50$ and 5 for 100$.

Now, if I choose Productname A, it has to show 150$.

All possible inputs are greatly appreciated.

Thanks in advance.

WebFocus 8.0.0.3
HTML Output

This message has been edited. Last edited by: <Kathryn Henning>,

Hi Praveen,
Though there can be various methods to achieve the requirement,One of the ways which I can think of is as below:
TABLE FILE FACT
PRINT
PRODUCTKEY
WHERE PRODUCTNAME EQ 'A'
ON TABLE HOLD
END
-READ HOLD &PROD_KEY
TABLE FILE DIMENSION
PRINT
REVENUE
BY PRODUCT_KEY
WHERE PRODUCT_KEY GT '&PROD_KEY'
ON TABLE HOLD AS HOLD_DIM
END

Here hold file is used to identify the product key for which the product name has been selected.
file HOLD_DIM holds all the required data.Now HOLD_DIM can be used either to join with the fact table or it can be directly used.
Hope this helps.