TM1 Forum

I've set up a picklist as a workaround to provide a dynamic dropdown customer list within a Revenue Planning spreadsheet

It's working great except for one major issue...

As soon as I protect the worksheet, I lose the dropdown functionality on the cells that have the Picklist DBRW formulas in them
These particular cells have been unlocked so one would assume that the drop down functionality would be available even if the worksheet has been protected

Has anyone experienced this issue? If so, any thoughts on a workaround since the picklist functionality is a great way of providing a dynamic list of elements without developing any VBA code

The TM1 environment is 9.5.1 and Excel 2007

Solanna

Hi Solanna:

I recently completed a Spending Plan spreadsheet for the users, and the task is to to create a relationship between parent element from a dimension in my title header and the child element from the same dimension in my rows. I did create attributes on the elements and built a dynamic subset (mdx query) filtering on those attributes?

I was able to complete the task, but one point caught my attention in your post. that is "since the picklist functionality is a great way of providing a dynamic list of elements without developing any VBA code".

Can you please explain on how to create(if this can be used as a solution to my issue) a picklist for displaying a dynamic list of elements.

Thanks for your time in advance.

Kal
The TM1 environment is 9.5.1 and Excel 2007

Kal - I'd suggest you read the manuals on creating picklists. The picklist attribute and picklists derived from picklist cubes can be rule defined, therefore depending on inputs in other cells it is quite simple to set up dynamic picklists by logical tests that then switch out different picklist dimensions, subsets or static (rule defined) lists. The only thing required to set this up is planning and a good handle on writing string rules. Picklists is definitely the way to go and will work in any TM1 UI, as a customer with 9.5 I would be very unhappy with a consultant who spent additional development time crafting an Excel/VBA solution when picklists were available.

Solana - is the workbook (as opposed to the worksheet) also protected? The way TM1 picklists are implemented in Excel is that a class module in Perspectives monitors the SelectionChange Worksheet event if a cell is selected that contains a DBRW which has a picklist defined then Perspectives inserts a new hidden worksheet into the workbook named "{PL}PickLst" and creates a named range in the hidden sheet called "TM1PICKLIST" and sets the data validation properties of the active cell to "List" populated by "TM1PICKLIST". If the workbook is protected then this process will fail as Perspectives won't be able to insert the worksheet or add a new named range. This could be your problem.

Note: I wrote the above, then I thought "why don't I test it out?" My workbook was definitely not protected and the picklist cells were unlocked. As soon as I protected the worksheet (even with no password) the picklist regeneration failed. I would call this a bug and encourage you to log it with IBM. There should be no reason from what I can tell that Perspectives should not be able to regenerate the picklist on SelectionChange under these circumstances. Probably there is a test in the picklist validation code in Perspectives whether the worksheet is protected and if this is true then the routine exits without attempting anything further (pure idle speculation!) Active forms used to have the same bug (not working on protected sheets even when there was no password) but this has now been addressed so I see no reason why it can't be fixed for picklists also. Frankly it is disappointing this was overlooked.

Of course the simple workaround is to NOT protect the worksheet which looks like your only option until a service pack addresses this issue. I was a hack Excel developer long before I was a TM1 developer so I am definitely in the camp that worksheet protection on reports and data input templates has its place. ESPECIALLY if the worksheets are also published in the Apps folder as websheets (users find it very disconcerting to be able to select a report heading on the web and clear or change the text for example!) Unfortunately Applix/Cognos/IBM just don't seem to "get" this and support for using worksheet protection in Excel models with TM1 has always been quite limited.

Lotsaram,

Thank you for running the same tests and validating my findings...

To answer a couple of your questions/statements...
No, the workbook was not protected
Yes, I was aware of the hidden worksheet as well as the range name

After coming across this protection issue, I came up with another workaround that would have worked brilliantly except for the fact the range in the Picklist sheet gets overwritten in Column A and I need two different picklists

There are a couple of Picklists I need to create within the same workbook so I figured I could have a couple of cells on a hidden Control sheet that would reference the Picklists
This would then create the range within the Picklist sheet
I then could create a range name using the offset function which would creat a dynamic range and finally I would use Data Validation for my dropdowns which WORK when the worksheet is protected
Alas, the range on the Picklist sheet gets overwritten when you go from one picklist to the other so it just becomes a jumbled mess of elements
Gotta love that IBM Cognos programming

