If you are unable to create a new account, please email support@bspsoftware.com

 

News:

MetaManager - Administrative Tools for IBM Cognos
Pricing starting at $2,100
Download Now    Learn More

Main Menu

generate excel output from an active report?!

Started by AlexRC93, 09 Jan 2012 02:25:25 AM

Previous topic - Next topic

AlexRC93

Hi all,

I'm working with active reports now for several days and I was wondering if it is possible to generate an excel output from an active report.
E.g. you use filters to generate a list and this list is what I need as an excel document. (That's what the users I'm creating the reports for do need)

I didn't find anything helpful here or at google. Do you have an idea how to implement this?

Thanks and kind regards
Alex

charon

hi alex,

does it has to be from an active report? if u have an report, you are able to run/ publish it in or for excel (2002 and 2007), just click on "run" in the menue bar and choose run report....

cheerz :P

AlexRC93

hey charon,
thanks for your reply. But I do need it out of an active report!
What I need is something like a link out of the active report pointing to excel and showing there the data selected in the active report.

regards
Alex

charon

hi alex,

as far as i know thats not possible, at least not directly. The reason is, the saved active report is an .mht file, which means it is an archive with design & layout information and the data itself saved into one file.
Once it is an offline readable document, its not possible (or easy) to return into the original file.

Though, there are several options u might wanne try out.
1) If you have the report it self online, not rendered as mht, you can export it for excel via cognos.
2) As xml file, you might wanne try to "crack" the mht file and get the code, maybe transform into some xml, and read that out. If you search with google, you will find several attempts, but imo thats quite an effort and you have to do it for each report.
3) Apparently, there are software solutions for that, e.g. from Spoton. I have no experience with that kind of tools, but u might wanne check them out if it is urgent and your company has a lot of reports that needs to be converted.

My advise would be to rebuild the active reports into normal reports (you are able to copy paste the most information and even queries), then publish for excel...

cheerz :P

AlexRC93

Thanks for your hints,
I'm gonna try these points.

But rebuilding the active reports to normal ones makes no sense, because we already have the normal reports and are now converting some of them into active reports. But the users do still want the possibility to open the results as an excel sheet. They are used to work with it and don't want to change their work =/ Although there are most of the options already available in cognos.

Greets

CognosPaul

#5
Unfortunately active reports can only export to MHT. Why not keep the originals, and set up links to the reports using the URL Parameters to get the excel output?

<a href="http://cognos10dev/cognos10/cgi-bin/cognosisapi.dll?b_action=cognosViewer&ui.action=run&ui.object=%2fcontent%2ffolder%5b%40name%3d%27Samples%27%5d%2ffolder%5b%40name%3d%27Cubes%27%5d%2fpackage%5b%40name%3d%27Sales%20and%20Marketing%20(cube)%27%5d%2ffolder%5b%40name%3d%27Report%20Studio%20Report%20Samples%27%5d%2freport%5b%40name%3d%27Top%20Retailers%20by%20Country%27%5d&ui.name=Top%20Retailers%20by%20Country&run.outputFormat=spreadsheetML&run.prompt=true">I'm a link. Click me.</a>

AlexRC93

This works, for sure, but only if the user is online and logged-in.
The active reports will be used offline, too.

Can't imagine that it's not possible to simply export a single list from cognos to excel. I do not need the whole report in excel.

e.g.
Drop Down List (select value)
Date List Box (select value)
---> result is a List --> export this content to excel.

CognosPaul

You should be able to set up a JavaScript button with a script to export the table to a csv. check http://forums.whirlpool.net.au/archive/798389 as a starting point

charon

Jeha, true.
Thats btw what i meant with point 2 in my list, geting into the code itself and extract the information. But as i said, thats some effort.

And about your statement that it should be possible...remember, the active report holds another purpose then extracting information into excel. Its and interactive, offline useable dashboard for different
browsers. Its like you have an video/ flash file with information you want to extract...its not the purpose of the flash file to deliver excel sheets but the hold grafik and maybe sound data to be interpreted
by a flash player as video. Same with active reports.
Cognos itself has the option to export for Excel, of course  :P Again, i suggest if your customer wants to use acitve reports for offline usage, its great, but if they want the crosstab data extracted for excel,
go to normal report level OR start coding :)

gl whatever you choose...and just in case you are going to create a script, i would be interested if you find a solution  ;D
cheezr :P

AlexRC93

an export to csv could be possible, you're right. Don't know if this also works for lists instead of tables.
But the output would be separated with commas wouldn't it?
That's not that useful.
I think the users should better use the normal reports for their work with excel and use the active reports for anythin else ;)
Thanks for your help so far, if you have another hint feel free to post it, I'll try to do some coding ::)

CognosPaul

I have to agree with Charon, active reports are not designed to be exported into Excel. You should be able to use Event Studio to attach the excel output from the original report in the same email as the active report. The filters in the active report won't effect the Excel, of course.

Ndriggers

#11
Is it possible to use the drill thru feature to run the old report in excel with parameters? Or are there no parameters to pass?