How to get the XML output for Report outside of Cognos Viewer

[mybrainhurts]

Good Afternoon,

I am new to this whole COGNOS world and have only just figured out how to create an url that will take me to the xml output of a Report. The problem is that the xml is displayed in COGNOS Viewer and I was hoping to actually have the url point to the XML output itself.  The reason being I want to parse the XML file using PHP so I can work with the results on a webpage (displaying the data in different ways, etc.)

Is this possible? Any help is greatly appreciated.

Thanks,
Mark

[adam.johnson]

Mark,

Can i ask you to confirm what version of Cognos you are using?

If you are using Cognos 11R5, new functionality has been introduced where you can, either, copy or embed a direct report URL.

I believe you require the embed functionally which you can use in an external web page.

You do need to note, however, that you cannot run a report as XML (to generate an output) and you would need to schedule a job to produce the output.

Adam

[mybrainhurts]

Hi Adam,

Thanks for taking the time to respond to my inquiry.

It appears that the version of Cognos I am working with is 10.2.2

If I am understanding you correctly, you are saying that I would need to schedule the report to run in order to produce an actual xml document?

Thanks,
Mark

[bdbits]

Have you looked at the SDK? I do not know for certain, but there are a ton of APIs available and I would bet one of them will give you the XML directly.

[mybrainhurts]

Thanks for the suggestion. I will scour the internets and see what I can find. =]

[CognosPaul]

If you're using 10.2 or above, the SDK comes preinstalled. With the SDK, you can use Cognos Mashup Services to expose report data.

Take a look at the following URL

Code Select Expand

http://[Server]/ibmcognos/cgi-bin/cognos.cgi/rds/reportData/searchPath/content/folder[@name='folderName']/folder[@name='reports']/report[@name='myReport']?p_param1=123&p_paramName2=abc&p_parameter3=<[[CDATA[this parameter value has special characters that need to be passed as is !@#$%^&*()]]>&fmt=dataset&async=off

You have the standard Cognos application URL, which needs to be modified to match your environment. If you're using isapi it should be cognosisapi.dll, if you're bypassing the gateway to talk to the dispatcher directly it might be: serverName:9300/p2pd/servlet/dispatch

/rds/reportData indicates that you want to retrieve report data through the reportDataServer.

/searchPath/content is the search path of the report. You can get that from the report properties.

?p_param=123&p_paramName=abc are parameters and values. The p_ bit is added in front of the parameter name, so this report might have a filter like [whatever]=?param?

&fmt=dataset is what's most important to you. There are a lot of options here. dataset returns the report data in a simple xml format. Only the report data is returned. You can also do JSON, or datasetJSON, or atom, or HTML, or HTMLFragment.

This request has async off, meaning that the connection stays open until the server stops sending data back. You can set that to auto or manual depending on your needs.

There's a lot more to CMS than that, read up on it here: public.dhe.ibm.com/software/data/cognos/documentation/docs/en/10.2.2/dg_cms.pdf

[bdbits]

Every technical site on the Internet needs a CognosPaul.   ;D

[CognosPaul]

Also, a small warning here.

There may be licensing implications when you expose Cognos like this. Who is the target audience, internal or public? Do you want to run the report each time a user opens the page? Do you have a PVU license? Is the report tailored for each visitor?

The project I'm currently on exposes the Cognos reports as datasetJSON and have a custom portal that puts everything together. Because we're getting thousands of unique visitors, there's no way we could do it without being on a PVU.

If you are on a per user license agreement, and this is for an external (public) audience, you might need to set up some ridiculous system to run the report to the filesystem and consume it from there.

[CognosPaul]

Every technical site on the Internet needs a CognosPaul.   ;D

I'm actually thinking of branching out and adopting a TM1Paul persona. Maybe TableauPaul? Maybe MicroStrategyPaul and I'll get the Minneapolis Airport to sponsor me.

[mybrainhurts]

Paul,

Thank you so much for your detailed response. I fear that I am not doing things correctly though. Base on your information I adapted the URL that I had put together to get the report to show up in Cognos viewer. The adapted version is as follows;

Code Select Expand


