Topic
1 reply Latest Post - ‏2013-04-26T18:33:37Z by CalebMF
CalebMF
CalebMF
53 Posts
ACCEPTED ANSWER

Pinned topic Using the Maximum Function

‏2013-04-22T20:50:50Z |

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

  • CalebMF
    CalebMF
    53 Posts
    ACCEPTED ANSWER

    Re: Using the Maximum Function

    ‏2013-04-25T19:55:42Z  in response to 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?

    Updated on 2013-04-26T18:33:28Z at 2013-04-26T18:33:28Z by CalebMF
    • This reply was deleted by CalebMF 2013-04-26T18:33:40Z.