MDX - Activeform vs Subset Editor results

[MikeF]

Hi there,

I'm scratching my head over some MDX that I'm trying to use in an activeform. If I copy the MDX string and paste it into the subset editor I get the expected results, but using it in an activeform leaves project codes that should be excluded.

Here's the (fairly long) MXD statement built from a series of subn's etc:

Code: Select all

{FILTER({TM1FILTERBYLEVEL( {TM1SUBSETALL( [Project] )}, 0)},[Project Rep].([Version].[Day 3],[Fin Year].[2016],[Period].[Jul],[Company].[AINZL],[Branch].[010],[Currency].[NZD],[ProjectRep Measure].[PMR Required])= 1  AND INSTR( [Project Rep].([Version].[Day 3],[Fin Year].[2016],[Period].[Jul],[Company].[AINZL],[Branch].[010],[Currency].[NZD],[ProjectRep Measure].[PMR Status]), "Excuse")= 0  )}

Basically what it's meant to do is only pick up project codes where a rule driven flag is set for that month (PMR Required) and the status hasn't been preloaded with a string containing Excused. Which as said works fine in the subset editor, but in the activeform those excused jobs are still in the list of projects.

Note all the jobs with the PRM required flag are listed, so it seems like the INSTR test is being applied, but giving the wrong result. And yes, I realise this is an undocumented "feature", but I'm not sure if there's an alternate way to do this?

Any clues as to the different behaviour?

Mike

[tomok]

Your string is 396 characters. There used to be a limitation of 256 characters in an active form MDX statement. Don't know if it still exists.

[MikeF]

Hi Tomok,

I figure the whole string must be being passed through, as until I got all the brackets in the right place at the very end of the concatenated string I was getting no results in the set at all. I can't prove that in any way though.

Mike

[tm123]

I think i was dealing with an issue like this a few months ago, I was trying to Use LEFT function in the MDX expression. I could never get it working in Perspectives but it worked in TM1Web. Just try this

[MikeF]

could never get it working in Perspectives but it worked in TM1Web

Well what do you know!? That's exactly what I'm getting after uploading the file to the applications folder. Works fine in TM1 Web, but still no go in Perspectives. Now if I can just conquer all the Web formatting issues that just might work well enough for us... Though it's always a bit confusing for users to be told to go here for this and there for that.

Thanks for the pointer. Glad to know a) my MDX actually works, and b) I'm not alone!

Mike