TM1 report bursting

Post Reply
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:

TM1 report bursting

Post by 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
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
User avatar
orlando
Community Contributor
Posts: 167
Joined: Fri Aug 04, 2017 8:27 am
OLAP Product: TM1
Version: PAL 2.0.8
Excel Version: Office 365

Re: TM1 report bursting

Post by 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
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: TM1 report bursting

Post by 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.
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
User avatar
ykud
MVP
Posts: 148
Joined: Sat Jan 10, 2009 10:52 am
Contact:

Re: TM1 report bursting

Post by ykud »

Wim Gielis wrote: Fri Mar 15, 2019 10:34 am 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
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: TM1 report bursting

Post by 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.
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
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
User avatar
ykud
MVP
Posts: 148
Joined: Sat Jan 10, 2009 10:52 am
Contact:

Re: TM1 report bursting

Post by ykud »

Wim Gielis wrote: Sat Mar 16, 2019 2:27 pm - 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
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: TM1 report bursting

Post by 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
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
jrizk
Posts: 48
Joined: Thu Nov 19, 2009 10:38 pm
OLAP Product: Tm1
Version: 10.2.2
Excel Version: 2010

Re: TM1 report bursting

Post by 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.
Attachments
sched.jpg
sched.jpg (28 KiB) Viewed 8057 times
dcom.jpg
dcom.jpg (46.73 KiB) Viewed 8057 times
J.Rizk
Tm1 for everyone
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: TM1 report bursting

Post by 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.
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
asvlad
Posts: 27
Joined: Wed Mar 17, 2010 2:41 pm
OLAP Product: TM1, Transformer, EP, MSAS
Version: 2.0.6
Excel Version: 2016
Location: Russian Federation

Re: TM1 report bursting

Post by 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
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: TM1 report bursting

Post by 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.
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
asvlad
Posts: 27
Joined: Wed Mar 17, 2010 2:41 pm
OLAP Product: TM1, Transformer, EP, MSAS
Version: 2.0.6
Excel Version: 2016
Location: Russian Federation

Re: TM1 report bursting

Post by 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
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: TM1 report bursting

Post by 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 :D The more details, the better.
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
Mal
Posts: 1
Joined: Wed Feb 12, 2020 12:47 am
OLAP Product: Planning Analytics
Version: various
Excel Version: various

Re: TM1 report bursting

Post by 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
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: TM1 report bursting

Post by Wim Gielis »

Mal wrote: Wed Feb 12, 2020 5:01 am 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.
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
HighKeys
Posts: 117
Joined: Fri Aug 09, 2019 10:11 am
OLAP Product: TM1 / TM1 Web / Perspectives
Version: Planning Analytics V2.0.9
Excel Version: Office 365

Re: TM1 report bursting

Post by 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)
Post Reply