Skipping prompt where all values are chosen

[138006]

hi
My requirement is like this:
I will have a prompt in the report like
Date >= Promt(Start Date)
and Date <= Prompt(End date)

Now these Prompts show list of values but are not mandatory (means skipping these will ideally give all results)

However when the prompt is skipped MSTR generates
Date >= ...... This is wrong.

How to get this?

 

[entaroadun]

Create a custom filter:

ApplyComparison("((case when #1 is null then true else #0>= #1) and (case when #2 is null then true else #0<=#2 end))",[Date],[Start Date],[End Date])

 

[138006]

hi
I have 2 elemnt prompts on Year called Start Yr and End yr.Clicked the list of elements button and unchecked "Prompt Answer is Required" checkbox(so that if the user doesn't wants all he just skips the prompt).

I ran the report with only year and the folowing filter (similar to what u sent but appropriately changed for Access--I amtrying on MSTR's Tutorial project):

ApplyComparison ("((IIF(#1 is null, true, #0>= #1)) and (IIF(#2 is null, true, #0<= #2)))", Year@ID, @[Start Yr], @[End Yr])

But this is the SQL that it generates:

select distinct a11.[YEAR_ID] AS YEAR_ID
from [LU_YEAR] a11
where ((IIF(a11.[YEAR_ID] in (2001) is null, true, a11.[YEAR_ID]>= a11.[YEAR_ID] in (2001))) and (IIF(a11.[YEAR_ID] in (2002) is null, true, a11.[YEAR_ID]<= a11.[YEAR_ID] in (2002))))

How to make it correct??

 

[entaroadun]

Hmm. What SQL does it generate if you give the element prompts empty lists?

 

[138006]

For All values(i.e skipping the prompts) for both Start Yr and End yr, it gives desired result and desired SQL:

select distinct a11.[YEAR_ID] AS YEAR_ID
from [LU_YEAR] a11

However if i skip the first prompt(Start Yr) and give 2002 as answer to prompt End Yr, I expect all years <=2002 to be shown. However then also, the above SQL is generated and the report gives all Year values.

 

[entaroadun]

Hmm... That's weird... Try breaking it into two filters:

ApplyComparison("(#0)", @[Start Yr])

ApplyComparison("(#0)", @[End Yr])

Bring both filters into the report. Try them out and let us know what SQL gets generated.

 

[138006]

created 2 filters
ApplyComparison ("(#0 >= #1)", Year@ID, @[Start Yr])
ApplyComparison ("(#0 <= #1)", Year@ID, @[End Yr])

This is the SQL

select distinct a11.[YEAR_ID] AS YEAR_ID
from [LU_YEAR] a11
where ((a11.[YEAR_ID] >= a11.[YEAR_ID] in (2000))
and (a11.[YEAR_ID] <= a11.[YEAR_ID] in (2002)))

obviously, again--no data

 

[entaroadun]

Try the structure I have listed, without the Year attribute. Test what happens when you answer the prompts and when you don't answer them.