Query Filtering

There are a number of ways, on top of using the filter axis to slice and dice data. Here are a few examples.

Axis Filtering

As you may have read on the Basic Queries page, the filter axis is like the Where clause in an SQL query. It will allow you to constrain the query but without rendering the constraints in the table. Of course some times you might want to show the detail in the table as well and that is perfectly possible.

Select Members screen

To select members first from the query design window we select the level we want to filter on.

Then on the right hand side there is a popup sidebar, from there press the plus button.

A new window will then slide over displaying all the available members to select from the database. Select the ones you require and press the right arrow. This will move them all over to the selected side. Press Save.

When you re run the query, you will see the table has been constrained by your selection.

If you press the Show MDX button you should also see that your query has been edited to include the members you selected similar to this:

WITH
SET [~COLUMNS] AS
    {[Customer].[Gender].[M]}
SET [~ROWS_Time_Time] AS
    {[Time].[Time].[Year].Members}
SET [~ROWS_Product_Products] AS
    {[Product].[Products].[Product Department].Members}
SELECT
NON EMPTY CrossJoin([~COLUMNS], {[Measures].[Unit Sales], [Measures].[Store Cost]}) ON COLUMNS,
NON EMPTY NonEmptyCrossJoin([~ROWS_Time_Time], [~ROWS_Product_Products]) ON ROWS
FROM [Sales]

Custom Filter

Limit

Sort

Feedback

Was this page helpful?

Yes No

Glad to hear it! Please tell us how we can improve.

Sorry to hear that. Please tell us how we can improve.