September 27, 2012 at 11:29 pm
Hi guys,
I am new to complex MDX queries
I have managed to write these queries but now i need to combine them all together so that I get 4 columns of result set instead of one columns in every result set.
Can you please help me with this request
Can you please use the comment heading for the column names
//=======================================================================================
// Latest Bill
//=======================================================================================
WITH SET [Row Axis] AS
NONEMPTY(
{[Dim Customer].[Party Name].[Party Name].MEMBERS} *
{[Insert Datetime].[Date].[Date].MEMBERS}
,([Measures].[New Charge Amount])
)
MEMBER [Measures].[RowNumber] AS
RANK (
(
[Dim Customer].[Party Name].CurrentMember,
[Insert Datetime].[Date].CurrentMember
),
[Row Axis]
)
SELECT
{
[Measures].[New Charge Amount]
} ON COLUMNS,
TOPCOUNT
(
[Row Axis],
1,
([Measures].[RowNumber])
) ON ROWS
FROM [ACCOUNT_LEVEL]
//=======================================================================================
// Oldest Bill
//=======================================================================================
WITH SET [Row Axis] AS
NONEMPTY(
{[Dim Customer].[Party Name].[Party Name].MEMBERS} *
{[Insert Datetime].[Date].[Date].MEMBERS}
,([Measures].[New Charge Amount])
)
MEMBER [Measures].[RowNumber] AS
RANK (
(
[Dim Customer].[Party Name].CurrentMember,
[Insert Datetime].[Date].CurrentMember
),
[Row Axis]
)
SELECT
{
[Measures].[New Charge Amount]
} ON COLUMNS,
BottomCOUNT
(
[Row Axis],
1,
([Measures].[RowNumber])
) ON ROWS
FROM [ACCOUNT_LEVEL]
//=======================================================================================
// Biggest Invoices
//=======================================================================================
SELECT
{[Measures].[New Charge Amount]} ON COLUMNS,
TOPCOUNT(
{[Dim Customer].[Party Name].CHILDREN}*
{([Insert Datetime].[Date].CHILDREN)},
1,
([Measures].[New Charge Amount])
) ON ROWS
FROM [ACCOUNT_LEVEL]
//=======================================================================================
// Lowest Invoices
//=======================================================================================
WITH
MEMBER [Measures].[Invoice Rank] AS
RANK(
[Insert Datetime].[Date].CurrentMember,
[Insert Datetime].[Date].CurrentMember.Siblings,
([Measures].[New Charge Amount])
)
SELECT
{
([Measures].[New Charge Amount])
} ON COLUMNS,
TOPCOUNT
(
FILTER
(
{[Dim Customer].[Party Name].CHILDREN}*
{[Insert Datetime].[Date].CHILDREN},
([Measures].[New Charge Amount])>0
),
1,
([Measures].[Invoice Rank])
)
ON ROWS
FROM [ACCOUNT_LEVEL]
Thank you in advance
October 2, 2012 at 8:04 pm
Hi Guys,
any thoughts about this post!
can help would be appreciated
Thank you
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply