TM1 report bursting

[Wim Gielis]

Hello all,

My case is the following. I have a Perspectives report with DBRW and SUBNM and I would like to:
- generate a version of the report with hard values
- email the report to a number of recipients
- this should be done in an automated way

No manipulations need to happen in the report, only getting rid of formulas. This report should be generated every night.

Now, I have it working up to a certain degree. I.e. Excel is installed on the server.
A TI process executes a Scheduled Task on the server.
This task executes a VBScript. The vbscript opens up a new Excel instance, and also an Excel file with 1 macro.
The VBA code in the macro logs on to TM1 Perspectives (no API coding, just TM1p.xla file and its macros), opens the report, zaps the formulas, saves a copy of the file, closes the original file and shuts down Excel.

This works perfectly if me or anyone else is logged on to the server when the TI process executes the vbscript.
However, in the case of NO USER being logged on to the server at that time, it does not work completely. I don't understand why.
It seems that the Application.Run "TM1REFRESH" or "TM1RECALC" is the culprit - or any such code. There, in the VBA code, it stops suddenly, and only when no user is logged on to the server. Otherwise the REFRESH just happens fine.

Does it ring a bell ? Does anyone have a clue what is missing here ? Or should I approach it differently ? The requirement is to generate a report with hard values in a fully automated way - including that no user should do it manually or that someone be logged on to the server. The TM1 Print Report wizard is not an option Nor the PAW bursting.

I already changed a number of Windows options and settings but somewhere there might still be a hidden option.

Thanks !

Wim

[orlando]

Hi Wim,

i did the same for a customer with TM1 10.2.2, but i startet the excelsheet (with the macor) directly via the scheduler (without startet by TI)
there wasn't someone logged on - it worked fine

maybe you should try it with task scheduler only

regards
orlando

[Wim Gielis]

Thanks, I will try to set up a variant without TI and without the vbscript part in the middle. Focus on the Excel and scheduled task part only.

[ykud]

This works perfectly if me or anyone else is logged on to the server when the TI process executes the vbscript.
However, in the case of NO USER being logged on to the server at that time, it does not work completely. I don't understand why.
It seems that the Application.Run "TM1REFRESH" or "TM1RECALC" is the culprit - or any such code. There, in the VBA code, it stops suddenly, and only when no user is logged on to the server. Otherwise the REFRESH just happens fine.

Try this:
- create desktop folder in c:\windows\system32\config\systemprofile\desktop
- create desktop folder in c:\windows\sysWOW64config\systemprofile\desktop
if it doesn't help, try this as well
- Go to "Start" -> "Run" and enter "MMC comexp.msc /32"
- Go to the properties of Microsoft Excel Application, under Identity, change it to This User from The Launching User (which is set by default). Input the credentials of TM1 services user account

[Wim Gielis]

Hello Yuri

Thanks. I should have added the following too:

- This topic is a continuation of my earlier topic: https://www.tm1forum.com/viewtopic.php?f=3&t=14157
It's the same challenge and the same problem. Last year I thought it worked, but it does not. I forgot to update that topic, I did this now. The setting is that I am doing this again for a different customer, the end result is similar (produce a report while automating Excel and TM1)

- I created a Word document with things I did and checked. Please find it attached, for those of us that experience the same issue - while it still does not work. I was looking at the identity properties of Excel too, but I used the 'Interactive user'. I tested also what you wrote down, thanks for that, but still no avail.

Automating TM1 report generation from from TM1 - technical settings.docx

(18.57 KiB) Downloaded 726 times

The testing that I am currently doing:
- I am not using a TI process to start anything, but just a scheduled task
- The task runs with an account that is part of the 'Administrators' group
- If I schedule the task to run at a certain point in time, so scheduled, and I am logged on: it works fine.
- If I schedule the task and I log off (and everyone else is logged off): it does not work
- I simplified the VBA coding in the Excel file to just create a text file on the server itself on a disk there. No TM1 stuff whatsoever
- The paths are local and on the server, I also tested with \\servername\... UNC paths: it still works when logged on, not working when logged off

[ykud]

- If I schedule the task to run at a certain point in time, so scheduled, and I am logged on: it works fine.

Can you see under what user 'excel.exe' process is running in this case? Yours or the one you set up in Scheduled tasks?

Interactive Identity setup will launch excel under your account, specifying an account should run it under that account.

[Wim Gielis]

Hi,

If Excel identity is interactive, it's my username (wimgieli)
If Excel identity is the launching user, it's the username with which the task is created (tm1burstservice)

