TM1 Picklist vs Excel Drop Down in Active form

[tosca1978]

Hi All,

Using 9.5.2 & excel 2007.

I have a couple of active forms where the TM1 picklists appear in excel just as they do in the cube viewer. However, every now and then the list will be full of "0"'s in the active form. I have no idea what triggers this, but I have to close the active form and excel and re-open again before it will clear. This only happens say 1/20 times the active form is opened but even so it will raise concerns about stability. The picklists are string values (where an end user will select a profile name for the phasing of a budget).

I wondered if
a) anyone else has experiences this and what could possibly be the reason
b) if what I am about to suggest it completely against best practice:

I could create an excel drop down list in it's place (this should copy down the rows with the TM1RPT formula) - the source would be a hardcoded list in hidden cells (the list SHOULD never change). This column could be the one on display for the end user to pick the profile. Then have a hidden column with a DBSS formula in sending the profile picked to the cube for it to calc.

Is this a stupid idea?!

Cheers

[tomok]

No, it's not a stupid idea because this is essentially how you used to have to do this before they added the pick list functionality. They way I used to do it as add data validation to the actual cell that contains the DBRW and tie that to a range in a hidden tab called "Lists". I would put all my drop-down lists in this tab. If the source of the drop-down was a dimension, or subset of a dimension, I would build it with SUBNM formulas. If not, I would hard code it. Works fine. Just make sure to make the first item in the list an empty string so the user can pick a blank item as this will be the only way to "erase" an existing string from a cell.

[tosca1978]

Hi Tomok,

thanks for your reply. Sounds like good advice (as usual). I'm trying to implement this now. So far I have removed the picklist from the element attributes on the DIM that contained it. I have checked and it no longer appears in the cube or active form.

I have created the data validation directly on the DBRW cell within the active form - using hardcoded list as it's source for now. Upon TM1 rebuild the data validation is wiped out of the cell and not copied down. I have tried inserting a new column within the active form with no DBRW in the top cell (the first TM1RPTROW of the active form) and completed the data validation. Again on TM1 Rebuild the data validation disappears.

I've tested this in a brand new workbook with a new active form and the same happens. Is this because TM1RPTROW does not like data validation? Or is it not supported in 9.5.2? I know that I have used both TM1 and excel formulas in the top rows before and they all get copied down no problem. I've tried looking to see if there is a config setting but cannot see one.

Any advice would be greatly received.

Cheers

[lotsaram]

You have to set the data validation in the hidden format area at the top of the active form, not on the 1st data row.

[tosca1978]

Lotsaram,

genius! Working perfectly!

Thanks very much.

[tomok]

You have to set the data validation in the hidden format area at the top of the active form, not on the 1st data row.

Sorry, forgot to mention that. Data validation is treated as "formatting" and everything related to formatting gets set from the format area at the top of the active form, not from the first row in the active form report area. Bottom line is that anything you set in Excel via a right-click and Format Cells (plus data validation and conditionalo formatting), must be done in the format area at the top, not the first line of the report.

[tosca1978]

No worries Tomok - you've been a great help as usual.

[njaroski]

How do you create the data validation in formatting range? I am trying to create a picklist for two different title dimensions

[tomok]

How do you create the data validation in formatting range? I am trying to create a picklist for two different title dimensions

You unhide the formatting range and apply data validation to the appropriate cells. You do understand I'm talking about Excel data validation don't you? If you don't know how to do that Google it, I provide TM1 help, not Excel help.

[rmackenzie]

I have a couple of active forms where the TM1 picklists appear in excel just as they do in the cube viewer. However, every now and then the list will be full of "0"'s in the active form. I have no idea what triggers this, but I have to close the active form and excel and re-open again before it will clear. This only happens say 1/20 times the active form is opened but even so it will raise concerns about stability. The picklists are string values (where an end user will select a profile name for the phasing of a budget).

I wondered if
a) anyone else has experiences this and what could possibly be the reason

Yes, I have experienced this and quite agree that is annoying and generates a lack of confidence in the functionality. One time it was resolved due to solving an issue with element security preventing WRITE access to the cell (not the ability to read the picklist). Another time it was resolved by correcting an error in rule on the cube.

b) if what I am about to suggest it completely against best practice:

I could create an excel drop down list in it's place (this should copy down the rows with the TM1RPT formula) - the source would be a hardcoded list in hidden cells (the list SHOULD never change). This column could be the one on display for the end user to pick the profile. Then have a hidden column with a DBSS formula in sending the profile picked to the cube for it to calc.

Is this a stupid idea?!

It's a great idea and as tomok mentioned it was the way to achieve this functionality prior to picklists. However, I'd encourage you to try and solve the picklist problem as being able to define the dropdown list on the server-side will bring you a lower overhead on report maintenance in the future if the number of reports using that dropdown grows.