none
SSAS Multidim - Detect if a user use filter on Excel ?

    Question

  • Hello,

    I have a problem with my cube, and I need some help...

    My client use Excel as reporting tool.

    I would like to create a calculated measure and my issue is that I want to do something different when the user put an attribut in filter.

    I checl with a profiler and it's seems that when the user do filter, the query behind that is a WHERE clause.

    My example : 

    Calculated Measure : A * B 

    When the user use filter :  A * B (filter on category C)   I want to change the calculation to ===>   A * Z (only on that filter).

    I explore the SCOPE() instructions but I don't find any answer...

    It's possible to detect if the user use a filter ?

    Thanks you in advance.

    Ratanou

    Tuesday, November 27, 2018 4:06 PM

All replies

  • yes

    You can use SCOPE for this purpose

    You can define a subcube for C filter and define calculation for it

    like

    CREATE MEMBER
       CURRENTCUBE.[Measures].[Your Measure Name]
       AS [Measures].[A] * [Measures].[B], VISIBLE = 1;
    Scope(DESCENDANTS([Category].[C],,AFTER));
        [Measures].[Your Measure Name] =
          [Measures].[A] * [Measures].[Z];
    End Scope;

    Assuming C is hierarchy within which you want to apply custom formula


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    Tuesday, November 27, 2018 6:29 PM
  • Do check the scenario where the user filters on several members of the C attribute simultaneously. In can very well produce a subselect instead of a WHERE.

    Expect me to help you solve your problems, not to solve your problems for you.

    Wednesday, November 28, 2018 8:53 AM
  • Hello,

    Thank you for your responses and sorry for my late answer.

    I tried your solution but I don't have the result expected...

    I will describe more precisely with details my case. 

    Thank you, again for your feedbacks !

    Monday, December 3, 2018 3:35 PM
  • Hello,

    Sorry for the long time with no answer.

    There is my problem:

    My client uses Excel as reporting tools, and everything have to work in Excel…

    I want rebase the calculations only with the selection with filter in Excel (only on displayed data) with if possible a generic calculated measures.

    For the moment I tried with the Axis() function and it’s the best way but don’t work on one case…

    There is my situation:

    I have a hierarchy like:

    Hierarchy

    Class L1

    Class L2

    Class L3

    Name

    There are many categories in each level attribute.

    Now I will describe many cases for my definition of done.

    Case 1: Measure 1 OK - When I sum every category of L1, I obtain 100% OK

    Hierarchy

    Measure 1

    L1

    1.87%

    L1

    0.00%

    L1

    0.00%

    L1

    0.00%

    L1

    0.00%

    L1

    0.00%

    L1

    0.05%

    L1

    0.00%

    L1

    0.13%

    L1

    0.01%

    L1

    0.08%

    L1

    95.66%

    L1

    2.20%

    Total général

    100.00%

    Case 2: Measure 1 OK - I collapse one category of L1 to L2, when I sum every L2’s lines I obtain the same % like my level 1, and if I sum L2 lines and the others level 1 lines, I obtain 100%.

    Hierarchy

    Measures 1

    L1

     

    L2 - A

    0.23%

    L2 - B

    0.32%

    L2 - C

    1.25%

    L2 - D

    0.06%

    L1

    0.00%

    L1

    0.00%

    L1

    0.00%

    L1

    0.00%

    L1

    0.00%

    L1

    0.05%

    L1

    0.00%

    L1

    0.13%

    L1

    0.01%

    L1

    0.08%

    L1

    95.66%

    L1

    2.20%

    Total général

    100.00%


    Friday, December 7, 2018 3:57 PM
  • Case 3: Measure 1 OK - I collapse down to my L2 to L3, when I sum every L3’s lines I obtain the same % like my level 2, and if I sum L3 lines and the others level 2 and level 1 lines, I obtain 100%.

    Hierarchy

    Measures 1

    L1

     

    L2 - A

    0.23%

    L2 - B

    0.32%

    L2 - C

    L3 - A

    0.03%

    L3 - B

    1.22%

    L2 - D

    0.06%

    L1

    0.00%

    L1

    0.00%

    L1

    0.00%

    L1

    0.00%

    L1

    0.00%

    L1

    0.05%

    L1

    0.00%

    L1

    0.13%

    L1

    0.01%

    L1

    0.08%

    L1

    95.66%

    L1

    2.20%

    Total général

    100.00%


    Case 4: Measure 1 OK - One more time but in level down (leaf level) I want the same behavior as cases before.

    And when I sum every leaf, L3, L2, L1, I obtain 100%.

    Hierarchy

    Measures 1

    L1

     

    L2 - A

    0.23%

    L2 - B

    0.32%

    L2 - C

    L3 - A

    0.03%

    L3 - B

    NAME - B1

    0.06%

    NAME - B2

    0.06%

    NAME - B3

    0.06%

    NAME - B4

    0.04%

    NAME - B5

    0.03%

    NAME - B6

    0.06%

    NAME - B7

    0.03%

    NAME - B8

    0.04%

    NAME - B9

    0.12%

    NAME - B10

    0.13%

    NAME - B11

    0.12%

    NAME - B12

    0.00%

    NAME - B13

    0.06%

    NAME - B14

    0.00%

    NAME - B15

    0.00%

    NAME - B16

    0.04%

    NAME - B17

    0.06%

    NAME - B18

    0.03%

    NAME - B19

    0.06%

    NAME - B20

    0.02%

    NAME - B21

    0.07%

    NAME - B22

    0.06%

    NAME - B23

    0.06%

    NAME - B24

    0.00%

    NAME - B25

    0.00%

    NAME - B26

    0.00%

    L2 - D

    0.06%

    L1

    0.00%

    L1

    0.00%

    L1

    0.00%

    L1

    0.00%

    L1

    0.00%

    L1

    0.05%

    L1

    0.00%

    L1

    0.13%

    L1

    0.01%

    L1

    0.08%

    L1

    95.66%

    L1

    2.20%

    Total général

    100.00%

    Friday, December 7, 2018 3:58 PM
  • Now, my problem:

    Users want to filter on specific L1, L2, L3 attributes (same attribute in hierarchy)

    Case filter L1:

    Filter L1

    L1 - A

    L1 OK

    Hierarchy

    Measure 1

    L1 - A

    100.00%

    Total général

    100.00%

    L2 OK

    Hierarchy

    Measure 1

    L1 - A

    L2 - A

    12.55%

    L2 - B

    17.34%

    L2 - C

    66.77%

    L2 - D

    3.34%

    Total général

    100.00%

    L3 OK

    Hierarchy

    Measure 1

    L1 - A

    L2 - A

    12.55%

    L2 - B

    17.34%

    L2 - C

    L3 - A

    1.61%

    L3 - B

    65.16%

    L2 - D

    3.34%

    Total général

    100.00%

    When I collapse to level NAME, there is y error, the percentage keep the same as my case 4. The result is 1.22% but I expect 65,16%. It seems that the filter is bypass…

    Hierarchy

    Measure 1

    L1 - A

    L2 - A

    12.55%

    L2 - B

    17.34%

    L2 - C

    L3 - A

    1.61%

    L3 - B

    Name - B1

    0.06%

    Name - B2

    0.06%

    Name - B3

    0.06%

    Name - B4

    0.04%

    Name - B5

    0.03%

    Name - B6

    0.06%

    Name - B7

    0.03%

    Name - B8

    0.04%

    Name - B9

    0.12%

    Name - B10

    0.13%

    Name - B11

    0.12%

    Name - B12

    0.00%

    Name - B13

    0.06%

    Name - B14

    0.00%

    Name - B15

    0.00%

    Name - B16

    0.04%

    Name - B17

    0.06%

    Name - B18

    0.03%

    Name - B19

    0.06%

    Name - B20

    0.02%

    Name - B21

    0.07%

    Name - B22

    0.06%

    Name - B23

    0.06%

    Name - B24

    0.00%

    Name - B25

    0.00%

    Name - B26

    0.00%

    L2 - D

    3.34%

    Total général

    100.00%

    I have the same problem on the Name level (leaf) when I want to filter with L2, L3.

    Do you have any solution to correct this problem and have the same behavior without filter, everything group in one calculated measure ?

    Thank you in advance for your feedbacks.

    Ratanou

    Friday, December 7, 2018 3:59 PM