Record Selection on multiple value parameters 1

[jedoublefunk]

I feel like I'm missing something obvious, but I can't seem to get this to work.

Using CR XI, I've setup a report that has a date range parameter to allows a user to limit the data that appears on the report. However, I'd also like to setup another parameter that lets the user choose which date fields they are querying on. For example, if the user chose Jan 1-07 to Jan 31-07, they also must choose one or all of the following dates to use to filter down further:

Hire Date
Termination Date
Birth Date

If they chose only Hire Date, the report would only show employees that had been hired in that time period. If they chose Hire Date and Termination Date, then it would show employees that were hired and terminiated in that date range and so on.

Is there an easy way to setup a record selection formula to accomodate the Dates to Use option?

I've come up with a formula (that I don't like) that almost works, but there has to be an easier way than this.

[COLOR=green]
//Start

[COLOR=blue]
local stringVar Date1;
local stringVar Date2;
[/color]

//Determines how many Dates were selected to query on puts 
// them in the order of Hire Date, Termination Date, BDay 
//Date so the record selection formula below can work 
//properly.

[COLOR=blue]
if ubound({?DatesToUse}) = 3
then    
    {?DatesToUse}[1]= 'Hire Date';
    {?DatesToUse}[2]= 'Termination Date';
    {?DatesToUse}[3]= 'BDay Date';

if ubound({?DatesToUse}) = 2
    then 
        Date1:={?DatesToUse}[1];
        Date2:={?DatesToUse}[2];

if Date1 <> "Hire Date"
then 
    if Date2 = "Hire Date"
    then 
        ({?DatesToUse}[1] = Date2;
        {?DatesToUse}[2] = Date1;)
    else
        if Date1 = "BDay Date"
        then 
            {?DatesToUse}[1] = Date2;
            {?DatesToUse}[2] = Date1;
    
[/color]
//Record Selection Formula, now that the Array is in the 
//correct order
[COLOR=blue]
if ubound({?DatesToUse}) = 3
then 
    ({Employees.Hire Date} in {?DateRange}
    OR {Employees.Termination Date} in {?DateRange}
    OR {Employees.BDay} in {?DateRange})
else 
    if ubound({?DatesToUse}) = 2
    then 
        if ({?DatesToUse}[1] = 'Hire Date' and {?DatesToUse}[2] = 'Termination Date')
        then 
            ({Employees.Hire Date} in {?DateRange}
            OR {Employees.Termination Date} in {?DateRange})
        else
            if ({?DatesToUse}[1] = 'Hire Date' and {?DatesToUse}[2] = 'BDay Date')
            then 
                 ({Employees.Hire Date} in {?DateRange}
                 OR {Employees.BDay} in {?DateRange})
            else
                if ({?DatesToUse}[1] = 'Termination Date' and {?DatesToUse}[2] = 'BDay Date')
                then 
                    ({Employees.Termination Date} in {?DateRange}
                    OR {Employees.BDay} in {?DateRange})
else 
    if {?DatesToUse}[1] = 'Hire Date'
    then {Employees.Hire Date} in {?DateRange}
else 
    if {?DatesToUse}[1] = 'Termination Date'
    then {Employees.Termination Date} in {?DateRange}
else 
    if {?DatesToUse}[1] = 'BDay Date'
    then {Employees.BDay} in {?DateRange}

[/color]

//END
[/color]

I'm lucky there's only 3 choices here, but if there were more options, I can't imagine the pain this would be for someone.

I'm not sure it matters, but it's an Oracle 9 db.

Help please!

 

[jedoublefunk]

The more I look at the formula, the more I realize it won't work. Still, I am stumped. I've also tried this formula:

if "BDay Date" in {?DatesToUse}
then 
    {Employees.BDay} in {?DateRange};

if "Termination Date" in {?DatesToUse} 
then 
    {Employees.Termination Date} in {?DateRange};

if "Hire Date" in {?DatesToUse}
then 
    {Employees.Hire Date} in {?DateRange};

if "Hire Date" in {?DatesToUse} and "BDay Date" in {?DatesToUse}
then 
    ({Employees.Hire Date} in {?DateRange}
    OR {Employees.BDay} in {?DateRange});

if "Hire Date" in {?DatesToUse} and "Termination Date" in {?DatesToUse} 
then 
    ({Employees.Hire Date} in {?DateRange}
    OR {Employees.Termination Date} in {?DateRange});

if "Hire Date" in {?DatesToUse} and "Termination Date" in {?DatesToUse} and "BDay Date" in {?DatesToUse}
then 
    ({Employees.Hire Date} in {?DateRange}
    OR {Employees.Termination Date} in {?DateRange}
    OR {Employees.BDay} in {?DateRange});

But this doesn't seem to work either...

I'm looking for any ideas. Thanks.

 

[smcnulty2000]

Make a param that has the three date options

"Based on (any or all): Hire date, Term Date, Birth"

then you can do this:

(if instr(param,"B",1)>0
then {Employees.BDay} in {?DateRange}
else 1=1)

and 
(if instr(param,"H",1)>0
then {Employees.Hire Date} in {?DateRange}
else 1=1)

and
(if instr(param,"Ter",1)>0
then {Employees.Termination Date} in {?DateRange}
else 1=1)

Adjust this to your parameter style, you could even make them input numbers and then run that through a formula to separate the 1s from the 10s and the 100s.

 

[jedoublefunk]

smcnulty2000,

I like the idea, but you because the parameter is an Array, you can't use the instr function.

The other wrinkle I see is there is no guarantee what order the user will select the Dates to use, so you could end up with BDay, Hire, Term, or Hire, BDay, Term in the Array dimensions 1 to 3.

If I could force the values chosen into the same order everytime then I could test for the length of the array and figure it out. That's what I was trying to do in the first post, but it's pretty ugly and I couldn't get it to work.

 

[MrBillSC]

Another option:
("Hire Date" in {?DatesToUse} and
{table.HireDate} = {?DateRange}) or
("Term Date" in {?DatesToUse} and
{table.TermDate} = {?DateRange}) or
("Birth Date" in {?DatesToUse} and
{table.BirthDate} = {?DateRange})

MrBill

 

[jedoublefunk]

Nice work!

I knew it didn't have to be that difficult but my logic cap just isn't working today. Thanks MrBill!

 

[smcnulty2000]

I actually wasn't recommending an array.

I am recommending you not use an array. You can just use a single string value.

I have about 70 reports using this technique in one way or another.