-
Need help to create a complicate MDX
Hello all,
I need to create a complicated calculated member that a similar as an SQL statement “select where in”. So there are two dimensions errors and products. Each product is assigned to an error or has no error. Each product has also some amount. For example, say we have errors E1, E2, E3 and N/A is for no errors and we have also three products P1, P2 and P3. For instance we have following situation:
Product Error Amount
P1 E1 10
P2 E1 20
P3 E2 30
P1 E2 10
P1 E3 10
P2 E3 20
P2 N/A 20
P2 N/A 20
P1 N/A 10
And I need to see following in the cube
Error Amount Failed Amount total
E1 30 120
E2 40 70
E3 30 120
N/A 50 120
Amount failed is amount for each error. So for E1 these are products P1 + P2 -> 10 + 20 = 30. For E2 -> P3 + P1 -> 30 + 10 = 40. For E3 -> P1 + P2 -> 10 + 20 = 30. For N/A -> P2 + P2 + P1 -> 20 + 20 +10 = 50. This is simply- My problem is Amount total. Amount total is a sum of amounts of all products that are exist on the current level. For example to E1 are following products assigned: P1 and P2. So I need to find all amounts for P1 and P2 on all levels. These are 10 (P1 E1) + 20 (P2 E1) + 10 (P1 E2) + 10 (P1 E3) + 20 (P2 E3) + 20 (P2 N/A) + 20 (P2 N/A) + 10 (P1 N/A) = 120. And this is my problem! If I would write a SQL I would do something like this: select sum(amount) from cube where product in (select product from cube where error = ‘E1’) and so on for E2 and E3. But how I can do the same with MDX?
Any help will be appreciating.
Thanks and best regards,
Andrej
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
|