Date Prompt range macro issue

[mrcool]

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

[sdf]

have you tried making the whole case statement as the macro?

[mrcool]

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

[CognosPaul]

Can you give an example of the passed value? Are you running DQM or CQM?

[mrcool]

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

[CognosPaul]

In DQM you can use the macro simple case:

Code Select Expand

#
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:

Code Select Expand

#
case promptmany('date','date','null')
when 'null' then 'cast(null,''integer'')'
else '[query item]'
end
#


[CognosPaul]

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...

[CognosPaul]

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.

[mrcool]

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