If you are unable to create a new account, please email support@bspsoftware.com

 

News:

MetaManager - Administrative Tools for IBM Cognos
Pricing starting at $2,100
Download Now    Learn More

Main Menu

deafult value for #prompt#

Started by lawrencebenson, 15 Mar 2011 11:16:19 AM

Previous topic - Next topic

lawrencebenson

Hello everyone,

I am using Report Studio 8.2.

I am trying to make the sql dynamic so the filter can be applied to the data prior to grouping it.  I have had no luck trying to make the following SQL statement work using a #prompt# with a default value for handling cases where no filter is passed but I am having no luck:


Select  a.evt_org, a.evt_class, b.cls_desc, trunc(avg(trunc(sysdate) - trunc(a.evt_Created))) AvgAge, count(1) "Count"
from (
        select * from r5events where evt_priority like #prompt('p_priority','varchar','%')#
        ) a,
r5classes b
where a.evt_rstatus = 'R'
and a.evt_class = b.cls_code
and a.evt_org = b.cls_org
and b.cls_entity = 'EVNT'
group by a.evt_org, a.evt_class, b.cls_desc

I get the following error:
An error occurred while performing operation 'sqlPrepareWithOptions' status='-9'.

UDA-SQL-0107 A general exception has occurred during the operation "prepare". ORA-00911: invalid character RSV-SRV-0042 Trace back: RSReportService.cpp(666): UDASQLException: CCL_CAUGHT: RSReportService::process() RSReportService.cpp(611): UDASQLException: RSAsyncThrowable: RSReportService::process RSASyncExecutionThread.cpp(203): UDASQLException: CCL_CAUGHT: RSASyncExecutionThread::run(): asynchRunSpecification_Request RSASyncExecutionThread.cpp(656): UDASQLException: CCL_RETHROW: RSASyncExecutionThread::processCommand(): asynchRunSpecification_Request RSQueryMgr.cpp(1466): UDASQLException: CCL_RETHROW: RSQueryMgr::executeRsapiCommand QFSSession.cpp(1479): UDASQLException: CCL_RETHROW: QFSSession::ProcessDoRequest() QFSSession.cpp(1477): UDASQLException: CCL_CAUGHT: QFSSession::ProcessDoRequest() QFSSession.cpp(1444): UDASQLException: CCL_RETHROW: QFSSession::ProcessDoRequest() QFSConnection.cpp(1671): UDASQLException: CCL_RETHROW: QFSConnection::DescribeDataSourceQuery QFSQuery.cpp(450): UDASQLException: CCL_RETHROW: QFSQuery::DescribeDataSourceQuery QFSQuery.cpp(450): UDASQLException: CCL_RETHROW: QFSQuery::DescribeDataSourceQuery Source/AQE_UDAFacade.cpp(1685): UDASQLException: CCL_RETHROW: apidyn.cpp(240): UDASQLException: CCL_RETHROW: sqlPrepareWithOptions


Any alternate solutions are welcome.

roadwarrior

Try the following syntax:

query_item like #sq(prompt( 'prompt_name', 'token', '%' ))#

when prompted, if you leave the response blank you will get the % as the default as desired, and you should return all results.
when prompted, if you enter A%, you will return all rows where query_item starts with A.