Page 1 of 1
"GROUP BY" MDX
Posted: Mon Feb 14, 2022 3:22 pm
by holger_b
Probably there is actually no way of solving that by MDX, but maybe someone can think of a solution? I need a list of products and countries plus a key figure, could be revenue. The challenge is, we want to apply a benchmark to the query, like a list of products and countries where revenue "by product and country" exceeds a certain level.
Any ideas? Thanks in advance, Holger.
Re: "GROUP BY" MDX
Posted: Mon Feb 14, 2022 3:57 pm
by Steve Rowe
You'll want to use ViewCreateByMDX, rather than the more usual subset logic.
https://www.ibm.com/docs/en/planning-an ... reatebymdx
See
https://www.tm1forum.com/viewtopic.php?t=16024 for a hopefully helpful approach on creating one...
Re: "GROUP BY" MDX
Posted: Mon Feb 14, 2022 5:26 pm
by holger_b
Brilliant, thank you, Steve! This may make me leave my good old 9.5.2 ways behind, looks really powerful... I could not find much syntax help yet, can anyone point me to something?
Regards
Holger
Re: "GROUP BY" MDX
Posted: Mon Feb 14, 2022 5:56 pm
by Steve Rowe
MDX views are quite hard, or at least require you to step up your MDX game significantly.
I started by looking at the MDX that PA was generating for a view (there's a lot of noise in this) and then reading up on the MDX documentation, and a lot of trial and error until I got to a method that worked for me.
https://docs.microsoft.com/en-us/analys ... llversions
There's a TM1 example below, I would caveat this to say it is derived from what I see PA report as the MDX used to return a view, I've not pushed harder on this to see if there is a more concise way of writing this! I suspect I could get rid of the cross joins but never really tried.
Code: Select all
sMDX='SELECT NON EMPTY
{[Dim5].[Hier5].[Member]} On 0,
Filter(
NONEMPTY(
CROSSJOIN(TM1SubsetToSet([Dim1].[Hier1], "Front End - Description"),
CROSSJOIN(TM1SubsetToSet([Dim2].[Hier2], "Default"),
CROSSJOIN(TM1SubsetToSet([Dim3].[Hier3], "L0"),
TM1SUBSETALL([Dim4])))),
{{
[Dim5].[Hier5].[Member]
}}),
[Dim5].[Hier5].[Another Member]="ABCDEF")
ON 1 From [Cube]';
Not sure what release the function arrived in, it's quite possible its not available in 10.2.2. that you list yourself as being on..
A perhaps easier road for you, is to use a ruled measure to flag the intersections you are interested in and then use this to drive the next step in what you are trying to do.
Re: "GROUP BY" MDX
Posted: Mon Feb 14, 2022 10:46 pm
by burnstripe
Following up on Steve's post you can also substitute crossjoin with * to make it a bit easier to read with fewer brackets
Code: Select all
CROSSJOIN(
TM1SubsetToSet([Dim1].[Hier1], "Front End - Description")
, TM1SubsetToSet([Dim2].[Hier2], "Default")
)
Could be replaced by
Code: Select all
TM1SubsetToSet([Dim1].[Hier1], "Front End - Description")
*
TM1SubsetToSet([Dim2].[Hier2], "Default")
If you're using an active form you could also just create your report returning everything then use TM1RPTFILTER to filter out and sort
I'd imagine the Mdx view to perform better (not tested) but it's another option
Re: "GROUP BY" MDX
Posted: Tue Feb 15, 2022 8:57 am
by holger_b
Actually I was trying to find a way how to avoid the flags which we currently use, as setting them takes quite some performance and I would rather avoid that. Sounds to me like creating the view through MDX might do the job but I do not seem to have the time to find out how to do it exactly...
Thank you
Holger
Re: "GROUP BY" MDX
Posted: Tue Feb 15, 2022 10:00 am
by MarenC
Hi,
I have a question for Steve, why is there no ON 1 (rows) defined in your MDX query, and why is there no From either?
Maren
Re: "GROUP BY" MDX
Posted: Tue Feb 15, 2022 10:42 am
by Steve Rowe
Thanks Maren,
Because in editing the code to make it non company specific I managed to drop off the end of the statement, now updated. Thanks again
Re: "GROUP BY" MDX
Posted: Tue Feb 15, 2022 12:44 pm
by Wim Gielis
Steve Rowe wrote: ↑Tue Feb 15, 2022 10:42 am
Thanks Maren,
Because in editing the code to make it non company specific I managed to drop off the end of the statement, now updated. Thanks again
Couldn’t we have some AI functionality that automatically finishes the statements just the way we want
