In this section, we experiment with common filters in the Analyzer and see their effect on the generated queries.
-
In the Analyzer, open the Tutorial cube.
The Analyzer refers to both cubes and subject areas as subject areas. The formal distinction between them is relevant only when you are creating them.
-
Click New.
The Analyzer displays Count (a count of the records):
Before we add a filter, let us see how the query is currently defined, so that we have a basis of comparison.
-
Click the Query Text button
.
The system then displays a dialog box that shows the MDX query that the Analyzer is using:
SELECT FROM [TUTORIAL]
-
Click OK.
-
Expand ColorD and Favorite Color.
-
Drag and drop Orange to Filters.
The Analyzer now uses only patients whose favorite color is orange. It looks something like this:
-
Click the Query Text button
.
The system then displays the following query:
SELECT FROM [TUTORIAL] %FILTER [COLORD].[H1].[FAVORITE COLOR].&[ORANGE]
The %FILTER keyword restricts the query. The fragment after %FILTER is a filter expression:
[COLORD].[H1].[FAVORITE COLOR].&[ORANGE]
This filter expression is a member expression, because it refers to a member (the Orange member of the Favorite Color level). A member is a set of records, and a member expression refers to that set of records.
Notice that this expression uses the dimension, hierarchy, and level names. The &[ORANGE] fragment refers to the key of the Orange member. The Analyzer uses keys rather than names, but you can use either if the member names are unique.
-
Click OK.
-
Add another color to the filter. To do so:
-
Click the X next to Orange in Filters.
This removes that filter.
-
Drag and drop Favorite Color to Filters. This adds a filter box directly above the pivot table.
-
In that filter box, click the search button and then select Orange and Purple.
-
Click the check mark.
This action filters the pivot table.
Important:
Do not drag and drop Orange and Purple separately to the Filters box. Instead drag the level as described and then select the members.
The Analyzer now looks something like this:
The system now uses only patients whose favorite color is orange or whose favorite color is purple. (Notice that the count is higher than it was for orange alone.)
-
Display the query text again. Now you should see the following:
SELECT FROM [TUTORIAL]
%FILTER %OR({[COLORD].[H1].[FAVORITE COLOR].&[ORANGE],[COLORD].[H1].[FAVORITE COLOR].&[PURPLE]})
In this case, the filter expression is as follows:
%OR({[COLORD].[H1].[FAVORITE COLOR].&[ORANGE],[COLORD].[H1].[FAVORITE COLOR].&[PURPLE]})
The %OR function is an InterSystems optimization; the argument to this function is a set.
The set is enclosed by curly braces {} and consists of a comma-separated list of elements. In this case, the set contains two member expressions. A set expression refers to all the records indicated by the elements of the set. In this case, the set refers to all patients whose favorite color is orange and all patients whose favorite color is purple.
-
Click OK.
-
Use the filter drop-down list and clear the check box next to Purple.
Now the Analyzer uses only patients whose favorite color is orange.
-
Expand AllerD and Allergies.
-
Drag and drop mold to Filters, beneath Orange.
The Analyzer now looks something like this:
Notice that the count is lower than we saw using just Orange alone. This pivot table displays only patients whose favorite color is orange and who are allergic to mold.
-
Display the query text again. Now you should see the following:
SELECT FROM [TUTORIAL]
%FILTER NONEMPTYCROSSJOIN([ALLERD].[H1].[ALLERGIES].&[MOLD],[COLORD].[H1].[FAVORITE COLOR].&[ORANGE])
In this case, the filter expression is as follows:
NONEMPTYCROSSJOIN([ALLERD].[H1].[ALLERGIES].&[MOLD],[COLORD].[H1].[FAVORITE COLOR].&[ORANGE])
The MDX function NONEMPTYCROSSJOIN combines two members and returns the resulting tuple. The tuple accesses only the records that belong to both of the given members.
set expression
When you use a set of members as a filter, the system accesses the records that belong to any of the given members. That is, the members are combined with logical OR.
You can write a set expression that refers to members as follows:
{member_expression,member_expression,member_expression...}
Where member_expression is a member expression.