0

I'm developing an application that uses a tabular database to show some business data.

I need to provide some basic filtering over measures values (equal to, greater than, lesser than etc.) and I'm currently analyzing the proper way to generate the MDX.

Looking at some documentation (and other threads on this site), I found that the most efficient approach would be using the FILTER or HAVING functions to filter out undesired values.

Unfortunately all examples normally include measures on one axis and dimension member on the other, but I potentially have dimension members in both axis and can't find a proper solution to use such functions to filter by measure value.

What have I done so far?

To make it easier to explain, let's say that we want to get the yearly sales quantities by product class filtering quantity > 1.3 milions

Trying to use HAVING or FILTER Functions, the resulting MDX I came up with is

SELECT 
NON EMPTY {[YearList].[Year].[Year].MEMBERS * [Measures].[Qty]} 
    HAVING [Measures].[Qty] > 1.3e6 ON COLUMNS,
NON EMPTY {[Classes].[cClass].[cClass].MEMBERS} 
    HAVING [Measures].[Qty] > 1.3e6 ON ROWS
FROM [Model]

or

SELECT 
NON EMPTY FILTER({[YearList].[Year].[Year].MEMBERS * [Measures].[Qty]}, 
    [Measures].[Qty] > 1.3e6) ON COLUMNS,
NON EMPTY FILTER({[Classes].[cClass].[cClass].MEMBERS} , 
    [Measures].[Qty] > 1.3e6) ON ROWS
FROM [Model]

But this is of course leading to unexpected result for the final user because the filter is happening on the aggregation of the quantities by the dimension on that axis only, which is greater then 1.3M

Wrong Result

The only way I found so far to achieve what I need is to define a custom member with an IIF statement

WITH
    MEMBER [Measures].[FilteredQty] AS 
    IIF ( [Measures].[Qty] > 1.3e6, [Measures].[Qty], NULL)

SELECT 
    NON EMPTY {[YearList].[Year].[Year].MEMBERS * [Measures].[FilteredQty]} ON COLUMNS,
    NON EMPTY {[Classes].[cClass].[cClass].MEMBERS} ON ROWS
FROM [Model]

The result is the one expected:

Expected Result

Is this the best approach or I should keep using FILTER and HAVING functions? Is there even a better approach I'm still missing? Thanks

Onur
  • 5,017
  • 5
  • 38
  • 54
SMarello
  • 309
  • 4
  • 11

1 Answers1

0

This is the best approach. You need to consider how MDX resolves result. In the example above it is a coincidence that your valid data in a continous region of first four columns of first row. Lets relax the filtering clause and make it >365000. Now take a look at last row of the result, the first two columns and the last column are eligible cells but the third and fourth column is not eligible. However your query will report it as null and the non empty function will not help. The reason is that non empty needs the entire row to be null Now the question that why filter is not eliminating the cell? Filter will eliminate a row or column when the criteria is greater then the sum on the other axis. So if filter is on columns the filter value has to be greater than the sum of rows for that column. Take a look at the sample below as soon as you remove the comments the last column will be removed. 

select 
non empty
filter(
([Measures].[Internet Sales Amount]
,{[Date].[Calendar Year].&[2013],[Date].[Calendar Year].&[2014]}
,[Date].[Calendar Quarter of Year].[Calendar Quarter of Year]
),([Date].[Calendar Year].currentmember,[Date].[Calendar Quarter of Year].currentmember,[Product].[Subcategory].currentmember,[Measures].[Internet Sales Amount])>45694.70--+0.05
)
on columns 
,
non empty
[Product].[Subcategory].members
on rows
from
[Adventure Works]

enter image description here

Edit another sample added.

with 
member [Measures].[Internet Sales AmountTest]
as 
iif(([Date].[Calendar Year].currentmember,[Date].[Calendar Quarter of Year].currentmember,[Product].[Subcategory].currentmember,[Measures].[Internet Sales Amount])>9000,
([Date].[Calendar Year].currentmember,[Date].[Calendar Quarter of Year].currentmember,[Product].[Subcategory].currentmember,[Measures].[Internet Sales Amount]),
null
)


select 
non empty
({[Measures].[Internet Sales Amount],[Measures].[Internet Sales AmountTest]}
,{[Date].[Calendar Year].&[2013]}
,[Date].[Calendar Quarter of Year].[Calendar Quarter of Year]
)
on columns 
,
non empty
[Product].[Subcategory].[Subcategory]
on rows
from
[Adventure Works]

enter image description here

MoazRub
  • 2,881
  • 2
  • 10
  • 20
  • Thanks for the reply. The problem is that the final user doesn't understand how MDX works, he just want to see data cells matching his filter criteria. I know that using the IIF statement I'll probably run in many misleading (null) values because the NON EMPTY acts only if the entire row or column is null, but still it's the best result I could achieve by a final user perspective. If the user sets a filter Quantity > 10, he doesn't want to see a row with 3 columns valued 5, 5 and 5. Using FILTER function, the condition would match and the data would be shown. – SMarello Jan 11 '19 at 11:05
  • @SMarello I understand what you are saying about the final user, Can you ask him if instead of null and empty cell works. Plus the issue that you stated "If the user sets a filter Quantity > 10, he doesn't want to see a row with 3 columns valued 5, 5 and 5" can be resolved. I am adding another query which will result null for any cell that is less than client value so in the above example the filter value is 9000. You will not find a cell less than 9000 for Internet Sales Amount Test. – MoazRub Jan 11 '19 at 11:30
  • @SMarello does the Edit resolve your issue, I will return null for any cell thats values is less then filter – MoazRub Jan 16 '19 at 15:35
  • Hi @MoazRub , Correct me if I'm wrong, but your last edit simply replicate what I already did in the last query of my question. – SMarello Jan 18 '19 at 07:24
  • @SMarello In the last edit, I tried to explain how you can return null for cells that dont clear the criteria. Which in your case may be acceptable for your client. – MoazRub Jan 18 '19 at 11:11
  • I understand what you did in the last edit, but isn't it the same as I did in my last query inside the first post? My question was actually about avoiding a lot of IIF statements and using more appropriate function, if possible. I'm also concerned about the query performance when using IIF instead of HAVING or FILTER. Unfortunately, I couldn't come up with a satisfying solution yet. – SMarello Jan 18 '19 at 14:28
  • @SMarello Yes it is similar to what you did.As far as performance goes, I am not sure how much effect will it have, you would need to try. However a filter will be evaluated for each row. Similarly this calculated measure would be evaluated for each row too so I think it might not be much, that is just my opinoin. – MoazRub Jan 18 '19 at 15:27