Cognos Automation

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

Post by stuartbarnes27 »

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
kavitha2002
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

Post by kavitha2002 »

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.
stuartbarnes27
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

Post by stuartbarnes27 »

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.
kavitha2002
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

Post by kavitha2002 »

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.
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)

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.
stuartbarnes27
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

Post by stuartbarnes27 »

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
Attachments
Accessibility.txt
(6.93 KiB) Downloaded 416 times
kavitha2002
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

Post by kavitha2002 »

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?
stuartbarnes27
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

Post by stuartbarnes27 »

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.
kavitha2002
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

Post by kavitha2002 »

I would not delete the Cognos_Office_Connection_Cache worksheet, it is hidden for a reason and is most likely needed by PAX.
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 tried with cognosofficeAutomationObject.ClearCache() -- but no effect
IBM were absolutely no help at all getting CognosAutomation to work
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?

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.
EP_explorer
Regular Participant
Posts: 200
Joined: Sat Dec 04, 2010 2:35 pm
OLAP Product: PAL
Version: 2.0.9
Excel Version: 2016

Re: Cognos Automation

Post by EP_explorer »

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.

May be you also know how to connect with IntegratedSecurityMode=5?
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.
kavitha2002
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

Post by kavitha2002 »

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.
EP_explorer
Regular Participant
Posts: 200
Joined: Sat Dec 04, 2010 2:35 pm
OLAP Product: PAL
Version: 2.0.9
Excel Version: 2016

Re: Cognos Automation

Post by EP_explorer »

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)
I see very strange behaviour of system (AD - namespace ID in IBM Cognos Configuration).
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
02.jpg
02.jpg (15.15 KiB) Viewed 9505 times
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
02.jpg
02.jpg (15.15 KiB) Viewed 9505 times
but bResult is True

That I should write to connect in one line and without errors I don't know
kavitha2002
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

Post by kavitha2002 »

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!
EP_explorer
Regular Participant
Posts: 200
Joined: Sat Dec 04, 2010 2:35 pm
OLAP Product: PAL
Version: 2.0.9
Excel Version: 2016

Re: Cognos Automation

Post by EP_explorer »

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!
Other clients work fine so I think I don't have problem with configuration.
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 ?
kavitha2002
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

Post by kavitha2002 »

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
What url is that??

Code: Select all

bResult = CognosOfficeAutomationExample.Logon(paw url, username, password, namespace) 
-> only this works

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.
EP_explorer
Regular Participant
Posts: 200
Joined: Sat Dec 04, 2010 2:35 pm
OLAP Product: PAL
Version: 2.0.9
Excel Version: 2016

Re: Cognos Automation

Post by EP_explorer »

kavitha2002 wrote: Tue Nov 06, 2018 1:28 pm
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
What url is that??
Simply http://name , like http://host (without 81/ibmcognos/cgi-bin/cognosisapi.dll)
bResult = CognosOfficeAutomationObject.Logon("http://host", "Admin", "Password", "AD/SData")
But it works only if you connected already in in PAX

Code: Select all

bResult = CognosOfficeAutomationExample.Logon(paw url, username, password, namespace) 
-> only this works

for me it always returns false, but connection got established. When I give fourth parameter as namespace/Tm1 server instance it not connected.
But if
bResult = CognosOfficeAutomationObject.Logon("http://host", "Admin", "Password", "AD") worked how you can connect to the Tm1 server?
kavitha2002
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

Post by kavitha2002 »

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