"View in EXCEL 2007 Data" doesnt work (CCLUnknownError:0:Fatal: RSV-SRV-0042)

[bladeless]

Hello,

When I try to run "View in EXCEL 2007 Data" option for my report that has 50000 rows in List on report page I get error after 8-10 minutes of waiting. During generating of the Excel file, Memory consumption of BiBus service on the Cognos server growth until it takes all available free memory and then I get the error:

RSV-SRV-0040
      The report server encountered an internal error. Check additional information associated with this error message. If cause of problem cannot be ascertained, increase the logging level in the IBM Cognos administration tool and reproduce the conditions that caused the error. If the problem persists, see the problem determination information on the IBM Cognos Support Portal page at http://www-947.ibm.com/support/entry/portal/Overview/Software/Information_Management/Cognos_Business_Intelligence.

     DetailsExpand
CCLUnknownError:0:Fatal: RSV-SRV-0042 Trace back:RSReportService.cpp(764): CCLUnknownError: CCL_CAUGHT: RSReportService::processImpl()RSReportServiceMethod.cpp(258): CCLUnknownError: CCL_RETHROW: RSReportServiceMethod::process(): asynchWait_RequestRSASyncExecutionThread.cpp(857): CCLUnknownError: RSASyncExecutionThread::checkExceptionRSASyncExecutionThread.cpp(325): CCLUnknownError: CCL_CAUGHT: RSASyncExecutionThread::runImpl(): reportRender_Request

All other "View in ..." options work well enough - generate files, but take a lot of time(8-10 mins).
I tried to add RSVP.EXCEL.EXCEL_2007_LARGE_WORKSHEET = true and RSVP.EXCEL.EXCEL_2007_OUTPUT_FRAGMENT_SIZE with different values (3000 - 30000) for ReportService, BatchReportService and other, but it didn't fix the error.

As for me, it looks like Cognos bug...  :(
Could anyone give me any ideas how to fix it?

Thanks for your help.

[AnalyticsWithJay]

IMO, you need to rule out whether running out of memory is causing the issue.

- Create a copy of the report, apply a filter to the data so the report comes back in 1 minute, and see what happens.

If the issue is still happening, take a look at this APAR:
http://www-01.ibm.com/support/docview.wss?uid=swg1PI57600

[bladeless]

Thank you, CognoidJay.

Cognos generates .xls file for the report with less rows - 10000 rows. Also it generates .xls file for the report with less columns - if the report contains at least 6 columns less(doesn't matter which -
initially the total number of rows = 40).
My cognos environment does not use advanced settings that mentioned in the link.

So, I still have this issue ((

[AnalyticsWithJay]

Great, so it sounds like this is likely an issue where you're running out of memory/resources, since the report runs with smaller subsets of data.

50,000 rows in a report sounds more like a data dump, of which there are much better tools out there for it. Could you re-design the report such that you have drill throughs from Summary to Detail? That way you could limit the amount of data appearing at a time.

Are you on a 32-bit installation? How much memory is available when you say it runs out of memory? How much is the BiBus consuming just before the report stops executing?

If you have heavy formatting and conditional variables, locally joined queries, local functions, and anything else for that matter that requires local processing on the Cognos end, you could work on pushing those to the database or removing them from the report. That should reduce the amount of memory consumption that is necessary.

I would seriously look at the 50,000 rows though, and push back on the client for a different solution. No one is looking at that much data in a report for BI.

[bladeless]

>Could you re-design the report such that you have drill throughs from Summary to Detail?
Unfortunately, the report is detail report and I simulate situation when user chose filters parameters that generate report page with huge amount of rows(List).

> Are you on a 32-bit installation?
64-bit. But, BiBus service is 32-bit

> How much memory is available when you say it runs out of memory? How much is the BiBus consuming just before the report stops executing?
5-10 MB. 3575-3580 MB

>If you have heavy formatting and conditional variables, locally joined queries, local functions, and anything else for that matter that requires local processing
Do not have any

>I would seriously look at the 50,000 rows though, and push back on the client for a different solution. No one is looking at that much data in a report for BI.
It is really strange issue for me - All "View in ..." options, except "View in EXCEL 2007 Data", generate view files(pdf, xlsx(Format), csv).

[AnalyticsWithJay]

Hi bladeless,

If this was a small report that was causing your environment to run out of memory, I would say you're probably running into a product issue and to contact Cognos support.

However, this is a 50,000 row report, which is huge in volume for a BI report. I'm not surprised you're running out of memory. So, you're faced with two options really -- increase memory (probably unlikely) or reduce the size of the report.

You don't want reports like this in your environment even if they run, because they use up all the resources and the rest of your environment is now extremely slow.

Can you tell me what the purpose of this report is? What is the user doing with 50,000 records? If it's just an excel data dump, why couldn't you use an ETL tool or the database to just dump this into Excel?

Once I know what the purpose of the report is, we could make more recommendations. But ultimately, if you're running out of memory, you either have to increase it or reduce the volume of the report.

[bladeless]

I totally agree with you and I will redevelop this report.
But, why does "View in Excel 2007 Format" work fine and create view file(*.xlsx file) for the report - It takes no more than 500 MB of RAM to generate *.xlsx file also I can manage how much memory the precess can use: RSVP.EXCEL.EXCEL_2007_OUTPUT_FRAGMENT_SIZE
and in the other hand "View in Excel 2007 Data" does not work (doesnt crate *.xlsx file)? Also It doesn't look logically... Data option should use less memory than Format option because Format option also adds formatting into output *.xlsx file...

[AnalyticsWithJay]

I totally agree with you and I will redevelop this report.
But, why does "View in Excel 2007 Format" work fine and create view file(*.xlsx file) for the report - It takes no more than 500 MB of RAM to generate *.xlsx file also I can manage how much memory the precess can use: RSVP.EXCEL.EXCEL_2007_OUTPUT_FRAGMENT_SIZE
and in the other hand "View in Excel 2007 Data" does not work (doesnt crate *.xlsx file)? Also It doesn't look logically... Data option should use less memory than Format option because Format option also adds formatting into output *.xlsx file...

Oh, I understand what you mean.

Do you have Master-Detail Relationships or more than one query in your report? Because Excel (Data) will ignore those, and that could be the reason why the report is not taking as long or as much memory.

If you only have one query and don't have master-detail relationships, that's a question you can ask Cognos support, as they will contact the Cognos development team for you and find out more information. That's a valid question if you're seeing Excel (format) go to only 500MB, but you have to make sure they're executing the same things.

[bladeless]

>Do you have Master-Detail Relationships or more than one query in your report?
No, I don't. Only one query and one list on report page

>That's a valid question ...
Ok, I will try. Thank you for your help!