How to Apply group by on mdx Query

Question

Working pentaho dashboard trying to generate report to get transaction count per day/Week/Month between two date.

Below MDX Query return transaction count 1 because 'created' is not group by

WITH
SET [~COLUMNS] AS Filter([created].DefaultMember.Children, [created].CurrentMember.name >= "2014-10-01" AND Left([created].CurrentMember.Name, 10) <= "2014-10-02")
SET [~ROWS] AS
    {[markup].[markup].Members}
SELECT
NON EMPTY CrossJoin([~COLUMNS], {[Measures].[Transaction Count]}) ON COLUMNS,
NON EMPTY [~ROWS] ON ROWS
FROM [transaction]

How to i can group column '[created]'

whytheq whytheq · Accepted Answer · 2018-01-11T15:24:23

Why not just move the measure into the WHERE clause:

WITH
SET [~COLUMNS] AS Filter([created].DefaultMember.Children, [created].CurrentMember.name >= "2014-10-01" AND Left([created].CurrentMember.Name, 10) <= "2014-10-02")
SET [~ROWS] AS
    {[markup].[markup].Members}
SELECT
NON EMPTY [~COLUMNS] ON COLUMNS,
NON EMPTY [~ROWS] ON ROWS
FROM [transaction]
WHERE [Measures].[Transaction Count]

How to Apply group by on mdx Query

1 Answers