TM1 Forum

Hi,

I have an issue in opening tm1 websheet excceding 100KB in size through tm1 web. I am preparing some excel templates for data input and publishing them under application folder for users. My input template has tm1 worksheets formulas for validations and data submit (DIMIX,ELLEV,DBRA,DBSS,DBS etc.) I am able to use the excel template opened through Excel 2010 and do the data input but when i try to open the same using TM1 web, i am getting timed Out error. This is happening for excel which are only more than 100KB in size (approx size). I have only 2 sheets in my workbook (Input template)and each sheet has 20 rows and 20 columns but the excel workbook size is nearly 190KB which is high. I tried deleting the empty cells from the excel but i was unsuccessful in reducing the size. I also tried to save as the excel template to HTML and then do reopen in excel but i am looking all the formula there. Please help me with your suggestions to reduce the size of the excel template with all the formula retained.

Thanks
Jagan

Hi I doubt your issue is size that sounds extremely low for a workbook size I would look more at your web server / firewall data transfers etc

If your really trying to shrink try this

Save your workbook to excel 95 file format it will kick out nearly every format you have it will also bloat in size.
Close it down
Open it again and resave it to the correct current xlsx

You should of lost a lot of memory usage


Goodluck

Hi,

Check that you do not have any unsupported functions in your workbook. Check that you don't have any #REF named ranges, links to external workbooks, unsupported controls etc.
Something as simple as a named range which cannot be resolved will stop your workbook from loading.

dwigsy wrote:Hi,

Check that you do not have any unsupported functions in your workbook. Check that you don't have any #REF named ranges, links to external workbooks, unsupported controls etc.
Something as simple as a named range which cannot be resolved will stop your workbook from loading.

Perhaps there is some vba in in the sheets? ... Which can not be used in TM1 Websheets....

Hi,

Thanks all for your responses.

Now I see the issue is with a hidden sheet for picklist ({PL}PickLst) where i have 43K elements for one of the measure element ( Ie. from dimension picklist - Level 0 for Cost center) and this is taking lot of time to load the websheets in TM1 Web. If i delete the sheet for picklist from the workbook and publish it to tm1 web, i'm not getting any values for my 2nd sheet which has DBRW formula in websheets. Things are working fine with Perspectives. I am thinking as a workaround that i will have to remove the picklist from the measure element.

Please let us know for any suggestion for the workaround.

Regards
Jagan

jagan_Palanisamy wrote:Hi,

Thanks all for your responses.

Now I see the issue is with a hidden sheet for picklist ({PL}PickLst) where i have 43K elements for one of the measure element ( Ie. from dimension picklist - Level 0 for Cost center) and this is taking lot of time to load the websheets in TM1 Web. If i delete the sheet for picklist from the workbook and publish it to tm1 web, i'm not getting any values for my 2nd sheet which has DBRW formula in websheets. Things are working fine with Perspectives. I am thinking as a workaround that i will have to remove the picklist from the measure element.

Glad you write it yourself, because I can't see people using a picklist of that size.
Is this what the users require?

Yes, that was the requirement initially to have a list of all level 0 cost centers for planning the employee transfers from one cc to another (Our module is workforceplanning). It looks like i can't have a large list of elements for the picklist and access it through TM1 websheets. I will go with the workaround for now.

Regards
Jagan

What good is a 40,000+ member picklist anyway? Who in their right mind would want to scroll down a list of that size on a transfer form. It sounds like you are letting them transfer to ANY existing cost center. A picklist is good for narrowing down that list to only those centers that are valid to accept transfers. In your case I would make them type in the cost center and then have a check next to the cell to show whether what they typed in is valid (by changing the cell color to green or red).

jagan_Palanisamy wrote:to have a list of all level 0 cost centers for planning the employee transfers from one cc to another

Although Excel drop downs are fine for small lists, as other have pointed out a 40,000 pick list is useless! Why not use the standard TM1 Subset Editor for selections? You can use subsets tied to users, security and other standard selector tools?

The performance won't be that great either.

Depending on your set up, if your cost centres roll up into a more manageable hierarchy you could set up a couple of extra columns and have a user first choose a "top level group" which would then (via rules) decide the subset based picklist for the next column... and you could have a few levels (as many as you need) before you break all the picklists down into manageable lists.

Having a "top level group" like LOB or Sub LOB as another column to filter out the level 0 through rules sounds like a good solution. I will try this out and see if i am able to get a shorter list for the picklist. If not, i will rather go back to have the cost code typed in and have another column to display its validation status.

Thanks all for posting your idea.