Database Reference
In-Depth Information
Axis-1
+-------------------
(East)
(New York)
(Massachusetts)
(Florida)
(California)
(Texas)
(Central)
(Illinois)
(Ohio)
(Colorado)
This is an example of how to combine functions to further scope the query.
SELECT {} ON AXIS(0),
{FILTER({Uda([Market], "Major Market")},
Substring([Market].CurrentMember.Member_Name, 1, 3) =
"New")} ON AXIS(1)
FROM [Sample.Basic];
Axis-1
+-------------------
(New York)
6.10.1.13 Counts with UDAs and Attributes
Again, using counts can yield useful infor-
mation about the database outline. This query returns the count of members with a
particular attribute.
WITH
MEMBER
[Geography].[AreaCode719Count] AS
'Count(Withattr([Area Code], "==", "719"))'
SELECT {[Measures]} ON AXIS(0),
{[Geography].[AreaCode719Count]} on AXIS(1)
FROM ASOSamp.Sample;
Axis-1 (Measures)
+-------------------+---------
(AreaCode719Count)
50.0000
use this next query to compare counts of members with and without an attribute. This
can be very helpful in resolving data that does not tie out due to a member not being
properly tagged with an attribute. Problems are often caused by improper outline main-
tenance such as when a new Sku (stock-keeping unit) is added and the person adding it
forgets to associate a color for the Sku; now the sum of all colors will not equal all Skus
in the base dimension.
WITH
MEMBER
[Geography].[NoAttribute] AS
'Count(Filter( [Geography].levels(0).members, NOT
IsValid([Geography].CurrentMember.[Area Code])))'