So in the end I have had to accept the fact that I cannot have a fully dynamic element list
However, I do have a semi-dynamic one where I have a hidden control sheet, utilizing the subsiz function and submn functions as well as a dynamic subset and the Excel offset function I mentioned earlier
I then created a Range Name using the offset function and a Data Validation drop down box
The only draw back is that I have copied 100 rows of subnm functions in my control sheet assuming the user community will not outgrow the template any time soon
When the users outgrow it, I will just go into the template and copy some additional rows down since the offset function will capture the range dynamically

I've used this same techique in the past when I was an OutlookSoft developer working with EVDRE spreadsheets
EVDRE to OutlookSoft/SAP BPC is Active Forms in TM1 except they are actually more powerful
Given that I've been developing TM1 models for 17 years and still love it there aren't too many areas where OutlookSoft/SAP BPC will win but in this case it does

As for logging this to IBM I am completely surprised and a bit disappointed that it's been over a year since 9.5 came out and no one else has come across this issue
Unfortunately what that says to me, which I already know is that IBM Cognos is putting absolutely no effort into Perspectives and do not respect the spreadsheet
How very very sad...

On that note, not every organization needs to build dimensions utilizing TI... I personally love to work with TI
But when you have dimensions that are static, the xdi works just fine
Have you created a new dimension using an XDI in 9.5 or 9.5.1?
Fortunately I've built so many dimensions that I know there is a missing column now and that I have to add it
For the newbies out there, how would they know?
How about rules?
While the enhanced rules editor is nice, I still like the XRU
I can have multiple rules files for development purposes within the same workbook
So when the "fake" error appears when I compile the rule I know to ignore it but again it's just a lack of interest on IBM Cognos to fix anything that is broken within the spreadsheet
And last but not least, the Processing Spreadsheet...
While I certainly do all of my ETL with TI, the processing spreadsheet was brilliant for zeroing out a cube
Now I have to create a bunch of throw away views to do it in TI since they broke the processing spreadsheet functionality back in 9.4

For all the new TM1 folks out there, the spreadsheet is still very powerful
The trend is to create all the logic within rules... I don't believe that is necessarily the best approach

I have actually done some development with IBM Cognos EP and certainly the Picklist is a nice feature (one of the few in my opinion)
But you certainly were never using it in a spreadsheet, thus the lack of effort developing it properly within perspectives

And one final note, the Application Folder is so powerful and I don't think most people really understand this
The fact that TM1 Contributor came out with no Application Folder capabilities just boggles the mind
Does IBM Cognos really think that work flow is more important than controlling the planning process?
Again, clueless...

Thanks again for validating my results... it is much appreciated

Solanna

Kal

