Hi,

I want to create an extract where the user can select multiple values and it will populate the records that have ANY of those values.

There are three variables - location, program, and department. I created a multiselect that let's the user select say, A location, AB program, and nothing for C and it would show that exact combination.

What I am looking for is if the user selects A Location, and B location, I will still show records that ONLY contain A location even if it does not contain A or B program, and it will also show records that belong in A program or B Program, even if it does not contain A location.


My code right now is:

AND ( FACT_RISKPRO.FACT_RISKPRO.LOCATION EQ &LOCATION.(OR(FIND FACT_RISKPRO.FACT_RISKPRO.LOCATION IN FACT_RISKPRO)).LOCATION:. )
AND ( FACT_RISKPRO.FACT_RISKPRO.PROGRAM EQ &PROGRAM.(OR(FIND FACT_RISKPRO.FACT_RISKPRO.PROGRAM IN FACT_RISKPRO)).PROGRAM:. )
AND ( FACT_RISKPRO.FACT_RISKPRO.DEPARTMENT EQ &DEPARTMENT.(OR(FIND FACT_RISKPRO.FACT_RISKPRO.DEPARTMENT IN FACT_RISKPRO)).DEPARTMNENT:. );

This message has been edited. Last edited by: hfung1,

I'm kind of confused.

The user selects A location and B location, but you don't want any records for B location to be retrieved??

Initially I thought changing the last two ANDs to ORs would do what you want, but now I'm not sure.

(Incidentally you have a typo on DEPARTMENT in the last line.)

This message has been edited. Last edited by: George Patton,

Hi,

Sorry perhaps a "chart" would be easier to understand

Using this as an example:
Location Possible Values: A B C D E F G
Program Possible Values: 1 2 3 4 5 6 7
Department Possible Values: T U V W X Y Z

So let's say my end user selects
Location: A B
Program: 1
Department: T U

I would like to show the records where ANY of those conditions are met
so a record with Location A, Program 2 Department V will still show
A record with Location B Program 1 Department V will still show
A record with Location G, Program 7, Department T will still show
A record with Location G, Program 1, Department Z will still show

I hope this clarifies.

As far as I can understand your request, change AND by OR :

AND ( ( FACT_RISKPRO.FACT_RISKPRO.LOCATION EQ &LOCATION.(OR(FIND FACT_RISKPRO.FACT_RISKPRO.LOCATION IN FACT_RISKPRO)).LOCATION:. )
OR ( FACT_RISKPRO.FACT_RISKPRO.PROGRAM EQ &PROGRAM.(OR(FIND FACT_RISKPRO.FACT_RISKPRO.PROGRAM IN FACT_RISKPRO)).PROGRAM:. )
OR ( FACT_RISKPRO.FACT_RISKPRO.DEPARTMENT EQ &DEPARTMENT.(OR(FIND FACT_RISKPRO.FACT_RISKPRO.DEPARTMENT IN FACT_RISKPRO)).DEPARTMNENT:. ) );

And I suggest to put brackets to enclose the ORs

Nope it is not working, I think it is still treating it as show the records that have that exact combination of Location/Program/Department, and not treating them independently.

With proper ORs and brackets it shouldn't.

Share your whole code.

Hi,

The code is pretty simple I think:


TABLE FILE FACT_RISKPRO
PRINT
FACT_RISKPRO.FACT_RISKPRO.PATIENT_ID
FACT_RISKPRO.FACT_RISKPRO.PATIENT_STATUS
FACT_RISKPRO.FACT_RISKPRO.PATIENT_NAME
FACT_RISKPRO.FACT_RISKPRO.PATIENT_CITY
FACT_RISKPRO.FACT_RISKPRO.INCIDENT_STATUS
FACT_RISKPRO.FACT_RISKPRO.INCIDENT_SEVERITY_LEVEL
FACT_RISKPRO.FACT_RISKPRO.INCIDENT_SEVERITY_DESCRIPTION
FACT_RISKPRO.FACT_RISKPRO.INCIDENT_DATE
FACT_RISKPRO.FACT_RISKPRO.INCIDENT_TYPE
FACT_RISKPRO.FACT_RISKPRO.INJURED
FACT_RISKPRO.FACT_RISKPRO.INCIDENT_DESCRIPTION
FACT_RISKPRO.FACT_RISKPRO.INCIDENT_OCCURED_WHEN
FACT_RISKPRO.FACT_RISKPRO.LOCATION
FACT_RISKPRO.FACT_RISKPRO.PROGRAM
FACT_RISKPRO.FACT_RISKPRO.DEPARTMENT
FACT_RISKPRO.FACT_RISKPRO.RESOLUTION
FACT_RISKPRO.FACT_RISKPRO.REPORTED_BY
FACT_RISKPRO.FACT_RISKPRO.REVIEW_MANAGER
FACT_RISKPRO.FACT_RISKPRO.OTHER_SERVICES_INVOLVED
FACT_RISKPRO.FACT_RISKPRO.INPATIENT_LOCATION
FACT_RISKPRO.FACT_RISKPRO.COUNT
WHERE ( FACT_RISKPRO.FACT_RISKPRO.INCIDENT_DATE GE '&START_DATE_yyyymmdd' ) AND ( FACT_RISKPRO.FACT_RISKPRO.INCIDENT_DATE LE '&END_DATE_yyyymmdd' ) AND ( FACT_RISKPRO.FACT_RISKPRO.INCIDENT_TYPE EQ '&INCIDENT_TYPE.(FIND FACT_RISKPRO.FACT_RISKPRO.INCIDENT_TYPE IN FACT_RISKPRO).INCIDENT_TYPE:.' ) AND ( FACT_RISKPRO.FACT_RISKPRO.LOCATION NE &LOCATION.(OR(FIND FACT_RISKPRO.FACT_RISKPRO.LOCATION IN FACT_RISKPRO)).LOCATION:. ) AND ( FACT_RISKPRO.FACT_RISKPRO.PROGRAM EQ &PROGRAM.(OR(FIND FACT_RISKPRO.FACT_RISKPRO.PROGRAM IN FACT_RISKPRO)).PROGRAM:. ) AND ( FACT_RISKPRO.FACT_RISKPRO.DEPARTMENT EQ &DEPARTMENT.(OR(FIND FACT_RISKPRO.FACT_RISKPRO.DEPARTMENT IN FACT_RISKPRO)).DEPARTMENT:. );
ON TABLE SET PAGE-NUM NOLEAD
ON TABLE NOTOTAL
ON TABLE PCHOLD FORMAT HTML
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
INCLUDE = IBFS:/EDA/EDASERVE/_EDAHOME/ETC/endeflt.sty,
$
TYPE=REPORT,
COLUMN=N3,
WRAP=6.000000,
$
ENDSTYLE
END

Look simple, but you haven't perform what we've suggested you.

Instead of :

WHERE ( FACT_RISKPRO.FACT_RISKPRO.INCIDENT_DATE GE '&START_DATE_yyyymmdd' )
 AND ( FACT_RISKPRO.FACT_RISKPRO.INCIDENT_DATE LE '&END_DATE_yyyymmdd' )
 AND ( FACT_RISKPRO.FACT_RISKPRO.INCIDENT_TYPE EQ '&INCIDENT_TYPE.(FIND FACT_RISKPRO.FACT_RISKPRO.INCIDENT_TYPE IN FACT_RISKPRO).INCIDENT_TYPE:.' )
 AND ( FACT_RISKPRO.FACT_RISKPRO.LOCATION NE &LOCATION.(OR(FIND FACT_RISKPRO.FACT_RISKPRO.LOCATION IN FACT_RISKPRO)).LOCATION:. )
 AND ( FACT_RISKPRO.FACT_RISKPRO.PROGRAM EQ &PROGRAM.(OR(FIND FACT_RISKPRO.FACT_RISKPRO.PROGRAM IN FACT_RISKPRO)).PROGRAM:. )
 AND ( FACT_RISKPRO.FACT_RISKPRO.DEPARTMENT EQ &DEPARTMENT.(OR(FIND FACT_RISKPRO.FACT_RISKPRO.DEPARTMENT IN FACT_RISKPRO)).DEPARTMENT:. );

Try :

 WHERE ( FACT_RISKPRO.FACT_RISKPRO.INCIDENT_DATE GE '&START_DATE_yyyymmdd' )
 AND ( FACT_RISKPRO.FACT_RISKPRO.INCIDENT_DATE LE '&END_DATE_yyyymmdd' )
 AND ( FACT_RISKPRO.FACT_RISKPRO.INCIDENT_TYPE EQ '&INCIDENT_TYPE.(FIND FACT_RISKPRO.FACT_RISKPRO.INCIDENT_TYPE IN FACT_RISKPRO).INCIDENT_TYPE:.' )
 AND (
       ( FACT_RISKPRO.FACT_RISKPRO.LOCATION NE &LOCATION.(OR(FIND FACT_RISKPRO.FACT_RISKPRO.LOCATION IN FACT_RISKPRO)).LOCATION:. )
    OR ( FACT_RISKPRO.FACT_RISKPRO.PROGRAM EQ &PROGRAM.(OR(FIND FACT_RISKPRO.FACT_RISKPRO.PROGRAM IN FACT_RISKPRO)).PROGRAM:. )
    OR ( FACT_RISKPRO.FACT_RISKPRO.DEPARTMENT EQ &DEPARTMENT.(OR(FIND FACT_RISKPRO.FACT_RISKPRO.DEPARTMENT IN FACT_RISKPRO)).DEPARTMENT:. )
	 );

Or what I prefer:

 WHERE ( FACT_RISKPRO.FACT_RISKPRO.INCIDENT_DATE GE '&START_DATE_yyyymmdd' );
 WHERE ( FACT_RISKPRO.FACT_RISKPRO.INCIDENT_DATE LE '&END_DATE_yyyymmdd' );
 WHERE ( FACT_RISKPRO.FACT_RISKPRO.INCIDENT_TYPE EQ '&INCIDENT_TYPE.(FIND FACT_RISKPRO.FACT_RISKPRO.INCIDENT_TYPE IN FACT_RISKPRO).INCIDENT_TYPE:.' );
 WHERE ( FACT_RISKPRO.FACT_RISKPRO.LOCATION NE &LOCATION.(OR(FIND FACT_RISKPRO.FACT_RISKPRO.LOCATION IN FACT_RISKPRO)).LOCATION:. )
    OR ( FACT_RISKPRO.FACT_RISKPRO.PROGRAM EQ &PROGRAM.(OR(FIND FACT_RISKPRO.FACT_RISKPRO.PROGRAM IN FACT_RISKPRO)).PROGRAM:. )
    OR ( FACT_RISKPRO.FACT_RISKPRO.DEPARTMENT EQ &DEPARTMENT.(OR(FIND FACT_RISKPRO.FACT_RISKPRO.DEPARTMENT IN FACT_RISKPRO)).DEPARTMENT:. );

Because this allow the use of FOC_NONE without issue of having the whole WHERE ignored. So that way some parameters can have FOC_NONE (first 3) when some others don't.

Sorry I had posted the original before I tried to fix it but thanks it works now!

Edit your first post then edit title to add [SOLVED] at the beginning.