Todays Queries MDX
Santosh Gadila
With Member GrossProfit
AS
([Measures].[ISSales Amount] + [Measures].[RSSales Amount])
select {[Measures].[ISSales Amount], [Measures].[RSSales Amount], GrossProfit}
on Columns,
[Product].[ProductCategories].[Product Category] on rows
from [B29Cube]
---------------------------
WITH MEMBER [Measures].[NET INCOME]
AS [Measures].[ISSales Amount]-[Measures].[ISProduct Standard Cost]
SELECT {[Order Date].[Calendar Year].&[2005]:[Order Date].[Calendar Year].&[2008]} ON ROWS ,
{
[Measures].[ISSales Amount],[Measures].[ISProduct Standard Cost],[Measures].[NET INCOME]
}
ON COLUMNS
FROM [B29Cube]
----------ADD A FORMAT--------------------
WITH MEMBER [Measures].[NET INCOME]
AS '[Measures].[ISSales Amount]-[Measures].[ISProduct Standard Cost]',FORMAT_STRING ='$##,##'
SELECT {[Order Date].[Calendar Year].&[2005]:[Order Date].[Calendar Year].&[2008]} ON ROWS ,
{
[Measures].[ISSales Amount],[Measures].[ISProduct Standard Cost],[Measures].[NET INCOME]
}
ON COLUMNS
FROM [B29Cube]
----------------------NAMED SETS-------------------------------
WITH SET [TopProducts] AS
TOPCOUNT([Product].[ProductCategories].[Product].Members,
50, [Measures].[ISSales Amount])
Select [Measures].[ISSales Amount] on Columns,
NON EMPTY [TopProducts] ON ROWS
FROM [B29Cube]
--------------------
WITH SET [OrderedSales] AS
ORDER (
([Customer].[State Province].CHILDREN,
[Product].[Product Subcategory].CHILDREN),
[Measures].[ISSales Amount], BDESC)
SELECT [Measures].[ISSales Amount]
ON COLUMNS,
NON EMPTY
[OrderedSales] ON ROWS
FROM [B29Cube]
/*The Order function can either be hierarchical
(as specified by using the ASC or DESC flag) or nonhierarchical
(as specified by using the BASC or BDESC flag;
the B stands for "break hierarchy").
If ASC or DESC is specified, the Order function first arranges
the members according to their position in the hierarchy,
and then orders each level. If BASC or BDESC is specified,
the Order function arranges members in the set without regard
to the hierarchy. In no flag is specified, ASC is the default.*/
----------------RANK FUNCTIONS---------------------------------------------
/*Sales ranking based on sales amount for subcategory
using Rank Function as Calculated Member*/
WITH SET [OrderedProducts] AS
ORDER
([Product].[Product Subcategory].CHILDREN,
[Measures].[ISSales Amount], BDESC )
MEMBER [SalesRank] AS
RANK ([Product].[Product SubCategory].CurrentMember,
[OrderedProducts])
SELECT
{[Measures].[ISSales Amount],
[SalesRank]} ON COLUMNS,
[OrderedProducts] ON ROWS
FROM [B29Cube]
/*Evaluating the contribution of each member relative to its
siblings, we can use the Rank function to rank each member
from highest to lowest based on sales. */
WITH MEMBER [Sibling Rank] AS
Rank(
[Product].[ProductCategories].CurrentMember,
[Product].[ProductCategories].CurrentMember.Siblings,
([Measures].[RSSales Amount])
)
SELECT
{
([Measures].[RSSales Amount]),
([Sibling Rank])
} ON COLUMNS,
{[Product].[ProductCategories].Members} ON ROWS
FROM [B29Cube]
--------------------------
SELECT [Measures].[RSSales Amount] on Columns,
[Product].[Product Category].lastchild.lag(3)
on rows
From [B29Cube]
--------
SELECT [Measures].[RSSales Amount] on Columns,
[Product].[Product Category].firstchild.lead(1)
on rows
From [B29Cube]
---------------------------------
SELECT [Measures].[RSSales Amount] ON 0,
Ancestor([Product].[ProductCategories].[Product].&[314],
2) ON 1
FROM [B29Cube]
/*Ancestor() takes two arguments. The first is the dimension member
to operate on, and the second is the hierarchy level to move up to.
In the preceding query, we return the Subcategory for the [Chain]
member of products. Ancestor() can also take a numeric value
instead of a hierarchy level. In that case, the result returned
is the specified number of steps up from the member in the
first argument.*/