Active Form - Is editable

[pmakulski]

I have a set of rules that determine if a field is open for editing. (If TRUE, STET, 0);

In Perspectives, this nicely shows white if editable or grey is not editable.

But in an Active Form they look the same.
I don't see a Spreadsheet formula for testing if a cell is editable (othwise I could probably use an Excel conditional format.

Can this be done?

[Martin Ryan]

Unfortunately there's no way of showing this in Excel - active forms or plain old slices. At least not without some serious ground work - e.g. creating a second cube that is populated by the "cellisupdateable" TI function to flag whether the original cube cells are calculated or input then using that in Excel to do conditional formatting.

It's only natively possible in Perspectives or Contributor.

Martin

[pmakulski]

Bummer.

This should be on the enhancements list.

Thanks for confirming.

[Martin Ryan]

I think it'd be a big deal to implement even assuming "cellisupdateable" is available in the api. The only way that I think you could make it work would be conditional formatting using a hidden sheet. But with conditional formatting you can't reference other sheets (or at least not in 2003, it may be possible in later releases of Excel) which then means you'd have to run a batch process that runs over all the cells and checks them, then colours them appropriately.

Also, IBM are trying to move people away from reporting in Excel and using BI or the Web, so I suspect an enhancement request for this is likely to fall on deaf ears.

What you mind get a bit more traction with is an enhancement request for TM1 tools (discussion post here). Assuming cellisupdateable is available in the api we might be able to work it in so that at least when you do a trace on the cell it can indicate whether its calculated.

Martin

[Andy Key]

Depending on the complexity of your Active Form, and if you have a way of determining which columns need to be locked and unlocked, you should be able to use the formula in the format column to reference an appropriately formatted row from the format area.

An example of this for Forecast input, where you can use the same report for all Forecasts, but the number of columns that are protected (because they are displaying Actual data and thus shouldn't be editable) changes based on an attribute on the appropriate Forecast version.

[Alan Kirk]

What you mind get a bit more traction with is an enhancement request for TM1 tools (discussion post here). Assuming cellisupdateable is available in the api we might be able to work it in so that at least when you do a trace on the cell it can indicate whether its calculated.

{Multiple choking sounds from the other side of the Tasman} Oh thanks good buddy!

Martin makes this offer secure in the knowledge of who has to write most of the API code in TM1 Tools.

No, seriously, that thought has crossed my mind before. It is technically possible; the relevant function is TM1ValIsUpdatable. However... while it's viable for a single value (creating a function that Martin could integrate with his tracer tool (amongst other places, including as a worksheet function) was indeed where my thoughts had headed on that... I'd be a bit concerned about the speed if it was used in the way described in this thread. That is, to drive the formatting of a whole range of cells. Each element would have to be whacked into a value capsule, you'd then need to use those to get the values, you'd then need to call the function to test thr R/W status of it...

Admittedly I haven't tested this at this time, but I'll be surprised if it doesn't slow down a big slice by a pretty big margin.

That notwithstanding, I'll formally add the function into the Tools To Do list.

[pmakulski]

Thanks for the suggestions.

In the absense of a "Iseditable" TM1 worksheet function, I made a workaround.

Since I only allow input in certain periods, certain measures and only at the leaf level,
some judicious combining of ELLEV()=0 and Period>=a starting period and Measure in the column is an input measure
I was able to use Excel conditional formating to shade the areas the use can input to.