I'm trying to build a SSRS report with multiple parameters, and when I add a standard START_DATE and END_DATE parameter I get the expected results from my SQL query line: WHERE SOME_FIELD BETWEEN (:P_START_DATE) and (:P_END_DATE)
I would like to do something like the following scenario:
Setup:
- Date Field 1 & 2: WHERE NEXT_SHIP_DUE_DATE BETWEEN (:P_NEXT_SHIP_START_DATE) AND (:P_NEXT_SHIP_END_DATE)
- Date Field 3 & 4: AND PRIMARY_IMAGE_DATE BETWEEN (:P_PID_START_DATE) AND (:P_PID_END_DATE)
All date parameters in SSRS report allow NULL values and are initially set to NULL.
User should be able to:
- Get all records when no date field parameters are requested
- Get only records based off the date fields that user selects This may be 1 or more.
Scenarios:
- Example 1: User selects no date fields - returns no data
- Example 2: User selects date fields 1 & 2 only - returns no data
- Example 3: User selects all date fields same date range - RETURNS CORRECT DATA
- Example 4: User selects all date fields different date ranges - SSRS error: date not valid for month specified
I've tried several different pieces of SQL but no luck so far. Any help would be appreciated!