Freezing of column in crosstab which should works in Excel output format too.

[yogeswari]

Hi,

I have an requirement which should be default in Excel where the columns of crosstab should be freezed.
I referred freezing of columns and rows of crosstab and list in the below link.

http://www.ibm.com/developerworks/library/ba-pp-infrastructure-cognos_specific-page641/index.html?ca=drs
http://www.ibm.com/developerworks/data/library/cognos/reporting/advanced_report_design/page622.html

This link is useful but it works only for HTML output and not for Excel output.
Also i blindly practized this link and work in the report for HTML output.
So could anyone knows how this can be achieved in Excel output? please help me as it is very new to me.

Thanks,
yogeswari.

[navissar]

There's no way to interfere with the way Cognos renders Excel files, and naturally HTML solution won't work.
I once developed a very elaborate solution, where the user saves the report to file system instead of running it live; when the report is saved, it triggers a batch file which opens the report in Excel, runs a macro function that freezes panes, and then utilizes VBA to send the report by mail to the person who ran it. This is an insanely complex endeavor and I couldn't hope to list it down step by step. For most cases, I'd suggest dropping it for being impossible. 

[yogeswari]

Hi,

first of all, thanks for your responses.
I am not getting your point.
Is it possible to do in the excel or not? please suggest.
Also i did not mention in the question regarding sending reports to mail.

Please help me because this is the requirement, which i am helpless to do.

thanks,
yogeswari.

[BigChris]

As Nimrod says, it's theoretically possible do this in Excel, but would involve considerable amounts of VBA coding and excel macros. If you're able to do that, then it can be done...but it would be an exercise in Excel coding and not something that would be possible in Cognos.

[Ixhers]

Working around the problem, providing I understood it correct that is,
you could output your results as .txt and pass that to a already formatted excel.


/ Rob

[yogeswari]

Hi,

thanks for your responses.

Chris,
out of curiosity i am asking this.  What is theoretically possible means???
what is VBA coding and excel macros? 
How could i get the source? or materials to learn? could you guide me regarding this.

Ixhers,
how to output the results as .txt? and how to pass to formatted excel??

i would consider your help as great in my career.

thanks,
yogeswari.

[Ixhers]

Sorry i meant as .CSV of course.

Although I don't know the size of your data.

Run the report as a list and set the output to save it as a .CSV where you see fit.
Copy paste that once into a fresh excel and create a pivot to look like your crosstab.
Set your formats etc.
Create a import of the data in your .CSV file to the Excel.
Then you just have to record a macro where you clean the old data, run the saved import and update the pivot.

Not as pretty as Cognos, but should do the trick.


/ Rob

[navissar]

Hey there,
My point is that this is a requirement I usually shoot down, saying it cannot be done.
There are workarounds, but these are
a. Very expensive in terms of work hours - the development of my solution took roughly a week, and I have strong background in VBA. If you're going to learn VBA for this, it'll take you weeks.
b. Not very elegant as they typically  requires the user changes their method of operation. Usually it is easier for the user to manually freeze panes than it would be to change the whole way a report is produced.
c. require some level of knowledge of Excel macros, VBA and batch.
If I were you - and I have been in this position many times - I would tell the customer that this requirement cannot be met, and that the workarounds aren't very feasible.

[CognosPaul]

Why not use Go Office? You could import the data directly from the report. The data will come in as an Excel table, and you'll be able to use any of the native Excel functionality with it.

[bdbits]

I kind of like CognosPaul's idea, and if your user base loves Excel they will probably like Go Office. It might be worth exploring as an option.

Your original links regarding freezing headers are for HTML reports, not Excel. Their methods go beyond out-of-the-box Cognos functionality and are directed to HTML presentation. They simply do not work outside of HTML presentation. The alternative custom approaches that have been suggested involve expertise it would take you some time to develop, as you clearly do not possess these skills right now. (That is not meant to offend you, it just is the way it is at this time.)

Nevertheless, instead of saying it cannot be done, I would tell your client:

"Cognos does not offer enough control over Excel output to meet your requirement. There are alternatives. For example, we could embed instructions into the report explaining how to freeze the headers using native Excel functionality. Or it may be possible to freeze the headers with custom development that applies changes to Excel output after it has been generated, but this will require considerable effort as well as expertise with Office VBA (Visual Basic for Applications). Or the Cognos SDK might be utilized to create a custom solution. We do not currently possess the expertise to utilize these custom development methods. You could choose to either live with the current product limitations, or acquire the expertise needed through training or hiring consultants with the necessary skills. How would you like to proceed?"

This keeps the client informed (they usually like that) and lets them decide if it is worth the effort. Sometimes, they ask for nice-to-have features without realizing what it takes to deliver it. Once they do realize the effort (and likely associated expense), they may just choose to use some instructions embedded in the report or train staff how to do it themselves. Or use Go Office. If not, they know it will take some time and maybe you will get some training out of it.