Pick Lists

[Steve Rowe]

I've been using pick lists for the first time, very exciting!

Some observations / queries.

1. They seem too slow to use in the cube viewer on longer lists, though an Excel slice with pick lists seems fine.
2. Once in Excel if the string cell contains a numeric value, the picklist returns 0 if the picklist item starts with text, if the item starts with a number you get the numbers until a text value is found.
3. Again in Excel if you set up the picklist with a blank as described in the guide then when you select the blank this overwrites the DBRW formula...
4. In vanilla form they seem pretty stable but once I started using some worksheet events to do some extra work they went wrong pretty regularly.

Working 951 with some but not all HFs.
If anyone has found any workarounds to the above I'd like to hear of them.

Cheers,

Steve

[Andy Key]

Don't worry, the excitement will soon wear off.

I've pretty much stopped using them due to the lack of speed and the poor handling of blank.

I can tell you (cos I was reading the doco yesterday to see if pick lists are supposed to work in Contributor) that the official line from IBM on number 3 is:

In TM1 Perspectives slices and Active Forms, do not click the null value in a string cell. This will delete the DBRW formula from the cell and you will no longer be able to retreive data for that cell from the IBM® Cognos® TM1® server.

Which roughly translates as:

Yes, we know that Pick Lists are broken. But if we write down that they are broken, we won't have to fix them.

[lotsaram]

Broken for blanks and and quirks when using picklists in excel aside (i.e. typical problem of excel trying to "help you out" by converting certain types of strings to numeric values) I haven't found picklists to be too bad, most of the time they are great in fact. The one thing you have to watch out for is certainly to limit the length of the picklist to less than 100 items or so, otherwise performance certainly suffers.

[Steve Rowe]

The blank thing is a bit of an issue but not too much of a drama (unless you actually need to use it!)
I've found the performance good in Excel, the longest list I was working with 800 and it was fine. In the cube viewer this a complete non-starter though.
As usual the frustration comes when you need to add the extra 10% of polish that you need to put something into production and it started to crumble. I added some code into the worksheet_change event and it just went wrong....
Cheers

[Martin Ryan]

I've had a quick Google but can't find an obvious answer. I'm just using picklists for the first time myself at the moment. I have a problem where once a user chooses something they can't set it back to blank. So if you want to use a subset or dimension as a picklist, but also allow the user to select blank, what's the PickList attribute syntax?

Martin

[rmackenzie]

I have a problem where once a user chooses something they can't set it back to blank. So if you want to use a subset or dimension as a picklist, but also allow the user to select blank, what's the PickList attribute syntax?

My experience is that it is not possible where you have the source as a dimension or subset. I've had people recommend the workaround of having an element in your dimension such as . or _ for the purpose of setting the 'blank' but this is a bad workaround IMO. I raised the issue to IBM ages ago but it has slipped down the back of the sofa, I think...

Here's some other chat about it:
http://www.tm1forum.com/viewtopic.php?f=3&t=2000

[Martin Ryan]

Yeah, I've done a workaround like that. Not ideal - not least because my rule is convoluted enough as it is - but I guess it'll have to do.

Ta,
Martin