Major Key items:
Axis
Columns
Rows
Pages
Member
Tuple
Set
• A query has one or more axes. (The first three axes that are found in MDX queries are known as rows, columns and pages.) Keep in mind that columns always come before rows, and rows always precede pages, within the query.
•Curled brackets "{}" are used in MDX to represent a set of members of a dimension or group of dimensions.
Columns
Rows
Pages
Member
Tuple
Set
• A query has one or more axes. (The first three axes that are found in MDX queries are known as rows, columns and pages.) Keep in mind that columns always come before rows, and rows always precede pages, within the query.
•Curled brackets "{}" are used in MDX to represent a set of members of a dimension or group of dimensions.
•We can display more than one dimension on a result axis. When we do this, an "intersection" occurs, in effect, and each cell appearing in the associated axis relates to the combination of a member from each of the indicated dimensions. When more than one dimension is mapped onto an axis, the axis is said to consist of "tuples," containing the members of each of the mapped dimensions.
Introduction to the Structure of the MDX Data Model
Introduction to the Structure of the MDX Data Model
Here we will introduce the MDX data model, together with numerous of its most basic and most common components. These components will include tuples, axes, and sets
Tuple:
A tuple is a collection of members, each of which is selected from a different dimension.
Set:
The set is an important component of MDX syntax. Sets are typically enclosed by curled "{ }" braces and often appear in the SELECT part of a query, acting to specify the cells of a cube that are to be returned by the query.
SELECT
{([Time].[1997]),([Time].[1998])}ON COLUMNS,
{[Pay Type].[Pay Type].Members} ON ROWS
FROM HR
WHERE ([Measures].[Count])
SELECT
{[Measures].[Store Invoice],[Measures].[Supply Time],
[Measures].[Warehouse Cost],[Measures].[Warehouse Sales],
[Measures].[Units Shipped],[Measures].[Units Ordered],
[Measures].[Warehouse Profit]} ON COLUMNS,
{ [Time].[1997], [Time].[1998] } ON ROWS
FROM Warehouse
WHERE ([Warehouse].[All Warehouses].[USA].[CA])
Colons:
The colon operator provides us a means of leveraging the natural order of members to create a set. Order is important, because the levels within a dimension house their respective members either in member name or member key order. We can take advantage of the order of the members, and define sets based upon ranges within the order, by using the colon operator.
When we use the colon operator to define a set, members on both sides of the colon operator are included in the resulting set. The fact that the range selection is inclusive is a key concept.
{[Measures].[Store Invoice],[Measures].[Supply Time], [Measures].[Warehouse Cost],
[Measures].[Warehouse Sales],[Measures].[Units Shipped],
[Measures].[Units Ordered],[Measures].[Warehouse Profit]}
OR
can be retrieved with the following syntax:
{ [Measures].[Store Invoice]: [Measures].[Warehouse Profit]}
Keep in mind that measures are themselves dimensions, and so they, too, are composed of members. To illustrate, for a dimension based upon geography, which might contain Country, State and City as levels, USA, Idaho and New Orleans might represent valid members. This might be trying to apply the .Parent function to a member at the top of the hierarchy (which has no parent, in this context).
Working with Members and the .Members Operator:
The .Members operator provides a ready means of obtaining the "membership" for a given level, hierarchy or dimension. The importance of the .Members operator becomes clear when we get enough exposure to MDX under our belts to realize that this is a very common point from which we conduct operations that are more involved.
Select [Measures].Members on Columns,
[Dim Product].[Color].members on Rows
From [Adventure Works DW]
NOTE: While we have yet to introduce Calculated Members to any real extent within the series, it is important to be aware that Calculated Members will not appear in the result dataset returned by the .Members function. As we will discover later in the Member Functions segment of the series, the .AllMembers function, among other means, exist to include Calculated Members in our result datasets.
Introduction to the "Family" Member Functions:
•.Parent
•.Children
•Ancestor()
•Cousin()
•.FirstChild
•.LastChild
•.FirstSibling
•.LastSibling
The "family" character is appropriate, because these functions perform operations on dimensions, hierarchies, and levels in a manner that mirrors movement about a "family tree." There are other "family" functions that return sets, but we will focus primarily on the member functions in this segment of the series.
The "family" functions, like other member functions, belong to two general groups, from the perspective of the result datasets they return. One group works within a "vertical" scope, traveling up or down between hierarchical levels, as we will see in the respective practice section for each function. Examples include the following functions:
•Ancestor()
•.Children
•.Parent
•.FirstChild
•.LastChild.
The second general group of "family" functions operates within a "horizontal" scope of the hierarchy involved. These functions travel within the same level of the hierarchy ("across" versus "up and down"), and include:
•.Cousin()
•.FirstSibling
•.LastSibling
The .Parent Function:
SELECT
{[Time].[Year].members} ON COLUMNS,
{[Booker].Parent} ON ROWS
FROM [Warehouse]
WHERE ([Measures].[Units Shipped])
SELECT
{[Time].[Year].Members} ON COLUMNS,
{[Product].[Product Family].[Food].[Frozen Foods]} ON ROWS
FROM [Warehouse]
WHERE ([Measures].[Units Shipped])
SELECT
{[Time].[Year].Members} ON COLUMNS,
{[Product].[Product Family].[Food].[Frozen Foods].Parent} ON ROWS
FROM [Warehouse]
WHERE ([Measures].[Units Shipped])
The .Children Function:
Much like the .Parent function, the .Children function works within a "vertical" scope, moving, as we noted in the introductory section of this lesson, down between hierarchical levels from the member to which the function is applied. As its name implies, the .Children function returns the children of the source member, using the syntax we describe below. Again resembling the .Parent function, the .Children function is especially useful in calculated members
SELECT
{[Time].[Year].members} ON COLUMNS,
{[Booker].Children} ON ROWS
FROM [Warehouse]
WHERE ([Measures].[Units Shipped])
SELECT
{[Time].[Year].Members} ON COLUMNS,
{[Product].[Product Family].[Food].[Frozen Foods].Children} ON ROWS
FROM [Warehouse]
WHERE ([Measures].[Units Shipped], [Warehouse].[USA])
The Ancestor() Function:
The Ancestor() function retrieves the member, at the specified level, or at the specified distance, that is the ancestor (the parent, parent of parent, or higher) of the source member under consideration. Like the .Parent and .Children functions, it travels within a "vertical" scope, moving up between hierarchical levels from the member to which it is applied.
A difference exists between the functions, however, in that the Ancestor() function can contain the aforementioned level, or distance, component.
SELECT
{(Measures.[Warehouse Sales]), (Measures.[Warehouse Cost])} ON COLUMNS,
{(Ancestor([Time].[Year].[1997].[Q2].[4], 2))} ON ROWS
FROM [Warehouse]
The query delivers the results that we requested: The target member, specified as "two steps above the source member," is Year (two steps above Month in the Time dimension hierarchy), together with the respective total Units Shipped.
An introduction to the .FirstChild, .LastChild, .FirstSibling and .LastSibling functions:
SELECT
{[Time].[Year].[1997].FirstChild} ON COLUMNS
FROM [Warehouse]
WHERE ([Measures].[Warehouse Sales])
SELECT
{[Time].[Year].[1997].FirstChild.Children} ON COLUMNS,
{[Product].[Product Department].Members} ON ROWS
FROM [Warehouse]
WHERE ([Measures].[Warehouse Sales])
SELECT
{[Time].[Year].[1997].LastChild} ON COLUMNS
FROM [Warehouse]
WHERE ([Measures].[Warehouse Sales])
SELECT
{[Time].[Year].[1997].LastChild} ON COLUMNS,
{[Product].[Product Family].Members} ON ROWS
FROM [Warehouse]
WHERE ([Measures].[Warehouse Sales])
SELECT
{[Time].[Year].[1997].LastChild.Children} ON COLUMNS,
{[Product].[Product Family].[Non-Consumable].Children} ON ROWS
FROM [Warehouse]
WHERE ([Measures].[Warehouse Sales])
The .FirstSibling Function:
The .FirstSibling function is a bit more confusing than .FirstChild to some who are new to MDX, mainly in the way that it works for the first child of a given member-parent, but it may help again to relate the concept to a "family" scenario. As we discussed in the introductory section for this article, the .FirstSibling (as well as the .LastSibling) function operates within a "horizontal" scope for the hierarchy involved. It effectively "travels" within the same level of the hierarchy ("across" versus "up and down," as it does in the case of the .FirstChild and .LastChild functions.
SELECT
{[Time].[Year].[1997].[Q3].FirstSibling} ON COLUMNS,
{[Product].[Product Department].[Beverages].Children} ON ROWS
FROM [Warehouse]
WHERE ([Measures].[Units Shipped])
SELECT
{[Time].[Year].[1997].[Q3].FirstSibling.Children} ON COLUMNS,
NON EMPTY {[Product].[Product Department].[Canned Foods].Children} ON ROWS
FROM [Warehouse]
WHERE ([Measures].[Units Shipped])
The .LastSibling Function:
The .LastSibling function resembles the .FirstSibling function in its capacity to induce confusion to those new to MDX: the way that it behaves with the last child of a given member-parent is the interesting part, and, here again, it may help to relate to a "family" scenario. Like .FirstSibling, the .LastSibling function operates within a "horizontal" scope from the perspective of the hierarchy to which it belongs. It behavior mimics "travel" within the same level of the hierarchy ("across," again unlike the .FirstChild and .LastChild functions that we discussed in earlier parts of the discussion).
SELECT
{[Time].[Year].[1997].[Q2].LastSibling} ON COLUMNS
FROM [Warehouse]
WHERE [Measures].[Warehouse Cost]
SELECT
{[Time].[Year].[1997].[Q1].LastSibling.Children} ON COLUMNS,
{[Store].[Store Country].[USA].Children} ON ROWS
FROM [Warehouse]
WHERE [Measures].[Warehouse Cost]
The Cousin() Function:
As we have read in our previous articles, the capability to perform operations within the vertical and horizontal scopes of the hierarchy can mean more efficient, simpler MDX queries. The Cousin() function belongs to the latter of the two general groups, as it returns a member that is "parallel" in position under a given ancestor member, as we will see. We'll take a look at the Cousin() function in this article, to obtain an appreciation for its capabilities.
As we will observe, the Cousin() function returns the child member that occupies the same relative position under a parent member as the source member (under its own respective parent) that is specified. The operation of the function is based upon the order and position of members within levels. Let's say that two dimensions exist, in which dimension one has three levels, and dimension two has five levels. In this case, the cousin of the second level of dimension one is the second level of the dimension two.
SELECT {Cousin([Time].[1997].[Q1],[Time].[1998])} ON COLUMNS
FROM [BUDGET]
WHERE ([Measures].[Amount])
SELECT {Cousin([Time].[1997].[Q1],[Time].[1998])} ON COLUMNS
FROM [BUDGET]
WHERE ([Measures].[Amount])
SELECT {Cousin([Time].[1997].[Q1],[Time].[1998])} ON COLUMNS,
{[Account].[All Account].[Net Income].[Total Expense].Children} ON ROWS
FROM [BUDGET]
WHERE ([Measures].[Amount])
The .CurrentMember and .PrevMember functions:
WITH
MEMBER [Measures].[Margin CY] AS
'([Time].CurrentMember , [Measures].[Warehouse Sales])-([Time].CurrentMember,
[Measures].[Warehouse Cost])'
MEMBER [Measures].[Margin PY] AS
'([Time].PrevMember , [Measures].[Warehouse Sales])-([Time].PrevMember,
[Measures].[Warehouse Cost])'
MEMBER [Measures].[$ Change] AS
'([Time].CurrentMember , [Measures].[Margin CY])-([Time].CurrentMember,
[Measures].[Margin PY])'
SELECT
{ [Measures].[Margin PY], [Measures].[Margin CY], [Measures].[$ Change]} ON COLUMNS,
{([Warehouse].[Country].[USA])} ON ROWS
FROM Warehouse
WHERE ([Time].[1998])
The .NextMember function works in a manner similar to the .PrevMember function, except that, instead of returning the member that occurs at the same level as the specified member, earlier in the hierarchy, it returns the same-level member that follows the specified member within the hierarchy.
WITH
MEMBER [Measures].[Warehouse Margin] AS
'[Measures].[Warehouse Sales] - [Measures].[Warehouse Cost]'
SELECT
{([Time].CurrentMember),([Time].NextMember)} ON COLUMNS,
{([Warehouse].[Country].USA)} ON ROWS
FROM Warehouse
WHERE ([Measures].[Warehouse Margin])
The PeriodsToDate() Function:
The PeriodsToDate() function allows us to meet very common business needs, including the calculation of a year-to-date total. The calculation of this total requires accumulation over a range of members of the Time dimension.
WITH
MEMBER [Measures].[YTD Salaries] AS
'Sum(PeriodsToDate([Time].[Year]),[Org Salary])'
SELECT
{[Measures].[Org Salary],[Measures].[YTD Salaries]} ON COLUMNS,
[Time].[Quarter].Members ON ROWS
FROM HR
YTD(), QTD(), MTD(), and WTD() functions:
WITH
MEMBER [Measures].[QTD Salaries] AS
'Sum(QTD(),[Org Salary])'
SELECT
{[Measures].[Org Salary] , [Measures].[QTD Salaries]} ON COLUMNS,
[Time].[Month].Members ON ROWS
FROM HR
OpeningPeriod() and ClosingPeriod() functions:
The OpeningPeriod() function allows us to meet numerous common business needs, including (and especially) those that require that we return a balance from the first member of a given level. That level might be, for example, Year, with the first member being January. A "relative" calculation can thus be driven for a single month in the Year level of the Time dimension, without hardcoding. While other, less direct approaches exist to meet this requirement, OpeningPeriod() is an easy route to the "beginning balance" for an account, and this is one of its most common uses.
WITH
MEMBER [Measures].[Beg Bal] AS
'([Measures].[Warehouse Cost],
OpeningPeriod ( [Time].[Month], [Time].CurrentMember))'
SELECT
{[Measures].[Beg Bal]} ON COLUMNS,
[Time].[Quarter].Members ON ROWS
FROM Warehouse
WITH
MEMBER [Measures].[Closing Bal] AS
'([Measures].[Warehouse Cost],
ClosingPeriod ( [Time].[Month], [Time].CurrentMember))'
SELECT
{[Measures].[Closing Bal]} ON COLUMNS,
[Time].[Quarter].Members ON ROWS
FROM Warehouse
The LastPeriods() and ParallelPeriod() Functions:
LastPeriods() is excellent for the derivation of "total activity for the last (whatever number) periods back," and, thus for a "to date" cumulative total of sorts.
SELECT
{[Measures].[Warehouse Cost]} ON COLUMNS,
LastPeriods(11,[Time].[1998].[Q3].[8]) ON ROWS
FROM Warehouse
OR
SELECT
{[Measures].[Warehouse Cost]} ON COLUMNS,
{ [Time].[1997].[Q4].[10],
[Time].[1997].[Q4].[11],
[Time].[1997].[Q4].[12],
[Time].[1998].[Q1].[1],
[Time].[1998].[Q1].[2],
[Time].[1998].[Q1].[3],
[Time].[1998].[Q2].[4],
[Time].[1998].[Q2].[5],
[Time].[1998].[Q2].[6],
[Time].[1998].[Q3].[7],
[Time].[1998].[Q3].[8]}
ON ROWS
FROM Warehouse
The ParallelPeriod() function:
The ParallelPeriod() function is more specifically adapted to time dimensions than Cousin(). It takes the ancestor of the specified member (in the typical case, a period), at a specified level, then looks at the specified sibling of the ancestor (who lags by a specified numeric expression) and returns the parallel period of the specified member from the descendants of that sibling.
SELECT
{[Measures].[Warehouse Cost]} ON COLUMNS,
{ ParallelPeriod ([Quarter], 2,[1998].[Q3].[9])}ON ROWS
FROM Warehouse
The Order() function:
The Order() function provides the sorting capabilities we need within MDX, and is thus an important part of our analysis toolsets.
Two general types of order, hierarchized and nonhierarchized, can be anticipated. Ordering in a hierarchized manner arranges members within a given hierarchy, and then arranges the hierarchical levels. Nonhierarchized ordering, in contrast, ignores hierarchy completely in the arrangement of all members within the affected set.
The Order() function returns hierarchized data when the ASC or DESC order specifications are appended to the function, and nonhierarchized data when BASC or BDESC are used. (The "B" serves as an instruction to "break," or "ignore" the hierarchy.) ASC is the default order specification when none is specified in the function.
SELECT
{[Measures].[Warehouse Cost]} ON COLUMNS,
{ ORDER({[Warehouse].[USA].[WA].Children, [Warehouse].[USA].[OR].Children},
([Measures].[Warehouse Cost]) , DESC)} ON ROWS
FROM Warehouse
WHERE ([Time].[Year].[1998])
SELECT
{[Measures].[Warehouse Cost]
} ON COLUMNS,
{ ORDER({[Warehouse].[USA].[WA].Children, [Warehouse].[USA].[OR].Children},
([Measures].[Warehouse Cost]) , BDESC)} ON ROWS
FROM Warehouse
WHERE ([Time].[Year].[1998])
Tuple:
A tuple is a collection of members, each of which is selected from a different dimension.
Set:
The set is an important component of MDX syntax. Sets are typically enclosed by curled "{ }" braces and often appear in the SELECT part of a query, acting to specify the cells of a cube that are to be returned by the query.
SELECT
{([Time].[1997]),([Time].[1998])}ON COLUMNS,
{[Pay Type].[Pay Type].Members} ON ROWS
FROM HR
WHERE ([Measures].[Count])
SELECT
{[Measures].[Store Invoice],[Measures].[Supply Time],
[Measures].[Warehouse Cost],[Measures].[Warehouse Sales],
[Measures].[Units Shipped],[Measures].[Units Ordered],
[Measures].[Warehouse Profit]} ON COLUMNS,
{ [Time].[1997], [Time].[1998] } ON ROWS
FROM Warehouse
WHERE ([Warehouse].[All Warehouses].[USA].[CA])
Colons:
The colon operator provides us a means of leveraging the natural order of members to create a set. Order is important, because the levels within a dimension house their respective members either in member name or member key order. We can take advantage of the order of the members, and define sets based upon ranges within the order, by using the colon operator.
When we use the colon operator to define a set, members on both sides of the colon operator are included in the resulting set. The fact that the range selection is inclusive is a key concept.
{[Measures].[Store Invoice],[Measures].[Supply Time], [Measures].[Warehouse Cost],
[Measures].[Warehouse Sales],[Measures].[Units Shipped],
[Measures].[Units Ordered],[Measures].[Warehouse Profit]}
OR
can be retrieved with the following syntax:
{ [Measures].[Store Invoice]: [Measures].[Warehouse Profit]}
Keep in mind that measures are themselves dimensions, and so they, too, are composed of members. To illustrate, for a dimension based upon geography, which might contain Country, State and City as levels, USA, Idaho and New Orleans might represent valid members. This might be trying to apply the .Parent function to a member at the top of the hierarchy (which has no parent, in this context).
Working with Members and the .Members Operator:
The .Members operator provides a ready means of obtaining the "membership" for a given level, hierarchy or dimension. The importance of the .Members operator becomes clear when we get enough exposure to MDX under our belts to realize that this is a very common point from which we conduct operations that are more involved.
Select [Measures].Members on Columns,
[Dim Product].[Color].members on Rows
From [Adventure Works DW]
NOTE: While we have yet to introduce Calculated Members to any real extent within the series, it is important to be aware that Calculated Members will not appear in the result dataset returned by the .Members function. As we will discover later in the Member Functions segment of the series, the .AllMembers function, among other means, exist to include Calculated Members in our result datasets.
Introduction to the "Family" Member Functions:
•.Parent
•.Children
•Ancestor()
•Cousin()
•.FirstChild
•.LastChild
•.FirstSibling
•.LastSibling
The "family" character is appropriate, because these functions perform operations on dimensions, hierarchies, and levels in a manner that mirrors movement about a "family tree." There are other "family" functions that return sets, but we will focus primarily on the member functions in this segment of the series.
The "family" functions, like other member functions, belong to two general groups, from the perspective of the result datasets they return. One group works within a "vertical" scope, traveling up or down between hierarchical levels, as we will see in the respective practice section for each function. Examples include the following functions:
•Ancestor()
•.Children
•.Parent
•.FirstChild
•.LastChild.
The second general group of "family" functions operates within a "horizontal" scope of the hierarchy involved. These functions travel within the same level of the hierarchy ("across" versus "up and down"), and include:
•.Cousin()
•.FirstSibling
•.LastSibling
The .Parent Function:
SELECT
{[Time].[Year].members} ON COLUMNS,
{[Booker].Parent} ON ROWS
FROM [Warehouse]
WHERE ([Measures].[Units Shipped])
SELECT
{[Time].[Year].Members} ON COLUMNS,
{[Product].[Product Family].[Food].[Frozen Foods]} ON ROWS
FROM [Warehouse]
WHERE ([Measures].[Units Shipped])
SELECT
{[Time].[Year].Members} ON COLUMNS,
{[Product].[Product Family].[Food].[Frozen Foods].Parent} ON ROWS
FROM [Warehouse]
WHERE ([Measures].[Units Shipped])
The .Children Function:
Much like the .Parent function, the .Children function works within a "vertical" scope, moving, as we noted in the introductory section of this lesson, down between hierarchical levels from the member to which the function is applied. As its name implies, the .Children function returns the children of the source member, using the syntax we describe below. Again resembling the .Parent function, the .Children function is especially useful in calculated members
SELECT
{[Time].[Year].members} ON COLUMNS,
{[Booker].Children} ON ROWS
FROM [Warehouse]
WHERE ([Measures].[Units Shipped])
SELECT
{[Time].[Year].Members} ON COLUMNS,
{[Product].[Product Family].[Food].[Frozen Foods].Children} ON ROWS
FROM [Warehouse]
WHERE ([Measures].[Units Shipped], [Warehouse].[USA])
The Ancestor() Function:
The Ancestor() function retrieves the member, at the specified level, or at the specified distance, that is the ancestor (the parent, parent of parent, or higher) of the source member under consideration. Like the .Parent and .Children functions, it travels within a "vertical" scope, moving up between hierarchical levels from the member to which it is applied.
A difference exists between the functions, however, in that the Ancestor() function can contain the aforementioned level, or distance, component.
SELECT
{(Measures.[Warehouse Sales]), (Measures.[Warehouse Cost])} ON COLUMNS,
{(Ancestor([Time].[Year].[1997].[Q2].[4], 2))} ON ROWS
FROM [Warehouse]
The query delivers the results that we requested: The target member, specified as "two steps above the source member," is Year (two steps above Month in the Time dimension hierarchy), together with the respective total Units Shipped.
An introduction to the .FirstChild, .LastChild, .FirstSibling and .LastSibling functions:
SELECT
{[Time].[Year].[1997].FirstChild} ON COLUMNS
FROM [Warehouse]
WHERE ([Measures].[Warehouse Sales])
SELECT
{[Time].[Year].[1997].FirstChild.Children} ON COLUMNS,
{[Product].[Product Department].Members} ON ROWS
FROM [Warehouse]
WHERE ([Measures].[Warehouse Sales])
SELECT
{[Time].[Year].[1997].LastChild} ON COLUMNS
FROM [Warehouse]
WHERE ([Measures].[Warehouse Sales])
SELECT
{[Time].[Year].[1997].LastChild} ON COLUMNS,
{[Product].[Product Family].Members} ON ROWS
FROM [Warehouse]
WHERE ([Measures].[Warehouse Sales])
SELECT
{[Time].[Year].[1997].LastChild.Children} ON COLUMNS,
{[Product].[Product Family].[Non-Consumable].Children} ON ROWS
FROM [Warehouse]
WHERE ([Measures].[Warehouse Sales])
The .FirstSibling Function:
The .FirstSibling function is a bit more confusing than .FirstChild to some who are new to MDX, mainly in the way that it works for the first child of a given member-parent, but it may help again to relate the concept to a "family" scenario. As we discussed in the introductory section for this article, the .FirstSibling (as well as the .LastSibling) function operates within a "horizontal" scope for the hierarchy involved. It effectively "travels" within the same level of the hierarchy ("across" versus "up and down," as it does in the case of the .FirstChild and .LastChild functions.
SELECT
{[Time].[Year].[1997].[Q3].FirstSibling} ON COLUMNS,
{[Product].[Product Department].[Beverages].Children} ON ROWS
FROM [Warehouse]
WHERE ([Measures].[Units Shipped])
SELECT
{[Time].[Year].[1997].[Q3].FirstSibling.Children} ON COLUMNS,
NON EMPTY {[Product].[Product Department].[Canned Foods].Children} ON ROWS
FROM [Warehouse]
WHERE ([Measures].[Units Shipped])
The .LastSibling Function:
The .LastSibling function resembles the .FirstSibling function in its capacity to induce confusion to those new to MDX: the way that it behaves with the last child of a given member-parent is the interesting part, and, here again, it may help to relate to a "family" scenario. Like .FirstSibling, the .LastSibling function operates within a "horizontal" scope from the perspective of the hierarchy to which it belongs. It behavior mimics "travel" within the same level of the hierarchy ("across," again unlike the .FirstChild and .LastChild functions that we discussed in earlier parts of the discussion).
SELECT
{[Time].[Year].[1997].[Q2].LastSibling} ON COLUMNS
FROM [Warehouse]
WHERE [Measures].[Warehouse Cost]
SELECT
{[Time].[Year].[1997].[Q1].LastSibling.Children} ON COLUMNS,
{[Store].[Store Country].[USA].Children} ON ROWS
FROM [Warehouse]
WHERE [Measures].[Warehouse Cost]
The Cousin() Function:
As we have read in our previous articles, the capability to perform operations within the vertical and horizontal scopes of the hierarchy can mean more efficient, simpler MDX queries. The Cousin() function belongs to the latter of the two general groups, as it returns a member that is "parallel" in position under a given ancestor member, as we will see. We'll take a look at the Cousin() function in this article, to obtain an appreciation for its capabilities.
As we will observe, the Cousin() function returns the child member that occupies the same relative position under a parent member as the source member (under its own respective parent) that is specified. The operation of the function is based upon the order and position of members within levels. Let's say that two dimensions exist, in which dimension one has three levels, and dimension two has five levels. In this case, the cousin of the second level of dimension one is the second level of the dimension two.
SELECT {Cousin([Time].[1997].[Q1],[Time].[1998])} ON COLUMNS
FROM [BUDGET]
WHERE ([Measures].[Amount])
SELECT {Cousin([Time].[1997].[Q1],[Time].[1998])} ON COLUMNS
FROM [BUDGET]
WHERE ([Measures].[Amount])
SELECT {Cousin([Time].[1997].[Q1],[Time].[1998])} ON COLUMNS,
{[Account].[All Account].[Net Income].[Total Expense].Children} ON ROWS
FROM [BUDGET]
WHERE ([Measures].[Amount])
The .CurrentMember and .PrevMember functions:
WITH
MEMBER [Measures].[Margin CY] AS
'([Time].CurrentMember , [Measures].[Warehouse Sales])-([Time].CurrentMember,
[Measures].[Warehouse Cost])'
MEMBER [Measures].[Margin PY] AS
'([Time].PrevMember , [Measures].[Warehouse Sales])-([Time].PrevMember,
[Measures].[Warehouse Cost])'
MEMBER [Measures].[$ Change] AS
'([Time].CurrentMember , [Measures].[Margin CY])-([Time].CurrentMember,
[Measures].[Margin PY])'
SELECT
{ [Measures].[Margin PY], [Measures].[Margin CY], [Measures].[$ Change]} ON COLUMNS,
{([Warehouse].[Country].[USA])} ON ROWS
FROM Warehouse
WHERE ([Time].[1998])
The .NextMember function works in a manner similar to the .PrevMember function, except that, instead of returning the member that occurs at the same level as the specified member, earlier in the hierarchy, it returns the same-level member that follows the specified member within the hierarchy.
WITH
MEMBER [Measures].[Warehouse Margin] AS
'[Measures].[Warehouse Sales] - [Measures].[Warehouse Cost]'
SELECT
{([Time].CurrentMember),([Time].NextMember)} ON COLUMNS,
{([Warehouse].[Country].USA)} ON ROWS
FROM Warehouse
WHERE ([Measures].[Warehouse Margin])
The PeriodsToDate() Function:
The PeriodsToDate() function allows us to meet very common business needs, including the calculation of a year-to-date total. The calculation of this total requires accumulation over a range of members of the Time dimension.
WITH
MEMBER [Measures].[YTD Salaries] AS
'Sum(PeriodsToDate([Time].[Year]),[Org Salary])'
SELECT
{[Measures].[Org Salary],[Measures].[YTD Salaries]} ON COLUMNS,
[Time].[Quarter].Members ON ROWS
FROM HR
YTD(), QTD(), MTD(), and WTD() functions:
WITH
MEMBER [Measures].[QTD Salaries] AS
'Sum(QTD(),[Org Salary])'
SELECT
{[Measures].[Org Salary] , [Measures].[QTD Salaries]} ON COLUMNS,
[Time].[Month].Members ON ROWS
FROM HR
OpeningPeriod() and ClosingPeriod() functions:
The OpeningPeriod() function allows us to meet numerous common business needs, including (and especially) those that require that we return a balance from the first member of a given level. That level might be, for example, Year, with the first member being January. A "relative" calculation can thus be driven for a single month in the Year level of the Time dimension, without hardcoding. While other, less direct approaches exist to meet this requirement, OpeningPeriod() is an easy route to the "beginning balance" for an account, and this is one of its most common uses.
WITH
MEMBER [Measures].[Beg Bal] AS
'([Measures].[Warehouse Cost],
OpeningPeriod ( [Time].[Month], [Time].CurrentMember))'
SELECT
{[Measures].[Beg Bal]} ON COLUMNS,
[Time].[Quarter].Members ON ROWS
FROM Warehouse
WITH
MEMBER [Measures].[Closing Bal] AS
'([Measures].[Warehouse Cost],
ClosingPeriod ( [Time].[Month], [Time].CurrentMember))'
SELECT
{[Measures].[Closing Bal]} ON COLUMNS,
[Time].[Quarter].Members ON ROWS
FROM Warehouse
The LastPeriods() and ParallelPeriod() Functions:
LastPeriods() is excellent for the derivation of "total activity for the last (whatever number) periods back," and, thus for a "to date" cumulative total of sorts.
SELECT
{[Measures].[Warehouse Cost]} ON COLUMNS,
LastPeriods(11,[Time].[1998].[Q3].[8]) ON ROWS
FROM Warehouse
OR
SELECT
{[Measures].[Warehouse Cost]} ON COLUMNS,
{ [Time].[1997].[Q4].[10],
[Time].[1997].[Q4].[11],
[Time].[1997].[Q4].[12],
[Time].[1998].[Q1].[1],
[Time].[1998].[Q1].[2],
[Time].[1998].[Q1].[3],
[Time].[1998].[Q2].[4],
[Time].[1998].[Q2].[5],
[Time].[1998].[Q2].[6],
[Time].[1998].[Q3].[7],
[Time].[1998].[Q3].[8]}
ON ROWS
FROM Warehouse
The ParallelPeriod() function:
The ParallelPeriod() function is more specifically adapted to time dimensions than Cousin(). It takes the ancestor of the specified member (in the typical case, a period), at a specified level, then looks at the specified sibling of the ancestor (who lags by a specified numeric expression) and returns the parallel period of the specified member from the descendants of that sibling.
SELECT
{[Measures].[Warehouse Cost]} ON COLUMNS,
{ ParallelPeriod ([Quarter], 2,[1998].[Q3].[9])}ON ROWS
FROM Warehouse
The Order() function:
The Order() function provides the sorting capabilities we need within MDX, and is thus an important part of our analysis toolsets.
Two general types of order, hierarchized and nonhierarchized, can be anticipated. Ordering in a hierarchized manner arranges members within a given hierarchy, and then arranges the hierarchical levels. Nonhierarchized ordering, in contrast, ignores hierarchy completely in the arrangement of all members within the affected set.
The Order() function returns hierarchized data when the ASC or DESC order specifications are appended to the function, and nonhierarchized data when BASC or BDESC are used. (The "B" serves as an instruction to "break," or "ignore" the hierarchy.) ASC is the default order specification when none is specified in the function.
SELECT
{[Measures].[Warehouse Cost]} ON COLUMNS,
{ ORDER({[Warehouse].[USA].[WA].Children, [Warehouse].[USA].[OR].Children},
([Measures].[Warehouse Cost]) , DESC)} ON ROWS
FROM Warehouse
WHERE ([Time].[Year].[1998])
SELECT
{[Measures].[Warehouse Cost]
} ON COLUMNS,
{ ORDER({[Warehouse].[USA].[WA].Children, [Warehouse].[USA].[OR].Children},
([Measures].[Warehouse Cost]) , BDESC)} ON ROWS
FROM Warehouse
WHERE ([Time].[Year].[1998])
The Union() function:
The Union() function in general, building to the union of two sets after creating an individual query for each, to show that the Union() function does, indeed, generate the results we might expect within the context of an illustrative business need.
Union() allows us to combine sets. The primary syntax we use to take advantage of Union() affords us an optional flag to force the presentation of duplicates that might occur within the sets we are subjecting to the Union() function. The default is the elimination of duplicates.
SELECT
{[Measures].[Warehouse Sales]} ON COLUMNS,
UNION(
{[Store].[All Stores].[USA].[WA].Children},
{[Store].[All Stores].[USA].[OR].[Portland]})
ON ROWS
FROM Warehouse
SELECT
{[Measures].[Warehouse Sales]} ON COLUMNS,
UNION(
{[Store].[All Stores].[USA].[WA].Children},
{[Store].[All Stores].[USA].[WA].[Bellingham]})
ON ROWS
FROM Warehouse
--- OR ----
SELECT
{[Measures].[Warehouse Sales]} ON COLUMNS,
({[Store].[All Stores].[USA].[WA].Children} +
{[Store].[All Stores].[USA].[WA].[Bellingham]})
ON ROWS
FROM Warehouse
SELECT
{[Measures].[Warehouse Sales]} ON COLUMNS,
UNION({{[Store].[All Stores].[USA].[WA].Children},
{[Store].[All Stores].[USA].[WA].[Bellingham]}},All)
ON ROWS
FROM Warehouse
SELECT
{[Measures].[Warehouse Sales]} ON COLUMNS,
{{[Store].[All Stores].[USA].[WA].Children},
{[Store].[All Stores].[USA].[WA].[Bellingham]}}
ON ROWS
FROM Warehouse
The Union() function in general, building to the union of two sets after creating an individual query for each, to show that the Union() function does, indeed, generate the results we might expect within the context of an illustrative business need.
Union() allows us to combine sets. The primary syntax we use to take advantage of Union() affords us an optional flag to force the presentation of duplicates that might occur within the sets we are subjecting to the Union() function. The default is the elimination of duplicates.
SELECT
{[Measures].[Warehouse Sales]} ON COLUMNS,
UNION(
{[Store].[All Stores].[USA].[WA].Children},
{[Store].[All Stores].[USA].[OR].[Portland]})
ON ROWS
FROM Warehouse
SELECT
{[Measures].[Warehouse Sales]} ON COLUMNS,
UNION(
{[Store].[All Stores].[USA].[WA].Children},
{[Store].[All Stores].[USA].[WA].[Bellingham]})
ON ROWS
FROM Warehouse
--- OR ----
SELECT
{[Measures].[Warehouse Sales]} ON COLUMNS,
({[Store].[All Stores].[USA].[WA].Children} +
{[Store].[All Stores].[USA].[WA].[Bellingham]})
ON ROWS
FROM Warehouse
SELECT
{[Measures].[Warehouse Sales]} ON COLUMNS,
UNION({{[Store].[All Stores].[USA].[WA].Children},
{[Store].[All Stores].[USA].[WA].[Bellingham]}},All)
ON ROWS
FROM Warehouse
SELECT
{[Measures].[Warehouse Sales]} ON COLUMNS,
{{[Store].[All Stores].[USA].[WA].Children},
{[Store].[All Stores].[USA].[WA].[Bellingham]}}
ON ROWS
FROM Warehouse
The Intersect() function:
The Intersect() function is to compare two sets, then to return a set that consists of members that exist in both original sets
SELECT
{[Measures].[Number of Employees]} ON COLUMNS,
INTERSECT
({([Store].[All Stores].[USA].[OR]),
([Store].[All Stores].[USA].[CA]),
([Store].[All Stores].[USA].[WA])} ,
{([Store].[All Stores].[USA].[OR]),
([Store].[All Stores].[USA].[WA])} )
ON ROWS
FROM HR
We can prove this to ourselves by overriding the default behavior, and using the ALL flag to allow the duplicate to be retained
{([Store].[All Stores].[USA].[OR].Children),([Store].[All Stores].[USA].[OR].[Portland])}, ALL )ON ROWS
The EXCEPT() Function:
EXCEPT() returns the difference between two sets.
SELECT
{Measures.[Amount] } ON COLUMNS,
{EXCEPT ([Store].[Store Country].Members, {[Store Country].[Canada]} )} ON ROWS
FROM Budget
WHERE ([Account].[All Account].[Net Income].[Total Expense].[General & Administration], [Time].[1998])
WITH MEMBER [Time].[Annual Delta] AS
'[Time].[1998] - [Time].[1997]'
SELECT
{Time.[1998] , Time.[1997] , [Time].[Annual Delta] } ON COLUMNS,
{ EXCEPT
([Store].[All Stores].[USA].Children, {[Store].[OR]} )} ON ROWS
FROM Budget
WHERE
([Account].[All Account].[Net Income].[Total Expense].[General & Administration], Measures.[Amount])
The Filter() function:
The general purpose of the Filter() function is to allow us to filter out parts of a set that we do not need in a given situation, and to return a subset of a larger set as a result. Uses of the Filter() function, as with many MDX functions, can range from the sublimely simple to the impressively advanced, and it can be used in many innovative ways.
The Filter() function returns the tuples of the specified set that meet the criteria of the search condition. The returned subset represents the portion of the larger, specified set that evaluates as "true" with regard to the logical expression contained in the Boolean search condition.
SELECT
{[Measures].[Warehouse Sales]} ON COLUMNS,
{Filter (
[Warehouse].[City].MEMBERS,
([Measures].[Warehouse Sales], [Time].[1998]) > 9999)
} ON ROWS
FROM [Warehouse]
WHERE ([Time].[1998])
The Count() function:
The Count() function counts the cells defined by the set that is specified in the function.
WITH
Member [Warehouse].[No. Locations] AS
'COUNT ( {[Warehouse].[City].MEMBERS })'
SELECT
{ [Warehouse]. [No. Locations] } ON COLUMNS,
{ [Product].[Product Department].Members} ON ROWS
FROM [Warehouse]
WHERE [Time].[1998]
WITH
Member [Store Type].[Types Count] AS
'Count( {[Store Type].[Store Type].MEMBERS })'
SELECT
{ [Store Type].[Types Count] } ON COLUMNS,
{ [Store].[Store Country].MEMBERS } ON ROWS
FROM [Warehouse]
WHERE ([Time].[1998], [Measures].[Units Ordered])
The EXCLUDEEMPTY keyword:
SELECT
{[Measures].[Units Ordered] } ON COLUMNS,
NON EMPTY
CROSSJOIN (
{[Store].[Store Country].Members},
{[Store Type].[Store Type].Members}
) ON ROWS
FROM Warehouse]
WHERE [Time].[1998]
The CrossJoin() function:
The CrossJoin() function is to allow us to produce all combinations of two sets
SELECT
{ [Measures].[Warehouse Sales], [Measures].[Warehouse Cost],
[Measures].[Warehouse Profit]} ON COLUMNS,
CROSSJOIN (
{[Time].[Year].Members},{[Store].[Store Country].[USA].Children} ) ON ROWS
FROM [Warehouse]
The Head() function:
The general purpose of the Head() function is to return the first specified number of elements in a set.
SELECT
{HEAD([Time].[1998].Children, 3)} ON COLUMNS,
{[WAREHOUSE].[Country].[USA].Children} ON ROWS
FROM
[Warehouse]
WHERE (Measures.[Units Shipped])
WITH
MEMBER [Measures].[Volume Delta] AS
'[Measures].[Units Ordered] - [Measures].[Units Shipped]'
SELECT
CROSSJOIN (
{HEAD([Time].[Year].[1998].Children, 2)},
{[Measures].[Units Ordered], [Measures].[Units Shipped],
[Measures].[Volume Delta]} ) ON COLUMNS,
{[WAREHOUSE].[Country].[USA].[WA].Children} ON ROWS
FROM [Warehouse]
The Tail() function:
The general purpose of the Tail() function is to return the last specified number of elements in a set. The Tail() function starts at the end of a set we designate.
SELECT
{TAIL([Time].[1998].Children, 3)} ON COLUMNS,
{[WAREHOUSE].[Country].[USA].Children} ON ROWS
FROM
[Warehouse]
WHERE (Measures.[Units Shipped])
WITH MEMBER [Measures].[Volume Delta] AS
'[Measures].[Units Ordered] - [Measures].[Units Shipped]'
SELECT
CROSSJOIN (
{TAIL([Time].[Year].[1998].Children, 2)},
{[Measures].[Units Ordered], [Measures].[Units Shipped],
[Measures].[Volume Delta]} ) ON COLUMNS,
{[WAREHOUSE].[Country].[USA].[WA].Children} ON ROWS
FROM [Warehouse]
The .Item() function:
The .Item() function "returns a member from a specified tuple" or "alternatively, returns a tuple from a set." Its use, depending upon whether it is being applied to a tuple or to a set, dictates the syntax involved. The indiscriminate use of the .Item() function can degrade performance in some scenarios, just as it can increase maintenance overhead.
SELECT
{[Time].[1998].Children.Item(0): [Time].[1998].Children.Item(2) } ON COLUMNS,
{CROSSJOIN([Store].[All Stores].[USA].Children, [Product].
[Product Family].Members).Item("[WA]","[Non-Consumable]")} ON ROWS
FROM [Warehouse]
WHERE ([Measures].[Units Shipped])
The AVG() Function:
The AVG() function "returns the average value of a numeric expression evaluated over a set." The function uses a numeric expression to indicate the base value for which the average will be calculated. The AVG() function ignores empty values found within the cells that are associated with the specified set. In cases where we wish to count the empty cells, as well, we can force the inclusion of these cells by employing the COALESCEEMPTY() function.
WITH MEMBER [Store].[NetAvg]
AS
'AVG( { [Store].[Store Country].Members}, [Measures].[Units Shipped])'
SELECT
{ [Store].[Store Country].Members, [Store].[NetAvg] } ON COLUMNS,
{[Product].[Product Family].[Non-Consumable].Children} ON ROWS
FROM
[Warehouse]
WHERE [Measures].[Units Shipped]
WITH MEMBER
[Product].[AverageSales] AS 'Avg({[Product].[Product Family].Members},
[Measures].[Warehouse Sales])'
SELECT
{ [Product].[Product Family].Members, [Product].[AverageSales]} ON COLUMNS,
NON EMPTY { [Store Type].Members } on rows
FROM
[Warehouse]
WHERE
([Measures].[Warehouse Sales])
The IsEmpty() Function:
ISEMPTY () affords us a means of testing for empty cells. ISEMPTY() is often accompanied by the conditional IIF() function. A logical function, ISEMPTY() returns "True" if the Value Expression is null, and "False" if it is not null.
WITH MEMBER [Measures].[Activity]
AS
'IIF( ISEMPTY( [Measures].[Units Shipped]), "None", "Activity:")'
SELECT
{[Warehouse].[State Province].Members} ON COLUMNS,
CROSSJOIN(
{[Product].[All Products].[Food].[Canned Foods].[Vegetables]},
{[Measures].[Activity], [Measures].[Units Shipped]}) ON ROWS
FROM [Warehouse]
WHERE ([Time].[Year].[1998])
WITH MEMBER
[Measures].[% Total Products]
AS
'[Measures].[Units Shipped]/([Measures].[Units Shipped],[Product].Parent)'
SELECT
{[Measures].[Units Shipped], [Measures].[% Total Products]} ON COLUMNS,
{DESCENDANTS([Product].[All Products], [Product].[Product Family],
SELF_AND_BEFORE)} ON ROWS
FROM [Warehouse]
WHERE ([Time].[Year].[1998])
WITH MEMBER
[Measures].[Warehouse Margin]
AS
'([Measures].[Warehouse Profit]/[Measures].[Warehouse Sales])',
FORMAT_STRING = '#.00%'
MEMBER
[Measures].[Margin Perf Indicator]
AS
'IIF(([Measures].[Warehouse Margin] > .55), "High Margin", "Low Margin")'
SELECT
{[Measures].[Warehouse Sales], [Measures].[Warehouse Cost],
[Measures].[Warehouse Profit], [Measures].[Warehouse Margin],
[Measures].[Margin Perf Indicator]} ON COLUMNS,
CROSSJOIN( {[Warehouse].Children},
{[Product].[Product Category].Members}) ON ROWS
FROM WAREHOUSE
WHERE ([Time].[Year].[1998])
The GENERATE() Function:
The GENERATE() function comes in two "models:" "set version" and "string version."
SELECT {[Measures].[Units Shipped]} ON COLUMNS,
{GENERATE
({[Warehouse].[City].Members},
TOPCOUNT(DESCENDANTS([Warehouse].Currentmember,[Warehouse].[Warehouse Name]),1,
[Measures].[Units Shipped]))} ON ROWS
FROM [WAREHOUSE]
WHERE ([Time].[1998])
The Distinct() Function:
The Distinct() function returns a set, removing duplicate tuples from a specified set.
The Intersect() function is to compare two sets, then to return a set that consists of members that exist in both original sets
SELECT
{[Measures].[Number of Employees]} ON COLUMNS,
INTERSECT
({([Store].[All Stores].[USA].[OR]),
([Store].[All Stores].[USA].[CA]),
([Store].[All Stores].[USA].[WA])} ,
{([Store].[All Stores].[USA].[OR]),
([Store].[All Stores].[USA].[WA])} )
ON ROWS
FROM HR
We can prove this to ourselves by overriding the default behavior, and using the ALL flag to allow the duplicate to be retained
{([Store].[All Stores].[USA].[OR].Children),([Store].[All Stores].[USA].[OR].[Portland])}, ALL )ON ROWS
The EXCEPT() Function:
EXCEPT() returns the difference between two sets.
SELECT
{Measures.[Amount] } ON COLUMNS,
{EXCEPT ([Store].[Store Country].Members, {[Store Country].[Canada]} )} ON ROWS
FROM Budget
WHERE ([Account].[All Account].[Net Income].[Total Expense].[General & Administration], [Time].[1998])
WITH MEMBER [Time].[Annual Delta] AS
'[Time].[1998] - [Time].[1997]'
SELECT
{Time.[1998] , Time.[1997] , [Time].[Annual Delta] } ON COLUMNS,
{ EXCEPT
([Store].[All Stores].[USA].Children, {[Store].[OR]} )} ON ROWS
FROM Budget
WHERE
([Account].[All Account].[Net Income].[Total Expense].[General & Administration], Measures.[Amount])
The Filter() function:
The general purpose of the Filter() function is to allow us to filter out parts of a set that we do not need in a given situation, and to return a subset of a larger set as a result. Uses of the Filter() function, as with many MDX functions, can range from the sublimely simple to the impressively advanced, and it can be used in many innovative ways.
The Filter() function returns the tuples of the specified set that meet the criteria of the search condition. The returned subset represents the portion of the larger, specified set that evaluates as "true" with regard to the logical expression contained in the Boolean search condition.
SELECT
{[Measures].[Warehouse Sales]} ON COLUMNS,
{Filter (
[Warehouse].[City].MEMBERS,
([Measures].[Warehouse Sales], [Time].[1998]) > 9999)
} ON ROWS
FROM [Warehouse]
WHERE ([Time].[1998])
The Count() function:
The Count() function counts the cells defined by the set that is specified in the function.
WITH
Member [Warehouse].[No. Locations] AS
'COUNT ( {[Warehouse].[City].MEMBERS })'
SELECT
{ [Warehouse]. [No. Locations] } ON COLUMNS,
{ [Product].[Product Department].Members} ON ROWS
FROM [Warehouse]
WHERE [Time].[1998]
WITH
Member [Store Type].[Types Count] AS
'Count( {[Store Type].[Store Type].MEMBERS })'
SELECT
{ [Store Type].[Types Count] } ON COLUMNS,
{ [Store].[Store Country].MEMBERS } ON ROWS
FROM [Warehouse]
WHERE ([Time].[1998], [Measures].[Units Ordered])
The EXCLUDEEMPTY keyword:
SELECT
{[Measures].[Units Ordered] } ON COLUMNS,
NON EMPTY
CROSSJOIN (
{[Store].[Store Country].Members},
{[Store Type].[Store Type].Members}
) ON ROWS
FROM Warehouse]
WHERE [Time].[1998]
The CrossJoin() function:
The CrossJoin() function is to allow us to produce all combinations of two sets
SELECT
{ [Measures].[Warehouse Sales], [Measures].[Warehouse Cost],
[Measures].[Warehouse Profit]} ON COLUMNS,
CROSSJOIN (
{[Time].[Year].Members},{[Store].[Store Country].[USA].Children} ) ON ROWS
FROM [Warehouse]
The Head() function:
The general purpose of the Head() function is to return the first specified number of elements in a set.
SELECT
{HEAD([Time].[1998].Children, 3)} ON COLUMNS,
{[WAREHOUSE].[Country].[USA].Children} ON ROWS
FROM
[Warehouse]
WHERE (Measures.[Units Shipped])
WITH
MEMBER [Measures].[Volume Delta] AS
'[Measures].[Units Ordered] - [Measures].[Units Shipped]'
SELECT
CROSSJOIN (
{HEAD([Time].[Year].[1998].Children, 2)},
{[Measures].[Units Ordered], [Measures].[Units Shipped],
[Measures].[Volume Delta]} ) ON COLUMNS,
{[WAREHOUSE].[Country].[USA].[WA].Children} ON ROWS
FROM [Warehouse]
The Tail() function:
The general purpose of the Tail() function is to return the last specified number of elements in a set. The Tail() function starts at the end of a set we designate.
SELECT
{TAIL([Time].[1998].Children, 3)} ON COLUMNS,
{[WAREHOUSE].[Country].[USA].Children} ON ROWS
FROM
[Warehouse]
WHERE (Measures.[Units Shipped])
WITH MEMBER [Measures].[Volume Delta] AS
'[Measures].[Units Ordered] - [Measures].[Units Shipped]'
SELECT
CROSSJOIN (
{TAIL([Time].[Year].[1998].Children, 2)},
{[Measures].[Units Ordered], [Measures].[Units Shipped],
[Measures].[Volume Delta]} ) ON COLUMNS,
{[WAREHOUSE].[Country].[USA].[WA].Children} ON ROWS
FROM [Warehouse]
The .Item() function:
The .Item() function "returns a member from a specified tuple" or "alternatively, returns a tuple from a set." Its use, depending upon whether it is being applied to a tuple or to a set, dictates the syntax involved. The indiscriminate use of the .Item() function can degrade performance in some scenarios, just as it can increase maintenance overhead.
SELECT
{[Time].[1998].Children.Item(0): [Time].[1998].Children.Item(2) } ON COLUMNS,
{CROSSJOIN([Store].[All Stores].[USA].Children, [Product].
[Product Family].Members).Item("[WA]","[Non-Consumable]")} ON ROWS
FROM [Warehouse]
WHERE ([Measures].[Units Shipped])
The AVG() Function:
The AVG() function "returns the average value of a numeric expression evaluated over a set." The function uses a numeric expression to indicate the base value for which the average will be calculated. The AVG() function ignores empty values found within the cells that are associated with the specified set. In cases where we wish to count the empty cells, as well, we can force the inclusion of these cells by employing the COALESCEEMPTY() function.
WITH MEMBER [Store].[NetAvg]
AS
'AVG( { [Store].[Store Country].Members}, [Measures].[Units Shipped])'
SELECT
{ [Store].[Store Country].Members, [Store].[NetAvg] } ON COLUMNS,
{[Product].[Product Family].[Non-Consumable].Children} ON ROWS
FROM
[Warehouse]
WHERE [Measures].[Units Shipped]
WITH MEMBER
[Product].[AverageSales] AS 'Avg({[Product].[Product Family].Members},
[Measures].[Warehouse Sales])'
SELECT
{ [Product].[Product Family].Members, [Product].[AverageSales]} ON COLUMNS,
NON EMPTY { [Store Type].Members } on rows
FROM
[Warehouse]
WHERE
([Measures].[Warehouse Sales])
The IsEmpty() Function:
ISEMPTY () affords us a means of testing for empty cells. ISEMPTY() is often accompanied by the conditional IIF() function. A logical function, ISEMPTY() returns "True" if the Value Expression is null, and "False" if it is not null.
WITH MEMBER [Measures].[Activity]
AS
'IIF( ISEMPTY( [Measures].[Units Shipped]), "None", "Activity:")'
SELECT
{[Warehouse].[State Province].Members} ON COLUMNS,
CROSSJOIN(
{[Product].[All Products].[Food].[Canned Foods].[Vegetables]},
{[Measures].[Activity], [Measures].[Units Shipped]}) ON ROWS
FROM [Warehouse]
WHERE ([Time].[Year].[1998])
WITH MEMBER
[Measures].[% Total Products]
AS
'[Measures].[Units Shipped]/([Measures].[Units Shipped],[Product].Parent)'
SELECT
{[Measures].[Units Shipped], [Measures].[% Total Products]} ON COLUMNS,
{DESCENDANTS([Product].[All Products], [Product].[Product Family],
SELF_AND_BEFORE)} ON ROWS
FROM [Warehouse]
WHERE ([Time].[Year].[1998])
WITH MEMBER
[Measures].[Warehouse Margin]
AS
'([Measures].[Warehouse Profit]/[Measures].[Warehouse Sales])',
FORMAT_STRING = '#.00%'
MEMBER
[Measures].[Margin Perf Indicator]
AS
'IIF(([Measures].[Warehouse Margin] > .55), "High Margin", "Low Margin")'
SELECT
{[Measures].[Warehouse Sales], [Measures].[Warehouse Cost],
[Measures].[Warehouse Profit], [Measures].[Warehouse Margin],
[Measures].[Margin Perf Indicator]} ON COLUMNS,
CROSSJOIN( {[Warehouse].Children},
{[Product].[Product Category].Members}) ON ROWS
FROM WAREHOUSE
WHERE ([Time].[Year].[1998])
The GENERATE() Function:
The GENERATE() function comes in two "models:" "set version" and "string version."
SELECT {[Measures].[Units Shipped]} ON COLUMNS,
{GENERATE
({[Warehouse].[City].Members},
TOPCOUNT(DESCENDANTS([Warehouse].Currentmember,[Warehouse].[Warehouse Name]),1,
[Measures].[Units Shipped]))} ON ROWS
FROM [WAREHOUSE]
WHERE ([Time].[1998])
The Distinct() Function:
The Distinct() function returns a set, removing duplicate tuples from a specified set.
The IS Operator:
"When comparing members, do this:
IIF([Date].[Fiscal]. CurrentMember IS [Date].[Calendar].[Calendar Year].[CY 2004], ...)
"... not this:
IIF([Date].[Fiscal]. CurrentMember.Name = "CY 2004", ...)
WITH MEMBER
[Measures].[3-Mo Rolling Avg Customer Count]
AS
'IIF(
[Date].[Calendar].CURRENTMEMBER.LEVEL IS [Date].[Calendar].[Month],
AVG(LASTPERIODS(3, [Date].[Calendar].CURRENTMEMBER),
[Measures].[Customer Count]),
NULL
)', FORMAT_STRING = "#,###.00"
SELECT
DESCENDANTS(
[Date].[Calendar].[Calendar Year].[CY 2004],[Date].[Calendar].[Month],
SELF_AND_BEFORE) ON AXIS(0),
CROSSJOIN(
{[Product].[Product Categories].[Category].[Bikes].CHILDREN },
{[Measures].[Customer Count],
[Measures].[3-Mo Rolling Avg Customer Count]}
) ON AXIS(1)
FROM [Adventure Works]
SELECT
{[Measures].[Internet Sales Amount]} ON AXIS(0),
{FILTER ( [Customer].[Customer Geography].MEMBERS,
[Customer].[Customer Geography].CURRENTMEMBER.PARENT
IS [Customer].[Customer Geography].[State-Province].[Georgia]
)} ON AXIS(1)
FROM [Adventure Works]
WHERE ([Date].[Fiscal].[Fiscal Year].[FY 2004])
The .Ordinal Function:
The general purpose of the .Ordinal function is to return the ordinal value of a specified dimensional level. The .Ordinal function is particularly useful within scenarios where we employ it with the IIF() function to determine the position of a level within a dimensional hierarchy for various reasons.
WITH MEMBER [Measures].[SalesTerrName]
AS
'[Sales Territory].[Sales Territory].CURRENTMEMBER.NAME'
MEMBER
[Measures].[SalesTerrLevNo]
AS
'[Sales Territory].[Sales Territory].CURRENTMEMBER.LEVEL.ORDINAL'
SELECT
{[Measures].[SalesTerrName], [Measures].[SalesTerrLevNo]} ON AXIS(0),
{[Sales Territory].[Sales Territory].MEMBERS} ON AXIS(1)
FROM [Adventure Works]
WITH MEMBER [Measures].[LevelNo]
AS
'[Geography].[Geography].CURRENTMEMBER.LEVEL.ORDINAL'
MEMBER [Measures].[DisplayName]
AS
'[Geography].[Geography].CURRENTMEMBER.MEMBER_CAPTION'
MEMBER [Measures].[UniqueName]
AS
'[Geography].[Geography].CURRENTMEMBER.UNIQUENAME'
SELECT
{[Measures].[LevelNo], [Measures].[DisplayName],
[Measures].[UniqueName]} ON AXIS(0) ,
{[Geography].[Geography].MEMBERS} ON AXIS(1)
FROM [Adventure Works]
The IsLeaf() operator:
The IsLeaf() operator, like other logical operators, evaluates values and returns a Boolean value. The utility of IsLeaf() becomes clear when we realize its value in helping us to determine the position of a member within a dimensional hierarchy. IsLeaf() more specifically allows us to test if a member is at leaf level, or at the “bottom” level of the dimension to which it belongs.
WITH MEMBER
[Measures].[BegCount]
AS
IIF(ISLEAF([Date].[Calendar].CURRENTMEMBER),
[Measures].[Order Count],
([Date].[Calendar].CURRENTMEMBER.FIRSTCHILD, [Measures].[BegCount]))
SELECT
{[Measures].[BegCount]}ON COLUMNS,
{[Date].[Calendar].MEMBERS} ON ROWS
FROM
[Adventure Works]
The IsSibling() Function:
The general purpose of IsSibling(), a logical function, is to return whether or not a member that we specify is the sibling of another member we specify. (By “sibling,” of course, we mean that the two members share the same parent.)
WITH MEMBER
[Measures].[SelectCount]
AS
IIF( ISSIBLING([Date].[Calendar].CURRENTMEMBER,
[Date].[Calendar].[Date].[January 1, 2004]),
[Measures].[Order Count], NULL)
SELECT
{[Measures].[SelectCount]}ON AXIS(0),
NON EMPTY{[Date].[Calendar].MEMBERS} ON AXIS(1)
FROM [Adventure Works]
WITH MEMBER
[Measures].[3-Mo Rolling Avg Customer Count]
AS
'IIF(
ISSIBLING([Date].[Calendar].CURRENTMEMBER,
[Date].[Calendar].[Month].[January 2004]),
AVG(LASTPERIODS(3, [Date].[Calendar].CURRENTMEMBER),
[Measures].[Customer Count]),NULL
)', FORMAT_STRING = "#,###.00"
SELECT
{DESCENDANTS(
[Date].[Calendar].[Calendar Year].[CY 2003]:[CY 2004],
[Date].[Calendar].[Month],
SELF_AND_BEFORE)} ON AXIS(0),
CROSSJOIN(
{[Product].[Product Categories].[Category].[Bikes].CHILDREN },
{[Measures].[Customer Count],
[Measures].[3-Mo Rolling Avg Customer Count]}
) ON AXIS(1)
FROM [Adventure Works]
The IsAncestor() Function:
The IsAncestor() function “returns whether a specified member is an ancestor of another specified member.” A Boolean value of “True” is returned if the member expression to which the function is applied. IsAncestor() is often employed in conjunction with the IIF function to conditionally drive the return of data, such as a member or members, or values, based upon the relationship between members as ancestor / descendant.
WITH MEMBER
[Measures].[InternetSelectCount]
AS
'IIF(
ISANCESTOR([Date].[Calendar].[Calendar Year].[CY 2003],
[Date].[Calendar].CURRENTMEMBER),
[Measures].[Internet Order Count], NULL)'
SELECT
{[Measures].[InternetSelectCount]}ON AXIS(0),
NON EMPTY{[Date].[Calendar].MEMBERS} ON AXIS(1)
FROM [Adventure Works]
WITH MEMBER
[Measures].[Tentative Reseller Share]
AS
'SUM(DESCENDANTS([Product].[Product Categories].CURRENTMEMBER,,LEAVES),
IIF(
ISANCESTOR([Product].[Product Categories].[Bikes],
[Product].[Product Categories].CURRENTMEMBER),
0.075 * [Measures].[Reseller Sales Amount],
0.0575 * [Measures].[Reseller Sales Amount]
))',
FORMAT_STRING='Currency'
SELECT
{[Measures].[Reseller Sales Amount], [Measures].[Tentative Reseller Share]}
ON AXIS(0),
NON EMPTY {[Product].[Product Categories].MEMBERS} ON AXIS(1)
FROM [Adventure Works]
WHERE ([Date].[Calendar].[Calendar Year].[CY 2003])
The HAVING clause:
The HAVING clause is particularly useful in cases where we need to insert a means of filtering within our query so as to take advantage of the fact that NON EMPTY logic has already been applied.
We typically employ the HAVING clause at the axis level. The HAVING expression is applied against each of the axis’ tuples – and therefore operates within the scope of the axis.
SELECT CROSSJOIN(
{[Date].[Calendar].[Calendar Quarter].[Q1 CY 2004].CHILDREN},
{[Measures].[Reseller Sales Amount] } )
ON AXIS(0),
NON EMPTY
{[Sales Territory].[Sales Territory Group].CHILDREN}
HAVING [Measures].[Reseller Sales Amount] > 0
ON AXIS(1)
FROM [Adventure Works]
WHERE ([Reseller].[Reseller Type].[Business Type].[Value Added Reseller])
SELECT
{[Measures].[Reseller Order Quantity], [Measures].[Reseller Sales Amount] }
ON AXIS(0),
NON EMPTY
{[Reseller].[Reseller Type].[Business Type].[Value Added Reseller].CHILDREN}
HAVING [Measures].[Reseller Order Quantity] > 10
ON AXIS(1)
FROM [Adventure Works]
WHERE ([Product].[Product Categories].[Category].[Components],
[Date].[Calendar].[Calendar Year].[CY 2003] )
The Max() and Min():
WITH MEMBER
[Sales Territory].[Sales Territory].[Top Sales]
AS
'MAX( { [Sales Territory].[Sales Territory].[Country].MEMBERS},
[Measures].[Internet Sales Amount])'
SELECT
{ [Sales Territory].[Sales Territory].[Country].MEMBERS,
[Sales Territory].[Top Sales] } ON AXIS(0),
NON EMPTY {[Product].[Product Categories].[Category].MEMBERS} ON AXIS(1)
FROM [Adventure Works]
WHERE ([Measures].[Internet Sales Amount])
-------------------------------Additional Exercise for learning more or Revision -----------------------------------
SELECT [Measures].[Order Quantity] ON COLUMNS
FROM [Adventure Works DW]
--------------------------------------------------
--CAN BE USE 0 AND 1
SELECT [Measures].[Order Quantity] ON 0
FROM [Adventure Works DW]
--------------------------------------------------
--ADDITION OF ROWS
SELECT [Measures].[Order Quantity] ON COLUMNS ,
[Dim Product].[Model Name] ON ROWS
FROM [Adventure Works DW]
--------------------------------------------------
--CAN BE USE 0 AND 1
-- WITH ALL (AGGREGATED)
SELECT [Measures].[Order Quantity] ON 0 ,
[Dim Product].[Model Name] ON 1
FROM [Adventure Works DW]
--------------------------------------------------
--WITH ATTRIBUTE
SELECT [Measures].[Order Quantity] ON COLUMNS ,
[Dim Product].[Model Name].[Model Name] ON ROWS
FROM [Adventure Works DW]
--------------------------------------------------
--REMOVE NULL
SELECT [Measures].[Order Quantity] ON COLUMNS ,
NON EMPTY [Dim Product].[Model Name].[Model Name] ON ROWS
FROM [Adventure Works DW]
--------------------------------------------------
--USE MORE THAN 1 MEASURES THEN USE {} AND SEPARATE WITH COMMAS (,)
SELECT {[Measures].[Order Quantity],[Measures].[Sales Amount]} ON COLUMNS ,
NON EMPTY [Dim Product].[Model Name].[Model Name] ON ROWS
FROM [Adventure Works DW]
--------------------------------------------------
--LET US CHANGE THE ORDER (APPEARNACE)
SELECT {[Measures].[Order Quantity],[Measures].[Sales Amount]} ON ROWS ,
NON EMPTY [Dim Product].[Model Name].[Model Name] ON COLUMNS
FROM [Adventure Works DW]
--OR
SELECT {[Measures].[Order Quantity],[Measures].[Sales Amount]} ON 1 ,
NON EMPTY [Dim Product].[Model Name].[Model Name] ON 0
FROM [Adventure Works DW]
--------------------------------------------------
--WITH TWO ATTRIBUTES SO USE , AND ()
SELECT {[Measures].[Order Quantity],[Measures].[Sales Amount]} ON COLUMNS ,
NON EMPTY ([Dim Product].[Model Name].[Model Name] , [Dim Product].[Size].[Size]) ON ROWS
FROM [Adventure Works DW]
--------------------------------------------------
-- MEMBERS /ALLMEMBERS
SELECT {[Measures].[Order Quantity],[Measures].[Sales Amount]} ON COLUMNS ,
NON EMPTY [Dim Product].[Model Name].ALLMEMBERS ON ROWS
FROM [Adventure Works DW]
--OR
SELECT {[Measures].[Order Quantity],[Measures].[Sales Amount]} ON COLUMNS ,
NON EMPTY [Dim Product].[Model Name].MEMBERS ON ROWS
FROM [Adventure Works DW]
SELECT {[Measures].[Order Quantity],[Measures].[Sales Amount]} ON COLUMNS ,
NON EMPTY [Dim Product].[Model Name].[Model Name] ON ROWS
FROM [Adventure Works DW]
SELECT {[Measures].[Order Quantity],[Measures].[Sales Amount]} ON COLUMNS ,
NON EMPTY [Dim Product].[Model Name].CHILDREN ON ROWS
FROM [Adventure Works DW]
--------------------------------------------------
--WITH HIERARCHY
SELECT {[Measures].[Order Quantity],[Measures].[Sales Amount]} ON COLUMNS ,
NON EMPTY [Dim Product].[Hierarchy].MEMBERS ON ROWS
FROM [Adventure Works DW]
--OR
SELECT {[Measures].[Order Quantity],[Measures].[Sales Amount]} ON COLUMNS ,
NON EMPTY [Dim Product].[Hierarchy].ALLMEMBERS ON ROWS
FROM [Adventure Works DW]
SELECT {[Measures].[Order Quantity],[Measures].[Sales Amount]} ON COLUMNS ,
NON EMPTY [Dim Product].[Hierarchy].CHILDREN ON ROWS
FROM [Adventure Works DW]
SELECT {[Measures].[Order Quantity],[Measures].[Sales Amount]} ON COLUMNS ,
NON EMPTY [Dim Product].[Hierarchy].[Class] ON ROWS
FROM [Adventure Works DW]
SELECT {[Measures].[Order Quantity],[Measures].[Sales Amount]} ON COLUMNS ,
NON EMPTY [Dim Product].[Hierarchy].[English Product Category Name] ON ROWS
FROM [Adventure Works DW]
--USE OF COMMA IN HIERACHY AND USE {}
SELECT {[Measures].[Order Quantity],[Measures].[Sales Amount]} ON COLUMNS ,
NON EMPTY {[Dim Product].[Hierarchy].[English Product Subcategory Name],[Dim Product].[Hierarchy].[English Product Category Name]} ON ROWS
FROM [Adventure Works DW]
SELECT {[Measures].[Order Quantity],[Measures].[Sales Amount]} ON COLUMNS ,
NON EMPTY {[Dim Product].[Hierarchy].[English Product Category Name],[Dim Product].[Hierarchy].[English Product Subcategory Name]} ON ROWS
FROM [Adventure Works DW]
--------------------------------------
SELECT {[Measures].[Order Quantity],[Measures].[Sales Amount]} ON COLUMNS ,
NON EMPTY ([Dim Customer].[Gender].[Gender],[Dim Account].[Account Type].[Account Type],[Dim Product].[Model Name].[Model Name]) ON ROWS
FROM [Adventure Works DW]
--OR
SELECT {[Measures].[Order Quantity],[Measures].[Sales Amount]} ON COLUMNS ,
NON EMPTY ([Dim Customer].[Gender].[Gender].MEMBERS ,[Dim Account].[Account Type].[Account Type].MEMBERS , [Dim Product].[Model Name].[Model Name].MEMBERS) ON ROWS
FROM [Adventure Works DW]
SELECT {[Measures].[Order Quantity],[Measures].[Sales Amount]} ON COLUMNS ,
NON EMPTY ([Dim Customer].[Gender].[Gender].MEMBERS , [Dim Product].[Model Name].[Model Name].MEMBERS) ON ROWS
FROM [Adventure Works DW]
SELECT {[Measures].[Order Quantity],[Measures].[Sales Amount]} ON COLUMNS ,
NON EMPTY ([Dim Customer].[Gender].[Gender].MEMBERS ,[Dim Product].[Size].[Size].MEMBERS) ON ROWS
FROM [Adventure Works DW]
--------------------------------------------------
--ORDER BY DESC/ASC/BDESC
SELECT {[Measures].[Order Quantity],[Measures].[Sales Amount]} ON COLUMNS ,
NON EMPTY
ORDER( ([Dim Customer].[Gender].[Gender].MEMBERS ,[Dim Product].[Size].[Size].MEMBERS),[Measures].[Order Quantity],DESC) ON ROWS
FROM [Adventure Works DW]
SELECT {[Measures].[Order Quantity],[Measures].[Sales Amount]} ON COLUMNS ,
NON EMPTY
ORDER( ([Dim Customer].[Gender].[Gender].MEMBERS ,[Dim Product].[Size].[Size].MEMBERS),[Measures].[Order Quantity],ASC) ON ROWS
FROM [Adventure Works DW]
--BDESC (BREAK THE HIERARCHY)
SELECT {[Measures].[Order Quantity],[Measures].[Sales Amount]} ON COLUMNS ,
NON EMPTY
ORDER( ([Dim Customer].[Gender].[Gender].MEMBERS ,[Dim Product].[Size].[Size].MEMBERS),[Measures].[Order Quantity],BDESC) ON ROWS
FROM [Adventure Works DW]
--------------------------------------------------
-- LITTLE ADVANCE
-- BEST PRACTIVE
-- IF THE MEASURE DOES NOT EXISTS (DYNAMIC/ON RUNNING TIME)
WITH MEMBER [MEASURES].[AVG COST] AS
[Measures].[Sales Amount]/[Measures].[Order Quantity],
FORMAT_STRING = '$0.00'
SELECT {[Measures].[Order Quantity],[Measures].[Sales Amount], [MEASURES].[AVG COST]} ON COLUMNS ,
NON EMPTY
ORDER( ([Dim Customer].[Gender].[Gender].MEMBERS ,[Dim Product].[Size].[Size].MEMBERS),[Measures].[Order Quantity],BDESC) ON ROWS
FROM [Adventure Works DW]
WITH MEMBER [MEASURES].[AVG COST] AS
[Measures].[Sales Amount]/[Measures].[Order Quantity],
FORMAT_STRING = '$0.00'
MEMBER [MEASURES].[PROPOSED COST] AS
[Measures].[Sales Amount]+([Measures].[Sales Amount]*10/100),
FORMAT_STRING = '$0.00'
SELECT {[Measures].[Order Quantity],[Measures].[Sales Amount], [MEASURES].[AVG COST],[MEASURES].[PROPOSED COST]} ON COLUMNS ,
NON EMPTY
ORDER( ([Dim Customer].[Gender].[Gender].MEMBERS ,[Dim Product].[Size].[Size].MEMBERS),[Measures].[Order Quantity],BDESC) ON ROWS
FROM [Adventure Works DW]
--CREATE MEMBER FROM RECENTLY CREATED MEASURE
WITH MEMBER [MEASURES].[AVG COST] AS
[Measures].[Sales Amount]/[Measures].[Order Quantity],
FORMAT_STRING = '$0.00'
MEMBER [MEASURES].[PROPOSED COST] AS
[Measures].[Sales Amount]+([Measures].[Sales Amount]*10/100),
FORMAT_STRING = '$0.00'
MEMBER[MEASURES].[PROPOSED AVG COST] AS
[MEASURES].[AVG COST]+[MEASURES].[AVG COST]*10/100,
FORMAT_STRING = '$0.00'
SELECT {[Measures].[Order Quantity],[Measures].[Sales Amount], [MEASURES].[AVG COST],[MEASURES].[PROPOSED COST],[MEASURES].[PROPOSED AVG COST]} ON COLUMNS ,
NON EMPTY
ORDER( ([Dim Customer].[Gender].[Gender].MEMBERS ,[Dim Product].[Size].[Size].MEMBERS),[Measures].[Order Quantity],BDESC) ON ROWS
FROM [Adventure Works DW]
--------------------------------------------------
--NAMED CALCULATIONS/CALCULATED SETS
WITH MEMBER [MEASURES].[AVG COST] AS
[Measures].[Sales Amount]/[Measures].[Order Quantity],
FORMAT_STRING = '$0.00'
MEMBER [MEASURES].[PROPOSED COST] AS
[Measures].[Sales Amount]+([Measures].[Sales Amount]*10/100),
FORMAT_STRING = '$0.00'
MEMBER[MEASURES].[PROPOSED AVG COST] AS
[MEASURES].[AVG COST]+[MEASURES].[AVG COST]*10/100,
FORMAT_STRING = '$0.00'
SET [BY GENDER PRDSIZE] AS
ORDER( ([Dim Customer].[Gender].[Gender].MEMBERS ,
[Dim Product].[Size].[Size].MEMBERS),
[Measures].[Order Quantity],BDESC)
SELECT {[Measures].[Order Quantity],[Measures].[Sales Amount],
[MEASURES].[AVG COST],[MEASURES].[PROPOSED COST],
[MEASURES].[PROPOSED AVG COST]} ON COLUMNS ,
NON EMPTY
[BY GENDER PRDSIZE] ON ROWS
FROM [Adventure Works DW]
--MORE COMPLEX
WITH MEMBER [MEASURES].[AVG COST] AS
[Measures].[Sales Amount]/[Measures].[Order Quantity],
FORMAT_STRING = '$0.00'
MEMBER [MEASURES].[PROPOSED COST] AS
[Measures].[Sales Amount]+([Measures].[Sales Amount]*10/100),
FORMAT_STRING = '$0.00'
MEMBER[MEASURES].[PROPOSED AVG COST] AS
[MEASURES].[AVG COST]+[MEASURES].[AVG COST]*10/100,
FORMAT_STRING = '$0.00'
SET [BY GENDER PRDSIZE] AS
FILTER(
ORDER( ([Dim Customer].[Gender].[Gender].MEMBERS ,
[Dim Product].[Size].[Size].MEMBERS),
[Measures].[Order Quantity],BDESC),
[MEASURES].[PROPOSED AVG COST]<50 non_empty_behavior =" [Measures].[Freight]" format_string="Percent"> ([Measures].[Reseller Sales Amount])
) ON ROWS
FROM [Step-by-Step]
SELECT
{
([Measures].[Reseller Sales Amount]),
([Measures].[Internet Sales Amount])
} ON COLUMNS,
Filter(
{[Product].[Product].[Product].Members},
([Measures].[Internet Sales Amount]) > ([Measures].[Reseller Sales Amount])
AND
(
[Product].[Category].CurrentMember Is [Product].[Category].[Clothing] OR
[Product].[Category].CurrentMember Is [Product].[Category].[Accessories]
)
) ON ROWS
FROM [Step-by-Step]
SELECT
{
([Measures].[Reseller Sales Amount]),
([Measures].[Internet Sales Amount])
} ON COLUMNS,
Filter(
{[Product].[Product].[Product].Members},
([Measures].[Internet Sales Amount]) >
([Measures].[Reseller Sales Amount]) AND
NOT (
[Product].[Category].CurrentMember Is [Product].[Category].[Clothing] OR
[Product].[Category].CurrentMember Is [Product].[Category].[Accessories]
)
) ON ROWS
FROM [Step-by-Step]
SELECT
{([Measures].[Reseller Sales Amount])} ON COLUMNS,
Union(
TopCount(
{[Product].[Product].[Product].Members},
10,
([Measures].[Reseller Sales Amount], [Date].[Calendar Year].[CY 2003])
),
TopCount(
{[Product].[Product].[Product].Members},
10,
([Measures].[Reseller Sales Amount], [Date].[Calendar Year].[CY 2004])
)
) ON ROWS
FROM [Step-by-Step]
SELECT
{([Measures].[Reseller Sales Amount])} ON COLUMNS,
Except(
TopCount(
{[Product].[Product].[Product].Members},
10,
([Measures].[Reseller Sales Amount], [Date].[Calendar Year].[CY 2004])
),
TopCount(
{[Product].[Product].[Product].Members},
10,
([Measures].[Reseller Sales Amount], [Date].[Calendar Year].[CY 2003])
)
) ON ROWS
FROM [Step-by-Step]
SELECT
{([Measures].[Reseller Sales Amount])} ON COLUMNS,
Intersect(
TopCount(
{[Product].[Product].[Product].Members},
10,
([Measures].[Reseller Sales Amount], [Date].[Calendar Year].[CY 2004])
),
TopCount(
{[Product].[Product].[Product].Members},
10,
([Measures].[Reseller Sales Amount], [Date].[Calendar Year].[CY 2003])
)
) ON ROWS
FROM [Step-by-Step]
WITH
MEMBER [Measures].[Sibling Rank] AS
Rank(
[Product].[Product Categories].CurrentMember,
[Product].[Product Categories].CurrentMember.Siblings,
([Measures].[Reseller Sales Amount])
)
SELECT
{
([Measures].[Reseller Sales Amount]),
([Measures].[Sibling Rank])
} ON COLUMNS,
{[Product].[Product Categories].Members} ON ROWS
FROM [Step-by-Step]
SELECT
{([Measures].[Reseller Sales Amount])} ON COLUMNS,
Descendants(
{[Product].[Product Categories].[Category].[Bikes]},
[Product].[Product Categories].[Subcategory]
) ON ROWS
FROM [Step-by-Step]
SELECT
{([Measures].[Reseller Sales Amount])} ON COLUMNS,
Descendants(
{[Product].[Product Categories].[Category].[Bikes]},
[Product].[Product Categories].[Subcategory],
AFTER
) ON ROWS
FROM [Step-by-Step]
SELECT
{([Measures].[Reseller Sales Amount])} ON COLUMNS,
Descendants(
{[Product].[Product Categories].[Category].[Bikes]},
[Product].[Product Categories].[Subcategory],
SELF_AND_AFTER
) ON ROWS
FROM [Step-by-Step]
WITH
MEMBER [Measures].[Number of Children] AS
IIF(
IsLeaf([Product].[Product Categories].CurrentMember),
"N/A",
COUNT(
[Product].[Product Categories].CurrentMember.Children
)
)
SELECT
{[Measures].[Number of Children]} ON COLUMNS,
{[Product].[Product Categories].Members} ON ROWS
FROM [Step-by-Step]
SELECT
{([Measures].[Reseller Sales Amount])} ON COLUMNS,
{
LastPeriods(
3,
[Date].[Calendar].[Month].[January 2002]
)
} ON ROWS
FROM [Step-by-Step]
SELECT
{([Measures].[Reseller Sales Amount])} ON COLUMNS,
{
Descendants(
[Date].[Calendar].[Calendar Year].[CY 2003],
[Date].[Calendar].[Month],
SELF
)
} ON ROWS
FROM [Step-by-Step]
WITH
MEMBER [Measures].[x] AS
Cousin(
[Date].[Calendar].CurrentMember,
Ancestor(
[Date].[Calendar].CurrentMember,
[Date].[Calendar].[Calendar Year]
).Lag(1)
).Name
SELECT
{
([Measures].[Reseller Sales Amount]),
([Measures].[x])
} ON COLUMNS,
{
Descendants(
[Date].[Calendar].[Calendar Year].[CY 2003],
[Date].[Calendar].[Month],
SELF
)
} ON ROWS
FROM [Step-by-Step]
WITH
MEMBER [Measures].[x] AS
ParallelPeriod(
[Date].[Calendar].[Calendar Year],
1,
[Date].[Calendar].CurrentMember
).Name
SELECT
{
([Measures].[Reseller Sales Amount]),
([Measures].[x])
} ON COLUMNS,
{
Descendants(
[Date].[Calendar].[Calendar Year].[CY 2002],
[Date].[Calendar].[Month],
SELF
)
} ON ROWS
FROM [Step-by-Step]
WITH MEMBER [Measures].[ParameterCaption] AS '[Dim Product].[Model Name].CURRENTMEMBER.MEMBER_CAPTION'
MEMBER [Measures].[ParameterValue] AS '[Dim Product].[Model Name].CURRENTMEMBER.UNIQUENAME'
MEMBER [Measures].[ParameterLevel] AS '[Dim Product].[Model Name].CURRENTMEMBER.LEVEL.ORDINAL'
SELECT {[Measures].[ParameterCaption], [Measures].[ParameterValue], [Measures].[ParameterLevel]} ON COLUMNS ,
[Dim Product].[Model Name].[Model Name]ON ROWS
FROM [Adventure Works DW]
WITH MEMBER [Measures].[ParameterCaption] AS '[Dim Product].[Model Name].CURRENTMEMBER.MEMBER_CAPTION'
MEMBER [Measures].[ParameterValue] AS '[Dim Product].[Model Name].CURRENTMEMBER.UNIQUENAME'
MEMBER [Measures].[ParameterLevel] AS '[Dim Product].[Model Name].CURRENTMEMBER.LEVEL.ORDINAL'
SELECT {[Measures].[ParameterCaption], [Measures].[ParameterValue], [Measures].[ParameterLevel]} ON COLUMNS ,
[Dim Product].[Hierarchy].[English Product Category Name] ON ROWS
FROM [Adventure Works DW]
"When comparing members, do this:
IIF([Date].[Fiscal]. CurrentMember IS [Date].[Calendar].[Calendar Year].[CY 2004], ...)
"... not this:
IIF([Date].[Fiscal]. CurrentMember.Name = "CY 2004", ...)
WITH MEMBER
[Measures].[3-Mo Rolling Avg Customer Count]
AS
'IIF(
[Date].[Calendar].CURRENTMEMBER.LEVEL IS [Date].[Calendar].[Month],
AVG(LASTPERIODS(3, [Date].[Calendar].CURRENTMEMBER),
[Measures].[Customer Count]),
NULL
)', FORMAT_STRING = "#,###.00"
SELECT
DESCENDANTS(
[Date].[Calendar].[Calendar Year].[CY 2004],[Date].[Calendar].[Month],
SELF_AND_BEFORE) ON AXIS(0),
CROSSJOIN(
{[Product].[Product Categories].[Category].[Bikes].CHILDREN },
{[Measures].[Customer Count],
[Measures].[3-Mo Rolling Avg Customer Count]}
) ON AXIS(1)
FROM [Adventure Works]
SELECT
{[Measures].[Internet Sales Amount]} ON AXIS(0),
{FILTER ( [Customer].[Customer Geography].MEMBERS,
[Customer].[Customer Geography].CURRENTMEMBER.PARENT
IS [Customer].[Customer Geography].[State-Province].[Georgia]
)} ON AXIS(1)
FROM [Adventure Works]
WHERE ([Date].[Fiscal].[Fiscal Year].[FY 2004])
The .Ordinal Function:
The general purpose of the .Ordinal function is to return the ordinal value of a specified dimensional level. The .Ordinal function is particularly useful within scenarios where we employ it with the IIF() function to determine the position of a level within a dimensional hierarchy for various reasons.
WITH MEMBER [Measures].[SalesTerrName]
AS
'[Sales Territory].[Sales Territory].CURRENTMEMBER.NAME'
MEMBER
[Measures].[SalesTerrLevNo]
AS
'[Sales Territory].[Sales Territory].CURRENTMEMBER.LEVEL.ORDINAL'
SELECT
{[Measures].[SalesTerrName], [Measures].[SalesTerrLevNo]} ON AXIS(0),
{[Sales Territory].[Sales Territory].MEMBERS} ON AXIS(1)
FROM [Adventure Works]
WITH MEMBER [Measures].[LevelNo]
AS
'[Geography].[Geography].CURRENTMEMBER.LEVEL.ORDINAL'
MEMBER [Measures].[DisplayName]
AS
'[Geography].[Geography].CURRENTMEMBER.MEMBER_CAPTION'
MEMBER [Measures].[UniqueName]
AS
'[Geography].[Geography].CURRENTMEMBER.UNIQUENAME'
SELECT
{[Measures].[LevelNo], [Measures].[DisplayName],
[Measures].[UniqueName]} ON AXIS(0) ,
{[Geography].[Geography].MEMBERS} ON AXIS(1)
FROM [Adventure Works]
The IsLeaf() operator:
The IsLeaf() operator, like other logical operators, evaluates values and returns a Boolean value. The utility of IsLeaf() becomes clear when we realize its value in helping us to determine the position of a member within a dimensional hierarchy. IsLeaf() more specifically allows us to test if a member is at leaf level, or at the “bottom” level of the dimension to which it belongs.
WITH MEMBER
[Measures].[BegCount]
AS
IIF(ISLEAF([Date].[Calendar].CURRENTMEMBER),
[Measures].[Order Count],
([Date].[Calendar].CURRENTMEMBER.FIRSTCHILD, [Measures].[BegCount]))
SELECT
{[Measures].[BegCount]}ON COLUMNS,
{[Date].[Calendar].MEMBERS} ON ROWS
FROM
[Adventure Works]
The IsSibling() Function:
The general purpose of IsSibling(), a logical function, is to return whether or not a member that we specify is the sibling of another member we specify. (By “sibling,” of course, we mean that the two members share the same parent.)
WITH MEMBER
[Measures].[SelectCount]
AS
IIF( ISSIBLING([Date].[Calendar].CURRENTMEMBER,
[Date].[Calendar].[Date].[January 1, 2004]),
[Measures].[Order Count], NULL)
SELECT
{[Measures].[SelectCount]}ON AXIS(0),
NON EMPTY{[Date].[Calendar].MEMBERS} ON AXIS(1)
FROM [Adventure Works]
WITH MEMBER
[Measures].[3-Mo Rolling Avg Customer Count]
AS
'IIF(
ISSIBLING([Date].[Calendar].CURRENTMEMBER,
[Date].[Calendar].[Month].[January 2004]),
AVG(LASTPERIODS(3, [Date].[Calendar].CURRENTMEMBER),
[Measures].[Customer Count]),NULL
)', FORMAT_STRING = "#,###.00"
SELECT
{DESCENDANTS(
[Date].[Calendar].[Calendar Year].[CY 2003]:[CY 2004],
[Date].[Calendar].[Month],
SELF_AND_BEFORE)} ON AXIS(0),
CROSSJOIN(
{[Product].[Product Categories].[Category].[Bikes].CHILDREN },
{[Measures].[Customer Count],
[Measures].[3-Mo Rolling Avg Customer Count]}
) ON AXIS(1)
FROM [Adventure Works]
The IsAncestor() Function:
The IsAncestor() function “returns whether a specified member is an ancestor of another specified member.” A Boolean value of “True” is returned if the member expression to which the function is applied. IsAncestor() is often employed in conjunction with the IIF function to conditionally drive the return of data, such as a member or members, or values, based upon the relationship between members as ancestor / descendant.
WITH MEMBER
[Measures].[InternetSelectCount]
AS
'IIF(
ISANCESTOR([Date].[Calendar].[Calendar Year].[CY 2003],
[Date].[Calendar].CURRENTMEMBER),
[Measures].[Internet Order Count], NULL)'
SELECT
{[Measures].[InternetSelectCount]}ON AXIS(0),
NON EMPTY{[Date].[Calendar].MEMBERS} ON AXIS(1)
FROM [Adventure Works]
WITH MEMBER
[Measures].[Tentative Reseller Share]
AS
'SUM(DESCENDANTS([Product].[Product Categories].CURRENTMEMBER,,LEAVES),
IIF(
ISANCESTOR([Product].[Product Categories].[Bikes],
[Product].[Product Categories].CURRENTMEMBER),
0.075 * [Measures].[Reseller Sales Amount],
0.0575 * [Measures].[Reseller Sales Amount]
))',
FORMAT_STRING='Currency'
SELECT
{[Measures].[Reseller Sales Amount], [Measures].[Tentative Reseller Share]}
ON AXIS(0),
NON EMPTY {[Product].[Product Categories].MEMBERS} ON AXIS(1)
FROM [Adventure Works]
WHERE ([Date].[Calendar].[Calendar Year].[CY 2003])
The HAVING clause:
The HAVING clause is particularly useful in cases where we need to insert a means of filtering within our query so as to take advantage of the fact that NON EMPTY logic has already been applied.
We typically employ the HAVING clause at the axis level. The HAVING expression is applied against each of the axis’ tuples – and therefore operates within the scope of the axis.
SELECT CROSSJOIN(
{[Date].[Calendar].[Calendar Quarter].[Q1 CY 2004].CHILDREN},
{[Measures].[Reseller Sales Amount] } )
ON AXIS(0),
NON EMPTY
{[Sales Territory].[Sales Territory Group].CHILDREN}
HAVING [Measures].[Reseller Sales Amount] > 0
ON AXIS(1)
FROM [Adventure Works]
WHERE ([Reseller].[Reseller Type].[Business Type].[Value Added Reseller])
SELECT
{[Measures].[Reseller Order Quantity], [Measures].[Reseller Sales Amount] }
ON AXIS(0),
NON EMPTY
{[Reseller].[Reseller Type].[Business Type].[Value Added Reseller].CHILDREN}
HAVING [Measures].[Reseller Order Quantity] > 10
ON AXIS(1)
FROM [Adventure Works]
WHERE ([Product].[Product Categories].[Category].[Components],
[Date].[Calendar].[Calendar Year].[CY 2003] )
The Max() and Min():
WITH MEMBER
[Sales Territory].[Sales Territory].[Top Sales]
AS
'MAX( { [Sales Territory].[Sales Territory].[Country].MEMBERS},
[Measures].[Internet Sales Amount])'
SELECT
{ [Sales Territory].[Sales Territory].[Country].MEMBERS,
[Sales Territory].[Top Sales] } ON AXIS(0),
NON EMPTY {[Product].[Product Categories].[Category].MEMBERS} ON AXIS(1)
FROM [Adventure Works]
WHERE ([Measures].[Internet Sales Amount])
-------------------------------Additional Exercise for learning more or Revision -----------------------------------
SELECT [Measures].[Order Quantity] ON COLUMNS
FROM [Adventure Works DW]
--------------------------------------------------
--CAN BE USE 0 AND 1
SELECT [Measures].[Order Quantity] ON 0
FROM [Adventure Works DW]
--------------------------------------------------
--ADDITION OF ROWS
SELECT [Measures].[Order Quantity] ON COLUMNS ,
[Dim Product].[Model Name] ON ROWS
FROM [Adventure Works DW]
--------------------------------------------------
--CAN BE USE 0 AND 1
-- WITH ALL (AGGREGATED)
SELECT [Measures].[Order Quantity] ON 0 ,
[Dim Product].[Model Name] ON 1
FROM [Adventure Works DW]
--------------------------------------------------
--WITH ATTRIBUTE
SELECT [Measures].[Order Quantity] ON COLUMNS ,
[Dim Product].[Model Name].[Model Name] ON ROWS
FROM [Adventure Works DW]
--------------------------------------------------
--REMOVE NULL
SELECT [Measures].[Order Quantity] ON COLUMNS ,
NON EMPTY [Dim Product].[Model Name].[Model Name] ON ROWS
FROM [Adventure Works DW]
--------------------------------------------------
--USE MORE THAN 1 MEASURES THEN USE {} AND SEPARATE WITH COMMAS (,)
SELECT {[Measures].[Order Quantity],[Measures].[Sales Amount]} ON COLUMNS ,
NON EMPTY [Dim Product].[Model Name].[Model Name] ON ROWS
FROM [Adventure Works DW]
--------------------------------------------------
--LET US CHANGE THE ORDER (APPEARNACE)
SELECT {[Measures].[Order Quantity],[Measures].[Sales Amount]} ON ROWS ,
NON EMPTY [Dim Product].[Model Name].[Model Name] ON COLUMNS
FROM [Adventure Works DW]
--OR
SELECT {[Measures].[Order Quantity],[Measures].[Sales Amount]} ON 1 ,
NON EMPTY [Dim Product].[Model Name].[Model Name] ON 0
FROM [Adventure Works DW]
--------------------------------------------------
--WITH TWO ATTRIBUTES SO USE , AND ()
SELECT {[Measures].[Order Quantity],[Measures].[Sales Amount]} ON COLUMNS ,
NON EMPTY ([Dim Product].[Model Name].[Model Name] , [Dim Product].[Size].[Size]) ON ROWS
FROM [Adventure Works DW]
--------------------------------------------------
-- MEMBERS /ALLMEMBERS
SELECT {[Measures].[Order Quantity],[Measures].[Sales Amount]} ON COLUMNS ,
NON EMPTY [Dim Product].[Model Name].ALLMEMBERS ON ROWS
FROM [Adventure Works DW]
--OR
SELECT {[Measures].[Order Quantity],[Measures].[Sales Amount]} ON COLUMNS ,
NON EMPTY [Dim Product].[Model Name].MEMBERS ON ROWS
FROM [Adventure Works DW]
SELECT {[Measures].[Order Quantity],[Measures].[Sales Amount]} ON COLUMNS ,
NON EMPTY [Dim Product].[Model Name].[Model Name] ON ROWS
FROM [Adventure Works DW]
SELECT {[Measures].[Order Quantity],[Measures].[Sales Amount]} ON COLUMNS ,
NON EMPTY [Dim Product].[Model Name].CHILDREN ON ROWS
FROM [Adventure Works DW]
--------------------------------------------------
--WITH HIERARCHY
SELECT {[Measures].[Order Quantity],[Measures].[Sales Amount]} ON COLUMNS ,
NON EMPTY [Dim Product].[Hierarchy].MEMBERS ON ROWS
FROM [Adventure Works DW]
--OR
SELECT {[Measures].[Order Quantity],[Measures].[Sales Amount]} ON COLUMNS ,
NON EMPTY [Dim Product].[Hierarchy].ALLMEMBERS ON ROWS
FROM [Adventure Works DW]
SELECT {[Measures].[Order Quantity],[Measures].[Sales Amount]} ON COLUMNS ,
NON EMPTY [Dim Product].[Hierarchy].CHILDREN ON ROWS
FROM [Adventure Works DW]
SELECT {[Measures].[Order Quantity],[Measures].[Sales Amount]} ON COLUMNS ,
NON EMPTY [Dim Product].[Hierarchy].[Class] ON ROWS
FROM [Adventure Works DW]
SELECT {[Measures].[Order Quantity],[Measures].[Sales Amount]} ON COLUMNS ,
NON EMPTY [Dim Product].[Hierarchy].[English Product Category Name] ON ROWS
FROM [Adventure Works DW]
--USE OF COMMA IN HIERACHY AND USE {}
SELECT {[Measures].[Order Quantity],[Measures].[Sales Amount]} ON COLUMNS ,
NON EMPTY {[Dim Product].[Hierarchy].[English Product Subcategory Name],[Dim Product].[Hierarchy].[English Product Category Name]} ON ROWS
FROM [Adventure Works DW]
SELECT {[Measures].[Order Quantity],[Measures].[Sales Amount]} ON COLUMNS ,
NON EMPTY {[Dim Product].[Hierarchy].[English Product Category Name],[Dim Product].[Hierarchy].[English Product Subcategory Name]} ON ROWS
FROM [Adventure Works DW]
--------------------------------------
SELECT {[Measures].[Order Quantity],[Measures].[Sales Amount]} ON COLUMNS ,
NON EMPTY ([Dim Customer].[Gender].[Gender],[Dim Account].[Account Type].[Account Type],[Dim Product].[Model Name].[Model Name]) ON ROWS
FROM [Adventure Works DW]
--OR
SELECT {[Measures].[Order Quantity],[Measures].[Sales Amount]} ON COLUMNS ,
NON EMPTY ([Dim Customer].[Gender].[Gender].MEMBERS ,[Dim Account].[Account Type].[Account Type].MEMBERS , [Dim Product].[Model Name].[Model Name].MEMBERS) ON ROWS
FROM [Adventure Works DW]
SELECT {[Measures].[Order Quantity],[Measures].[Sales Amount]} ON COLUMNS ,
NON EMPTY ([Dim Customer].[Gender].[Gender].MEMBERS , [Dim Product].[Model Name].[Model Name].MEMBERS) ON ROWS
FROM [Adventure Works DW]
SELECT {[Measures].[Order Quantity],[Measures].[Sales Amount]} ON COLUMNS ,
NON EMPTY ([Dim Customer].[Gender].[Gender].MEMBERS ,[Dim Product].[Size].[Size].MEMBERS) ON ROWS
FROM [Adventure Works DW]
--------------------------------------------------
--ORDER BY DESC/ASC/BDESC
SELECT {[Measures].[Order Quantity],[Measures].[Sales Amount]} ON COLUMNS ,
NON EMPTY
ORDER( ([Dim Customer].[Gender].[Gender].MEMBERS ,[Dim Product].[Size].[Size].MEMBERS),[Measures].[Order Quantity],DESC) ON ROWS
FROM [Adventure Works DW]
SELECT {[Measures].[Order Quantity],[Measures].[Sales Amount]} ON COLUMNS ,
NON EMPTY
ORDER( ([Dim Customer].[Gender].[Gender].MEMBERS ,[Dim Product].[Size].[Size].MEMBERS),[Measures].[Order Quantity],ASC) ON ROWS
FROM [Adventure Works DW]
--BDESC (BREAK THE HIERARCHY)
SELECT {[Measures].[Order Quantity],[Measures].[Sales Amount]} ON COLUMNS ,
NON EMPTY
ORDER( ([Dim Customer].[Gender].[Gender].MEMBERS ,[Dim Product].[Size].[Size].MEMBERS),[Measures].[Order Quantity],BDESC) ON ROWS
FROM [Adventure Works DW]
--------------------------------------------------
-- LITTLE ADVANCE
-- BEST PRACTIVE
-- IF THE MEASURE DOES NOT EXISTS (DYNAMIC/ON RUNNING TIME)
WITH MEMBER [MEASURES].[AVG COST] AS
[Measures].[Sales Amount]/[Measures].[Order Quantity],
FORMAT_STRING = '$0.00'
SELECT {[Measures].[Order Quantity],[Measures].[Sales Amount], [MEASURES].[AVG COST]} ON COLUMNS ,
NON EMPTY
ORDER( ([Dim Customer].[Gender].[Gender].MEMBERS ,[Dim Product].[Size].[Size].MEMBERS),[Measures].[Order Quantity],BDESC) ON ROWS
FROM [Adventure Works DW]
WITH MEMBER [MEASURES].[AVG COST] AS
[Measures].[Sales Amount]/[Measures].[Order Quantity],
FORMAT_STRING = '$0.00'
MEMBER [MEASURES].[PROPOSED COST] AS
[Measures].[Sales Amount]+([Measures].[Sales Amount]*10/100),
FORMAT_STRING = '$0.00'
SELECT {[Measures].[Order Quantity],[Measures].[Sales Amount], [MEASURES].[AVG COST],[MEASURES].[PROPOSED COST]} ON COLUMNS ,
NON EMPTY
ORDER( ([Dim Customer].[Gender].[Gender].MEMBERS ,[Dim Product].[Size].[Size].MEMBERS),[Measures].[Order Quantity],BDESC) ON ROWS
FROM [Adventure Works DW]
--CREATE MEMBER FROM RECENTLY CREATED MEASURE
WITH MEMBER [MEASURES].[AVG COST] AS
[Measures].[Sales Amount]/[Measures].[Order Quantity],
FORMAT_STRING = '$0.00'
MEMBER [MEASURES].[PROPOSED COST] AS
[Measures].[Sales Amount]+([Measures].[Sales Amount]*10/100),
FORMAT_STRING = '$0.00'
MEMBER[MEASURES].[PROPOSED AVG COST] AS
[MEASURES].[AVG COST]+[MEASURES].[AVG COST]*10/100,
FORMAT_STRING = '$0.00'
SELECT {[Measures].[Order Quantity],[Measures].[Sales Amount], [MEASURES].[AVG COST],[MEASURES].[PROPOSED COST],[MEASURES].[PROPOSED AVG COST]} ON COLUMNS ,
NON EMPTY
ORDER( ([Dim Customer].[Gender].[Gender].MEMBERS ,[Dim Product].[Size].[Size].MEMBERS),[Measures].[Order Quantity],BDESC) ON ROWS
FROM [Adventure Works DW]
--------------------------------------------------
--NAMED CALCULATIONS/CALCULATED SETS
WITH MEMBER [MEASURES].[AVG COST] AS
[Measures].[Sales Amount]/[Measures].[Order Quantity],
FORMAT_STRING = '$0.00'
MEMBER [MEASURES].[PROPOSED COST] AS
[Measures].[Sales Amount]+([Measures].[Sales Amount]*10/100),
FORMAT_STRING = '$0.00'
MEMBER[MEASURES].[PROPOSED AVG COST] AS
[MEASURES].[AVG COST]+[MEASURES].[AVG COST]*10/100,
FORMAT_STRING = '$0.00'
SET [BY GENDER PRDSIZE] AS
ORDER( ([Dim Customer].[Gender].[Gender].MEMBERS ,
[Dim Product].[Size].[Size].MEMBERS),
[Measures].[Order Quantity],BDESC)
SELECT {[Measures].[Order Quantity],[Measures].[Sales Amount],
[MEASURES].[AVG COST],[MEASURES].[PROPOSED COST],
[MEASURES].[PROPOSED AVG COST]} ON COLUMNS ,
NON EMPTY
[BY GENDER PRDSIZE] ON ROWS
FROM [Adventure Works DW]
--MORE COMPLEX
WITH MEMBER [MEASURES].[AVG COST] AS
[Measures].[Sales Amount]/[Measures].[Order Quantity],
FORMAT_STRING = '$0.00'
MEMBER [MEASURES].[PROPOSED COST] AS
[Measures].[Sales Amount]+([Measures].[Sales Amount]*10/100),
FORMAT_STRING = '$0.00'
MEMBER[MEASURES].[PROPOSED AVG COST] AS
[MEASURES].[AVG COST]+[MEASURES].[AVG COST]*10/100,
FORMAT_STRING = '$0.00'
SET [BY GENDER PRDSIZE] AS
FILTER(
ORDER( ([Dim Customer].[Gender].[Gender].MEMBERS ,
[Dim Product].[Size].[Size].MEMBERS),
[Measures].[Order Quantity],BDESC),
[MEASURES].[PROPOSED AVG COST]<50 non_empty_behavior =" [Measures].[Freight]" format_string="Percent"> ([Measures].[Reseller Sales Amount])
) ON ROWS
FROM [Step-by-Step]
SELECT
{
([Measures].[Reseller Sales Amount]),
([Measures].[Internet Sales Amount])
} ON COLUMNS,
Filter(
{[Product].[Product].[Product].Members},
([Measures].[Internet Sales Amount]) > ([Measures].[Reseller Sales Amount])
AND
(
[Product].[Category].CurrentMember Is [Product].[Category].[Clothing] OR
[Product].[Category].CurrentMember Is [Product].[Category].[Accessories]
)
) ON ROWS
FROM [Step-by-Step]
SELECT
{
([Measures].[Reseller Sales Amount]),
([Measures].[Internet Sales Amount])
} ON COLUMNS,
Filter(
{[Product].[Product].[Product].Members},
([Measures].[Internet Sales Amount]) >
([Measures].[Reseller Sales Amount]) AND
NOT (
[Product].[Category].CurrentMember Is [Product].[Category].[Clothing] OR
[Product].[Category].CurrentMember Is [Product].[Category].[Accessories]
)
) ON ROWS
FROM [Step-by-Step]
SELECT
{([Measures].[Reseller Sales Amount])} ON COLUMNS,
Union(
TopCount(
{[Product].[Product].[Product].Members},
10,
([Measures].[Reseller Sales Amount], [Date].[Calendar Year].[CY 2003])
),
TopCount(
{[Product].[Product].[Product].Members},
10,
([Measures].[Reseller Sales Amount], [Date].[Calendar Year].[CY 2004])
)
) ON ROWS
FROM [Step-by-Step]
SELECT
{([Measures].[Reseller Sales Amount])} ON COLUMNS,
Except(
TopCount(
{[Product].[Product].[Product].Members},
10,
([Measures].[Reseller Sales Amount], [Date].[Calendar Year].[CY 2004])
),
TopCount(
{[Product].[Product].[Product].Members},
10,
([Measures].[Reseller Sales Amount], [Date].[Calendar Year].[CY 2003])
)
) ON ROWS
FROM [Step-by-Step]
SELECT
{([Measures].[Reseller Sales Amount])} ON COLUMNS,
Intersect(
TopCount(
{[Product].[Product].[Product].Members},
10,
([Measures].[Reseller Sales Amount], [Date].[Calendar Year].[CY 2004])
),
TopCount(
{[Product].[Product].[Product].Members},
10,
([Measures].[Reseller Sales Amount], [Date].[Calendar Year].[CY 2003])
)
) ON ROWS
FROM [Step-by-Step]
WITH
MEMBER [Measures].[Sibling Rank] AS
Rank(
[Product].[Product Categories].CurrentMember,
[Product].[Product Categories].CurrentMember.Siblings,
([Measures].[Reseller Sales Amount])
)
SELECT
{
([Measures].[Reseller Sales Amount]),
([Measures].[Sibling Rank])
} ON COLUMNS,
{[Product].[Product Categories].Members} ON ROWS
FROM [Step-by-Step]
SELECT
{([Measures].[Reseller Sales Amount])} ON COLUMNS,
Descendants(
{[Product].[Product Categories].[Category].[Bikes]},
[Product].[Product Categories].[Subcategory]
) ON ROWS
FROM [Step-by-Step]
SELECT
{([Measures].[Reseller Sales Amount])} ON COLUMNS,
Descendants(
{[Product].[Product Categories].[Category].[Bikes]},
[Product].[Product Categories].[Subcategory],
AFTER
) ON ROWS
FROM [Step-by-Step]
SELECT
{([Measures].[Reseller Sales Amount])} ON COLUMNS,
Descendants(
{[Product].[Product Categories].[Category].[Bikes]},
[Product].[Product Categories].[Subcategory],
SELF_AND_AFTER
) ON ROWS
FROM [Step-by-Step]
WITH
MEMBER [Measures].[Number of Children] AS
IIF(
IsLeaf([Product].[Product Categories].CurrentMember),
"N/A",
COUNT(
[Product].[Product Categories].CurrentMember.Children
)
)
SELECT
{[Measures].[Number of Children]} ON COLUMNS,
{[Product].[Product Categories].Members} ON ROWS
FROM [Step-by-Step]
SELECT
{([Measures].[Reseller Sales Amount])} ON COLUMNS,
{
LastPeriods(
3,
[Date].[Calendar].[Month].[January 2002]
)
} ON ROWS
FROM [Step-by-Step]
SELECT
{([Measures].[Reseller Sales Amount])} ON COLUMNS,
{
Descendants(
[Date].[Calendar].[Calendar Year].[CY 2003],
[Date].[Calendar].[Month],
SELF
)
} ON ROWS
FROM [Step-by-Step]
WITH
MEMBER [Measures].[x] AS
Cousin(
[Date].[Calendar].CurrentMember,
Ancestor(
[Date].[Calendar].CurrentMember,
[Date].[Calendar].[Calendar Year]
).Lag(1)
).Name
SELECT
{
([Measures].[Reseller Sales Amount]),
([Measures].[x])
} ON COLUMNS,
{
Descendants(
[Date].[Calendar].[Calendar Year].[CY 2003],
[Date].[Calendar].[Month],
SELF
)
} ON ROWS
FROM [Step-by-Step]
WITH
MEMBER [Measures].[x] AS
ParallelPeriod(
[Date].[Calendar].[Calendar Year],
1,
[Date].[Calendar].CurrentMember
).Name
SELECT
{
([Measures].[Reseller Sales Amount]),
([Measures].[x])
} ON COLUMNS,
{
Descendants(
[Date].[Calendar].[Calendar Year].[CY 2002],
[Date].[Calendar].[Month],
SELF
)
} ON ROWS
FROM [Step-by-Step]
WITH MEMBER [Measures].[ParameterCaption] AS '[Dim Product].[Model Name].CURRENTMEMBER.MEMBER_CAPTION'
MEMBER [Measures].[ParameterValue] AS '[Dim Product].[Model Name].CURRENTMEMBER.UNIQUENAME'
MEMBER [Measures].[ParameterLevel] AS '[Dim Product].[Model Name].CURRENTMEMBER.LEVEL.ORDINAL'
SELECT {[Measures].[ParameterCaption], [Measures].[ParameterValue], [Measures].[ParameterLevel]} ON COLUMNS ,
[Dim Product].[Model Name].[Model Name]ON ROWS
FROM [Adventure Works DW]
WITH MEMBER [Measures].[ParameterCaption] AS '[Dim Product].[Model Name].CURRENTMEMBER.MEMBER_CAPTION'
MEMBER [Measures].[ParameterValue] AS '[Dim Product].[Model Name].CURRENTMEMBER.UNIQUENAME'
MEMBER [Measures].[ParameterLevel] AS '[Dim Product].[Model Name].CURRENTMEMBER.LEVEL.ORDINAL'
SELECT {[Measures].[ParameterCaption], [Measures].[ParameterValue], [Measures].[ParameterLevel]} ON COLUMNS ,
[Dim Product].[Hierarchy].[English Product Category Name] ON ROWS
FROM [Adventure Works DW]
