In Report Studio. I have an Expression that mines through a comments field and simplifies it with the following formula:
[Expression1] = If([Comments] like'%bt0%') Then('BT') Else(If([Comments] like '%ct0%') Then('CT') Else('NA'))
Then I am creating another expression to limit each of the comments to one row per client:
[Expression 2] = Maximum([Expression1] For [ClientLastName], [ClientFirstName], [Date])
This is needed because each client could have 50 comments so when I insert [Expression1] into the Report List I get 50 duplicate rows even when the comments field is the only unique field. [Expression2] Correctly gives me the maximum of [Expression1] when I run the report with both [Expression1] and [Expression2] within the Report List. The problem I have is that if I remove [Expression1] from the Report List (but still leave it in the Query), I get the error that is below. I need to be able to make it so there is only 1 [Expression1] per client. Any and all help is greatly appreciated.
Error:
UDA-SQL-0460 A general exception has occurred during local processing.UDA-EE-0094 The operation "matchpattern" is invalid for the following combination of data types: "textblob" and "character"RSV-SRV-0042 Trace back:RSReportService.cpp(826): QFException: CCL_CAUGHT: RSReportService::process()RSReportServiceMethod.cpp(259): QFException: CCL_RETHROW: RSReportServiceMethod::process(): promptPagingForward_RequestRSASyncExecutionThread.cpp(789): QFException: RSASyncExecutionThread::checkExceptionRSASyncExecutionThread.cpp(250): QFException: CCL_CAUGHT: RSASyncExecutionThread::runImpl(): promptPagingForward_RequestRSASyncExecutionThread.cpp(842): QFException: CCL_RETHROW: RSASyncExecutionThread::processCommand(): promptPagingForward_RequestExecution/RSRenderExecution.cpp(625): QFException: CCL_RETHROW: RSRenderExecution::executeAssembly/RSDocAssemblyDispatch.cpp(290): QFException: CCL_RETHROW: RSDocAssemblyDispatch::dispatchAssemblyAssembly/RSLayoutAssembly.cpp(79): QFException: CCL_RETHROW: RSLayoutAssembly::assembleAssembly/RSDocAssemblyDispatch.cpp(357): QFException: CCL_RETHROW: RSDocAssemblyDispatch::dispatchChildrenAssemblyForwardAssembly/RSReportPagesAssembly.cpp(179): QFException: CCL_RETHROW: RSReportPagesAssembly::assembleAssembly/RSDocAssemblyDispatch.cpp(307): QFException: CCL_RETHROW: RSDocAssemblyDispatch::dispatchAssemblyAssembly/RSPageAssembly.cpp(303): QFException: CCL_RETHROW: RSPageAssembly::assembleAssembly/RSDocAssemblyDispatch.cpp(307): QFException: CCL_RETHROW: RSDocAssemblyDispatch::dispatchAssemblyAssembly/RSTableRowAssembly.cpp(175): QFException: CCL_RETHROW: RSTableRowAssembly::assembleAssembly/RSDocAssemblyDispatch.cpp(307): QFException: CCL_RETHROW: RSDocAssemblyDispatch::dispatchAssemblyAssembly/RSTableCellAssembly.cpp(137): QFException: CCL_RETHROW: RSTableCellAssembly::assembleAssembly/RSDocAssemblyDispatch.cpp(357): QFException: CCL_RETHROW: RSDocAssemblyDispatch::dispatchChildrenAssemblyForwardAssembly/RSAssembly.cpp(660): QFException: CCL_RETHROW: RSAssembly::createListIteratorAssembly/RSAssembly.cpp(712): QFException: CCL_RETHROW: RSAssembly::createListIteratorRSQueryMgr.cpp(1088): QFException: CCL_RETHROW: RSQueryMgr::getListIteratorRSQueryMgr.cpp(1164): QFException: CCL_RETHROW: RSQueryMgr::getResultSetIteratorRSQueryMgr.cpp(1324): QFException: CCL_RETHROW: RSQueryMgr::createIteratorRSQueryMgr.cpp(1624): QFException: CCL_RETHROW: RSQueryMgr::executeRsapiCommandRSQueryMgr.cpp(1614): QFException: CCL_RETHROW: RSQueryMgr::executeRsapiCommandRSQueryMgrExecutionHandlerImpl.cpp(189): QFException: CCL_RETHROW: RSQueryMgrExecutionHandlerImpl::execute()RSQueryMgrExecutionHandlerImpl.cpp(173): QFException: CCL_RETHROW: RSQueryMgrExecutionHandlerImpl::execute()QFSSession.cpp(1147): QFException: CCL_RETHROW: QFSSession::ProcessDoRequest()QFSSession.cpp(1145): QFException: CCL_CAUGHT: QFSSession::ProcessDoRequest()QFSSession.cpp(1102): QFException: CCL_RETHROW: QFSSession::ProcessDoRequest()QFSSession.cpp(1078): QFException: CCL_RETHROW: QFSSession::ProcessDoRequest()QFSConnection.cpp(788): QFException: CCL_RETHROW: QFSConnection::ExecuteQFSQuery.cpp(213): QFException: CCL_RETHROW: QFSQuery::Execute v2CoordinationQFSQuery.cpp(4452): QFException: CCL_THROW: CoordinationPlanner
Can't you just group it?
Quote from: yossiea on 25 Apr 2013 02:40:15 PM
Can't you just group it?
Even when they are grouped, and [Expression1] has been removed from the list, the rows are still being duplicated because of the group. When I used to work in Business Objects, I could just great a Maximum() of a field, and place the [Maximum] in the List without the field the Maximum() was based on in the list. Does Cognos not have this capability?
Referenced dataitems should either be in the layout OR associated with the datacontainer properties. So, in case of a list , associate the dataitem that is not used in the properties of the list.
Scratch that. Grouping did work. What I ended up doing was:
- Grouped the [Expression2] with [Expression1] while both were in the list.
- Removed the Group from [Expression1] and removed it from the list.
- Set the Aggregate Function and Rollup Aggregate Function to Maximum on [Expression2]
But now it shows one long column for the groups. Is there anyone to make it so it still shows [Expression2] By Line, instead of just entering in 1 [Expression2] for the group and merging many rows?
Quote from: CalebMF on 25 Apr 2013 03:09:48 PM
But now it shows one long column for the groups. Is there anyway to make it so it still shows [Expression2] By Line, instead of just entering in 1 [Expression2] for the group and merging many rows?
Any help on breaking out the mergin of the rows would be greatly appreciated.
Thanks
There is a "Group Span" property associated with the List Column Body. Try setting it to "none" for your grouped column.