How to dynamically create default selection of current year for Value Prompt?

[abcuser]

Hi,
using Cognos BI 10.2.1 fixpack 6 Report Studio accessing Cognos TM1 10.1.1 fixpack 2 cube. In cube there is Time dimension with year level.

I would like to create a Value Prompt with default selection of current year. So when report is run this year it should display 2015, next year then display 2016 etc.

Question: How to set default selection of Value Prompt object to current year?


I tried to solve this with macro:
1. I created Crosstab and in Columns I dragged measure QuantitySold in Rows I have dragged Query Calculation and in Expression Definition I have copied the following macro:

#'[Cube].[TimeDimension].[TimeHierarchy].[YearLevel]->:[TM].[TimeDimension].[TimeHierarchy].[@MEMBER].[' + timestampMask ( $current_timestamp, 'yyyy' ) + ']'#

This macro generated current year member from Time dimension Year level. So macro is working fine.

2. I deleted current year member from Columns and added some other member to Columns.
3. Created Value Prompt selecting YearLevel.
4. I have set Default Selection copied MUN of current year and report is working fine and 2015 is displayed as default selection in value prompt.
5. I changed Default Selection and copied in above macro. Run report and got error:

Found an internal error: 'Invalid MDDS MUN: #'[Cube].[TimeDimension].[TimeHierarchy].[YearLevel]->:[TM].[TimeDimension].[TimeHierarchy].[@MEMBER].[' + timestampMask ( $current_timestamp, 'yyyy' ) + ']'#


Any idea how to set default selection to current year for Value Prompt object?

Regards

[MFGF]

Hi,
using Cognos BI 10.2.1 fixpack 6 Report Studio accessing Cognos TM1 10.1.1 fixpack 2 cube. In cube there is Time dimension with year level.

I would like to create a Value Prompt with default selection of current year. So when report is run this year it should display 2015, next year then display 2016 etc.

Question: How to set default selection of Value Prompt object to current year?


I tried to solve this with macro:
1. I created Crosstab and in Columns I dragged measure QuantitySold in Rows I have dragged Query Calculation and in Expression Definition I have copied the following macro:

#'[Cube].[TimeDimension].[TimeHierarchy].[YearLevel]->:[TM].[TimeDimension].[TimeHierarchy].[@MEMBER].[' + timestampMask ( $current_timestamp, 'yyyy' ) + ']'#

This macro generated current year member from Time dimension Year level. So macro is working fine.

2. I deleted current year member from Columns and added some other member to Columns.
3. Created Value Prompt selecting YearLevel.
4. I have set Default Selection copied MUN of current year and report is working fine and 2015 is displayed as default selection in value prompt.
5. I changed Default Selection and copied in above macro. Run report and got error:

Found an internal error: 'Invalid MDDS MUN: #'[Cube].[TimeDimension].[TimeHierarchy].[YearLevel]->:[TM].[TimeDimension].[TimeHierarchy].[@MEMBER].[' + timestampMask ( $current_timestamp, 'yyyy' ) + ']'#


Any idea how to set default selection to current year for Value Prompt object?

Regards

Hi,

