TM1 report bursting
-
- MVP
- Posts: 3117
- 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
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
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
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
- 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
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
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
-
- MVP
- Posts: 3117
- 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
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
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
Re: TM1 report bursting
Try this: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.
- 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
-
- MVP
- Posts: 3117
- 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
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
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
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
Re: TM1 report bursting
Can you see under what user 'excel.exe' process is running in this case? Yours or the one you set up in Scheduled tasks?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.
Interactive Identity setup will launch excel under your account, specifying an account should run it under that account.
-
- MVP
- Posts: 3117
- 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
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
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
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
-
- Posts: 48
- Joined: Thu Nov 19, 2009 10:38 pm
- OLAP Product: Tm1
- Version: 10.2.2
- Excel Version: 2010
Re: TM1 report bursting
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.
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 (28 KiB) Viewed 8135 times
-
- dcom.jpg (46.73 KiB) Viewed 8135 times
J.Rizk
Tm1 for everyone
Tm1 for everyone
-
- MVP
- Posts: 3117
- 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
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.
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
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
-
- 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
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).
-
- MVP
- Posts: 3117
- 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
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.
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
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
-
- 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
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
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
-
- MVP
- Posts: 3117
- 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
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.
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
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
-
- Posts: 1
- Joined: Wed Feb 12, 2020 12:47 am
- OLAP Product: Planning Analytics
- Version: various
- Excel Version: various
Re: TM1 report bursting
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
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
-
- MVP
- Posts: 3117
- 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
Hello MalMal 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
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
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
-
- 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
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)
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)