TM1 Forum

Hi,

I just got a call from a customer who wants to generate a set of reports based on an active form using the 'Print Report' function in Excel. The problem however is that this option is greyed out in the Excel TM1 menu.
The user is running Excel 2010 on a TM1 9.5.2 local server machine. The user is connected to the server - the Active Form is showing numbers. We've already tried restarting Excel and rebuilding the Active Form. On my machine (TM1 9.5.1 and Excel 2003) the 'Print Report' option is active (and working) on the same report.
Does anybody know what could be causing the 'Print Report' function to be greyed out?

TIA, Michel

Hi Michel, you have to roll your own VBA. The print report function only supports slice and not active form.

lotsaram wrote:Hi Michel, you have to roll your own VBA. The print report function only supports slice and not active form.

That's sad to hear. Is this a documented omission that you know of?
Under Excel 2003 / TM1 9.5.1 the Print Report function is working for me on Active Forms (a bit flaky - but I thought this was part of the concept ).

Michel

If you step away and think about how the Print Report function operates you'll understand why it won't work for Active Form reports. For a given row and column layout, it cycles through the title dimensions, replacing each element in sequence, recalculating, and then "printing". The missing step for an Active Form report would be "rebuilding" the Active Form, recalculating won't do it. I guess someone in IBM decided that was going to be either 1) too hard to program, or 2) not worth it because they want you to use Cognos BI for that type of function.

Michel Zijlema wrote:That's sad to hear. Is this a documented omission that you know of?
Under Excel 2003 / TM1 9.5.1 the Print Report function is working for me on Active Forms (a bit flaky - but I thought this was part of the concept )

I think it's just a symptom of the Excel interface not receiving much attention. I don't know what you mean by "flaky" as I don't think this print report has ever worked, maybe the elements in the SUBNM got cycled through but on each change the print report function only runs a Recalc and not a Refresh so if any change to the rowset is called for then I guess that would be "flaky"!

tomok wrote:If you step away and think about how the Print Report function operates you'll understand why it won't work for Active Form reports. For a given row and column layout, it cycles through the title dimensions, replacing each element in sequence, recalculating, and then "printing". The missing step for an Active Form report would be "rebuilding" the Active Form, recalculating won't do it. I guess someone in IBM decided that was going to be either 1) too hard to program, or 2) not worth it because they want you to use Cognos BI for that type of function.

I don't know that I agree. The way I see it fact is is should work for active forms. The programming change you would think would be a pretty minor change to call the refresh function rather then the recalc function, shouldn't be that difficult and really makes one wonder how it can be that it hasn't managed to make it into a release since active forms have now been around for quite some time. (I think I agree in some respects, i.e. with your conspiracy theory that IBM would prefer users to purchase Cog BI to burst reports with report studio.)

lotsaram wrote:

Michel Zijlema wrote:That's sad to hear. Is this a documented omission that you know of?
Under Excel 2003 / TM1 9.5.1 the Print Report function is working for me on Active Forms (a bit flaky - but I thought this was part of the concept )

I think it's just a symptom of the Excel interface not receiving much attention. I don't know what you mean by "flaky" as I don't think this print report has ever worked, maybe the elements in the SUBNM got cycled through but on each change the print report function only runs a Recalc and not a Refresh so if any change to the rowset is called for then I guess that would be "flaky"!

Hi lotsa,

What I meant by flaky is the fact that you run into a lot of Excel crashes when editing Active Forms. I think indeed that's a symptom of the Excel interface not receiving much attention.
When we developed and tested the application the Print Report function was actually working as expected: its rebuilding the sheet (a stacked, zero surpressed rowset) for each element selected in the iteration subset. I tested the database and Active Form just now on a Win7 machine with TM1 10.1 and Excel 2010 installed and the report ran correctly...

I think apart from the discussion of whether Active Forms are supported to run through the Print Report function, the key issue is that the Print Report option was greyed out at my customer site. My original question was whether any one would know a reason why this could be. I can't think of a reason why this is the case

lotsaram wrote:

tomok wrote:If you step away and think about how the Print Report function operates you'll understand why it won't work for Active Form reports. For a given row and column layout, it cycles through the title dimensions, replacing each element in sequence, recalculating, and then "printing". The missing step for an Active Form report would be "rebuilding" the Active Form, recalculating won't do it. I guess someone in IBM decided that was going to be either 1) too hard to program, or 2) not worth it because they want you to use Cognos BI for that type of function.

I don't know that I agree. The way I see it fact is is should work for active forms. The programming change you would think would be a pretty minor change to call the refresh function rather then the recalc function, shouldn't be that difficult and really makes one wonder how it can be that it hasn't managed to make it into a release since active forms have now been around for quite some time. (I think I agree in some respects, i.e. with your conspiracy theory that IBM would prefer users to purchase Cog BI to burst reports with report studio.)

I agree - this function should work, but IBM Cognos seems to be more interested in using the server core to push BI sales than in the Excel client - which in still in most cases is the TM1 key selling point for finance people...

Michel

Michel Zijlema wrote: I just got a call from a customer who wants to generate a set of reports based on an active form using the 'Print Report' function in Excel. The problem however is that this option is greyed out in the Excel TM1 menu.
The user is running Excel 2010 on a TM1 9.5.2 local server machine. The user is connected to the server - the Active Form is showing numbers. We've already tried restarting Excel and rebuilding the Active Form. On my machine (TM1 9.5.1 and Excel 2003) the 'Print Report' option is active (and working) on the same report.
Does anybody know what could be causing the 'Print Report' function to be greyed out?

