Hi All,
Am trying to a build a chart report where the category should be changed automatically based on date range selected.
Example - If the difference between selected Start Date and End Date is less than 30, then [XXX].[XXX_END_DATE].[XXX_END_DATE].[DATE],
If the difference between selected Start Date and End Date is between 30 and 180, then [XXX].[XXX_END_DATE].[XXX_END_DATE].[MONTH],
If the difference between selected Start Date and End Date is between 181 and 365, then [XXX].[XXX_END_DATE].[XXX_END_DATE].[QUARTER],
If the difference between selected Start Date and End Date is > 365, then [XXX].[XXX_END_DATE].[XXX_END_DATE].[YEAR]
Am able to find out the difference between selected dates through COUNT and INTERSECT function. But not able to display the set expression/level dynamically based on the difference in date value. Tried with simple case when statement and throws an error
XQE-PLN-0264 The argument at position 2 of the function 'when' is invalid in data item 'XXXX' of query 'XXXXX', expected one of the following types: 'value'.
The expression used in data item -
case
when 1 < 5
then [XXX].[XXX_END_DATE].[XXX_END_DATE].[DATE]
else [XXX].[XXX_END_DATE].[XXX_END_DATE].[MONTH]
end
We do display another dimension based on prompt selection and seems to be displayed properly. The expression used in the data item
case
when 1 = 1
then [XXX].[XXX_END_DATE].[XXX_PROJ_TYPE].[PTC]
else [XXX].[XXX_END_DATE].[XXX_DEPT].[DC]
end
Not able to understand on this error, as when the expression is compared with =, the report works fine, while comparison based on <,>, between throws an error.
Gurus - please help on this issue.
Thanks!
end