Cognos Automation
-
- Posts: 11
- Joined: Tue Jul 09, 2013 7:29 pm
- OLAP Product: TM1 / Planning Analytics (dev)
- Version: 10.2.2
- Excel Version: 2007 2010 2016
Cognos Automation
Has anyone managed to get the Cognos Automation functionality to work in PAX. Either in VBA in Excel or outside of Excel with a VB script.
I am trying to have a file open, connect to two TM1 servers and refresh all the data in the workbook, unlink all the data and then save the workbook as a different filename. Basically I want to pre-calc a large workbook and paste the values and save to a different name.
I've looked at the sample code IBM provides but I cannot seem to get it to work.
Part of the issue I believe relates to the CognosOfficeAutomationObject.Logon object.
IBM's documentation does not make it clear what to put for the Namespace.
What I believe it is, or at least what returns true is the Friendly Name for the connection, a forward slash and the TM1 server name.
Here is the code I have so far. The issue appears that the data does not always refresh consistently and you are left with #Values, or RECALC_ values in the cells. Any help is greatly appreciated.
Dim wb As Workbook
Dim Current As Worksheet
Dim wbname As String
wbname = (ThisWorkbook.Sheets("Main").Range("Workbook").Value)
Set wb = Workbooks.Open(Filename:=wbname, UpdateLinks:=0)
wb.Activate
Dim overview, sidepane As Boolean
'Hides toolbar area above sheet
overview = CognosOfficeAutomationObject.OverviewVisible
CognosOfficeAutomationObject.OverviewVisible = False
result = CognosOfficeAutomationObject.Logon("http://10.5.194.90", "Admin", "Password", "Production/msh_prd")
result = CognosOfficeAutomationObject.Logon("http://10.5.194.90", "Admin", "Password", "Production/msh_copa")
'Hides side pane
sidepane = CognosOfficeAutomationObject.ApplicationPaneVisible
CognosOfficeAutomationObject.ApplicationPaneVisible = False
DoEvents
Application.ScreenUpdating = False
'Refresh all data in the workbook
CognosOfficeAutomationObject.RefreshAllData
'Set visibility back to initial state
CognosOfficeAutomationObject.OverviewVisible = overview
CognosOfficeAutomationObject.ApplicationPaneVisible = sidepane
Application.ScreenUpdating = True
CognosOfficeAutomationObject.Logoff
wb.SaveAs Filename:=ThisWorkbook.Sheets("Main").Range("Workbook_SaveAs").Value, AccessMode:=xlExclusive, ConflictResolution:=True
wb.Close
Set wb = Nothing
I am trying to have a file open, connect to two TM1 servers and refresh all the data in the workbook, unlink all the data and then save the workbook as a different filename. Basically I want to pre-calc a large workbook and paste the values and save to a different name.
I've looked at the sample code IBM provides but I cannot seem to get it to work.
Part of the issue I believe relates to the CognosOfficeAutomationObject.Logon object.
IBM's documentation does not make it clear what to put for the Namespace.
What I believe it is, or at least what returns true is the Friendly Name for the connection, a forward slash and the TM1 server name.
Here is the code I have so far. The issue appears that the data does not always refresh consistently and you are left with #Values, or RECALC_ values in the cells. Any help is greatly appreciated.
Dim wb As Workbook
Dim Current As Worksheet
Dim wbname As String
wbname = (ThisWorkbook.Sheets("Main").Range("Workbook").Value)
Set wb = Workbooks.Open(Filename:=wbname, UpdateLinks:=0)
wb.Activate
Dim overview, sidepane As Boolean
'Hides toolbar area above sheet
overview = CognosOfficeAutomationObject.OverviewVisible
CognosOfficeAutomationObject.OverviewVisible = False
result = CognosOfficeAutomationObject.Logon("http://10.5.194.90", "Admin", "Password", "Production/msh_prd")
result = CognosOfficeAutomationObject.Logon("http://10.5.194.90", "Admin", "Password", "Production/msh_copa")
'Hides side pane
sidepane = CognosOfficeAutomationObject.ApplicationPaneVisible
CognosOfficeAutomationObject.ApplicationPaneVisible = False
DoEvents
Application.ScreenUpdating = False
'Refresh all data in the workbook
CognosOfficeAutomationObject.RefreshAllData
'Set visibility back to initial state
CognosOfficeAutomationObject.OverviewVisible = overview
CognosOfficeAutomationObject.ApplicationPaneVisible = sidepane
Application.ScreenUpdating = True
CognosOfficeAutomationObject.Logoff
wb.SaveAs Filename:=ThisWorkbook.Sheets("Main").Range("Workbook_SaveAs").Value, AccessMode:=xlExclusive, ConflictResolution:=True
wb.Close
Set wb = Nothing
-
- Community Contributor
- Posts: 180
- Joined: Sat May 05, 2018 11:48 am
- OLAP Product: tm1
- Version: 10.3.10100.8
- Excel Version: 14
Re: Cognos Automation
Hi,
For CognosOfficeAutomationObject.Logon(paw url, admin, pwd, namespace) --> returns true if connection success
4th parameter Namespace, need to be specified as Host/Tm1 Server instance for IntegratedSecurityMode=1 -> Host machine name where Tm1 is running and Tm1 server instance is case-sensitive.
Tm1 Pax is strict, for DBRW() it returns #Values!, earlier in Tm1 perpectives DBRW() returns empty.
1) You have to manually click on the IBM Planning Analytics menu to load the ribbon.
2) then logon through VBA into TM1Pax.
Try this.
For CognosOfficeAutomationObject.Logon(paw url, admin, pwd, namespace) --> returns true if connection success
4th parameter Namespace, need to be specified as Host/Tm1 Server instance for IntegratedSecurityMode=1 -> Host machine name where Tm1 is running and Tm1 server instance is case-sensitive.
Tm1 Pax is strict, for DBRW() it returns #Values!, earlier in Tm1 perpectives DBRW() returns empty.
1) You have to manually click on the IBM Planning Analytics menu to load the ribbon.
2) then logon through VBA into TM1Pax.
Try this.
-
- Posts: 11
- Joined: Tue Jul 09, 2013 7:29 pm
- OLAP Product: TM1 / Planning Analytics (dev)
- Version: 10.2.2
- Excel Version: 2007 2010 2016
Re: Cognos Automation
Are you saying that I would need to manually click on the PAX ribbon before running any VBA code to connect and refresh worksheets?
The whole idea is to automate the calculation of these workbooks, if we have to manually click on the ribbon that would defeat the whole purpose.
And to confirm, for the namespace, when you say host, is that the TM1 server host (i.e. Admin Host), not the PAW host? My TM1 server and PAW are running on different machines.
The whole idea is to automate the calculation of these workbooks, if we have to manually click on the ribbon that would defeat the whole purpose.
And to confirm, for the namespace, when you say host, is that the TM1 server host (i.e. Admin Host), not the PAW host? My TM1 server and PAW are running on different machines.
-
- Community Contributor
- Posts: 180
- Joined: Sat May 05, 2018 11:48 am
- OLAP Product: tm1
- Version: 10.3.10100.8
- Excel Version: 14
Re: Cognos Automation
Here Host I mean Tm1 Server Host Where the Tm1 installed and the first parameter of Logon is the Paw Ip address.(http://paw Ip)And to confirm, for the namespace, when you say host, is that the TM1 server host (i.e. Admin Host), not the PAW host? My TM1 server and PAW are running on different machines.
In my scenario, I have invoked excel and called the active reports via code but did the Logon via VBA code, but reports was not in good shape and does not connected to TM1 server, and i have noticed that the Dynamic reports folder in Task pane doesnt have any reference to active reports. This was resolved once I manually click the IBM planning analytics menu before logon. Its quite annoying for me too. Just check if you also experience the same.
-
- Posts: 11
- Joined: Tue Jul 09, 2013 7:29 pm
- OLAP Product: TM1 / Planning Analytics (dev)
- Version: 10.2.2
- Excel Version: 2007 2010 2016
Re: Cognos Automation
After a lot of digging I was able to locate a piece of VBA code that could select the IBM Planning Analytics ribbon. See attached VBA module. It would not let me attach the .bas file, so just change the extension on the txt file to .bas
To call it use the Sub Switchtab:
SwitchTab "IBM" + Chr(174) + " Planning Analytics"
Hope this helps
To call it use the Sub Switchtab:
SwitchTab "IBM" + Chr(174) + " Planning Analytics"
Hope this helps
- Attachments
-
- Accessibility.txt
- (6.93 KiB) Downloaded 425 times
-
- Community Contributor
- Posts: 180
- Joined: Sat May 05, 2018 11:48 am
- OLAP Product: tm1
- Version: 10.3.10100.8
- Excel Version: 14
Re: Cognos Automation
Hi,
I have tried your code, it enabled the IBM planning analytics Menu, and works very nicely. Its a big surprise for me that you made this work. Great!!
What about your problem?? Is it solved?
One more thing which I noticed is when i open the active reports, Cognos_Office_Connection_Cache worksheet is also appended in VBA. After saving the sheet its also getting saved in some sheets. But I would like to get rid off this. Any idea?
I have tried your code, it enabled the IBM planning analytics Menu, and works very nicely. Its a big surprise for me that you made this work. Great!!
What about your problem?? Is it solved?
One more thing which I noticed is when i open the active reports, Cognos_Office_Connection_Cache worksheet is also appended in VBA. After saving the sheet its also getting saved in some sheets. But I would like to get rid off this. Any idea?
-
- Posts: 11
- Joined: Tue Jul 09, 2013 7:29 pm
- OLAP Product: TM1 / Planning Analytics (dev)
- Version: 10.2.2
- Excel Version: 2007 2010 2016
Re: Cognos Automation
I believe I got it to work, but seems to be inconsistent.
I would not delete the Cognos_Office_Connection_Cache worksheet, it is hidden for a reason and is most likely needed by PAX.
IBM were absolutely no help at all getting CognosAutomation to work, I'm not convinced any of their support people know what they are doing.
I would not delete the Cognos_Office_Connection_Cache worksheet, it is hidden for a reason and is most likely needed by PAX.
IBM were absolutely no help at all getting CognosAutomation to work, I'm not convinced any of their support people know what they are doing.
-
- Community Contributor
- Posts: 180
- Joined: Sat May 05, 2018 11:48 am
- OLAP Product: tm1
- Version: 10.3.10100.8
- Excel Version: 14
Re: Cognos Automation
While working with the reports its ok. But when saving the reports the Cognos_Office_Connection_Cache worksheet is also get saved. Problem is I am using the same report for tm1 perpectives and for tm1 pax in my application, depends on user selection. They may switch back and forth.I would not delete the Cognos_Office_Connection_Cache worksheet, it is hidden for a reason and is most likely needed by PAX.
I tried with cognosofficeAutomationObject.ClearCache() -- but no effect
I have also noticed that when Tm1 Pax timed out, then it triggered with login screen but after logged in manually into pax, the formula in reports does not give proper value. In Tm1RTPROW -- it gives "pending_reports.xlsx:worksheetname:Tm1TRPTRow" ... ?? cant trace out whats the problem is?IBM were absolutely no help at all getting CognosAutomation to work
Where the idle time out for tm1 pax is set? or How can I capture that in application? Have to give the app closing alert if Tm1 pax is timed out.
-
- Regular Participant
- Posts: 202
- Joined: Sat Dec 04, 2010 2:35 pm
- OLAP Product: PAL
- Version: 2.0.9
- Excel Version: 2016
Re: Cognos Automation
May be you also know how to connect with IntegratedSecurityMode=5?kavitha2002 wrote: ↑Mon Oct 22, 2018 8:27 am Hi,
For CognosOfficeAutomationObject.Logon(paw url, admin, pwd, namespace) --> returns true if connection success
4th parameter Namespace, need to be specified as Host/Tm1 Server instance for IntegratedSecurityMode=1 -> Host machine name where Tm1 is running and Tm1 server instance is case-sensitive.
Connecting with IntegratedSecurityMode=1 using
result = CognosOfficeAutomationObject.Logon("http://10.5.194.90", "Admin", "Password", "Production/msh_prd")
is fine
but I made several attempts to write connection for IntegratedSecurityMode=5 but without any success.
-
- Community Contributor
- Posts: 180
- Joined: Sat May 05, 2018 11:48 am
- OLAP Product: tm1
- Version: 10.3.10100.8
- Excel Version: 14
Re: Cognos Automation
what problem are you facing in IntegratedSecurityMode=5?
bResult = CognosOfficeAutomationExample.Logon("paw url", username, password, "namespace")
First parameter is paw url
second and third parameters are windows username and password
fourth is namespace what you have configured in IBM Cognos Configuration-- Its caseSensitive .. . (I have configured Active Directory Namespace)
But for me connection got established, but reports in excel not opening in good shape, gives #values! or reports doesnt have reference in paw Taskpane. I have to Manually select the Tm1 instance in excel paw, after loading the task pane only reports are working.
bResult = CognosOfficeAutomationExample.Logon("paw url", username, password, "namespace")
First parameter is paw url
second and third parameters are windows username and password
fourth is namespace what you have configured in IBM Cognos Configuration-- Its caseSensitive .. . (I have configured Active Directory Namespace)
But for me connection got established, but reports in excel not opening in good shape, gives #values! or reports doesnt have reference in paw Taskpane. I have to Manually select the Tm1 instance in excel paw, after loading the task pane only reports are working.
-
- Regular Participant
- Posts: 202
- Joined: Sat Dec 04, 2010 2:35 pm
- OLAP Product: PAL
- Version: 2.0.9
- Excel Version: 2016
Re: Cognos Automation
I see very strange behaviour of system (AD - namespace ID in IBM Cognos Configuration).kavitha2002 wrote: ↑Mon Nov 05, 2018 1:20 pm what problem are you facing in IntegratedSecurityMode=5?
bResult = CognosOfficeAutomationExample.Logon("paw url", username, password, "namespace")
First parameter is paw url
second and third parameters are windows username and password
fourth is namespace what you have configured in IBM Cognos Configuration-- Its caseSensitive .. . (I have configured Active Directory Namespace)
1. I.e. I write
bResult = CognosOfficeAutomationObject.Logon("http://10.5.194.90", "Admin", "Password", "AD/SData")
bResult is False
2. After it I write
bResult = CognosOfficeAutomationObject.Logon("http://10.5.194.90", "Admin", "Password", "AD")
bResult is False and also I receive error
3. After it I repeat step 1
bResult = CognosOfficeAutomationObject.Logon("http://10.5.194.90", "Admin", "Password", "AD/SData")
bResult is True
Also I can write
bResult = CognosOfficeAutomationObject.Logon("http://10.5.194.90", "Admin", "Password", "AD")
bResult = CognosOfficeAutomationObject.Logon("http://10.5.194.90", "Admin", "Password", "SData")
I receive error after 1 step but bResult is True
That I should write to connect in one line and without errors I don't know
-
- Community Contributor
- Posts: 180
- Joined: Sat May 05, 2018 11:48 am
- OLAP Product: tm1
- Version: 10.3.10100.8
- Excel Version: 14
Re: Cognos Automation
There may be some misconfiguration. Have you tested the PAW login url in browser? In excel pax login, connection to TM1 Sever is established?
This link may help!
This link may help!
-
- Regular Participant
- Posts: 202
- Joined: Sat Dec 04, 2010 2:35 pm
- OLAP Product: PAL
- Version: 2.0.9
- Excel Version: 2016
Re: Cognos Automation
Other clients work fine so I think I don't have problem with configuration.kavitha2002 wrote: ↑Tue Nov 06, 2018 12:47 pm There may be some misconfiguration. Have you tested the PAW login url in browser? In excel pax login, connection to TM1 Sever is established?
This link may help!
Also I've noticed it I connected to server in IBM Planning Analytics in Excel
bResult = CognosOfficeAutomationObject.Logon("http://10.5.194.90", "Admin", "Password", "AD/SData")
bResult is True
if I don't
bResult is False
May be CognosOfficeAutomationObject.Logon works only if you connected in IBM Planning Analytics in Excel ?
-
- Community Contributor
- Posts: 180
- Joined: Sat May 05, 2018 11:48 am
- OLAP Product: tm1
- Version: 10.3.10100.8
- Excel Version: 14
Re: Cognos Automation
What url is that??Also I've noticed it I connected to server in IBM Planning Analytics in Excel
bResult = CognosOfficeAutomationObject.Logon("http://10.5.194.90", "Admin", "Password", "AD/SData")
bResult is True
Code: Select all
bResult = CognosOfficeAutomationExample.Logon(paw url, username, password, namespace)
for me it always returns false, but connection got established. When I give fourth parameter as namespace/Tm1 server instance it not connected.
I tried with bResult = CognosOfficeAutomationExample.Logon("http://host:81/ibmcognos/cgi-bin/cognosisapi.dll", username, password, namespace) not working at all suggested by IBM.
After bResult return true, in excel -> go to IBM planning Analytics Menu -> Connection -> paw instance -> select the tm1 instance, to check if its really establish the connection.
-
- Regular Participant
- Posts: 202
- Joined: Sat Dec 04, 2010 2:35 pm
- OLAP Product: PAL
- Version: 2.0.9
- Excel Version: 2016
Re: Cognos Automation
Simply http://name , like http://host (without 81/ibmcognos/cgi-bin/cognosisapi.dll)kavitha2002 wrote: ↑Tue Nov 06, 2018 1:28 pmWhat url is that??Also I've noticed it I connected to server in IBM Planning Analytics in Excel
bResult = CognosOfficeAutomationObject.Logon("http://10.5.194.90", "Admin", "Password", "AD/SData")
bResult is True
bResult = CognosOfficeAutomationObject.Logon("http://host", "Admin", "Password", "AD/SData")
But it works only if you connected already in in PAX
But if-> only this worksCode: Select all
bResult = CognosOfficeAutomationExample.Logon(paw url, username, password, namespace)
for me it always returns false, but connection got established. When I give fourth parameter as namespace/Tm1 server instance it not connected.
bResult = CognosOfficeAutomationObject.Logon("http://host", "Admin", "Password", "AD") worked how you can connect to the Tm1 server?
-
- Community Contributor
- Posts: 180
- Joined: Sat May 05, 2018 11:48 am
- OLAP Product: tm1
- Version: 10.3.10100.8
- Excel Version: 14
Re: Cognos Automation
bResult = CognosOfficeAutomationObject.Logon("http://host", "Admin", "Password", "AD") worked how you can connect to the Tm1 server?
Actually I cant able to give the Tm1 instance, but its connecting to tm1 server instance. I have only one instance running in mode 5. Even I am wondering how its connecting to tm1 instance without giving tm1 instance name.
I raised the same question to IBM and waiting for reply.