http://cognosprd1.prd.zzz/ibmcognos/cgi-bin/cognosisapi.dll/rds/reportData/CAMID(AD_ZZZ:u:e85e9cb512522947b2e3f525ca619e6f)/folder[@name='My Folders']/report[@name='ATF02 - Comparison']?p_pDateFrom=2017-02-01&p_pDateTo=2017-03-01&fmt=dataset&async=off


The code doesn't bring anything back though. As mentioned, I have a feeling I haven't fully grasped the information you provided. If my provided code sheds any light on my apparent ignorance I would greatly appreciate further assistance.

I am working solely for internal purposes. There is no external access whatsoever. Simply put, I am creating an internal website that will provide the information from various reports in an interactive manner. Theses reports are refreshed monthly, which is why I am passing the date specific parameters in the url.  I would just end up writing some code that would check the date on page load and if the month has passed then to grab the new xml for the report. Hope that makes sense.

Again, I really appreciate everyone's help.

Mark

[CognosPaul]

When you say you're not getting anything back, do you mean you just get an empty <dataSet/> tag? Are you getting an error?

Based on your URL, if you go to http://cognosprd1.prd.zzz/ibmcognos/cgi-bin/cognosisapi.dll you'll see Cognos connection, right?

As a sanity check, does the report have any lists or crosstabs, not just queries? What happens when you run the report with the same parameters?

What happens when you run this:

Code Select Expand

http://cognosprd1.prd.zzz/ibmcognos/cgi-bin/cognosisapi.dll?b_action=cognosViewer&ui.action=run&ui.object=/CAMID(AD_ZZZ:u:e85e9cb512522947b2e3f525ca619e6f)/folder[@name='My Folders']/report[@name='ATF02 - Comparison']?p_pDateFrom=2017-02-01&p_pDateTo=2017-03-01&run.outputFormat=&run.prompt=false

[mybrainhurts]

Good Morning Paul,

Thanks again for your reply.

yes, when I run your first link I get connected to Cognos on the Public Folders page.

For the second link I get the following error:

The search path "/CAMID(AD_ZZZ:u:e85e9cb512522947b2e3f525ca619e6f)/folder[@name='My Folders']/report[@name='ATF02 - Comparison']?p_pDateFrom=2017-02-01" is invalid. An object may contain invalid syntax, or an unsupported character, or the user account in the namespace may not have sufficient privileges. Check the object to ensure that the target destination location does not contain special characters.

The report itself contains tables and bar charts, but I have a feeling its the way I am linking to the report. I will try variations and see what happens.

Mark

[CognosPaul]

whoops, try this

Code Select Expand

http://cognosprd1.prd.zzz/ibmcognos/cgi-bin/cognosisapi.dll?b_action=cognosViewer&ui.action=run&ui.object=/CAMID(AD_ZZZ:u:e85e9cb512522947b2e3f525ca619e6f)/folder[@name='My Folders']/report[@name='ATF02 - Comparison']&p_pDateFrom=2017-02-01&p_pDateTo=2017-03-01&run.outputFormat=&run.prompt=false

The first one had a ? after the report location.



and I see what's wrong with your CMS call:

http://cognosprd1.prd.zzz/ibmcognos/cgi-bin/cognosisapi.dll/rds/reportData/CAMID(AD_ZZZ:u:e85e9cb512522947b2e3f525ca619e6f)/folder[@name='My Folders']/report[@name='ATF02 - Comparison']?p_pDateFrom=2017-02-01&p_pDateTo=2017-03-01&fmt=dataset&async=off

should be:

http://cognosprd1.prd.zzz/ibmcognos/cgi-bin/cognosisapi.dll/rds/reportData/searchPath/CAMID(AD_ZZZ:u:e85e9cb512522947b2e3f525ca619e6f)/folder[@name='My Folders']/report[@name='ATF02 - Comparison']?p_pDateFrom=2017-02-01&p_pDateTo=2017-03-01&fmt=dataset&async=off

you were missing /searchPath, which instructs Cognos exactly what path to take to find your report.

[mybrainhurts]

YOU SIR ARE AN EFFING GENIUS!!!!!

Thank you sooooooo much.

Awesome. Pure pure awesome. =]

