TM1 Forum

Hi

Really hoping you could help me out here

I have made a perspectives application which is published onto planning analytics workspace

On the sheet is a list of brands and then various measures (sales, markdown etc)

It is great apart from the inability to sort by fields in the calculation dimension

By default the brands are listed alphabetically whereas I would like them sorting by last year sales

Additionally the sort would need to work (and automatically update) when we are filtering on a subset of items (ie mens clothing) so it is correct for whatever is filtered

Could anyone please advise how to do this?

Thanks

Matt

Use MDX http://www.bihints.com/tm1sort_tm1sortbyindex_and_order.

tomok wrote: ↑Tue Sep 24, 2019 10:29 am Use MDX http://www.bihints.com/tm1sort_tm1sortbyindex_and_order.

Thank you for the quick reply!

I usually use performance modeller to build cubes / write rules etc and I have not encountered this sort of syntax before.

If I were to replicate a sort rule similar to the link you provided, where would I input the rule? Is this in the rules file of the cube in which the dimension will require to be sorted? Or elsewhere?

Sorry if this is a daft question, and many thanks in advance!

MDX cannot be used in a rule. You would use this in your report.

tomok wrote: ↑Tue Sep 24, 2019 11:19 am MDX cannot be used in a rule. You would use this in your report.

Ah right thank you!

In which case do you know how I would access the code behind the report I made in Perspectives in order to add the MDX rule?

cleggy wrote: ↑Tue Sep 24, 2019 11:24 am In which case do you know how I would access the code behind the report I made in Perspectives in order to add the MDX rule?

Assuming this is an active form report, which it would have to be in order to filter like you want, the MDX statement would go in the TM1RPTROW formula for the Brand dimension.

tomok wrote: ↑Tue Sep 24, 2019 10:34 pm

cleggy wrote: ↑Tue Sep 24, 2019 11:24 am In which case do you know how I would access the code behind the report I made in Perspectives in order to add the MDX rule?

Assuming this is an active form report, which it would have to be in order to filter like you want, the MDX statement would go in the TM1RPTROW formula for the Brand dimension.

Thank you!

Where in the following formula would I put the MDX rule to sort?

It would be good if you learn how to search online for help.
Meaning, start Google, enter tm1rptrow, hit enter, and read the first hit you get. It's a page by IBM.

Wim Gielis wrote: ↑Wed Sep 25, 2019 8:16 am It would be good if you learn how to search online for help.
Meaning, start Google, enter tm1rptrow, hit enter, and read the first hit you get. It's a page by IBM.

Thank you for your incredibly useful response, how silly of me to ask for help on a forum designed to assist collaboration and provide support in the TM1 community!

Funnily enough I did google this, however after editing the formula as per the IBM page I was getting an error which is why I asked for help.

The original brand dimension rule:

=TM1RPTROW($B$9,"BuyingBudget:CC_Brand3to5","",'{AR}01'!$B$20:$B$1548,"",0)

The MDX rule I have edited as per the link my more helpful american friend tomok provided:

{
ORDER(
{ TM1FILTERBYLEVEL(
{TM1SUBSETALL( [CC_Brand3to5] )}
,0)}
, [CCB_3to5CatGendBrandPlan].([CCB_c_3to5CatGendBrandPlan].[TY Cost of Sales 52wk]), BDESC)
}

Going from the IBM page it looks like MDX is inserted as the 7th clause in this rule, so I thought I would edit the original formula as follows:

[CC_Brand3to5] = dimension I would like to sort
[CCB_3to5CatGendBrandPlan] = name of cube in which I would like to sort
[CCB_c_3to5CatGendBrandPlan] = calculation dimension
[TY Cost of Sales 52wk] = value I would like to sort on

=TM1RPTROW($B$9,"BuyingBudget:CC_Brand3to5",,,,,{
ORDER(
{ TM1FILTERBYLEVEL(
{TM1SUBSETALL( [CC_Brand3to5] )}
,0)}
, [CCB_3to5CatGendBrandPlan].([CCB_c_3to5CatGendBrandPlan].[TY Cost of Sales 52wk]), BDESC)
})

However this did not work, the brands from my dimension disappeared and it didnt seem to be recognised as a formula, hence why I posted to check if I was doing something wrong, which I'm sure I am, this is the first time I am editing one of these formulas afterall.

Hopefully someone will have a more helpful response than yourself.

cleggy wrote: ↑Wed Sep 25, 2019 8:29 amFunnily enough I did google this

I wouldn't call it funny, because you hide useful information for the helpers here. With the risk of being not very helpful, again, I would ask: why didn't you put your final, modified, formula here ? It would show us what you did before reaching out here.

Anyway, at least I would expect:
- double quotes around the MDX statement in the formula
- that you tested the MDX expression in a view in, for example, the cube viewer and check that it returns the correct rows

If you can't get this MDX to work in Excel, which in fact you should, you can always create a public subset on that dimension with the MDX attached to it. Then simply use that subset in the correct argument of the TM1RptRow function. You know where to find the order of the arguments.

Apologies if it's me providing further help rather than our more helpful American friend.

Wim Gielis wrote: ↑Wed Sep 25, 2019 8:42 am

cleggy wrote: ↑Wed Sep 25, 2019 8:29 amFunnily enough I did google this

I wouldn't call it funny, because you hide useful information for the helpers here. With the risk of being not very helpful, again, I would ask: why didn't you put your final, modified, formula here ? It would show us what you did before reaching out here.

Anyway, at least I would expect:
- double quotes around the MDX statement in the formula
- that you tested the MDX expression in a view in, for example, the cube viewer and check that it returns the correct rows

If you can't get this MDX to work in Excel, which in fact you should, you can always create a public subset on that dimension with the MDX attached to it. Then simply use that subset in the correct argument of the TM1RptRow function. You know where to find the order of the arguments.

Apologies if it's me providing further help rather than our more helpful American friend.

It was the double quotes around the MDX formula that was the issue, thanks for your help.

cleggy wrote: ↑Wed Sep 25, 2019 8:47 amIt was the double quotes around the MDX formula that was the issue, thanks for your help.

Then I wonder how you could save the TM1RptRow formula in Excel, without these double quotes.
Glad it's solved now.