TM1 Forum

It does work, that probably wasn't clear from the screenshot or the explanation.

When the arithmetic options come up, they only show one element (in my example 825 and 269) not all of the selection. Hence why in the above it just shows 2021 - 2020.

Ah, I didn't know about that parameter.
Thanks George

Hello I've looked at this thread as well. https://www.tm1forum.com/viewtopic.php?t=14451 The TM1ToggleExpandMode seems to stop the dynamic formatting working in a Dynamic PAfE report, making all roll ups be Level 0? In this case, at the bottom, 5 Total Interest... is actually a Level 1 5100 Interest...

I'm glad you posted your new comment Mark. When I tried to substitute my typed in parameter for a cube based one, it stopped working again. But the NumberToString that you suggested made it work, so thank you! This seems to be rather bizarre converting numbers to strings and back to numbers mind you...

That bit worked fine Mark, it was the next bit. This works. I needed single quotes around the number defined in the parameter and had to remove the double quotes as Steve suggested. I thought that I had tried every combo, but obviously not that one! Thanks for looking. pPayrollPeriodNumber='114'; MD...

I then get the "Process ... saved with errors " message due to:

Syntax error on or before: " pPayrollPeriodNumber invalid operator "

Hello pPayrollPeriodNumber=114; MDXRef='{FILTER([Year for Payroll Total].[Year for Payroll Total].MEMBERS , ([Year for Payroll Total].CURRENTMEMBER.PROPERTIES("Year") = "'|CellGetS('Parameter','Parameter','Payroll year')|'") AND #(VAL([Year for Payroll Total].CURRENTMEMBER.PROPER...

Thanks folks, this will be very useful.

I find that the set editor in PAW is a useful resource as a starting point for seeing what the MDX should be, so between that and this guide, hopefully that should cover most things that I may want to do.

This is really helpful, thanks everyone. While testing, I wrote the wrong dimension name in the cube, but it still worked (in PAfE)? It doesn't work if you don't put in any dimension name. {FILTER([Permanent Staff].MEMBERS,Instr(1,[Current year staff forecast].([Year for payroll total].[Forename]),&...

Yes, they both have more characters than my original solution.

There is never the "perfect" solution. Good to know the syntax to get those to work though for future reference.

Thanks all

This gives me all Ancestors, but not the leaves: {HIERARCHIZE( GENERATE( FILTER( {TM1SUBSETALL ( [Year historic data] ) }, ([Year historic data].[Period number])> 84 ) ,[Year historic data].CurrentMember.ANCESTORS) )} [b]This returns nothing:[/b] {HIERARCHIZE( GENERATE( FILTER( {TM1SUBSETALL ( [Yea...

Indeed. I have solved it but thought any of those 3 would be more elegant...but couldn't get them to work so I thought I'd ask.

Hello I managed to get this MDX to do what I want (for use in a PAfE report), which is filtering on the period number, then generating the parents and sorting by hierarchy. It seems a bit odd to generate all Ascendants then, filter some of them out again. I have tried using Parent/Ancestor/Ancestors...