TM1 Web vs Excel view inconsistencies

[jo_hen]

Hi all

Newby here - funnily enough IBM don't promote this site and I never even knew it existed!

Apologies if this has been covered previously but I've had a quick look and haven't spotted anything.

I have an active form that works fine in Excel but for some reason when publishing it to the Web, I get 3 issues:

1) The column widths are nothing like what they are on the excel version - they are huge with lots of blank space at the end. If you try to shrink the column in Excel it just chops the end off
2) Custom formats don't seem to be applying properly, i.e. at the consolidated level I've applied a custom format in the format area that adds "sub total" to the beginning of the element. This works fine in Excel and is just missed off entirely in Web. I thought maybe it wasn't compatible but the custom format was already there for another item so I just amended it.
3) For some reason, data seems to be missing and it only seems to be on columns that is a manually added column which adds 2 TM1 columns together. I.e. employee costs = indirect costs + direct costs. Some rows show blank and others are correct, so it's not even consistent.

I've logged it with IBM and the first response I got back was that it may be a known issue. Then I got that they couldn't recreate it and to send a stand-alone test case with only the problem items in. I've done this but all has gone quiet now.

Has anyone else seen this before/know of a resolution?

We're on v9.5.0 on Excel 2003.

Thanks

[jim wood]

I haven't really used active forms as I'm still stuck with 9.1.3. This version however does include dynamic slices. When using these the active slice uses a set area within excel. I wonder if active forms works the same way?

Jim.

[sivan307]

I have seen something similar happen to me a few days back. The problem is solved now. I don't know what I did that made the format go off in the first place & I don't know what I did that solved the problem. I hope someone tells us what might have happened.

[tomok]

1) The column widths are nothing like what they are on the excel version - they are huge with lots of blank space at the end. If you try to shrink the column in Excel it just chops the end off

Do you have any hidden columns inside the body of the active form? If you have any hidden columns it throws off the width formatting and will produce unpredictable results. A workaround is instead of hiding the columns, shrink their width to .5 and change the content color to the same as the background. The data won't show and TM1 Web will not mess up the rest of the report.

2) Custom formats don't seem to be applying properly, i.e. at the consolidated level I've applied a custom format in the format area that adds "sub total" to the beginning of the element. This works fine in Excel and is just missed off entirely in Web. I thought maybe it wasn't compatible but the custom format was already there for another item so I just amended it.

I'm confused by this. Where did you add this "sub total" and what do you mean by it adds "sub total"? Is that done via a TM1 formula or did you manually take what was in the column and change it to concatenate the characters "sub total"? I don't believe a format range name can be a formula (I'm talking about the format range at the top, not the actual active form area). Make those literal values and try again.

3) For some reason, data seems to be missing and it only seems to be on columns that is a manually added column which adds 2 TM1 columns together. I.e. employee costs = indirect costs + direct costs. Some rows show blank and others are correct, so it's not even consistent.

Were these "manually added columns" inserted inside the range defined by TM1RPTFMTRNG or just added to the right? If you manually add columns to the right of an existing active form you have to manually modify the definition of TM1RPTFMTRNG. Just like any other named range inside Excel, adding rows our columns inside the range will automatically expand the definition of the range while rows and columns added just to the left, right, above, or below a named range will not cause the range definition to expand to include them, you have to change the definition manually.

[lotsaram]

One thing that I would add is to ensure that any report being built in Excel for end consumption in TM1 Web must be built with the excel "zoom" setting at 100% otherwise the appearance in web can be quite different than excel as the tm1excelservice uses the 100% zoom seting regardless of the setting saved with the excel file.

[jo_hen]

Thanks guys, will have a look at all the suggestions and update.

[jo_hen]

1) The column widths are nothing like what they are on the excel version - they are huge with lots of blank space at the end. If you try to shrink the column in Excel it just chops the end off

Do you have any hidden columns inside the body of the active form? If you have any hidden columns it throws off the width formatting and will produce unpredictable results. A workaround is instead of hiding the columns, shrink their width to .5 and change the content color to the same as the background. The data won't show and TM1 Web will not mess up the rest of the report.

2) Custom formats don't seem to be applying properly, i.e. at the consolidated level I've applied a custom format in the format area that adds "sub total" to the beginning of the element. This works fine in Excel and is just missed off entirely in Web. I thought maybe it wasn't compatible but the custom format was already there for another item so I just amended it.

I'm confused by this. Where did you add this "sub total" and what do you mean by it adds "sub total"? Is that done via a TM1 formula or did you manually take what was in the column and change it to concatenate the characters "sub total"? I don't believe a format range name can be a formula (I'm talking about the format range at the top, not the actual active form area). Make those literal values and try again.

3) For some reason, data seems to be missing and it only seems to be on columns that is a manually added column which adds 2 TM1 columns together. I.e. employee costs = indirect costs + direct costs. Some rows show blank and others are correct, so it's not even consistent.

