Set 12 Data Item Names' Labels by Expression/ Code?

[Cognos_Jan2017]

A Prompt Page includes Value Prompts to select 1 of 4 Months (March, June, September, and December) and a Year.

Based on the selections, we have 12 data items for the 12 Quarters (starting w/ a 2-month lag), preceding the
selected Month and Year, to Total the appropriate 3 months measures for each Quarter.

We would like to utilize a Case When to assign Labels to the 12 Data Items ...
IE, if June 2017 is selected, we would like to do something like ...
... for ...
R3 (1st Qtr back) ... 'Feb - Apr FY 17 Q3'
R6 (2nd Qtr back) ... 'Nov - Jan FY 17 Q2'
R9 (3rd Qtr back) ... 'Aug - Oct FY 17 Q1'
R12 (4th Qtr back) ... 'May - Jul FY 16 Q4'
R15 (5th Qtr back) ... 'Feb - Apr FY 16 Q3'
R18 (6th Qtr back) ... 'Nov - Jan FY 16 Q2'
R21 (7th Qtr back) ... 'Aug - Oct FY 16 Q1'
R24 (8th Qtr back) ... 'May - Jul FY 15 Q4'
R27 (9th Qtr back) ... 'Feb - Apr FY 15 Q3'
R30 (10th Qtr back) ... 'Nov - Jan FY 15 Q2'
R33 (11th Qtr back) ... 'Aug - Oct FY 15 Q1'
R36 (12th Qtr back) ... 'May - Jul FY 14 Q4'

Can that be done?  I understand Cognos isn't
a programming language like Visual Basic, but
many statements are similar, including functions.

Have a good weekend, and TIA, Bob

[New_Guy]

Hi,
Is this dimensional or relational reporting?
Good luck
New guy

[Cognos_Jan2017]

Thank you.  Relational.

Playing w/ idea today to write query calculation for each of the 12 Data Items, unlock the Label, replace w/ the query calculation, and lock.

Problem was, always got parsing error at last position.

Tried something like this ...
Case
When ?p_TMonth?=6 and ?p_TCalYr?=2017 Then 'Feb - Apr FYI 17 Q3' <--- using 6 for June ...
... other When scenarios similar to above ...
Else 'R3'
End

Ideas?  TIA, Bob

[BigChris]

That case statement looks ok from what I can see...can you post the parsing error that you're getting?

[Cognos_Jan2017]

Thank you BigChris.

Just tried ...
Case
When ?p_TMonth? = 6 and ?p_TCalYr? = 2017 Then 'Feb - Apr FY 17 Q3'
End

IF I leave the "End" off, I get the parsing error at the position at the last, IE, " ...Q3'.  My fault.

With the End there, and even ...
Case
When ?p_TMonth? = 6 and ?p_TCalYr? = 2017 Then 'Feb - Apr FY 17 Q3'
Else
'R3'
End

... the error msg is ...
A text child node is expected ...  I don't have the skill, yet, to decipher what the error is saying.
RSV-SRV-0042 Trace back:
RSReportService.cpp(732): QFException: CCL_CAUGHT: RSReportService::processImpl()
RSReportServiceMethod.cpp(259): QFException: CCL_RETHROW: RSReportServiceMethod::process(): validateValidateSpecification_Request
RSASyncExecutionThread.cpp(871): QFException: RSASyncExecutionThread::checkException
RSASyncExecutionThread.cpp(566): QFException: CCL_CAUGHT: RSASyncExecutionThread::handlePromptException
RSASyncExecutionThread.cpp(916): QFException: CCL_RETHROW: RSASyncExecutionThread::processCommand(): validateValidateSpecification_Request
RSQueryMgrBasic.cpp(296): QFException: CCL_RETHROW: RSQueryMgrBasic::executeRsapiCommand
RSQueryMgrBasic.cpp(620): QFException: CCL_THROW: RSQueryMgrBasic::processException
RSFaultHandler.cpp(97): QFException: CCL_THROW: RSFaultHandler::handleNonCriticalException
QFSSession.cpp(1182): QFException: CCL_RETHROW: QFSSession::ProcessDoRequest()
QFSSession.cpp(1180): QFException: CCL_CAUGHT: QFSSession::ProcessDoRequest()
QFSSession.cpp(1137): QFException: CCL_RETHROW: QFSSession::ProcessDoRequest()
QFSSession.cpp(1113): QFException: CCL_RETHROW: QFSSession::ProcessDoRequest()
QFSConnection.cpp(943): QFException: CCL_RETHROW: QFSConnection::GetParameters
QFSQuery.cpp(488): QFException: CCL_RETHROW: QFSQuery::GetParameters
CoordinationQFSQuery.cpp(4774): QFException: CCL_THROW: CoordinationQFSQuery::RectifyMandatoryParameters

