Multiple Values Parameters...Again!!! 1

[ChiTownDiva]

Happy Monday Everybody!!!

I hate multiple parameters!!

I have a report where I need to be able to pull multiple instructors. The only thing that ties their name to the database is their id number.

Here's my selection criteria:

{CRSE.CRSE_CD} like {?Course Code} and
(IF {?Instructor} = 'Margaret Barber' THEN {PERSON.ID} = '10158C8' AND
{CRSE_SESSION.END_DT} in {?Start Date} to {?End Date} OR
IF {?Instructor} = 'Pamela Rockett' THEN {PERSON.ID} = '10158D6' AND
{CRSE_SESSION.END_DT} in {?Start Date} to {?End Date} OR
if {?Instructor} = "*" then {PERSON.PERS_ID} like "*" )

I cannot figure out for the life of me why this won't pull any records if I add both Margaret and Pamela...if I put in just 'Margaret Barber' or 'Pamela Rockett' or an "*", I get records.

What am I not doing?

Thanks in advance!

ChiTownDiva

 

[ChiTownDiva]

Hi...

I got it to work sort of...

{CRSE.CRSE_CD} like {?Course Code} and
{CRSE_SESSION.END_DT} in {?Start Date} to {?End Date} and
(IF {?Instructor} = 'Margaret Barber' THEN {PERSON.PERS_ID} = '10158C8' OR
IF {?Instructor} = 'Pamela Rockett' THEN {PERSON.PERS_ID} = '10158D6'
else
if {?Facilitator} = "All" then {PERSON.PERS_ID} like "*" )

What gives?

ChiTownDiva

 

[rhinok]

The major issue with this formula is that its inefficient. The If-Then-Else statements won't be evaluated until the records are returned from the DB.

Try the following formula instead:

{CRSE.CRSE_CD} like {?Course Code}
and {CRSE_SESSION.END_DT} in {?Start Date} to {?End Date}

//Set the Default Values for the (?Instructor}Parameter as 'ALL' followed
//by a list of PERS_ID values.
//Use the Instructor Names as the Descriptions for their respective IDs.  
//Display the Description only.
//Also, remember to Allow Multiple Values...
//The In statement allows the user to select an array of Instructors

and
(
  If {?Instructor} <> 'ALL'
  Then {PERSON.PERS_ID} In {?Instructor}
  Else If  {?Instructor} = 'ALL'
  True
)

You should find that the formula above is much more efficient (double-check Show SQL Query - the Where Clause should be populated with PERS_IDs). Also, it lets the User select a single Instructor, multiple Instructors or all Instructors.

I left the Course Code statement alone because I don't know if you are allowing searches on partial course codes. If so, the Like statement is fine (the only time I like to use 'Like'). If you're using Like to allow for 'All' courses, then I'd recommend the following formula (using the same general rules from the comments above):

(
  If {?Course Code} <> 'All'
  Then {CRSE.CRSE_CD} In {?Course Code}
  Else If {?Course Code} = 'All'
  Then True
)
and {CRSE_SESSION.END_DT} in {?Start Date} to {?End Date}
and
(
  If {?Instructor} <> 'ALL'
  Then {PERSON.PERS_ID} In {?Instructor}
  Else If  {?Instructor} = 'ALL'
  True
)

Hope this helps!

 

[ChiTownDiva]

Thanks for the response rhinok...

The problem I having is that 'Pamela Rockett' is the parameter value being passed...the only way to tie the parameter value of 'Pamela Rockett' to the database is by her id, 10158D6. The id means nothing to the end user, which is why I have to use their name.

Thanks.

ChiTownDiva

 

[rhinok]

The comments in the formula account for this

Let the user select the Description (the Instructor name), but use Pers_ID as the actual value for the parameter...

 

[ChiTownDiva]

Rhinok...

I thought that what I was trying to do...

{CRSE.CRSE_CD} like {?Course Code} and
(IF {?Instructor} = 'Margaret Barber' THEN {PERSON.ID} = '10158C8' AND
{CRSE_SESSION.END_DT} in {?Start Date} to {?End Date} OR
IF {?Instructor} = 'Pamela Rockett' THEN {PERSON.ID} = '10158D6' AND
{CRSE_SESSION.END_DT} in {?Start Date} to {?End Date} OR
if {?Instructor} = "*" then {PERSON.PERS_ID} like "*" )

ChiTownDiva

 

[synapsevampire]

What may not be clear is that Rhino is suggesting that the parameter be prefilled with both the ID and the text, display the text, and use the ID.

Unfortunately Crystal doesn't dynamically update parameters, so if new values show up, you'll have to open the report in designer and refresh the list, not exactly a reasonable way to conduct business.

I would also caution against using LIKE as it will degrade performance.

the last part of your formula states that if the instructor = "*" then don't bother with the dates, just return everything.

Is this what you want?

Also you're using OR when I think that you want AND:

(
{CRSE.CRSE_CD} like {?Course Code}
)
and
(
(IF {?Instructor} = 'Margaret Barber' THEN {PERSON.ID} = '10158C8' AND {CRSE_SESSION.END_DT} in {?Start Date} to {?End Date}
)
and
)
IF {?Instructor} = 'Pamela Rockett' THEN {PERSON.ID} = '10158D6' AND {CRSE_SESSION.END_DT} in {?Start Date} to {?End Date}
)
and
(
if {?Instructor} = "*" then
{CRSE_SESSION.END_DT} in {?Start Date} to {?End Date}
)
// just pull everything within the dates specified.

-k

 

[rhinok]

Chi,

In your example, the default values you've assigned to the {?Instructor} parameter are the Instructor's names. What I'm suggesting you do is to open up the parameter and change the default values to actually be the PERS_ID. After you do this, add a description for each PERS_ID. The description will be the Instructor's Name. Next, select 'Description' as the value for the 'Display' Drop Down List.

Doing this allows the User to select the value that is meaningful to them (the Name), but allows you to tie the actual data value (the ID) directly to the parameter. Don't forget, you also need a default value of 'ALL' with a description of 'ALL'. This should be the first value in the list.

Once you've changed the parameter, you can reference it more efficiently in the formula that I posted above. You can also avoid having to hard-code Names and IDs into your selection criteria. Instead, you can simply update the parameter list as needed.

 

[ChiTownDiva]

Rhinok...

Thank you, thank you, thank you!!! You get a star!!!

I've never used the "Display" option to set the default values of a parameter.

I learned something new...thanks again for all your help!!

ChiTownDiva