I’ve tried everything I know but the report still says the date prompt

[Lorraines33]

I want this report to have an optional date prompt so I can schedule it to email monthly using the “current date”. I also want the flexibility for users to be able to select a date when run from the Reports Menu so they can see previous months’ KPI’s on demand. I’ve tried everything I know but the report still says the date prompt is “Required” when run. How do I make that date prompt “Not Required”?

Date Prompt property says it is Not Required.
Query1 Filter is marked as Optional.
Query1 Filter:

CASE
WHEN (?KPIRunDate? is null) THEN ([Incident Opened Date]between(_first_of_month (_add_days(current_date,-60)))and (_last_of_month (_add_days(current_date,-60))))
ELSE ([Incident Opened Date]between(_first_of_month (_add_days(?KPIRunDate?,-60)))and (_last_of_month (_add_days(?KPIRunDate?,-60))))
END


Query2 (Singleton for display in report title - Incidents Created From xx To xx)
Query Calculation Data Item [From] Expression:

CASE
WHEN (?KPIRunDate? is null) THEN (_first_of_month (_add_days(current_date,-60)))
ELSE (_first_of_month (_add_days(?KPIRunDate?,-60)))
END


Query3 (Singleton for display in report title)
Query Calculation Data Item [To] Expression:

CASE
WHEN (?KPIRunDate? is null) THEN (_last_of_month (_add_days(current_date,-60)))
ELSE (_last_of_month (_add_days(?KPIRunDate?,-60)))
END


Query4(Singleton for display in report title - [KPIMonth])
Query Calculation Data Item [KPIMonth] Expression:

CASE
WHEN (?KPIRunDate? is null) THEN (_add_months (current_date,-1))
ELSE (_add_months (?KPIRunDate?,-1))
END

 

[elsolo21]

Maybe I'm not understanding what you're doing but even if the prompt is optional, it still needs to show. I have your issue myself and I've had to create two separate reports. One with the date logic built into the filters and another for the user to use with the date they want.

-Eric

 

[blom0344]

An alternative is to build 2 prompt pages and then hide the second prompt page based on logic on the first prompt page. This can just be a simple radio button with yes/no with auto submit on. When the user selects no then the autosubmits passes the second promptpage entirely.
You need to create a render variable and set it on the page of the 2nd promptpage and base the logic on the yes/no of the radiobutton prompt of the 1st page..

Ties Blom

 

[Lorraines33]

RESOLVED!

Create a value prompt ( ?DateChoice? ) with static choices of "Current Date" and "Choose Date"; Create a default value to “Current Date”

Modify your Date Prompt that uses ?KPIRunDate? to add a default date value to something not useful: 1901-01-01 or 2999-12-31

Both prompts and the filter can be required.

Query1 Filter:
CASE
WHEN ( ?DateChoice? = 'Current Date') THEN ([Incident Opened Date]between(_first_of_month (_add_days(current_date,-60)))and (_last_of_month (_add_days(current_date,-60))))
ELSE ([Incident Opened Date]between(_first_of_month (_add_days(?KPIRunDate?,-60)))and (_last_of_month (_add_days(?KPIRunDate?,-60))))
END

Query2 (Singleton for display in report title - Incidents Created From xx To xx)
Query Calculation Data Item [From] Expression

CASE
WHEN (?DateChoice?='Current Date') THEN (_first_of_month (_add_days(current_date,-60)))
ELSE (_first_of_month (_add_days(?KPIRunDate?,-60)))
END

Query3 (Singleton for display in report title)
Query Calculation Data Item [To] Expression:
CASE
WHEN (?DateChoice?='Current Date') THEN (_last_of_month (_add_days(current_date,-60)))
ELSE (_last_of_month (_add_days(?KPIRunDate?,-60)))
END

Query4(Singleton for display in report title - [KPIMonth])
Query Calculation Data Item [KPIMonth] Expression:
CASE
WHEN (?DateChoice?='Current Date') THEN (_add_months (current_date,-1))
ELSE (_add_months (?KPIRunDate?,-1))
END