June 27, 2006 at 1:07 am
Hi all,
i am new to this MDX queries and analysis services.As i am facing some problem which i expected to work on either way
i composed an MDX query
With Member
[Measures].[Display_year]
as
'[Time].Currentmember.Parent.Parent.Name'
Member
[Measures].[Display_month]
as
'[Time].Currentmember.Name'
Select
{Descendants([Time].[1998],[Time].[Month])} on Columns,
{
{([Warehouse].[All Warehouses].[USA],[Measures].[Display_year])},
{([Warehouse].[All Warehouses].[USA],[Measures].[Display_month])},
CrossJoin({Descendants([WareHouse].[All WareHouses].[USA].Children]},{[Measures].[Units Shipped]})
} on Rows
From Warehouse
In this MDX query,I was issued an Error"Unknown Error"
Later i made a Modification to the Cross join in the query to show like this
With Member
[Measures].[Display_year]
as
'[Time].Currentmember.Parent.Parent.Name'
Member
[Measures].[Display_month]
as
'[Time].Currentmember.Name'
Select
{Descendants([Time].[1998],[Time].[Month])} on Columns,
{
{([Warehouse].[All Warehouses].[USA],[Measures].[Display_year])},
{([Warehouse].[All Warehouses].[USA],[Measures].[Display_month])},
CROSSJOIN({DESCENDANTS([Warehouse].[All Warehouses].[USA],
[Warehouse]. [State Province])},
{[Measures].[Units Shipped]})}
} on Rows
From Warehouse
Now it was Working Fine
I want to know what went wrong in the first query and what should i know here
Any suggestions or Answers are highly appreciated
Thank You
Raj Deep.A
June 27, 2006 at 6:06 am
a child is 1 hierarchal step down from the parent. a descendent is any member found down from the parent to the last leaf-level member.
cant help you any further - i'm shyte with mdx queries!
June 30, 2006 at 5:33 am
dont think you can (or need) to cross join with measures which is why initial query didnt work, generally cross join dimensions on rows and have measures on columns. descendants has various options where you can include level selected, levels in between etc. If you dont need this may be more readable with .children for example Descendants([Time].[1998],[Time].[Month])} is sames as {[Time].[1998].members}, [Time].[1998].children returns all months without year total. Best to buy (and read!) mdx solutions book.
With Member
[Measures].[Display_year]
as
'[Time].Currentmember.Parent.Parent.Name'
Member
[Measures].[Display_month]
as
'[Time].Currentmember.Name'
Select
{Descendants([Time].[1998],[Time].[Month])} on Columns,
{
{([Warehouse].[All Warehouses].[USA],[Measures].[Display_year])},
{([Warehouse].[All Warehouses].[USA],[Measures].[Display_month])},
CROSSJOIN({DESCENDANTS([Warehouse].[All Warehouses].[USA],
[Warehouse]. [State Province])},
{[Measures].[Units Shipped]})}
} on Rows
From Warehouse
seems to me would be better to rewrite as this and format within the report:
With Member
[Measures].[Display_year]
as
'[Time].Currentmember.Parent.Parent.Name'
Member
[Measures].[Display_month]
as
'[Time].Currentmember.Name'
Select
{[Measures].[Units Shipped], [Measures].[Display_year], [Measures].[Display_month]} on columns,
{
CROSSJOIN({DESCENDANTS([Warehouse].[All Warehouses].[USA],
[Warehouse]. [State Province])}, {Descendants([Time].[1998],[Time].[Month])})
} on Rows
From Warehouse
Phil Nicholas
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply