Selection Criteria Problem

[ChiTownDiva]

Afternoon All...

I have a problem with a selection criteria and I can't see why it doesn't work...

(({FAC_SITE.TTL} like {?Location}) or isnull ({FAC_SITE.TTL})) and
{CRSE.LOCAL_CRSE_CD} LIKE {?Course} and
{CRSE_SESSION.START_DT} in {?Start Date} to {?End Date} and
(
if {?Class Status} = 'All' then {CRSE_SESSION.CRSE_SESSION_STATUS_CD} like '*' else
if {?Class Status} = 'Scheduled Only' then {CRSE_SESSION.CRSE_SESSION_STATUS_CD} like 'S' else
if {?Class Status} = 'Scheduled & Full' then {CRSE_SESSION.CRSE_SESSION_STATUS_CD} like ['S', 'F'] else
if {?Class Status} = 'Cancelled Only' then {CRSE_SESSION.CRSE_SESSION_STATUS_CD} like 'C' ELSE
IF {?Class Status} = 'On Hold' then {CRSE_SESSION.CRSE_SESSION_STATUS_CD} like 'H'
) and
(if {?NVP} ='All' then {CRSE_SESSION.CRSE_SESSION_SHORT_TTL} like '*' else
if {?NVP} ='AGERTON' then {CRSE_SESSION.CRSE_SESSION_SHORT_TTL} like 'AG*' else
if {?NVP} ='BECKER' then {CRSE_SESSION.CRSE_SESSION_SHORT_TTL} like 'BE*' else
if {?NVP} ='BICKERSTAFF' then {CRSE_SESSION.CRSE_SESSION_SHORT_TTL} like 'BI*' else
if {?NVP} ='CHANDLER' then {CRSE_SESSION.CRSE_SESSION_SHORT_TTL} like 'CHA*' else
if {?NVP} ='CHRISTIAN' then {CRSE_SESSION.CRSE_SESSION_SHORT_TTL} like 'CHR*' else
if {?NVP} ='COOPER' then {CRSE_SESSION.CRSE_SESSION_SHORT_TTL} like 'CO*' else
if {?NVP} ='FREEMAN-BUTLER' then {CRSE_SESSION.CRSE_SESSION_SHORT_TTL} like 'FR*' else
if {?NVP} ='FUNDERBURG' then {CRSE_SESSION.CRSE_SESSION_SHORT_TTL} like 'FU*' else
if {?NVP} ='HENDERSON' then {CRSE_SESSION.CRSE_SESSION_SHORT_TTL} like 'H*' else
if {?NVP} ='MULCAHY' then {CRSE_SESSION.CRSE_SESSION_SHORT_TTL} like 'MUL*' else
if {?NVP} ='MUNIZ' then {CRSE_SESSION.CRSE_SESSION_SHORT_TTL} like 'MUN*' else
if {?NVP} ='ODOM' then {CRSE_SESSION.CRSE_SESSION_SHORT_TTL} like 'O*' else
if {?NVP} ='PRIMELLES' then {CRSE_SESSION.CRSE_SESSION_SHORT_TTL} like 'P*' else
if {?NVP} ='RUSSELL' then {CRSE_SESSION.CRSE_SESSION_SHORT_TTL} like 'R*' else
if {?NVP} ='SHUMPERT' then {CRSE_SESSION.CRSE_SESSION_SHORT_TTL} like 'Sh*' else
if {?NVP} ='STACY' then {CRSE_SESSION.CRSE_SESSION_SHORT_TTL} like 'Stac*' else
if {?NVP} ='STAPP' then {CRSE_SESSION.CRSE_SESSION_SHORT_TTL} like 'Stap*' ELSE
if {?NVP} ='TOLBERT' then {CRSE_SESSION.CRSE_SESSION_SHORT_TTL} like 'T*' )

The problem started when I added the {?NVP} parameter. If a user adds one NVP, they get results, but when they add multiple NVPs, they get no data.

I can't figure out why.

Can someone help?

Thanks in advance.

ChiTownDivaus

 

[pgtek]

hi
you have many parameter

you should beak then up
they user must be prompt to insert
either 'all' or key word as i can see
you should change your parameter to multiple entries

cheers

pgtek

 

[ChiTownDiva]

Thanks pgtek...

In my last sentence, I stated that when the user enters multiple NVPs no data is returned.

Any other ideas.

ChiTownDiva

 

[pgtek]

hi

sorry no clue if you had more than one vnp
they should be: is on of {?VNP}

try it

pgtek

 

[ChiTownDiva]

pgtek...

I get an "The Array Must be Subscripted." error message.

ChiTownDiva

 

[Naith]

Hey ChiTown,

How come you're using LIKE all over the place? Is there some reason why you have such an extensive {?NVP} handler, instead of something like:

If {?NVP} <> 'All'
Then {CRSE_SESSION.CRSE_SESSION_SHORT_TTL} = {?NVP}
Else If
{?NVP} = 'All'
Then True
)

This should speed up processing compared to what you have now because of three things:

(a) You lose all the IF clauses
(b) You lose all the LIKEs, which cause a big hit on processing
(c) You pass the processing to the database, instead sifting through all the data on the client, like you're doing now.

