Active Form creating hundred of blank columns?

Post Reply
dwise
Posts: 4
Joined: Fri Oct 02, 2015 5:19 am
OLAP Product: TM1
Version: 10.2.2
Excel Version: 2010

Active Form creating hundred of blank columns?

Post by dwise »

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
tomok
MVP
Posts: 2831
Joined: Tue Feb 16, 2010 2:39 pm
OLAP Product: TM1, Palo
Version: Beginning of time thru 10.2
Excel Version: 2003-2007-2010-2013
Location: Atlanta, GA
Contact:

Re: Active Form creating hundred of blank columns?

Post by tomok »

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.
Tom O'Kelley - Manager Finance Systems
American Tower
http://www.onlinecourtreservations.com/
dwise
Posts: 4
Joined: Fri Oct 02, 2015 5:19 am
OLAP Product: TM1
Version: 10.2.2
Excel Version: 2010

Re: Active Form creating hundred of blank columns?

Post by dwise »

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
User avatar
gtonkin
MVP
Posts: 1192
Joined: Thu May 06, 2010 3:03 pm
OLAP Product: TM1
Version: Latest and greatest
Excel Version: Office 365 64-bit
Location: JHB, South Africa
Contact:

Re: Active Form creating hundred of blank columns?

Post by gtonkin »

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.
Wim Gielis
MVP
Posts: 3105
Joined: Mon Dec 29, 2008 6:26 pm
OLAP Product: TM1, Jedox
Version: PAL 2.0.9.18
Excel Version: Microsoft 365
Location: Brussels, Belgium
Contact:

Re: Active Form creating hundred of blank columns?

Post by Wim Gielis »

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
Best regards,

Wim Gielis

IBM Champion 2024
Excel Most Valuable Professional, 2011-2014
https://www.wimgielis.com ==> 121 TM1 articles and a lot of custom code
Newest blog article: Deleting elements quickly
dwise
Posts: 4
Joined: Fri Oct 02, 2015 5:19 am
OLAP Product: TM1
Version: 10.2.2
Excel Version: 2010

Re: Active Form creating hundred of blank columns?

Post by dwise »

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
lotsaram
MVP
Posts: 3651
Joined: Fri Mar 13, 2009 11:14 am
OLAP Product: TableManager1
Version: PA 2.0.x
Excel Version: Office 365
Location: Switzerland

Re: Active Form creating hundred of blank columns?

Post by lotsaram »

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.
Please place all requests for help in a public thread. I will not answer PMs requesting assistance.
User avatar
Steve Rowe
Site Admin
Posts: 2410
Joined: Wed May 14, 2008 4:25 pm
OLAP Product: TM1
Version: TM1 v6,v7,v8,v9,v10,v11+PAW
Excel Version: Nearly all of them

Re: Active Form creating hundred of blank columns?

Post by Steve Rowe »

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.
Technical Director
www.infocat.co.uk
dwise
Posts: 4
Joined: Fri Oct 02, 2015 5:19 am
OLAP Product: TM1
Version: 10.2.2
Excel Version: 2010

Re: Active Form creating hundred of blank columns?

Post by dwise »

Hi Steve

No PMR raised as far as I know.

Cheers
David
jkassier
Posts: 6
Joined: Mon Sep 01, 2008 4:29 am

Re: Active Form creating hundred of blank columns?

Post by jkassier »

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!
User avatar
Steve Rowe
Site Admin
Posts: 2410
Joined: Wed May 14, 2008 4:25 pm
OLAP Product: TM1
Version: TM1 v6,v7,v8,v9,v10,v11+PAW
Excel Version: Nearly all of them

Re: Active Form creating hundred of blank columns?

Post by Steve Rowe »

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
Technical Director
www.infocat.co.uk
babytiger
Posts: 78
Joined: Wed Jul 31, 2013 4:32 am
OLAP Product: Cognos TM1, EP, Analyst
Version: 10.2.2
Excel Version: 2013
Location: Sydney AU

Re: Active Form creating hundred of blank columns?

Post by babytiger »

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.
MK
Wim Gielis
MVP
Posts: 3105
Joined: Mon Dec 29, 2008 6:26 pm
OLAP Product: TM1, Jedox
Version: PAL 2.0.9.18
Excel Version: Microsoft 365
Location: Brussels, Belgium
Contact:

Re: Active Form creating hundred of blank columns?

Post by Wim Gielis »

Best regards,

Wim Gielis

IBM Champion 2024
Excel Most Valuable Professional, 2011-2014
https://www.wimgielis.com ==> 121 TM1 articles and a lot of custom code
Newest blog article: Deleting elements quickly
Post Reply