TIA, Bob

[Cognos_Jan2017]

For what it's worth ... Google search found ...
" ... How big is the report?  The CCL errors typically indicate a memory issue. ..."

The Report has 7 Lists on one Page, and the Prompt Page has 7 Value Prompts, but only
5 of those Prompts are currently used for the Report.

[MFGF]

Thank you BigChris.

Just tried ...
Case
When ?p_TMonth? = 6 and ?p_TCalYr? = 2017 Then 'Feb - Apr FY 17 Q3'
End

IF I leave the "End" off, I get the parsing error at the position at the last, IE, " ...Q3'.  My fault.

With the End there, and even ...
Case
When ?p_TMonth? = 6 and ?p_TCalYr? = 2017 Then 'Feb - Apr FY 17 Q3'
Else
'R3'
End

... the error msg is ...
A text child node is expected ...  I don't have the skill, yet, to decipher what the error is saying.
RSV-SRV-0042 Trace back:
RSReportService.cpp(732): QFException: CCL_CAUGHT: RSReportService::processImpl()
RSReportServiceMethod.cpp(259): QFException: CCL_RETHROW: RSReportServiceMethod::process(): validateValidateSpecification_Request
RSASyncExecutionThread.cpp(871): QFException: RSASyncExecutionThread::checkException
RSASyncExecutionThread.cpp(566): QFException: CCL_CAUGHT: RSASyncExecutionThread::handlePromptException
RSASyncExecutionThread.cpp(916): QFException: CCL_RETHROW: RSASyncExecutionThread::processCommand(): validateValidateSpecification_Request
RSQueryMgrBasic.cpp(296): QFException: CCL_RETHROW: RSQueryMgrBasic::executeRsapiCommand
RSQueryMgrBasic.cpp(620): QFException: CCL_THROW: RSQueryMgrBasic::processException
RSFaultHandler.cpp(97): QFException: CCL_THROW: RSFaultHandler::handleNonCriticalException
QFSSession.cpp(1182): QFException: CCL_RETHROW: QFSSession::ProcessDoRequest()
QFSSession.cpp(1180): QFException: CCL_CAUGHT: QFSSession::ProcessDoRequest()
QFSSession.cpp(1137): QFException: CCL_RETHROW: QFSSession::ProcessDoRequest()
QFSSession.cpp(1113): QFException: CCL_RETHROW: QFSSession::ProcessDoRequest()
QFSConnection.cpp(943): QFException: CCL_RETHROW: QFSConnection::GetParameters
QFSQuery.cpp(488): QFException: CCL_RETHROW: QFSQuery::GetParameters
CoordinationQFSQuery.cpp(4774): QFException: CCL_THROW: CoordinationQFSQuery::RectifyMandatoryParameters

TIA, Bob

Hi,

I'm wondering if your prompt parameters are text values not numbers? You could try:

Case
When ?p_TMonth? = '6' and ?p_TCalYr? = '2017' Then 'Feb - Apr FY 17 Q3'
End

Does this work?

MF.

[Cognos_Jan2017]

Thank you.

I wondered the same thing ... if the Static Choices could somehow require ensuring a number.