I think the issue here is that you are using the macro in the wrong place. I assume (you didn't specify) that somewhere you have a query calculation to drive the prompt parameter - eg [Cube].[TimeDimension].[TimeHierarchy].[YearLevel] -> ?Your year parameter?

If so, replace this with an expression using the macro prompt function

eg

#prompt('Your year parameter', 'MUN', '[Cube].[TimeDimension].[TimeHierarchy].[YearLevel]->:[TM].[TimeDimension].[TimeHierarchy].[@MEMBER].[' + timestampMask ( $current_timestamp, 'yyyy' ) + ']')#

The third argument of the prompt macro is the default value to be used if the parameter is not set.

Cheers!

MF.

[abcuser]

@MFGF, no I don't have Query Calculation. Just: Columns: Quantity, Rows: Customer and Value Prompt with time. I have copied macro into Default Selection property of Value Prompt object. It looks to me that code macro is not accepted in Properties.

I now added Query Calculation object into Rows with expression you have provided (changing parameter name to the Value Prompt parameter name). Report can now be executed without errors, I can see year 2015 in crosstab, but Value Prompt is not set to 2015 (it is set to "level year" - by the way I have cleared Default Selection property in Report Studio). So this is not working fine.

I probably didn't understand you. Can you please provide details where should your suggested code be copied into?

[MFGF]

@MFGF, no I don't have Query Calculation. Just: Columns: Quantity, Rows: Customer and Value Prompt with time. I have copied macro into Default Selection property of Value Prompt object. It looks to me that code macro is not accepted in Properties.

I now added Query Calculation object into Rows with expression you have provided (changing parameter name to the Value Prompt parameter name). Report can now be executed without errors, I can see year 2015 in crosstab, but Value Prompt is not set to 2015 (it is set to "level year" - by the way I have cleared Default Selection property in Report Studio). So this is not working fine.

I probably didn't understand you. Can you please provide details where should your suggested code be copied into?

Hi,

How is your prompt driving the results displayed in the crosstab? Have you coded a detail filter? If so, that's really the wrong thing to do when using a dimensional package. If not, you need to explain how your prompt is working. If you do have a detail filter, go to your query, delete the detail filter, then drag a slicer member set from the toolbox into the slicer filter area and code the expression I gave you.

MF.


Sent from my iPad using Tapatalk HD

[abcuser]

Sorry for late reply... yes I had Detailed Filter which is created by default using Value Prompt object.

I did the following:
1. Right click on Crosstab and selected "Go to Query".
2. On right site I have deleted Detailed Filter.
3. Moved "Slicer Member Set" to Slicer window.
4. Copied your code into Expression Definition window and clicked on Validate button and there is "No error" message. It looks like prompt has correct syntax.
5. Run report and I am getting the following error:

Need to resolve prompts.
XQE-PLN-0107 Invalid format for prompt 'Parameter1'. Expected format is unknown.

RSV-SRV-0042 Trace back:RSReportService.cpp(756): XQEException: CCL_CAUGHT: RSReportService::processImpl()RSReportServiceMethod.cpp(259): XQEException: CCL_RETHROW: RSReportServiceMethod::process(): asynchRunSpecification_RequestRSASyncExecutionThread.cpp(854): XQEException: RSASyncExecutionThread::checkExceptionRSASyncExecutionThread.cpp(311): XQEException: CCL_CAUGHT: RSASyncExecutionThread::runImpl(): asynchRunSpecification_RequestRSASyncExecutionThread.cpp(910): XQEException: CCL_RETHROW: RSASyncExecutionThread::processCommand(): asynchRunSpecification_RequestExecution/RSRenderExecution.cpp(585): XQEException: CCL_RETHROW: RSRenderExecution::executeAssembly/RSDocAssemblyDispatch.cpp(325): XQEException: CCL_RETHROW: RSDocAssemblyDispatch::dispatchAssemblyAssembly/RSLayoutAssembly.cpp(79): XQEException: CCL_RETHROW: RSLayoutAssembly::assembleAssembly/RSDocAssemblyDispatch.cpp(419): XQEException: CCL_RETHROW: RSDocAssemblyDispatch::dispatchChildrenAssemblyForwardAssembly/RSReportPagesAssembly.cpp(178): XQEException: CCL_RETHROW: RSReportPagesAssembly::assembleAssembly/RSDocAssemblyDispatch.cpp(369): XQEException: CCL_RETHROW: RSDocAssemblyDispatch::dispatchAssemblyAssembly/RSPageAssembly.cpp(314): XQEException: CCL_RETHROW: RSPageAssembly::assembleAssembly/RSDocAssemblyDispatch.cpp(369): XQEException: CCL_RETHROW: RSDocAssemblyDispatch::dispatchAssemblyAssembly/RSTableRowAssembly.cpp(177): XQEException: CCL_RETHROW: RSTableRowAssembly::assembleAssembly/RSDocAssemblyDispatch.cpp(369): XQEException: CCL_RETHROW: RSDocAssemblyDispatch::dispatchAssemblyAssembly/RSTableCellAssembly.cpp(151): XQEException: CCL_RETHROW: RSTableCellAssembly::assembleAssembly/RSDocAssemblyDispatch.cpp(419): XQEException: CCL_RETHROW: RSDocAssemblyDispatch::dispatchChildrenAssemblyForwardAssembly/RSDocAssemblyDispatch.cpp(369): XQEException: CCL_RETHROW: RSDocAssemblyDispatch::dispatchAssemblyRSQueryMgr.cpp(587): XQEException: CCL_RETHROW: RSQueryMgr::getResultSetIteratorRSQueryMgr.cpp(679): XQEException: CCL_RETHROW: RSQueryMgr::createIteratorRSQueryMgrBasic.cpp(289): XQEException: CCL_RETHROW: RSQueryMgrBasic::executeRsapiCommandRSQueryMgrExecutionHandlerImpl.cpp(175): XQEException: CCL_RETHROW: RSQueryMgrExecutionHandlerImpl::execute()QFSSession.cpp(1153): XQEException: CCL_RETHROW: QFSSession::ProcessDoRequest()QFSSession.cpp(1151): XQEException: CCL_CAUGHT: QFSSession::ProcessDoRequest()QFSSession.cpp(1108): XQEException: CCL_RETHROW: QFSSession::ProcessDoRequest()QFSConnection.cpp(800): XQEException: CCL_RETHROW: QFSConnection::ExecuteQFSQuery.cpp(213): XQEException: CCL_RETHROW: QFSQuery::Execute v2XQEConnector.cpp(289): XQEException: CCL_THROW: XQEConnector::send

6. I have searched the web and there is nothing really useful expect suggesting to install fixpacks. I already have Cognos 10.2.1 + Fixpack 6 + Intermi Fix 10.

Any idea what is wrong?

[MFGF]

Sorry for late reply... yes I had Detailed Filter which is created by default using Value Prompt object.

I did the following:
1. Right click on Crosstab and selected "Go to Query".
2. On right site I have deleted Detailed Filter.
3. Moved "Slicer Member Set" to Slicer window.
4. Copied your code into Expression Definition window and clicked on Validate button and there is "No error" message. It looks like prompt has correct syntax.
5. Run report and I am getting the following error:

Need to resolve prompts.
XQE-PLN-0107 Invalid format for prompt 'Parameter1'. Expected format is unknown.

RSV-SRV-0042 Trace back:RSReportService.cpp(756): XQEException: CCL_CAUGHT: RSReportService::processImpl()RSReportServiceMethod.cpp(259): XQEException: CCL_RETHROW: RSReportServiceMethod::process(): asynchRunSpecification_RequestRSASyncExecutionThread.cpp(854): XQEException: RSASyncExecutionThread::checkExceptionRSASyncExecutionThread.cpp(311): XQEException: CCL_CAUGHT: RSASyncExecutionThread::runImpl(): asynchRunSpecification_RequestRSASyncExecutionThread.cpp(910): XQEException: CCL_RETHROW: RSASyncExecutionThread::processCommand(): asynchRunSpecification_RequestExecution/RSRenderExecution.cpp(585): XQEException: CCL_RETHROW: RSRenderExecution::executeAssembly/RSDocAssemblyDispatch.cpp(325): XQEException: CCL_RETHROW: RSDocAssemblyDispatch::dispatchAssemblyAssembly/RSLayoutAssembly.cpp(79): XQEException: CCL_RETHROW: RSLayoutAssembly::assembleAssembly/RSDocAssemblyDispatch.cpp(419): XQEException: CCL_RETHROW: RSDocAssemblyDispatch::dispatchChildrenAssemblyForwardAssembly/RSReportPagesAssembly.cpp(178): XQEException: CCL_RETHROW: RSReportPagesAssembly::assembleAssembly/RSDocAssemblyDispatch.cpp(369): XQEException: CCL_RETHROW: RSDocAssemblyDispatch::dispatchAssemblyAssembly/RSPageAssembly.cpp(314): XQEException: CCL_RETHROW: RSPageAssembly::assembleAssembly/RSDocAssemblyDispatch.cpp(369): XQEException: CCL_RETHROW: RSDocAssemblyDispatch::dispatchAssemblyAssembly/RSTableRowAssembly.cpp(177): XQEException: CCL_RETHROW: RSTableRowAssembly::assembleAssembly/RSDocAssemblyDispatch.cpp(369): XQEException: CCL_RETHROW: RSDocAssemblyDispatch::dispatchAssemblyAssembly/RSTableCellAssembly.cpp(151): XQEException: CCL_RETHROW: RSTableCellAssembly::assembleAssembly/RSDocAssemblyDispatch.cpp(419): XQEException: CCL_RETHROW: RSDocAssemblyDispatch::dispatchChildrenAssemblyForwardAssembly/RSDocAssemblyDispatch.cpp(369): XQEException: CCL_RETHROW: RSDocAssemblyDispatch::dispatchAssemblyRSQueryMgr.cpp(587): XQEException: CCL_RETHROW: RSQueryMgr::getResultSetIteratorRSQueryMgr.cpp(679): XQEException: CCL_RETHROW: RSQueryMgr::createIteratorRSQueryMgrBasic.cpp(289): XQEException: CCL_RETHROW: RSQueryMgrBasic::executeRsapiCommandRSQueryMgrExecutionHandlerImpl.cpp(175): XQEException: CCL_RETHROW: RSQueryMgrExecutionHandlerImpl::execute()QFSSession.cpp(1153): XQEException: CCL_RETHROW: QFSSession::ProcessDoRequest()QFSSession.cpp(1151): XQEException: CCL_CAUGHT: QFSSession::ProcessDoRequest()QFSSession.cpp(1108): XQEException: CCL_RETHROW: QFSSession::ProcessDoRequest()QFSConnection.cpp(800): XQEException: CCL_RETHROW: QFSConnection::ExecuteQFSQuery.cpp(213): XQEException: CCL_RETHROW: QFSQuery::Execute v2XQEConnector.cpp(289): XQEException: CCL_THROW: XQEConnector::send

6. I have searched the web and there is nothing really useful expect suggesting to install fixpacks. I already have Cognos 10.2.1 + Fixpack 6 + Intermi Fix 10.

Any idea what is wrong?

I suspect you still have something left over in your prompt's default selection value from your previous attempt? Clear this out and try again?

Cheers!

MF.

[abcuser]

Hi,
yes that was the problem, default selection still having some left overs. But I have decided to create a new report, because I have tested so many things that I am not sure if there are some other left overs.

Exact steps:
1. New Crosstab report.
2. Quantity measure to Columns.
3. Organization unit member to Rows.
4. Run report - working fine.
5. Added Value Prompt in wizard pointed it to the Year level and set Auto-Submit property to Yes.
6. Run report - I can select year from report and crosstab is filtered by Value Prompt selection. Working fine.
7. Rigth click on Crosstab and selected "Go to Query".
8. Deleted filter from Detail Filters window.
9. Added Slicer Member Set to Slicer window and added your suggested "prompt" command.
9. Validated the expression with no error.
10. Run report and report has been filtered by "current year" 2015. OK, this is working fine.

But now two problems appeared:
1. In Value Prompt drop down selection I have three members: mylevel  (from step 5), 2014 and 2015. When report is run Crosstab is filtered by "current year" as defined in above steps, but default selection of Value Prompt object still displays "mylevel", I expected to be displayed "2015". You know just like when I define a Default Selection property.

2. The second problem: the 2015 Value Prompt selection and "mylevel" selection now returns the same result in crosstab -  in both cases it is filtered by 2015. I can't select "mylevel" any more to get sum of "all years".

Am I still missing something?

[abcuser]

Any help???

[MFGF]

Any help???

Hi,

If you want to be able to select "All years" as well as being able to select a year, go to the query being used by your value prompt, add a query calculation that uses the expression

union([Your All Years member], children([Your All Years member]))

then use this as the Use Value of your prompt. This should allow you to choose any year or the All Years member. Don't forget to set the prompt property to Auto Submit = Yes if it's on the report page.

Cheers!

MF.

[abcuser]

MFGF, thank you for your help. Additional problem appered...

Additional steps after my previous post steps:
11. Right click on time Value Prompt and selected "Go to Query".
12. Data Items field double click on data item.
13. Expression Definion. Removed "YearLevel" with: union([Your All Years member], children([Your All Years member]))
14. Back to Page Explorer | Report Pages | Page1
15. Click on time Value Prompt and make sure Data | Use Value property is set to step 12 Data Item. Yes already is.
16. Also make sure Value Prompt property General | Auto-Submit is set to Yes. Yes already is.
17. Run report.

I can now see my "All Year" top member which is OK, but now parameter name got displayed as top Value Prompt default option.
I would like to be displayed "PRE 2015" (2015 as current year) and not parameter name.

Please see attachment for detailed explanation of current problem.

[MFGF]

MFGF, thank you for your help. Additional problem appered...

Additional steps after my previous post steps:
11. Right click on time Value Prompt and selected "Go to Query".
12. Data Items field double click on data item.
13. Expression Definion. Removed "YearLevel" with: union([Your All Years member], children([Your All Years member]))
14. Back to Page Explorer | Report Pages | Page1
15. Click on time Value Prompt and make sure Data | Use Value property is set to step 12 Data Item. Yes already is.
16. Also make sure Value Prompt property General | Auto-Submit is set to Yes. Yes already is.
17. Run report.

I can now see my "All Year" top member which is OK, but now parameter name got displayed as top Value Prompt default option.
I would like to be displayed "PRE 2015" (2015 as current year) and not parameter name.

Please see attachment for detailed explanation of current problem.

Ok - the obvious solution seems to be to change the name of your parameter from P1 to PRE 2015? Does this give you what you need?

MF.

[abcuser]

Now every year I have to change Default Selection to current year - the point of this thread is how to set default selection automatically to current year. If I have to manually rename parameter every year, then I am back to where I have started with this thread: manually changing reports every year to "current year" default Value Prompt selections.

By the way, I have tried to change parameter name to current year with macro, but in this case report fails... because I have to manually change Use Value of Value Prompt from P1 parameter name to this new parameter name.

Maybe name the parameter something like "ParameterCurrentYear" - so user would know current year is selected by default, but this is little big ugly solution, because it is more user friendly if "2015" is displayed.