Issue with Date Macro

[Sundaramoorthy]

Hi All,

I am getting error QE-DEF-0405 - Incompatible data types in case statement if i used below case statement in an data item.

case when ('C1' in (#promptmany('Introdate','date')#))
then ([Sales (query)].[Products].[Introduction date])
else('dummy')
end

Here introduction date has an data type(timestamp) as default in DB. Even i changed the data type to timestamp in macro expression, but still it is not working. Here C1 is meant for dynamic selection of column from value prompt.

Could you please assist me on this.

[Lynn]

Every possible outcome from the case statement must be of the same data type. The error message you are getting is trying to tell you this by saying the data types are incompatible.

Your "when" clause is just a condition to decide if the then or the else is returned by the expression. I don't understand how a string C1 could ever be found in a date or a timestamp data item so I suspect you'll have issues resolving that logic.

Your "then" clause is referring to a data item which you say is a timestamp, but your "else" clause is very clearly a string. You could try null for the else or cast null to a timestamp or use cast or _make_timestamp to create some other dummy value that is truly, actually, positively a timestamp.

If you fix the "else" I suspect you'll still get some type of error related to your "when" clause that is trying to compare a string to a date.

[Sundaramoorthy]

Hi Lynn,

Actually my requirement is, whatever columns i got selected in static prompt that column alone need to get displayed in the report. For that scenario,i have kept it as below

Use: c1
Display: Introduction Date

Please find attached the report xml which used the sample package 'GO Sales (query)'.

[Lynn]

Hi Lynn,

Actually my requirement is, whatever columns i got selected in static prompt that column alone need to get displayed in the report. For that scenario,i have kept it as below

Use: c1
Display: Introduction Date

Please find attached the report xml which used the sample package 'GO Sales (query)'.

You still need to have a case statement that resolves in every possible way to the same data type. I modified your sample to use a case statement for each of the columns that either returns the requested column or is null. I then use and advanced conditional style to suppress anything that the user does not select.

If it were for a single column I'd use a prompt macro with type 'token' and set the use values in the prompt control to be the various column references. I suspect there is an elegant way to do the same for a multi-select situation using other macro functions to pick up the appropriate element for each column but I didn't hit on the answer right away so gave up. If any other prompt macro experts out there pick up the challenge I'd be happy to see a solution!

Code Select Expand


case
  when
       '[Sales (query)].[Products].[Product line]'
            in ( #substitute( ';', ',', promptmany('SelectedColumn','string' ))# )
  then [Sales (query)].[Products].[Product line]
  else null
end


[Sundaramoorthy]

Lynn,

Prompt macro got worked for date field..i actually forgot to replace the data type as date in else statement & also in macro i replaced data type as nvarchar instead of date...