"GROUP BY" MDX

[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.

[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...

[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

[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]';

Edit, missed the last bit off in the original post.

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.

[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

[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

[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

[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

[Wim Gielis]

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