Including a Simple Filter in the Query

Including a Simple Filter in the Query

An MDX query can also include a filter, which reduces the number of rows of the fact table that the query could potentially use. To add a filter to a query, add a clause like the following to the end of your SELECT statement:

WHERE filter_details

For filter_details, the simplest form is as follows:

[dim_name].[hier_name].[level_name].[member_name]

You can use the same variations here as described in Referring to Members and Measures, earlier in this page.

This expression filters the query so that the system accesses only the records associated with this member. For example, the following query uses only patients who have osteoporosis:

SELECT MEASURES.[%COUNT] ON 0,aged.[age bucket].MEMBERS ON 1 FROM demomdx WHERE diagd.osteoporosis

                                    %COUNT
1 0 to 9                                  *
2 10 to 19                                *
3 20 to 29                                *
4 30 to 39                                *
5 40 to 49                                *
6 50 to 59                                *
7 60 to 69                                7
8 70 to 79                                7
9 80+                                     8

The MDX shell uses an asterisk (*) to indicate that a value is null.

Filtering a Query discusses WHERE in more detail.