Trying that still gives A Text Child Node is Expected w/ error of ...
RSV-SRV-0042 Trace back:
RSReportService.cpp(732): QFException: CCL_CAUGHT: RSReportService::processImpl()
RSReportServiceMethod.cpp(259): QFException: CCL_RETHROW: RSReportServiceMethod::process(): validateValidateSpecification_Request
RSASyncExecutionThread.cpp(871): QFException: RSASyncExecutionThread::checkException
RSASyncExecutionThread.cpp(566): QFException: CCL_CAUGHT: RSASyncExecutionThread::handlePromptException
RSASyncExecutionThread.cpp(916): QFException: CCL_RETHROW: RSASyncExecutionThread::processCommand(): validateValidateSpecification_Request
RSQueryMgrBasic.cpp(296): QFException: CCL_RETHROW: RSQueryMgrBasic::executeRsapiCommand
RSQueryMgrBasic.cpp(620): QFException: CCL_THROW: RSQueryMgrBasic::processException
RSFaultHandler.cpp(97): QFException: CCL_THROW: RSFaultHandler::handleNonCriticalException
QFSSession.cpp(1182): QFException: CCL_RETHROW: QFSSession::ProcessDoRequest()
QFSSession.cpp(1180): QFException: CCL_CAUGHT: QFSSession::ProcessDoRequest()
QFSSession.cpp(1137): QFException: CCL_RETHROW: QFSSession::ProcessDoRequest()
QFSSession.cpp(1113): QFException: CCL_RETHROW: QFSSession::ProcessDoRequest()
QFSConnection.cpp(943): QFException: CCL_RETHROW: QFSConnection::GetParameters
QFSQuery.cpp(488): QFException: CCL_RETHROW: QFSQuery::GetParameters
CoordinationQFSQuery.cpp(4774): QFException: CCL_THROW: CoordinationQFSQuery::RectifyMandatoryParameters

[Cognos_Jan2017]

The error identifier, if that will help find the Root Cause, is ...  QFS-ERR-0125.

As discussed earlier, the Case When w/ the Prompt descriptions, in theory, should work.

TIA, Bob

[Cognos_Jan2017]

Solution found ...
New_Guy, BigChris, and MFGF.  It had failed because ...
We upgraded to Cognos Analytics a week ago.  Opening the Report to edit, in Analytics, had the Radio Button prompts for Month and Year w/ no values.

I am still new to Cognos, but I thought, in 10.2.2 Report Studio, testing a validation where the required prompts had no value would force prompts.

Apparently that doesn't occur in Analytics.  However the error 0125 might pinpoint that.

I will need to write Query Calculations for 12 Quarters, then unlock, delete the Data Item label, assign the Query Calculation, and lock.  Should work fine.

Follow-up thoughts ... the Report has 7 Lists w/ separate queries assigned.  I guess the 12 Query Calculations will need to be written 84 times?

I will also play w/ some Word Wrap search results to see how the columns won't be too wide.

Will also learn Cast better to get the two far right values to concatenate to FY so the code written now will always work for the appropriate FY.

THANK all of you for helping, Bob

[Cognos_Jan2017]

Will also learn Cast better to get the two far right values to concatenate to FY so the code written now will always work for the appropriate FY.

For people following this, NO Cast.  Using 'Right', although the 1st argument is supposed to be a string, works fine, IE ...

Case
When ?p_TMonth? = 6 Then 'Feb - Apr FY '+Right(?p_TCalYr?,2)+ ' Q3'
When ?p_TMonth? = 3 Then 'Nov - Jan FY '+Right(?p_TCalYr?,2)+ ' Q2'
When ?p_TMonth? = 12 Then 'Aug - Oct FY '+Right(?p_TCalYr?,2)+ ' Q1'
When ?p_TMonth? = 9 Then 'May - Jul FY '+Right(?p_TCalYr?,2)+ ' Q4'
End

[Cognos_Jan2017]

Case
When ?p_TMonth? = 6 Then 'Feb - Apr FY '+Right(?p_TCalYr?,2)+ ' Q3'
When ?p_TMonth? = 3 Then 'Nov - Jan FY '+Right(?p_TCalYr?,2)+ ' Q2'
When ?p_TMonth? = 12 Then 'Aug - Oct FY '+Right(?p_TCalYr?,2)+ ' Q1'
When ?p_TMonth? = 9 Then 'May - Jul FY '+Right(?p_TCalYr?,2)+ ' Q4'
End

Note:  Above is for the 1st Quarter (Rolling 3) w/ a 2-month lag.

I will write code to adjust appropriately running back 12 Quarters.