Database Reference
In-Depth Information
SELECT {} ON AXIS(0),
Distinct({FILTER({[Products].members},
InStr(1, [Products].CurrentMember.Member_Name,
"Digital", 1) > 0)}) ON AXIS(1)
FROM [ASOsamp.Sample];
This query is an example where Distinct() works. In the first query, we create a com-
pound set of all level-0 members of the time dimension and the children of [Qtr1].
Because children of [Qtr1] are level-0 members of the time dimension, this will inten-
tionally result in a set with duplicate members. In the second query, the use of the
Distinct() function eliminates the duplicate members from the compound set.
SELECT {} ON AXIS(0),
{[Time].levels(0).members, [Qtr1].Children} ON AXIS(1)
FROM [ASOsamp.Sample];
/*vs*/
SELECT {} ON AXIS(0),
Distinct({[Time].levels(0).members, [Qtr1].Children}) ON
AXIS(1)
FROM [ASOsamp.Sample];
6.10.1.10 Quer ying Proper ties
These are codes used by Essbase for member-type prop-
erties. note that in this context a “measure” will be any member in the dimension
tagged “Account,” which does not have a member formula and is not the dimension
root member.
0 = Member (Non-Measure)
1 = Dimension Root member
2 = Member with Formula
3 = Measure
This query returns a list of members with the member type property displayed.
SELECT {} ON AXIS(0),
[Measures].members DIMENSION PROPERTIES MEMBER_TYPE ON AXIS(1)
FROM Sample.Basic;
Axis-1 Axis-1.properties
+-------------------+-------------------
(Measures) (MEMBER_TYPE = 1, t
(Profit) (MEMBER_TYPE = 3, t
(Margin) (MEMBER_TYPE = 3, t
(Sales) (MEMBER_TYPE = 3, t
(COGS) (MEMBER_TYPE = 3, t
(Total Expenses) (MEMBER_TYPE = 3, t
(Marketing) (MEMBER_TYPE = 3, t
(Payroll) (MEMBER_TYPE = 3, t
(Misc) (MEMBER_TYPE = 3, t
(Inventory) (MEMBER_TYPE = 3, t
(Opening Inventory) (MEMBER_TYPE = 2, t