I can't check with a 9.5.2 / Excel 2010 commbination at the moment (will be able to later), but I can tell you that the only way that I can get the Print Report function to be greyed out in 10.1 / Excel 2010 is if I'm not connected to a server. It's even fine if I'm connected as a user who has no access to anything. I hear what you say about the numbers being visible but they remain visible if the active form was created during a previous login. How much do you trust this customer's assertion that s/he is really logged in?

Edit: I emphasise that when I say "it's fine" I'm referring to the fact that the button is enabled. As for the active form itself, permissions allowing, it will loop through the elements of a title dimension just fine and dandy but that doesn't mean that it'll rebuild the form as it goes as noted in preceding posts. Provided that you're dealing with fixed rows and columns it's OK, though.

If print reports is working with active forms in 10.1 then at least that's something (your current case point aside.)

I can test tomorrow with 9.5.2 + XL2010.

Alan Kirk wrote:How much do you trust this customer's assertion that s/he is really logged in?

Hi Alan,

I'm pretty confident, as:
1. this person is using a local server (which logs you in 'automatically')
2. I asked the user to do an ALT-F9 and the numbers where still there.

Alan Kirk wrote:Edit: I emphasise that when I say "it's fine" I'm referring to the fact that the button is enabled. As for the active form itself, permissions allowing, it will loop through the elements of a title dimension just fine and dandy but that doesn't mean that it'll rebuild the form as it goes as noted in preceding posts. Provided that you're dealing with fixed rows and columns it's OK, though.

The report has a stacked, zero suppressed rowset that changes depending on the selected title elements. Based on the commentary sofar I 'feel lucky' (which is not a positive observation) that the Print Report function has been working OK (that is rebuilding the sheet, showing the right rows for each generated snapshot of the Form) on the Active Form for me until now...

Michel

Michel Zijlema wrote:

Alan Kirk wrote:How much do you trust this customer's assertion that s/he is really logged in?

Hi Alan,

I'm pretty confident, as:
1. this person is using a local server (which logs you in 'automatically')

{Pedantry corner}: Strictly speaking this is only true if you don't put a password on the Admin account of the local server. Otherwise when it loads you get a message that the password is invalid and you have to enter it. Probably safe to say that your client would know whether that was the case, though, since rarely would anyone put a password on the account of a local server.

However... the fact that it is a local server could be what makes the difference.

Michel Zijlema wrote:2. I asked the user to do an ALT-F9 and the numbers where still there.

Alan Kirk wrote:Edit: I emphasise that when I say "it's fine" I'm referring to the fact that the button is enabled. As for the active form itself, permissions allowing, it will loop through the elements of a title dimension just fine and dandy but that doesn't mean that it'll rebuild the form as it goes as noted in preceding posts. Provided that you're dealing with fixed rows and columns it's OK, though.

The report has a stacked, zero suppressed rowset that changes depending on the selected title elements. Based on the commentary sofar I 'feel lucky' (which is not a positive observation) that the Print Report function has been working OK (that is rebuilding the sheet, showing the right rows for each generated snapshot of the Form) on the Active Form for me until now...

OK, results of my test with 9.5.2 / 2010. For the heck of it I set up a local server and created a report definition that looped through an active form sheet which had varying accounts as rows. I had the output go to a new file. The loop was through 12 elements in a title dimension.

The button was available and the report ran but I got variable results with it. With zero suppress on the original sheet it would usually either zero suppress the correct rows or show no rows at all, but oftentimes the numbers would be *Recalc_xxx values. Without zero suppression I'd still get the Recalc values on some of the sheets. However that's not the question, the question is the button.

I managed to make it ghost out on me in one and only one circumstance... where I had disconnected from all servers using the Disconnect command. Now in reality that does not disconnect you from a local server; go to Server Explorer and you're still logged in, press [Alt]+[F9] on the Active Form, and it will refresh correctly. But Print Report, along with most but not all of the other buttons, will be ghosted until such time as you log in to a remote server or recycle the local server. However you indicate that you've tried recycling the Excel session and still have the problem, so I'm not sure how that could be the case here. Nonetheless (and allowing for the fact that I got pretty much useless reports because the report loop was faster than the sheet update), the button was enabled and the loop ran so unless there's a rogue HF applied to the client's 9.5.2 installation I don't think it's a version issue.

Alan Kirk wrote:I managed to make it ghost out on me in one and only one circumstance... where I had disconnected from all servers using the Disconnect command. Now in reality that does not disconnect you from a local server; go to Server Explorer and you're still logged in, press [Alt]+[F9] on the Active Form, and it will refresh correctly. But Print Report, along with most but not all of the other buttons, will be ghosted until such time as you log in to a remote server or recycle the local server. However you indicate that you've tried recycling the Excel session and still have the problem, so I'm not sure how that could be the case here. Nonetheless (and allowing for the fact that I got pretty much useless reports because the report loop was faster than the sheet update), the button was enabled and the loop ran so unless there's a rogue HF applied to the client's 9.5.2 installation I don't think it's a version issue.

Hi Alan,

This is an interesting observation. I already asked the customer this morning to log in to all available TM1 servers in the Perspectives session to see whether this would make a difference - I'm still waiting for the results of this test. I will check whether they use the Excel disconnect option to log out (from remote server sessions).

Thanks, Michel

Tested with 9.5.2 and XL2010 and it worked for separate worksheets but not for separate workbooks which I think is related to an intermittent Excel "cannot empty clipboard" error that I get that seems to always crop up when refreshing active forms.