Using the Maximum Function

[CalebMF]

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

[yossiea]

Can't you just group it?

[CalebMF]

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?

[blom0344]

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.

[CalebMF]

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?

[CalebMF]

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

[Lynn]

There is a "Group Span" property associated with the List Column Body. Try setting it to "none" for your grouped column.