No Report Output for Excel or PDF

[k2]

Hi there,

Am sure most of you have faced this situation earlier. I am currently working on a report which has nearly 20 queries which includes 7 unions and few render variables too. When I run the report it works fine only in HTML but throws error I try to get the output either in Excel or PDF.
I tried changing the WITH Clause in Query property to NO but the report takes ages to run but still didnt get the Excel output. Could somebody shed some light please. Software version is Cognos 8.4, Oracle DB.

The Error message follows:

RQP-DEF-0177

      An error occurred while performing operation 'sqlPrepareWithOptions' status='-9'.

UDA-SQL-0107 A general exception has occurred during the operation "prepare".ORA-32035: unreferenced query name defined in WITH clause RSV-SRV-0042 Trace back:RSReportService.cpp(758): QFException: CCL_CAUGHT: RSReportService::process()RSReportServiceMethod.cpp(239): QFException: CCL_RETHROW: RSReportServiceMethod::process(): asynchWait_RequestRSASyncExecutionThread.cpp(774): QFException: RSASyncExecutionThread::checkExceptionRSASyncExecutionThread.cpp(211): QFException: CCL_CAUGHT: RSASyncExecutionThread::run(): reportRender_RequestRSASyncExecutionThread.cpp(824): QFException: CCL_RETHROW: RSASyncExecutionThread::processCommand(): reportRender_RequestExecution/RSRenderExecution.cpp(593): QFException: CCL_RETHROW: RSRenderExecution::executeAssembly/RSDocAssemblyDispatch.cpp(264): QFException: CCL_RETHROW: RSDocAssemblyDispatch::dispatchAssemblyAssembly/RSLayoutAssembly.cpp(64): QFException: CCL_RETHROW: RSLayoutAssembly::assembleAssembly/RSDocAssemblyDispatch.cpp(331): QFException: CCL_RETHROW: RSDocAssemblyDispatch::dispatchChildrenAssemblyForwardAssembly/RSReportPagesAssembly.cpp(163): QFException: CCL_RETHROW: RSReportPagesAssembly::assembleAssembly/RSDocAssemblyDispatch.cpp(281): QFException: CCL_RETHROW: RSDocAssemblyDispatch::dispatchAssemblyAssembly/RSPageAssembly.cpp(287): QFException: CCL_RETHROW: RSPageAssembly::assembleAssembly/RSDocAssemblyDispatch.cpp(281): QFException: CCL_RETHROW: RSDocAssemblyDispatch::dispatchAssemblyAssembly/RSTableRowAssembly.cpp(160): QFException: CCL_RETHROW: RSTableRowAssembly::assembleAssembly/RSDocAssemblyDispatch.cpp(281): QFException: CCL_RETHROW: RSDocAssemblyDispatch::dispatchAssemblyAssembly/RSTableCellAssembly.cpp(122): QFException: CCL_RETHROW: RSTableCellAssembly::assembleAssembly/RSDocAssemblyDispatch.cpp(331): QFException: CCL_RETHROW: RSDocAssemblyDispatch::dispatchChildrenAssemblyForwardAssembly/RSTableAssembly.cpp(97): QFException: CCL_RETHROW: RSTableAssembly::assembleAssembly/RSDocAssemblyDispatch.cpp(331): QFException: CCL_RETHROW: RSDocAssemblyDispatch::dispatchChildrenAssemblyForwardAssembly/RSTableRowAssembly.cpp(160): QFException: CCL_RETHROW: RSTableRowAssembly::assembleAssembly/RSDocAssemblyDispatch.cpp(281): QFException: CCL_RETHROW: RSDocAssemblyDispatch::dispatchAssemblyAssembly/RSTableCellAssembly.cpp(122): QFException: CCL_RETHROW: RSTableCellAssembly::assembleAssembly/RSDocAssemblyDispatch.cpp(331): QFException: CCL_RETHROW: RSDocAssemblyDispatch::dispatchChildrenAssemblyForwardAssembly/RSSingletonAssembly.cpp(101): QFException: CCL_RETHROW: RSSingletonAssembly::assembleAssembly/RSAssembly.cpp(626): QFException: CCL_RETHROW: RSAssembly::createListIteratorRSQueryMgr.cpp(978): QFException: CCL_RETHROW: RSQueryMgr::getListIteratorRSQueryMgr.cpp(1051): QFException: CCL_RETHROW: RSQueryMgr::getResultSetIteratorRSQueryMgr.cpp(1211): QFException: CCL_RETHROW: RSQueryMgr::createIteratorRSQueryMgr.cpp(1511): QFException: CCL_RETHROW: RSQueryMgr::executeRsapiCommandRSQueryMgr.cpp(1498): QFException: CCL_RETHROW: RSQueryMgr::executeRsapiCommandRSQueryMgrExecutionHandlerImpl.cpp(174): QFException: CCL_RETHROW: RSQueryMgrExecutionHandlerImpl::execute()RSQueryMgrExecutionHandlerImpl.cpp(154): QFException: CCL_RETHROW: RSQueryMgrExecutionHandlerImpl::execute()RSQFSession.cpp(243): QFException: CCL_RETHROW: RSQFSession::DoRequestQFSSession.cpp(603): QFException: CCL_RETHROW: QFSSession::ProcessDoRequest()QFSSession.cpp(601): QFException: CCL_CAUGHT: QFSSession::ProcessDoRequest()QFSSession.cpp(558): QFException: CCL_RETHROW: QFSSession::ProcessDoRequest()QFSConnection.cpp(737): QFException: CCL_RETHROW: QFSConnection::ExecuteQFSQuery.cpp(199): QFException: CCL_RETHROW: QFSQuery::Execute v2CoordinationQFSQuery.cpp(4174): QFException: CCL_THROW: CoordinationPlanner

[tjohnson3050]

One thing to keep in mind, when a report runs in HTML versus Excel and PDF is that the entire report is run for Excel and PDF, and only the portion of the report needed for the current page is run for HTML. 

Your report might run fine for the first HTML page, but error out further down when paging through using the HTML page down navigation buttons.

With so many queries, I would start the troubleshooting process by testing each of the 20 queries one at a time to make sure each query can execute.

[k2]

Thanks for your response John.
I have checked my report till the end in HTML everything seems to be fine. I believe the issue is to do something with memory.... any ideas?? cheers

[tjohnson3050]

Did you try testing each query one at a time?