Increasing the # of rows in Excel Output

[jguevin]

When running a report from inside Report Studio to Excel 2007 if the output exceeds 65K then the rest of the data is put onto new excel tabs.  When we run  or schedule the same report from Cognos Connection the excel output only returns the first 65K rows and nothing else.  Now as Admin I have gone into the settings of both the ReportService as well as the BatchReportService of each dispatcher and added the following advanced properties
RSVP.EXCEL.EXCEL_2007_LARGE_WORKSHEET and set to True and RSVP.EXCEL.EXCEL_2007_WORKSHEET_MAXIMUM_ROWS and set it to 1048756 and RSVP.EXCEL.EXCEL_2007_OUTPUT_FRAGMENT_SIZE and set it to 1048756...all of which are instructions I found on IBMs knowledge base.  I then restarted both servers.  The behavior explained at the beginning of this post still happens, nothing has changed.  Any suggestions? thanks Jeff

[bdbits]

I have to ask why you need so much data in a spreadsheet. If the users want to slice/dice data, Cognos is more than adequate to that task. If this is exporting data for consumption in another system, there are much better ways of accomplishing it than using a spreadsheet. Sometimes it pays to step back and figure out the real need and how best to solve it.

[jguevin]

Believe me I asked the same question of the business and it is a requirement.  So am I missing anything in the steps I took to try and enable this?
thanks Jeff

[bdbits]

Well, at least you tried to make them do it more sensibly. You might be surprised how many people will not even ask the question.

It looks like you have set the right things, so I am not sure why that would not work but I have not had to use this myself. Does it have to be Excel format? If the data is the important thing (no charts or particular formatting) you might try either Excel Data or even CSV. In fact CSV as far as I know has no limits and for certain Excel reads it in fine.

Have you checked the Cognos server logs at the time the execution completes? There may be an informational message there that could help find what is preventing this from working.

[Lynn]

Is the report generated from manually coded SQL queries and accessed via a JavaScript-laden prompt page that only one guy on the planet fully understands?

Hee hee, sorry, couldn't resist.

;)

[sunosoft]

When running a report from inside Report Studio to Excel 2007 if the output exceeds 65K then the rest of the data is put onto new excel tabs.  When we run  or schedule the same report from Cognos Connection the excel output only returns the first 65K rows and nothing else.  Now as Admin I have gone into the settings of both the ReportService as well as the BatchReportService of each dispatcher and added the following advanced properties
RSVP.EXCEL.EXCEL_2007_LARGE_WORKSHEET and set to True and RSVP.EXCEL.EXCEL_2007_WORKSHEET_MAXIMUM_ROWS and set it to 1048756 and RSVP.EXCEL.EXCEL_2007_OUTPUT_FRAGMENT_SIZE and set it to 1048756...all of which are instructions I found on IBMs knowledge base.  I then restarted both servers.  The behavior explained at the beginning of this post still happens, nothing has changed.  Any suggestions? thanks Jeff

Compare all settings of reportservice and batchreport service in your environment and check if you can find some important difference which may lead you to resolve your issue.

[Suresh Reddy]

Hi

Recently i have implemented this in my environment. Please apply only below two parameters to see more retards in one sheet (Excel 2007 Format).

"RSVP.EXCEL.EXCEL_2007_LARGE_WORKSHEET" -  Values " TRUE"
"RSVP.EXCEL.EXCEL_2007_WORKSHEET_MAXIMUM_ROWS"  - Value 1,048,576

Find the link below for more information:
http://www-01.ibm.com/support/docview.wss?uid=swg21419957
http://www.ibm.com/support/knowledgecenter/SSEP7J_10.2.1/com.ibm.swg.ba.cognos.ug_cra.10.2.1.doc/t_config_lgwrkst_ex.html

This parameters will work only for list reports.

As per my view first one is enef for your requirement, but if you want restrict the number of row you can use second one with specific value.

Thanks.

[sdf]

Are there any disadvantage or negative impact in setting the max rows to 1M?

sdf