Cognos Impromptu Series 7 - Eporting to Excel 2

[AndrewKent]

Hi there,

I have recently upgraded from Cognos Impromptu Series 6 to 7. I need to run a full extract from our database (30,000+ rows) and export into excel. In version 6 this would export onto one worksheet but in 7 it splits it by Sheet1, Sheet2 and Sheet3.

Can anyone help me?

Cheers,

Andy

 

[drlex]

Andy,
Me again [Happy].
Have you tried setting the 'Version' on Report>Excel to 'Excel 2002 and above'? Works for me upto 64K, but is much slower than the multi-worksheet render of 'Excel 2000'.
Otherwise you have to export as csv (unsatisfactory) or script to open Excel and stitch the multiple worksheets together into one (messy).
Lex


soi la, soi carré

 

[AndrewKent]

Hi Lex,

Kind of figured it would be yourself replying ;-)

Right, I'm in the process of extracting this data so I'll let you know how I get on. I REALLY hope to avoid manually putting it all back onto one sheet!

Cheers,

Andy

 

[nallyatstsci]

Hi Andy and Lex:

I am having a similar problem - we have Impromptu Version 6 and we cannot export more than 16,384 rows to Excel (we get this message: "The Excel export has a row limit of 16,384 rows. If a report contains more than 16,384 rows, the report is truncated and the remaining rows are lost.") As you suggested, I tried exporting it as Excel (with Format) and it does export successfully - but it splits it into multiple sheets (Sheet1, Sheet2, etc.)

Andy, I'm wondering why you could export 30K+ rows in Version 6 - and we are getting Version 7 behavior (multiple sheets) with our current version.

Any suggestions would be most welcomed (and this is my first post!).

Thanks,

Karen

 

[drlex]

Karen,
Welcome to the forum.
May I ask what you're ultimately trying to achieve with your report? It's just that whilst I know people *love* spreadsheets, I do wonder why they need thousands of rows.
I do know that a colleague set himself up an Excel macro that reconfigured multiple sheets onto a single one to get over this issue.
Alternatively, could a csv file be a work-around?
Lex

soi la, soi carré

 

[AndrewKent]

Hi there,

Thanks for the reply. I'm not sure as to why I seemed to have no problem with this in version 6, but my data is being split into three worksheets in version 7. That seems to be a bit of a thinker. To get round this I used VB coding to combine all three worksheets onto one (although I wanted to avoid having to do that if there was a way to achieve this in Cognos).

As for having to have thousands of rows. Each row of data exported is a customer record. The company I work for deals with thousands of these each week thus theres a requirement for the data not to be split out.

As of yet I still haven't found a perfect solution to this.

Andy

 

[nallyatstsci]

Thank you both for the quick replies!

The data is a single fiscal month/year of labor and time charged to projects. Many people x many projects equates to thousands of detail rows. I'm not sure what our business user does with the data once it is exported in an Excel spreadsheet, but this is probably a good time to look at other solutions or reporting tools. The .csv route was tried originally and was fairly messy.

The solution then appears to be a programmatic one. Is it worth sending a suggestion to Cognos to fix this shortcoming in a future release?

Karen

 

[drlex]

Karen,
Thanks for the star.
I doubt Cognos will 'fix' anything here, as it's not really broken IMO. Version 7 will give you 64K rows, which is Excel's limit. If you're over V6's limit and the 'messyness' of csv is too much, I'd look at either other tools or get the business user to link to a DB.
Lex

soi la, soi carré

 

[nallyatstsci]

Thanks Lex - I did suggest using another reporting tool (maybe Crystal Reports - I don't know if it exports to Excel) or accessing the database directly through VB or Java.

Karen