Were these "manually added columns" inserted inside the range defined by TM1RPTFMTRNG or just added to the right? If you manually add columns to the right of an existing active form you have to manually modify the definition of TM1RPTFMTRNG. Just like any other named range inside Excel, adding rows our columns inside the range will automatically expand the definition of the range while rows and columns added just to the left, right, above, or below a named range will not cause the range definition to expand to include them, you have to change the definition manually.

I've had a look at the suggestions and number 1 has worked fine, thanks. I've unhidden the hidden columns and changed the formatting and looks much better.

No.2 - it's the custom format applied in the format range at the top that I'm meaning. If you go into format and then custom format - in the type I've put "Sub Total "@ which is just an amendment to the custom format that was already in there. It was "-"@ previously which put a "-" at the front of the element.

No.3 - I've inserted the columns within the main range, not to the left or right of the range so do you have any ideas why the figures aren't being carried across to TM1 Web? You can see the figures no problem in Excel, just not in Web.

Thanks again.
Jo.

[paulsimon]

jo

I believe that custom formats like the sub total one are not supported in TM1 Web. I guess that the answer is to add a column and use a format to concatenate the cells.

With regard to the missing columns, check that all the range names prefixed by TM1 have the correct ranges.

The only row that matters as far as DBRW or other Formulae go in an Active Form is the first one, since that is copied down to all the other rows. Do you definitely have the additional formulae in that row?

Have you used Rebuild Active Sheet rather than just Re-Calculate?

Sorry if you have already tried these

Regards

Paul Simon

[tomok]

No.2 - it's the custom format applied in the format range at the top that I'm meaning. If you go into format and then custom format - in the type I've put "Sub Total "@ which is just an amendment to the custom format that was already in there. It was "-"@ previously which put a "-" at the front of the element.

I'm going to assume that you are talking about using Excel conditional formatting functionality inside the active form format section at the top of your report. Yes, you can do that but please be advised that only the very rudimentary conditional formatting, as found in Excel 2003, is supported in TM1 Web. Most of the additional conditional formatting introduced in Excel 2007 is not supported in TM1 Web. However, that shouldn't stop you from doing what you want because you can build your own "conditional formatting" range at the top of the report and use that lookup table to format the report conditionally, based on that range. When you create an active form from scratch it builds that sample formatting section for you, based on the element levels in the dimensions. If you look at the formulas in column A down in the report section you see those IF statements that look at the element levels and return a value, based on element levels, and then use that value as a lookup to the format section to determine how to format that particular row. YOU DON"T HAVE TO USE THOSE GIVEN FORMULAS. You can create your own Excel formulas that create a value in column A and then use that as a lookup to format section. You also can create you own format section to have as many different formatting rows as you want. With these two things you can custom create your own formatting to have a report look EXACTLY how you want it to look based on just about anything you want. The only requirement is you have to be a little bit creative in your thinking.

[vins]

I looked a lot on google to find out the reason and i came to the open thread which has similar issue.


For the below third pointed mentioned by you.

3)For some reason, data seems to be missing and it only seems to be on columns that is a manually added column which adds 2 TM1 columns together. I.e. employee costs = indirect costs + direct costs. Some rows show blank and others are correct, so it's not even consistent.

I read through all the posts and it was really helpful. However, I am facing similar kind of issue now. My rows are in the active form range.
When i open my report in tm1 web it shows me correct data. After rebuilding the active forms its not showing the correct data.

I have Jan, Feb , Mar and so on Columns for Cost distribution per month and these are TM1 rule based columns.

Can any one please give some input on this.

Thanks in advance !!!

[declanr]

I looked a lot on google to find out the reason and i came to the open thread which has similar issue.


For the below third pointed mentioned by you.

3)For some reason, data seems to be missing and it only seems to be on columns that is a manually added column which adds 2 TM1 columns together. I.e. employee costs = indirect costs + direct costs. Some rows show blank and others are correct, so it's not even consistent.

I read through all the posts and it was really helpful. However, I am facing similar kind of issue now. My rows are in the active form range.
When i open my report in tm1 web it shows me correct data. After rebuilding the active forms its not showing the correct data.

I have Jan, Feb , Mar and so on Columns for Cost distribution per month and these are TM1 rule based columns.

Can any one please give some input on this.

Thanks in advance !!!

When you rebuild an active form, the formula that is in the top row is copied to all the rows that are rebuilt.
Is it actually the case that you have 1 simple formula you are applying across the board or are you doing something such as (assuming row 10 is your top row) if you saved it with D10's formula being (=C10) and D11's formula being (=B11+C11) then when you rebuild the form it will result in the following:
D10 = C10
D11 = C11

Is this the sort of problem you are encountering? It is very possible that it isn't simply because Active Forms have a lot of little complexities to them - in order for us to be able to provide the adequate level of assistance it would be extremely beneficial if you could provide:

• The formula that is in each column.
• The paramters of the named ranges created by the active form
• A screenshot
• Basically anything and everything relating to the issue