I have two tables in my Data model as
Since Product ID is the common field. Both are connected. This is it from the Data model.
Now, I have two straight tables in UI.
1st Straight Table:
Dimension - Product ID,Product Name
Expression - 2001,2002 (No functions, just used the field names in expression)
2nd Straight Table:
Dimension - Product ID
Expression - Jan 2001, Jan 2002
Since Jan 2001 is a subset of 2001, I need to show just Product ID and Jan 2001 when the user clicks 2001. Similarly for 2002.
Say, when the user click 2001 in the 1st straight table, I should get
Can someone tell me, how to do this? Guess it's similar to Ad-hoc Reporting. But not sure how to proceed with this.
Maybe you can achieve something like you want using triggers, but I would consider changing the data model, so that the month / year relation is associative.
Maybe using a fact table with mixed granularity:
Fact Table with Mixed Granularity
Then, using a calendar that is linked to the mixed granular time dimension, and selecting Year, you should get both tables filtered accordingly.
I actually used Year and Month just for namesake. Please consider the one in 1st table as Category and 2nd table as Sub category.
How to do this with triggers?