Cognos PP Cubes to MS Access

[hendrixharrison]

Is it possible for MS Access to be able to link to Cognos Powerplay cubes through an ODBC connection? I would like to be able to slice and dice and link various cubes together in Access.

Inform me if this is possible.

 

[GJParker]

Not possible



Gary Parker
MIS Data Analyst
Manchester, England

 

[hendrixharrison]

So ok if it is not possible, what sort of solution would you suggest I should do to go about this?

Currently I am forced to create powerplay reports and then save them as excel files and then import them into ms access. Is there a better solution to this problem?

There should be one...

 

[DoubleD]

You could automate the save of the PowerPlay reports to Excel files.

OR

Is it an option to create Impromptu reports to generate the data you need, then import those files into Access?

I am what I am based on the decisions I have made.

DoubleD

 

[drlex]

I'll echo DoubleD's thoughts; if you're trying to combine information from different sources, can you not try a shorter path? If the data is on varying databases (and so prevents single Impromptu queries), why not aim to combine the data into a datamart? SQL server or MySQL (depending on budget and experience) and then use Access (since you mention it) for reporting.

soi la, soi carré

 

[hendrixharrison]

Thanks for all the solutions all of you brought up. I definitely have to use impromptu on the future until my company fix up my set up on my machine.

The thing is that we do not have my sql or sql server. If we do things would be alot easier. I am just starting to use cognos right now.

 

[hendrixharrison]

I was wondering if there is a way for Access VBA to run cognos powerplay as application ala word.application

Then save the file as an excel file.

I am going to test some codes right now turning my normal office application related vba codes and apply them to cognos.

 

[DoubleD]

HendrixHarrison,
Look into the CognosScript documentation. You absolutely can control Cognos objects using code, and CognosScript translates almost directly to VBA. Good luck with this, if you need any help, let us know.

I am what I am based on the decisions I have made.

DoubleD

 

[hendrixharrison]

so doubled have you done it before?

I dont mind seeing a sample of it.

thanks

 

[DoubleD]

I've automated Impromptu to do a plethora of things. I've also automated Transformer to update the location of IQD files and replace the database connection. However, I have not automated Powerplay to save to Excel.

It would probably look something like this:

Sub Main()
   Dim objPPRep as Object
   Set objPPRep = CreateObject ("CognosPowerPlay.Report")
   objPPRep.Open ("C:\Cubes and Reports\Sample1.mdc")
   objPPRep.SaveAs "MyNewReport", 4
   Set objPPRep = Nothing
End Sub

I found this info in CognosScript help by looking up Powerplay, Report Object, and SaveAs.

I am what I am based on the decisions I have made.

DoubleD

 

[hendrixharrison]

Thanks Double D

Here is the end result....

Sub Cognosapp()


Dim objPPRep As Object
Set objPPRep = CreateObject("CognosPowerPlay.Report")
objPPRep.Open ("Y:\documents\cognos\budget.ppx")
objPPRep.SaveAs ("Y:\documents\cognos\budget.xls"), 4
Set objPPRep = Nothing
End Sub

 

[siokping88]

Hi all,

I am new to cognos macro,
where can i get the cognos script documentation mentioned
by DoubleD?

many thanks~

 

[DoubleD]

If you have Cognos Series 7 installed on your PC, search for the following file:

cgspmac.pdf

I am what I am based on the decisions I have made.

DoubleD

 

[siokping88]

Hi DoubleD,

Thanks for the reply.
I am using Cognos 6.61.
I will get the file from my fren,
many thanks!

Cheers
Jasmine