Database Reference
In-Depth Information
6.10.1.9 Filters
This next query uses the Filter() function and a text function to find
members that begin with a particular text string; “801”.
SELECT {} ON AXIS(0),
{FILTER({[Geography].members},
Substring([Geography].CurrentMember.Member_Name, 1, 3) = "801")} ON
AXIS(1)
FROM [ASOsamp.Sample];
Axis-1
+-----------------------------
(80101)
(80107)
(80154)
(80116)
…
Similar to the query above, this query will search for members that end with a par-
ticular text string, “25”:
SELECT {} ON AXIS(0),
{FILTER({[Area Code].levels(0).members},
Substring([Area Code].CurrentMember.Member_Name,
Len([Area Code].CurrentMember.Member_Name ) - 1) =
"25")} ON AXIS(1)
FROM [ASOsamp.Sample];
Axis-1
+-----------------------------
(225)
(425)
(925)
In a similar manner to the two queries above, this query will search for members that
contain a particular text string (“Digital”) acting as the equivalent of a wildcard.
SELECT {} ON AXIS(0),
{FILTER({[Products].members},
InStr(1, [Products].CurrentMember.Member_Name,
"Digital", 1) > 0)} ON AXIS(1)
FROM [ASOsamp.Sample];
Axis-1
+-----------------------------
(Digital Cameras/Camcorders)
(Digital Cameras)
(Digital Recorders)
(Digital Recorders)
take note that using Distinct() will not eliminate duplicates in this situation because
shared members are not considered duplicates.