It should also stop you encountering your problem, as long as NVP is a discrete parameter.

A couple of points before I go: if you have set up the {?Class Status} parameter like you have the {?NVP} parameter, are you sure you are not getting this error for that parameter too, or have you not tested Class Status with a value of 'All'?
Secondly, you probably meant to do this, but are you aware that you have mixed up the case of the strings you have hardcoded in your selection criteria? (e.g.

if {?NVP} ='BECKER' then {CRSE_SESSION.CRSE_SESSION_SHORT_TTL} like 'BE*'
...
if {?NVP} ='STAPP' then {CRSE_SESSION.CRSE_SESSION_SHORT_TTL} like 'Stap*'

)

You can use the UCase or LCase function to ensure that you don't slip past the data you want just because you missed the right case.

Naith

 

[ChiTownDiva]

Hi Naith...

The reason why I created the parameter the way I did is because the only way to distinguish one session from another (by VP) is by the course session title--i.e., MULCAHY - AM SESSION, SHUMPERT - PM SESSION, MULCHAY - GFTG, SHUMPERT - GFTG, etc. There is nothing else on this record that would distinguish Mulchay's sessions from Shumperts. The {?NVP} parameter can't equal to anything.

The users don't need to pull just &quot;MULCAHY - AM SESSION&quot;, but ALL of his sessions.

Naith, I agree with your assessment of &quot;like&quot; statements--I try not to use them if I can get around it. I actually stole the {?Class Status} parameter code from another report written by someone else. I've actually changed it to:

if {?Class Status} = 'All' then {CRSE_SESSION.CRSE_SESSION_STATUS_CD} IN ['S','F', 'C', 'H'] ELSE
if {?Class Status} = 'Scheduled Only' then {CRSE_SESSION.CRSE_SESSION_STATUS_CD} = 'S' else
if {?Class Status} = 'Scheduled & Full' then {CRSE_SESSION.CRSE_SESSION_STATUS_CD} IN ['S', 'F'] else
if {?Class Status} = 'Cancelled Only' then {CRSE_SESSION.CRSE_SESSION_STATUS_CD} = 'C' ELSE
IF {?Class Status} = 'On Hold' then {CRSE_SESSION.CRSE_SESSION_STATUS_CD} = 'H'

Without the VP info, the report works great, but I have no idea how to work around using the course session title to pull the sessions.

What do you think?

Thanks, Naith

ChiTownDiva

 

[Naith]

Thanks for the heads up.

Can you do two things:

Can you confirm that {?Class Status} and {?NVP} are both set up as the same type of parameters? (e.g. Discrete only, not set to multiple values, etc.)

Also, can you place the {?NVP} clause in the selection criteria on it's own, and observe the outcome.

Incidentally, you'll still get database pass if you handle the clause like this:
(
If {?NVP} <> 'All'
Then
if {?NVP} ='AGERTON' then {CRSE_SESSION.CRSE_SESSION_SHORT_TTL} like 'AG*' else
if {?NVP} ='BECKER' then {CRSE_SESSION.CRSE_SESSION_SHORT_TTL} like 'BE*' else...etc...
if {?NVP} = 'All' then True
)

Naith

 

[Ngolem]

if {?NVP} ='STAPP' then {CRSE_SESSION.CRSE_SESSION_SHORT_TTL} like 'Stap*' ELSE
if {?NVP} ='TOLBERT' then {CRSE_SESSION.CRSE_SESSION_SHORT_TTL} like 'T*' )

The problem started when I added the {?NVP} parameter. If a user adds one NVP, they get results, but when they add multiple NVPs, they get no data.
***************************************

The reason that it will not work is that these statements are valid only for discrete values of NVP.

if you want {?NVP} to have multiple values try this

if 'STAPP' in {?NVP} then

But I can see a lot of problems having Multiple values for {?NVP}

let us consider the following section of the formula

if {?NVP} ='FUNDERBURG' then {CRSE_SESSION.CRSE_SESSION_SHORT_TTL} like 'FU*' else
if {?NVP} ='HENDERSON' then {CRSE_SESSION.CRSE_SESSION_SHORT_TTL} like 'H*' else
if {?NVP} ='MULCAHY' then {CRSE_SESSION.CRSE_SESSION_SHORT_TTL} like 'MUL*' else
if {?NVP} ='MUNIZ' then {CRSE_SESSION.CRSE_SESSION_SHORT_TTL} like 'MUN*' else

And let us suppose that the user entered the following values for {?NVP}: 'FUNDERBURG' , 'MUNIZ'

the formula would kick out at the 'FUNDERBERG' statement and never reach the 'MUNIZ' (assuming you changed the formula as I suggested earlier)

SO...this type of formula is only valid for single values of {?NVP} you should make the parameter only accept one value or redesign the formula


Jim Broadbent

 

[ChiTownDiva]

Thanks Naith and Nogolem...

By golly I solved it!!!

I wrote one simple line and it works!!!

{CRSE_SESSION.CRSE_SESSION_SHORT_TTL} startswith {?NVP}

I changed all of the NVP's to uppercase since I can't write UCASE({?NVP}).

Thanks guys for all of your help...I really do appreciate it!

ChiTownDiva