Sorting and Paging in MDX Query (MS Analysis Services)
I have built an MDX query to get specific articles from an OLAP cube. It basically returns articles below a specific product category node that are produced by a specific manufacturer:
SELECT NON EMPTY
(
Hierarchize
(
{
DrilldownLevel
(
{
[T DAT Article].[Ar ID].[All]
}
)
}
)
)
DIMENSION PROPERTIES PARENT_UNIQUE_NAME,
[T DAT Article].[Ar ID].[Ar ID].[Ar Key],
[T DAT Article].[Ar ID].[Ar ID].[Ar LongName]
ON COLUMNS
FROM [Catalog_2009]
WHERE
(
[T DAT Structure].[St St ID FK].&[193066], -- specific article-category node
[T DAT Firm].[Fi ID].&[86] -- specific manufacturer
)
CELL PROPERTIES VALUE, FORMAT_STRING, LANGUAGE, BACK_COLOR, FORE_COLOR, FONT_FLAGS
Now I want to improve this query to support paging and sorting. Value I can provide:
- Page index (e.g. 0)
- Page size (e.g. 30)
- Column sort (e.g. Ar LongName)
- Sort order (for example, ascending)
What approach should be taken? I looked at Subset and order . But mostly this limited the results of "hierarchizing" the query part, that is, they cut off the hierarchies instead of the final result.
Can anyone give me a hint on how to get paging and sorting to work?
a source to share
Sorting is a pain in MDX, or at least one that I've always felt. If your query only has one dimension in rows and one dimension in columns, it will make life much easier.
The ORDER keyword can be applied to a set and you just need to give it a sort number and indicate which path to go with ASC or DESC.
The ASC or DESC problem is confusing as you can specify BASC or BDESC as well. "B" stands for "break" and it says if you want the rows (or columns) to be sorted in their hierarchical groups, or if you want to completely break the hierarchy when sorting and completely reorder the rows.
Here's a simple example
ORDER({[Time].[Month].members}, [Measures].[Gross Sales], BASC)
This means that you want to list all Months in ascending order of gross sales value. You break the hierarchy so that the Months do not remain grouped during their parent Years and can be completely shuffled in a sort operation.
a source to share
Take a look at the example below
I have listed the Products in descending order of sales
select
{[Measures].[Internet Sales Amount]}on 0,
order ({[Product].[Product].[Product]},
[Measures].[Internet Sales Amount],desc
)on 1
from
[Adventure Works]
Result
Now let's take a look at them. I am creating a page of 10 lines, page 1
select
{[Measures].[Internet Sales Amount]}
on 0,
subset(
order
(
{
[Product].[Product].[Product]
},[Measures].[Internet Sales Amount],desc
)
,0,10)
on 1
from
[Adventure Works]
Result
Now Page 2
select
{[Measures].[Internet Sales Amount]}
on 0,
subset(
order
(
{
[Product].[Product].[Product]
},[Measures].[Internet Sales Amount],desc
)
,10,10)
on 1
from
[Adventure Works]
Result
a source to share