Hi All,
I am trying to capture the date range prompt that is coming from summary report in the drill through report using the prompt macro.
What I am trying to do here is, if the dates from the date range prompt aren't selected in the summary report I want to make a column null in the detail report. I am doing this using the case statement.
I am using the below statement which isn't working:
case when
#promptmany('date','date','null')# is null then null
else
[query item]
end
error: Found an internal error:
com.cognos.xqe.query.parameters.BoundedRangeParameterValueItem incompatible with com.cognos.xqe.query.parameters.V5ParameterValueItem
I even tried giving sq but it didn't work.
any ideas?
Thanks,
mc
have you tried making the whole case statement as the macro?
Hi sdf,
I didn't try that but I think the problem is the query engine not able to read the format.
Can you please modify the above statement I sent to make it clear?
Thanks,
mc
Can you give an example of the passed value? Are you running DQM or CQM?
Hi Paul,
I am using DQM and I am using package based drill through. When I view the passed values in target mapping I just see 2 dates(attached the screenshot) in use value as I have date range prompt in the parent report.
Thanks,
mc
In DQM you can use the macro simple case:
#
case promptmany('date','date','null')
when 'null' then 'null'
else '[query item]'
end
#
You may need to cast the null, in which case it might be:
#
case promptmany('date','date','null')
when 'null' then 'cast(null,''integer'')'
else '[query item]'
end
#
Actually no, this won't work. Range parameters don't work with macros for some reason. You might have to adapt this to start_date and end_date. Checking...
Meh. Range prompt values are only valid as a prompt alias (?prompt name?). There are a few things you could do.
1. Switch the prompts to individual start/end prompts instead of a range.
2. Reference another parameter that determines if a date range is being passed. This can be accomplished in source report by creating a hidden prompt and using the prompt API to interrogate the range prompt.
3. Open a PMR/RFE with IBM to get them to create a way to reference range prompts in a macro.
Quote from: CognosPaul on 20 Feb 2017 08:26:23 AM
Meh. Range prompt values are only valid as a prompt alias (?prompt name?). There are a few things you could do.
1. Switch the prompts to individual start/end prompts instead of a range.
2. Reference another parameter that determines if a date range is being passed. This can be accomplished in source report by creating a hidden prompt and using the prompt API to interrogate the range prompt.
3. Open a PMR/RFE with IBM to get them to create a way to reference range prompts in a macro.
Hi Paul,
Thank you so much for your inputs. I will raise a PMR with IBM on this.
Cheers,
mc