We have a table with one Measure [Discount Amount] on the COLUMNS and CROSSJOIN of [Product].[Product Categories] and [Geography].[Geography] on ROWS axis.
We use ORDER function to sort entities by [Discount Amount] with option "BDESC".
MDX:
SELECT
NON EMPTY
{[Measures].[Discount Amount]} ON COLUMNS
,NON EMPTY
Order
(
{
Hierarchize
(
{
CrossJoin
(
{
Hierarchize
(
{
[Product].[Product Categories].[All Products]
,[Product].[Product Categories].[All Products].Children
}
)
}
,{Hierarchize({[Geography].[Geography].[All Geographies]})}
)
}
)
}
,[Measures].[Discount Amount]
,BDESC
) ON ROWS
FROM [Adventure Works];
Table looks good and displays information as we want it.
Then we want to expand [All Categories] element for entity [Clothing]. To do that we need to make several changes:
- modify existing Crossjoin (as before but without Clothing);
- add a new Crossjoin for [Clothing] entity with expansion of selected member [All Categories];
new MDX:
SELECT
NON EMPTY
{[Measures].[Discount Amount]} ON COLUMNS
,NON EMPTY
{
Order
(
{
Hierarchize
(
{
CrossJoin
(
{
Except
(
{
Hierarchize
(
{
[Product].[Product Categories].[All Products]
,[Product].[Product Categories].[All Products].Children
}
)
}
,{[Product].[Product Categories].[Category].&[3]}
)
}
,{Hierarchize({[Geography].[Geography].[All Geographies]})}
)
}
)
,CrossJoin
(
{[Product].[Product Categories].[Category].&[3]}
,{
Hierarchize
(
{
[Geography].[Geography].[All Geographies]
,[Geography].[Geography].[All Geographies].Children
}
)
}
)
}
,[Measures].[Discount Amount]
,BDESC
)
} ON ROWS
FROM [Adventure Works];
and result table in SQL Server Management Studio:
As you can see all children of [Clothing] entity loses their hierarchy and has been displayed as separate entities in table.
But we want these elements to be under [All Geographies] of [Clothing].
If we try to change sorting type to "DESC" (hierarchical) then children displays correctly but ordering in table doesn't work:
So we are searching for solution how to make work ordering and expanding in such tables.
Thank you.