TM1 Forum

Hi

First time poster so please forgive me if I break any forum rules/etiquette.

We are using TM1 10.2.2 on Excel 2010 and I have noticed an issue with active forms I've created recently: on rebuild the active form seems to generate blank columns up to column ALL. When I say blank I mean that Excel thinks there is something in the columns, so when you go Ctrl+End the active cell ends up way over in column ALL. Because of all this blank space the Excel file size becomes 10 times larger than it should.

I can delete the blank columns and re-save the file which brings the file size back to what it should be, however when the active forms are rebuilt the blank space is recreated and the file size balloons again.

I've tried searching for similar problems on tm1forum and ibm but have had no luck so far so any suggestions/help would be greatly appreciated!

Thanks
David

Look in the named ranges in your Excel file for the range TM1RPTFMTRNG. It should look something like =Sheet1!$B$1:$F$5. In this example, TM1 is going to treat columns B through F as part of the active form, meaning it's going to copy down anything found in the first row of the active form down to the end. I'm guessing the range in your TM1RPTFMTRNG goes too far to the right. Just go into the name manager in Excel and fix it to only include the columns you want, then save the file.

Hi Tomok

Thanks for the reply. I had already checked the TM1RPTFMTRNG range and it is correct.

As an example on one active form the TM1RPTFMTRNG is from $C$2:$Q$9 but the last active cell (Ctrl+End) changes to column ALL on rebuilding the active form, while the TM1RPTFMTRNG range doesn't change.

I had already checked all other named ranges but they are all correct.

Any other suggestions?

Thanks
David

I had a similar issue a while back with some reports and deleting the unused columns, saving and re-opening the workbook did not seem to resolve.
What I recall doing was adding something in column XFC then isnerting a column before XFC to push contents to last column, saving, closing, reopening then deleting columns again. Ctrl+End (UsedRange) was as exspected.

Hello David,

This will not be the only issue with Active forms, frankly.
I have seen this one come up a lot of times as well.

I will try the advice of gtonkin and see if that helps. Thanks.

TM1RPTFMTRNG does not seem to cause problems in Active forms, in my opinion.

By the way, column ALL in an Excel sheet is column number 1000,
so I wouldn't be surprised if someone inserted hard-coded stuff in the TM1 addin code.

Wim

gtonkin wrote:I had a similar issue a while back with some reports and deleting the unused columns, saving and re-opening the workbook did not seem to resolve.
What I recall doing was adding something in column XFC then isnerting a column before XFC to push contents to last column, saving, closing, reopening then deleting columns again. Ctrl+End (UsedRange) was as exspected.

Thanks for your suggestion, unfortunately it didn't work for me. It was ok initially, after re-opening and deleting columns. But after saving, closing and reopening, when the active form was rebuilt it used up the blank columns to column ALL again.

Wim - looks like you are right, I've since found some posts where someone was trying to create a report with more than 1000 columns (who knows why they needed a report that big...) and it wasn't rebuilding anything past column 1000.

I think I'll log this with IBM.

Cheers
David

This is a bit of an annoying one. I admit to never having logged it as a bug with IBM because quite frankly dealing with IBM support does my head in. I always get around it by deleting the excess columns (and rows) then saving the file without first rebuilding the report and only then publishing (or updating to) the application tree. Then at least the file size and performance for the "officially published and sanctioned" TM1 reports is optimal.

In a narrow and more perfect view of the world If a user then subsequently opens from the application folder or exports from TM1 Web and then does a local Save As which results in Excel file bloat then it's their problem. Of course the file bloat due to the extra columns shouldn't happen. Anyway I wish you luck with getting support to recognize that this is in face a bug so that engineering can actually fix the issue.

Sorry to necro an old thread.

Does anyone know if this got raised as a PMR? We're pushing with IBM atm on this since we it is also impacting TM1 Web when an AF is published.

Old news I guess but frustrating that obvious stuff like this doesn't just get resolved.

Hi Steve

No PMR raised as far as I know.

Cheers
David

Hi all.

Apologies for jumping onto this thread. I have now suddenly also come across this issue at 2 different client sites. Am trying to check with IBM whether this has been lodged or resolved in updated versions. Has anyone had any luck confirming this? The rebuild clearly makes a single cell in column 1000 and in a row number exactly double the number of rows of the active form, active. If this single cell is cleared, the last active cell returns to the end of the active form...

Thanks very much for any feedback!

We've been told that it won't be fixed as it is in perspectives and they seem to think that it isn't a big deal anyway.

Our PMR 10166,019,866

Sorry for bringing this old thread back from the dead.

2 years later, looks like the issue still exists, with the number of columns expanded to 1000 upon activeforms rebuild. Nothing we can do there.

In terms of the file size though, I have found a way to minimise the file size. Because that's my main issue with the additional blank columns added.

Steps:
1. Highlight all the blank columns (columns shouldn't have any data), and click on the "Clear All" (Home ribbon --> Editing section --> Clear --> Clear All)
2. Save the file.

This will clear all formatting and data from the highlighted columns. And when saving the file, Excel seems to ignore those values, even after another activeforms rebuild.