I now have the following:
- everyone logged off, Excel identity set as the user wimigeli and the scheduled task running under wimgieli: it runs a macro BUT !
* opening an Excel file normally (logged on again, as wimgieli) seems to produce error messages in Excel about the content of the file (I have to change the Excel identity back to the launching user to open up Excel files normally again - I wonder whether that will give adverse effects in the future in case (power) users log on to the server and open a file directly on the server
* Application.Run TM1REFRESH and similar macro's do not run while clearly the tm1p.xla is loaded (confirmed with text files). It's about twevrecalc1 and such errors

[jrizk]

I don't think it will work when everyone is logged off. The only way it seems to work is to schedule the task under the Administrators group - not a user account which is part of the Administrators group - and this only runs when a user is logged on. The Excel identity is 'The Interactive user'. I think in my last post about this I mentioned you can run the task via TI using

sCmd = 'cmd /c schtasks /Run /TN "Your Task Name Here"';
ExecuteCommand (sCmd, 1);

also may have mistakenly mentioned to try this when logged off but it does require a login. Probably doesn't resolve having the report bursting done when there is no login but it's the only way it the scripts and Tm1/Excel appear to behave correctly - unless someone else has been able to get it to work with no login.

[Wim Gielis]

Hello jrizk,

Thank your for confirming that this isn't going to work. But... it did work in previous versions of Windows. Anyways.

What are the different options I still have ?
- Someone here on the forum, or within my organisation, the customer's organisation, the Internet, ... knows a way to do it. I doubt it very much to be honest.
- Someone is logging on every day and generates the report(s). Not ideal of course.
- If such reporting is really important and involves more than 1-2 reports, I might think of a solution in which:
* in an automatic way, a 'user' is logged on if not already logged on, the report is created, and (optionally) the 'user' is logged off again. Not sure how difficult this will be.
* relying on someone to be logged on on the server because in that case it seems to work fine. If the file is not present in a certain folder and with the timestamp of today, probably due to no user being logged on, then shoot an email to some users in the organisation to ask them to do it manually
- This week a couple of thousands of Microsoft MVPs (Most Valuable Professionals) gather in Seattle at Microsoft for their annual summit, including Excel MVPs. I try my luck by shooting an email to friends/contacts there. So they can spend their evenings over beers and a real-world problem
- Looking at a paid solution and understanding what are the requirements there (i.e. how can another solution automate the reports if we assume that it's not possible ? Different technology ?)

It will be important to not disturb users by changing settings on the server, if that's not necessary.

Thanks all for your efforts.

[asvlad]

Why don't you look to the TI Java Extension and POI side. These both can help you to generate Excel reports directly from TI without involving your OS and its troubles. To do that you have to investigte POI and TI Java Extension (and java).

[Wim Gielis]

Good question, thanks. Personally, I never wrote a line of Java coding, so that's enough for your answer

Seriously though, with an online tutorial I might give it a go but finding it out all by myself will cost too much time and effort I'm afraid.
If you know of good to the ploint and complete resources, please add them to this topic please.

[asvlad]

To start with Java Extensions - https://www.ibm.com/support/knowledgece ... rator.html

To start with POI - https://poi.apache.org/

You have to use Eclipse Java EE IDE as developer tool for this task

With POI you can do with Excel documen almost all, what you usually can do in Excel.
With help of TI Java Extensions you can access to TM1 data and instruct POI to place it in existing or new excel book

Regards

[Wim Gielis]

Thanks. If someone wants to dive into it and document the different steps to burst an existing Perspectives report, feel free to do so The more details, the better.

[Mal]

Hi Wim,

Whichever account you are running the Excel VBA as needs to have it's profile set up correctly.

You need to log in as this user (on the server, or whichever box you are running it from) and make sure the TM1 Add-In is loaded.

You will also need to make sure there are no pop-ups for this user (eg. if it crashes - which it will occasionally - then you'll get the "do you want to recover …") that will stuff up your macros. Also sometimes after MS patches you will get prompts which will stuff up your macros, so you'll have to log in as this account any time it stops working.

I have also used a VBScript in the past to kill any stale Excel sessions more than a certain age, as I've found multiple Excel sessions left on the server causing problems.

Cheers
Mal

[Wim Gielis]

Hi Wim,

Whichever account you are running the Excel VBA as needs to have it's profile set up correctly.

You need to log in as this user (on the server, or whichever box you are running it from) and make sure the TM1 Add-In is loaded.

You will also need to make sure there are no pop-ups for this user (eg. if it crashes - which it will occasionally - then you'll get the "do you want to recover …") that will stuff up your macros. Also sometimes after MS patches you will get prompts which will stuff up your macros, so you'll have to log in as this account any time it stops working.

I have also used a VBScript in the past to kill any stale Excel sessions more than a certain age, as I've found multiple Excel sessions left on the server causing problems.

Cheers
Mal

Hello Mal

Thanks for this but that was already known.

In addition, I (or the user) cannot log on to the server to execute the script since it should run in an automated way. If we log on, it works and there is no issue.

[HighKeys]

noobish Input:

Some time ago, i want to do something simliar, but not on a server, cause i didnt get one, so i set up a normal workstation in the office and called it "Reporting Station" it was up 24/7 with a system user and filled every sheet and send automatic mails with the reports attached.

Its not the best way cause u need a user and a workstation, but it should work(around)

[lav4you]

Hi All,

I know this is an old thread.
One of client is asking for the similar feature.

I am wondering if there is any working solution now available to automatically refresh data and burst report through a non interactive user?

[Mike Cowie]

I am wondering if there is any working solution now available to automatically refresh data and burst report through a non interactive user?

Please have a look at this post in the commercial forum about ReportWORQ, which is a product designed to solve the problems you're describing. Full disclosure: I work for the company (QueBIT) that develops ReportWORQ, so feel free to contact me via private forum message, too.