Monday, August 31, 2009

SSAS with SSRS filtering and sorting MDX results

In SSRS MDX Query builder I cannot find a way to filter results based on Measures. Here's what I mean:
As you can see, you can only select dimensions from the filtering section. Also there is no option for sorting. Heres how you can do it:


There are two options. One from the query editor. Other from the tablix properties. I prefer the tablix properties (since sorting MUST be defined there and I found no other way to do it). I explain the first option here and the second option along with sorting.

  • Filtering using query properties:
This screenshot pretty much explains everything.

As it is ... if you press okay it will give the error : "Value is not an integer number". That is easily solved by setting the Expression for Value field as =.3 as shown.

All set. The procedure for setting the filter in tablix (your table) properties is presented next along with sorting :)

Select tablix properties:

Now you can add your sorts and your filters.

I would prefer the query to do this for me (to save SSRS resources and utilize SSAS resources) but I did not find that option (as of SSRS2008).