Another Regression Bug :@ (SUBNM)

Post Reply
User avatar
Steve Vincent
Site Admin
Posts: 1054
Joined: Mon May 12, 2008 8:33 am
OLAP Product: TM1
Version: 10.2.2 FP1
Excel Version: 2010
Location: UK

Another Regression Bug :@ (SUBNM)

Post by Steve Vincent »

I'm starting to get a little more than annoyed with basic functionality that is broken in TM1...

I've tried this on both the dev and prod servers that are using slightly different versions of 9.0 (dev is 9.0.3.182 update 7 / prod is 9.0.3.173) and both do the same thing. Its critical to one of my procedures to remove errors from data before it's submitted to us, and it simply doesn't work.

I have a subset in a raft of dimensions called "Validation List". It's built using MDX to grab only level zero elements AND only when they do not have a value in an attribute called "ValidationList". It allows us to exclude certain elements which are valid but we don't want people using, as well as exclude the usual mistakes of entering consolidations insteda of nodals. There is NOTHING wrong with these subsets, they are spot on. The problem is when SUBNM is used against them.

SUBNM can grab values from a subset via either index OR element name. If i write SUBNM using the index, it's fine - all i get are the elements that existing within my subset. But, if you do the same using the element name, its completely ignoring the subset and looking at the entire dim. That means my SUBNM returns a value even when checked against a consolidation or worse still, one of the "banned" elements.

I'm relying upon this to allow users to vet their data (as they are the only ones that know the valid entries, not us), and in Excel its the only way i can do it without going back to the old method of manually creating a validation file that each data submission is linked to.

Does anyone else using a version of 9.0 manage to get SUBNM to work correctly i this manner, and if so which version is it with? I'm desperate for a solution today, and i'll be ringing Cognos Support right after lunch, not that i hold out much hope as they still haven't given me an answer on the screwed up ViewCreate process in TI...
If this were a dictatorship, it would be a heck of a lot easier, just so long as I'm the dictator.
Production: Planning Analytics 64 bit 2.0.5, Windows 2016 Server. Excel 2016, IE11 for t'internet
User avatar
Alan Kirk
Site Admin
Posts: 6606
Joined: Sun May 11, 2008 2:30 am
OLAP Product: TM1
Version: PA2.0.9.18 Classic NO PAW!
Excel Version: 2013 and Office 365
Location: Sydney, Australia
Contact:

Re: Another Regression Bug :@ (SUBNM)

Post by Alan Kirk »

Steve Vincent wrote: I have a subset in a raft of dimensions called "Validation List". It's built using MDX to grab only level zero elements AND only when they do not have a value in an attribute called "ValidationList". It allows us to exclude certain elements which are valid but we don't want people using, as well as exclude the usual mistakes of entering consolidations insteda of nodals. There is NOTHING wrong with these subsets, they are spot on. The problem is when SUBNM is used against them.

SUBNM can grab values from a subset via either index OR element name. If i write SUBNM using the index, it's fine - all i get are the elements that existing within my subset. But, if you do the same using the element name, its completely ignoring the subset and looking at the entire dim. That means my SUBNM returns a value even when checked against a consolidation or worse still, one of the "banned" elements.
I know that this is of naff all use to you since neither version is an option, but... I can't reproduce this in either 8.2.12 or 9.4. Can you post the MDX code and I'll see whether I can reproduce with the same code? (I can bodgy up a dim.)

(NB: I'm not suggesting that there's something intrinsically wrong with the MDX... but there MAY be something funky in the way the MDX is interacting with SubNM. What I'm thinking specifically is if you flip the MDX expression so that it includes only those elements WITH a specific value, would that make a difference?)
"To them, equipment failure is terrifying. To me, it’s 'Tuesday.' "
-----------
Before posting, please check the documentation, the FAQ, the Search function and FOR THE LOVE OF GLUB the Request Guidelines.
User avatar
Steve Vincent
Site Admin
Posts: 1054
Joined: Mon May 12, 2008 8:33 am
OLAP Product: TM1
Version: 10.2.2 FP1
Excel Version: 2010
Location: UK

Re: Another Regression Bug :@ (SUBNM)

Post by Steve Vincent »

MDX:

Code: Select all

{FILTER( {TM1FILTERBYLEVEL( {TM1SORT( {TM1SUBSETALL( [Projects Placing Demand] )}, ASC)}, 0)}, [Projects Placing Demand].[ValidationList] = "")}
I know it's not this tho (i had the same feeling as you) as i've tested it with a simple static subset too, it does exactly the same :roll:
If this were a dictatorship, it would be a heck of a lot easier, just so long as I'm the dictator.
Production: Planning Analytics 64 bit 2.0.5, Windows 2016 Server. Excel 2016, IE11 for t'internet
User avatar
Alan Kirk
Site Admin
Posts: 6606
Joined: Sun May 11, 2008 2:30 am
OLAP Product: TM1
Version: PA2.0.9.18 Classic NO PAW!
Excel Version: 2013 and Office 365
Location: Sydney, Australia
Contact:

Re: Another Regression Bug :@ (SUBNM)

Post by Alan Kirk »

Steve Vincent wrote:MDX:

Code: Select all

{FILTER( {TM1FILTERBYLEVEL( {TM1SORT( {TM1SUBSETALL( [Projects Placing Demand] )}, ASC)}, 0)}, [Projects Placing Demand].[ValidationList] = "")}
I know it's not this tho (i had the same feeling as you) as i've tested it with a simple static subset too, it does exactly the same :roll:
I've only been able to do a rudimentary set of tests (I'm actually lying in bed with the notebook hooked into the Internet at the moment which set a new low in "taking work home" even for me), but I still can't reproduce it in 9.4... meaning that it may well be 9.0-specific.

Somewhere in the deep dark recesses of my mind I do seem to recall a problem with using SubNm with dimensions which had spaces in their names, but even if that WAS the problem... it's not by 9.4.

Still... you might want to try it on a dim which doesn't have any spaces in the name, at least to isolate whether that's where the problem is. It's a long shot, but alas you don't have much to lose at this point.
"To them, equipment failure is terrifying. To me, it’s 'Tuesday.' "
-----------
Before posting, please check the documentation, the FAQ, the Search function and FOR THE LOVE OF GLUB the Request Guidelines.
User avatar
Steve Vincent
Site Admin
Posts: 1054
Joined: Mon May 12, 2008 8:33 am
OLAP Product: TM1
Version: 10.2.2 FP1
Excel Version: 2010
Location: UK

Re: Another Regression Bug :@ (SUBNM)

Post by Steve Vincent »

Fair point, i tried it on the dimension "Site" and still the same. You may have missed my update in the bugs thread but Cognos have confirmed it was solved somewhere in 9.1, but i can't use 9.1 so basically i'm stuffed and have to do this the long way around. They are going to see if a hotfix for 9.0 can be engineered tho, which was a pleasent surprise :D
If this were a dictatorship, it would be a heck of a lot easier, just so long as I'm the dictator.
Production: Planning Analytics 64 bit 2.0.5, Windows 2016 Server. Excel 2016, IE11 for t'internet
Post Reply