We help IT Professionals succeed at work.

SSAS MDX Range " : " not working

Hi Experts,

I have a table that contains products and prices, eg
Price:
100,000
320,006
800,000
450,235
500,000
1,000,050

When I write an MDX query to simply get products IDs that have a price range eg from $300,000 to $800,000 (see MDX below), it returns products with prices above the max range (800,000).

SELECT NON EMPTY {  }  ON 0,
NONEMPTYCROSSJOIN    ( { [MLSListingMeasures].[ProductID].[ProductID] }
      , { [Products].[ListPrice].[ListPrice].[300000]:[Products].[ListPrice].[800000] }
          ) ON 1
FROM ProductsList

However, if I use the "exact existing" values, eg from 320,006 to 800,000 I get the correct results. So the MDx query below works
SELECT NON EMPTY {  }  ON 0,
NONEMPTYCROSSJOIN    ( { [MLSListingMeasures].[ProductID].[ProductID] }
      , { [Products].[ListPrice].[ListPrice].[320006]:[Products].[ListPrice].[800000] }
          ) ON 1
FROM ProductsList

The issue occurs not matter what ranges I use for min or max. If the values matches what exists in the db, it returns correct reulsts. This will not workk for us, as our users cannot/will not be asked to restirct their searches, but rather search in whatever range they want.

What are we doing wrong? Kindly assist. Thanks
Comment
Watch Question

AFAIK the range operator returns the result in physical order, e.g. if the data include 1,2,3,...,9 then 1:10 will only return 1.

I'd replace the range op with something  like this:
filter( [Products].[ListPrice].[ListPrice],
cint([Products].[ListPrice].[ListPrice].currentmember.memberkey) >= 320006 and cint([Products].[ListPrice].[ListPrice].currentmember.memberkey)  <= 800000)

Perhaps not as elegant as using the range op but it works.
 
You just have to note that the key column of each member can be inverted to int.

Author

Commented:
ProjectChampion,
Thanks for looking into this. I have tried what you suggested. Now I get an error msg (see below):

Executing the query ...
Query (5, 10) The CURRENTMEMBER function expects a hierarchy expression for the 1 argument. A member expression was used.
Execution complete
 
 

Author

Commented:
oh.. and I changed currentmember.memberkey to currentmember.member_key bcos it said
Parser: The syntax for 'MEMBERKEY' is incorrect.
The problem is that member [300000] doesn't exists.

Check syntax of Products dimension reference. The last [List Price] may be not good. You should have a hierarchy identifier exact before the CurrentMember.

best regards
Grzegorz

Author

Commented:
grzegorzs,
Thanks for your feedback. However, our users have no way of knowing what values "members" exists and we want allow them make searches within any range(s)
Please, what do you mean by "have a hierarchy identifier exact before the CurrentMember."  and how do I write, or implement that that in the MDX?
Thanks!!

You have got bad type of argument 1 of CurrentMember function.
The syntax of this function is:

hierarchy_expression.CurrentMember

where hierarchy_expression is any MDX expression returning a hierarchy. I think in your query this expression returns a member.

The best way to give users ability to filter members by criteria is to define member property in the dimension - nonaggregatable (AttributeHierarchyEnabled = False) attribute related to attribute you want to filter.

Try this query (using the Adventure Works cube):

with member [Measures].[Children] as
[Customer].[Customer].CurrentMember.Properties("Total Children")  
select    
  {[Measures].[Children]} ON 0,
  {Filter(
      EXISTING [Customer].[Customer].[Customer].Members,
      Val([Customer].[Customer].CurrentMember.Properties("Total Children"))>=4 and
      Val([Customer].[Customer].CurrentMember.Properties("Total Children"))<=5
         )
  } ON 1
from [Adventure Works]

Set returned by Filter function is defined with values of Total Children member property.

Best regards
Grzegorz