SYMPTOM:
Users get incorrect results when creating reports including a filter showing results between two date value prompts expressions in Strategy Desktop.
STEPS TO REPRODUCE:
This issue can be reproduced in the Strategy Tutorial project by creating a new date value prompt called 'Enter a Value' and a very simple report including the Day attribute and the Revenue metric.
The objective is to create a report where users are asked to insert a date and it will calculate the revenue on each of the seven previous days. In order to get that, it is necessary to include an Attribute Qualification filter based on Day and qualifying on the ID, as shown below:
This issue occurs when setting the filter to get the revenue of the days between two ‘Custom’ expressions like '? – 7' and '?'. The SQL generated does not include the WHERE statement of the filter and the report is showing the information unfiltered, as shown below:
select a11. AS DAY_DATE,
sum(a11.) AS Revenue
from a11
group by a11.
CAUSE:
The problem here is that the filter is set to qualify on two 'Custom' expressions, so it is expecting the second one to be an expression as well. In fact, when including two expressions like '? – 7' and '? + 2', it generates the correct SQL and therefore the correct report:
select a11. AS DAY_DATE,
sum(a11.) AS Revenue
from a11
where a11. between (#2006-10-04# - 7) and (#2006-10-04# + 2)
group by a11.
ACTION:
If the user creates the same filter in the same report but qualifying between the Custom” expression '? – 7' and the 'Simple Promp” expression 'Shortcut to: Enter a Value (Date)' that users get just dragging and dropping the prompt in the text box, the SQL is properly generated including the WHERE statement and users get the correct results in the report, as shown below:
select a11. AS DAY_DATE,
sum(a11.) AS Revenue
from a11
where a11. between (#2006-10-04# - 7) and #2006-10-04#
group by a11.
