What is it with Excel 2007 format?

[msn]

I have a report with 24 columns and 180K rows. In Run with options screen, I select to save the report in Excel 2007 or Excel 2007 Data formats. The report execution fails with the message:

DPR-ERR-2056 The Report Server is not responding. Handler trace back: [the_dispatcher] com.cognos.pogo.handlers.performance.PerformanceIndicationHandler [the_dispatcher] com.cognos.pogo.handlers.logic.ChainHandler [service_lookup] com.cognos.pogo.handlers.engine.ServiceLookupHandler [load_balancer] com.cognos.pogo.handlers.logic.ChainHandler [lb_forwarder] com.cognos.p2plb.clerver.LoadBalanceHandler [batchreportservicechain] com.cognos.pogo.handlers.logic.ChainHandler [warptask_with_authenticate]


But for the same report ->
In Run with options screen, I select to save the report in one of the other formats - Text, XML, Excel 2002, CSV formats and the report executed within 3 minutes. Though the size of the saved output is:
Text - 82 MB
CSV - 71 MB
Excel 2002 - 278 MB
XML - 160 MB

Why should the Excel 2007 report output fail? By introducing some filters and reducing the number of records retrieved I was able to generate the Excel 2007 report output upto a size of about 18 MB. But beyond that the report just fails.

The test enviornment has Cognos 10.2.1 FP4 and production environment has cognos 10.2.1 and this situation occurs in both environments.

In test environment, the settings for supporting large excel workbooks have been implemented.
http://www-01.ibm.com/support/knowledgecenter/SSEP7J_10.2.1/com.ibm.swg.ba.cognos.ug_cra.10.2.1.doc/t_config_lgwrkst_ex.html

But the problem remains.

Can anyone provide pointers on what needs to be done to generate the excel 2007 output without errors?

[bdbits]

That's not a report, that's a data dump. Which is a wholly wrong way to use Cognos.

If you insist on doing this, since you already set it up for large worksheets, I would open a support ticket with IBM and see what they have to say.

[msn]

 ;) Yes, it's a data dump that the users insist that they do it instead of depending on IT.

So, a workaround that they are comfortable with as of now is to generate the report in CSV or text format and then externally convert it to Excel 2007 format.

Will have to check what IBM has to say on this.

[Lynn]

;) Yes, it's a data dump that the users insist that they do it instead of depending on IT.

So, a workaround that they are comfortable with as of now is to generate the report in CSV or text format and then externally convert it to Excel 2007 format.

Will have to check what IBM has to say on this.

Sigh. This is ultimately a management failing, in my opinion. On the Business side they fail to support/fund/participate in projects with a long-term goal of providing a robust reporting environment that is capable of delivering business author self service reporting. They are addicted to the quick win which is, unfortunately, rarely repeatable or automate-able (if that's a word). The various desk drawer empires that result are fragile and prone to failure when the individual responsible moves elsewhere.

On the IT side they fail to march toward a long-term solution within the confines of what is funded and supported. They tend to do (barely) what is requested without a strategy road map to guide activities toward a goal beyond the endless individual "interim solutions."

Both sides bend to the whims of the spreadsheet control freaks who simply insist on having everything in their hot little hands so they can pivot to their hearts content.

If management could simply use these forces for good rather than evil there could be a (gasp) partnership between IT and business. Use those mad spreadsheet skills to figure out the requirements and then use that exercise to inform the development activities that can create it in a Cognos package. So much more....not enough time to rant.

[bdbits]

Thank you Lynn for saying it so well.

Especially since the data is apparently already available within Cognos, it seems like such a waste. And now how many versions of the truth will promulgate through the enterprise? Educate the users on how to use Cognos tools, turn them loose with their own data, and the results may be quite surprising.