[mybrainhurts]

Guess this wasn't going to be as simple and straightforward as I thought. =[

The XML content is displayed in the browser when I enter that created URL, but when I try to use it in my php code to grab the contents and put it into an xml file it doesn't work. I've tried a butt load of various ways to do it but none of them seem to work. That being said, when I just copy and paste the contents into a file and save it as an xml file, my php code works fine with it. So I am wondering if you have ever come across this problem, and if so, how you solved it.

I will continue to go through that pdf you linked to.

Thanks,

Mark

[CognosPaul]

I actually have no experience at all with PHP.

Can you post your code and whatever error's your getting? Are you using simplexml_load_file()? If so, try file_get_contents().

Can you try consuming a JSON output? Do you get the same or a different error?

[mybrainhurts]

Good Morning Paul,

I am having troubles with JSON output as well. I am reading the documentation file you provided and it mentions there are  bunch of sample applications that perform the functions I am interested in. It would save me from having to keep bothering everyone if I could get my hands on these samples. I try the locations they indicate but nothing comes up (website declined to show this page). is there another way to get the sample files?

Thanks,
Mark

[CognosPaul]

Start with reading this:

http://cognospaul.com/2013/02/18/cognos-mashup-services-a-brief-example/

It has an example of a method of running a report in JSON format and using the results in JS.

[mybrainhurts]

Hi Paul,

I've tried playing with your code but I feel I am experiencing the limitations of my skillset.

Here is where I am so far:

- I can get all the different types of output to load in an iFrame, but not outside of it. Trying to access the URL via an ajax or php call it brings back errors (essentially unable to fetch the xml, etc.)

This leads me to believe that when I load the output through the iframe my credentials must already be active in the browser, but when trying to get the output via ajax or php then those credentials are not passed to it. So, I have to figure out how I can do that and what the CMS needs from my end.

I will keep on trucking.

[CognosPaul]

can you paste in the error? I'm really flying blind here. What is the iframe doing?

[mybrainhurts]

Hi Paul,

Well. I have finally been able to get the report output programmatically. =]

At the moment it is still a workaround as I am hardcoding the username and password until I can find another way to get it. I know that cognos uses my workstation login as my credentials but I just need to get that without having to ask a user to log in again to get the report.

The other road block now is getting the output sent to a php file so it can be written to a file on the server. I think I am just not getting the delivery right. I have faith I will get it all working. =]

Just wanted to update you. =]

[sdswaim71]

I am in a very similar situation.   I have Cognos 10.2.2.   I dont have any access to the actual database.   I need to pull the dataset from a report.   I used to be able to just run the report from php (with all of my parameters in the url including the USERNAME and PASSWORD) and parse the output. This has worked for years. But now, the output does not contain any data. I assume that the backend cognos was updated to a new version and that is my issue.  I have tried to modify the link with the information you have provided above but I get a problem loading page. 

Here is my goal
1.  using PHP, I would like to read the output data from the report.  Would love to automate this and not have to manually log in and run the report
2.  I cand use XML, CSV, even HTML
3.  I can then parse the data and place it in my local database for additional reporting.   
4.  I dont have any access to the original database.  (This is managed by another company).  The only access to the data is through Cognos.


Any help would be appreciated.

Thanks
Scott


Original Link
https://MYSERVER/p2pd/servlet/dispatch?b_action=cognosViewer&ui.action=run&ui.object=%2fcontent%2fpackage%5b%40name%3d%27eCWEBO%27%5d%2ffolder%5b%40name%3d%270%20-%20Custom%20Reports%27%5d%2ffolder%5b%40name%3d%27Admin%27%5d%2freport%5b%40name%3d%27VisitInfo%27%5d&ui.name=VisitInfo&run.outputFormat=XML&run.promptCSVlse&CAMUsername=<<username>>&CAMPassword=<<password>>

Modified using previous post
http://MYSERVER/p2pd/servlet/dispatch/rds/reportData/searchPath/content/folder[@name='Public%20Folders']/folder[@name='eCWEBO']/folder[@name='0%20-%20Custom%20Reports']/folder[@name='Admin']/report[@name='VisitInfo']?fmt=dataset&async=off