Just something I picked up from reading your question. If all you want is to pick a parent on your sheet and to then show the children below it, you might get what you want just using the ELCOMP and ELCOMPN TM1-Excel functions. This may be a lot less complex than using MDX or Picklists (I don't think that the latter would give you what you want anyway).

Regards

Paul Simon

PaulSimon wrote:Kal

Just something I picked up from reading your question. If all you want is to pick a parent on your sheet and to then show the children below it, you might get what you want just using the ELCOMP and ELCOMPN TM1-Excel functions. This may be a lot less complex than using MDX or Picklists (I don't think that the latter would give you what you want anyway).

Regards

Paul Simon

The other thing that occurred to me was that if Kal is talking about generating a rowset for data entry in a template as opposed to an in-cell validation list then by far the best way to do this in 9.5 is with in an active form with parametrized MDX in the TM1RptRow function. This requires no VBA and is very effective.

Solana - I agree with you on many aspects of catering better for spreadsheets rather than focusing purely on web front ends but I have to disagree on processing spreadsheets. Yes I did have some models back quite a few years ago that I held off converting to TI due to the exercise in recoding but once done the result and speed is incomparable and I have to say that a processing sheet is a particularly poor and inefficient way to zero out a portion of a cube. There is no need to manually create a whole lot of views to use TI to zero out cube data as a well written parametrized TI can be flexible enough to zero out any part of any cube automatically almost like calling a function.

I do very much agree on Contributor's lack of support for TM1 websheets. Until Contributor supports using websheets and not just views I can't see that is is a viable solution versus an application developed in TM1 web. If you have come from Planning and are used to being tied down to a grid maybe it is not a problem but to anyone from a TM1 or spreadsheet background used to the flexibility it is simply too limiting (both from a UI perspective and for the undesirable trade-offs in cube design to force things into a single view for user interaction requirements.)

XDI - far superior to the dimension editor for small static dimensions, I don't think you'll get too much disagreement on that one around here. XRU vs. the newer rule editor, ... somewhat more difference of opinion.

Back to your solution with SUBNMs, sounds workable but horribly inefficient. However until we get a WAN optimized SUBNM function (or equivalent) there's nothing you can do about that.

I think active forms are the bee's knees I'm interested to know what you think is so great about EVDRE sheets that you can't do with TM1, feel free to PM me to explain.

@lotsaram:

This is exactly what I did when I completed( with help from KIT Forsee) this task of using the parametrized MDX in the TM1RptRow function within the active form & it is working like a charm.

For people who like to know the solution, I created attributes with a Y for all possible parent/child combinations and then used those attributes in the MDX expression using filer by attribute. This expression is used in the TM1RptRow function.

@Paul:

I will try your solution of using the ELCOMP and ELCOMPN TM1-Excel functions.

Thanks lotsaram & Paul for your replies.
Kal

Lotsaram,

Just to clarify the Processing Spreadsheet... I completely agree with your statements but my example was really in regards to development and the quick and dirty way it would zero portions of a cube
I certainly wasn't talking about using them in a Production environment

And yes, certainly I could create a TI process that will "parameterize" the process as I already do this... my point is when you just need to do this quickly with no real thought or development in mind that the processing spreadsheet was just a great option in those circumstances

Regarding my Subnm solution... I personally don't think it is that inefficient... the user will never see them and I don't think there will be a speed issue with them either
What they do provide is the dynamic capability of providing an element list from a dynamic subset

As for EVDRE... I would bet a dollar that Applix took the concept from OutlookSoft since EVDRE was around way before the Active Form
Anyhow, I took a short break from TM1 from 2005 to 2009 working for OutlookSoft and then SAP when we were unfortunately acquired
There wasn't much TM1 work out there locally at the time so I had to jump ship for awhile...
Anyhow, EVDRE which stands for Everest Dynamic Range Exchange, uses the concept of Ranges which I find to be more efficient
Also, there are no formulas in the cells... EVDRE just knows whether you are using DBR's ord DBS's simply by setting a switch
There are mulitple switches available on each worksheet which provides for flexibility as to what you want to accomplish within the report
The concept of EXPAND ONLY is without a doubt one of the best features
Imagine creating a view and then using it in an Active Form... but with the Expand Only option you will not see any data, only a shell of the view
You can then use that same view to populate another cube or section of your cube by entering data into the cells but have them reference the other cube or section
I have suggested this functionality to IBM Cognos but I don't think they get it
You can also use the EXPAND ONLY function to do exactly what I wanted to do with the picklist that I have ended up doing with subnm's instead
The EXPAND ONLY is also dynamic like everything else within an EVDRE report including the columns
While column expansion can be a bit querky at times, it's still available which is not possible within an Active Form
The formatting of data is also much more advanced than what is available in an Active Form
And regarding the expansion of rows and columns, you can actually turn the expansion on and off by specific ranges within your report
So maybe one section you want to expand the rows but another section you don't... it just provides more flexibility

While I am an advocate of the Active Form and I am using them extensively they are still not as functional or powerful as an EVDRE
Given the lack of assumed interest in the spreadsheet I doubt IBM Cognos will be enhancing them any time soon

Hope that give you and everyone else out there just an idea of EVDRE capabilities

Solanna

Solanna wrote: As soon as I protect the worksheet, I lose the dropdown functionality on the cells that have the Picklist DBRW formulas in them
These particular cells have been unlocked so one would assume that the drop down functionality would be available even if the worksheet has been protected

Has anyone experienced this issue? If so, any thoughts on a workaround since the picklist functionality is a great way of providing a dynamic list of elements without developing any VBA code

The TM1 environment is 9.5.1 and Excel 2007

Solanna

This is a known issue with Excel 2003 and 2007. This is a limitation imposed on TM1 Perspectives by Microsoft Excel. This is not a TM1 limitation or defect. Picklist on Protected sheets will work fine with Excel 2010.