Forum Discussion
Dynamic column value based on slicer selection
Hi,
Have searched the previous posts on Dynamic selected, but cannot find solution to my scenario.
Below are the tables I have.
| Table: Material Type | ||
| Mat Type Text | |
| A | AAA | |
| B | BBB | |
| C | CCC |
| Table: Material | ||||||
| Material Key | Material Text | Mat Type | Hier L1 | Hier L2 | Hier L3 | Material Group |
| M1 | A | H11 | H21 | H31 | MG1 | |
| M2 | A | H12 | H22 | H32 | MG3 | |
| M3 | B | H13 | H23 | H33 | MG3 | |
| M4 | C | H14 | H24 | H34 | MG4 | |
| M5 | C | H14 | H25 | H25 | MG4 |
| Fact table: Stock | |
| Material Key | Stock |
| M1 | 100 |
| M2 | 200 |
| M3 | 300 |
| M4 | 400 |
| M5 | 500 |
In the datamodel, below are the relationships:
Stock.MaterialKey = Material.MaterialKey (many to 1)
Material.Mat Type = Mat Type.Mat Type Key (many to 1)
In the report, I have slicer on Mat Type.Mat Type Key. Based on what is selected, I need to show Stock either by Hier L1 or Hier L2 or Material Group.
Slicer on Mat Type
A B C
When A is selected, I need to show Stock by Hier L1
Type Stock
H11 100
H12 200
When B is selected, I need to show Stock by Hier L2
Type Stock
H23 300
When C is selected, I need to show Stock by Material Group
Type Stock
MG4 900
Please help how to achieve this.
Regards.
5 Replies
Anonymous great explanation but it is not clear by selecting mat key why different Hier will show up, like in this case, what is the logic to show stock by Hier L2?
When B is selected, I need to show Stock by Hier L2
Type Stock
H23 300Thanks parry2k. That is my business requirement. The report should show different column values based on what is selected. Are you able to help how do I achieve this?
When B is selected, I need to show Stock by Hier L2
Type Stock
H23 300Anonymous ok, so you will never see stock for mat type B by any other Hier. Are these fixed business rule or it is dynamic?
HI Anonymous
When A is selected, I need to show Stock by Hier L1
Type Stock
H11 100
H12 200Why it shows Hier L1 but not Hier L2 or Hier L3 or Material Group?
When B is selected, I need to show Stock by Hier L2
Type Stock
H23 300Why it shows Hier L2 but not Hier L1 or Hier L3 or Material Group?
When C is selected, I need to show Stock by Material Group
Type Stock
MG4 900Why you need it show Material Group but not Hier L1 or Hier L2 or Hier L3?
What is the logic/rules for it?
Regards,
Lin
