Help in combining 4 different MDX queries

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Data Warehousing » Analysis Services » Help in combining 4 different MDX queries

Help in combining 4 different MDX queries

Rate Topic

Display Mode

Topic Options

Author

Message

8reat1s

Posted Thursday, September 27, 2012 11:29 PM

SSC Rookie

Group: General Forum Members
Last Login: Monday, June 03, 2013 6:45 PM
Points: 31, Visits: 107

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

Post #1365614

8reat1s

Posted Tuesday, October 02, 2012 8:04 PM

SSC Rookie

Group: General Forum Members
Last Login: Monday, June 03, 2013 6:45 PM
Points: 31, Visits: 107

Hi Guys,

any thoughts about this post!

can help would be appreciated

Thank you

Post #1367376

« Prev Topic | Next Topic »

Permissions