question on group 2

[tnguyen315]

Hello all..
I have a query showing something like this and each row is a single record:

ClientID ImmuCode Shot-Date
0001 PPD 1/1/2009
0001 DVS 2/5/2009
0002 IPV 5/10/2009
0003 PCV 5/15/2009
0003 DVS 5/20/2009
0004 IPV 1/20/2009
0004 PPD 4/18/2009

If any patient has "DVS" code showing in "ImmuCode", then I would like to eliminate that patient out of the list and want to show only:

ClientID ImmuCode Shot-Date
0002 IPV 5/10/2009
0004 IPV 1/20/2009
0004 PPD 4/18/2009

Right now on my report, I have on "Select Expert" if "ImmuCode" is not equal to "DVS" and the result just eliminate that record only and still showing that patient with other codes, not "DVS" such as:

ClientID ImmuCode Shot-Date
0001 PPD 1/1/2009
0002 IPV 5/10/2009
0003 PCV 5/15/2009
0004 IPV 1/20/2009
0004 PPD 4/18/2009
Please help, I'm very appreciated.
Thanks alots.

 

[Turkbear]

Try using a Command instead of your current query, something like:

Select * from table where ClientID Not In ( Select ClientID from table where ImmuCode = 'DVS')






To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[CrystalizeCanada]

There are two formulas needed. The first is needed to simply collect all the clientIDs with DVS.Later we can use this collection (or array) to eliminate those clients from the report.
Copy the formula below - call it @DVSClients and place it in the report footer. If it works you will see a list of DVS Clients. You have to change the formula to put in the correct field names. Assuming it works reply back and we can go onto the second step which is to elimated those clients from the report.

//PURPOSE OF FORMULA: to collect all the clients with DVS into an array
//TIMING OF FORMULA:
WhileReadingRecords;
//VARIABLE DECLARATIONS:
stringvar array DVSClients;
//FORMULA RESULT:
if {YourTable.ImmuCode}like 'DVS*' and not ({YourTable.ClientID} in DVSClients) Then
(
redim preserve DVSClients[count(DVSClients)+1];//make room in the array for the client
DVSClients[count(DVSClients)]:={YourTable.ClientID};//add that client to the array
);
Join(DVSClients,",")//displays the array in the report footer

Gordon BOCP
Crystalize

 

[tnguyen315]

I copied the code and changed to:

WhileReadingRecords;
stringvar array DVSClients;
if {IMFILE.VACCINE_CODE}like 'DVS*' and not ({IMFILE.PATIENT_NO} in DVSClients) Then
(
redim preserve DVSClients[count(DVSClients)+1];
DVSClients[count(DVSClients)]:={IMFILE.PATIENT_NO};
);
Join(DVSClients,",")

However, when I tried to save the formula, I got an error and "DVSClients" highlight and error message "A number range is required here"

Please help, thanks

 

[CrystalizeCanada]

I tried your formula and it worked on my machine - I used the data you typed in your posting. Perhaps you have different datatypes.

Is Patient_NO a number data type - I was assuming text. You can check this by right clicking the field and choosing Browse data.

If it is a number, then add a line (3rd line is good).
stringvar PatientNoAsText:=totext({IMFILE.PATIENT_NO},0,"");
Then modify the formula to refer to this variable instead of the field directly.

WhileReadingRecords;
stringvar array DVSClients;
stringvar PatientNoAsText:=totext({IMFILE.PATIENT_NO},0,"");
if {IMFILE.VACCINE_CODE}like 'DVS*' and not (PatientNoAsText in DVSClients) Then
(
redim preserve DVSClients[count(DVSClients)+1];
DVSClients[count(DVSClients)]:= PatientNoAsText;
);
Join(DVSClients,",")
Let me know if it works.

Gordon BOCP
Crystalize

 

[tnguyen315]

Yes, Patient No is a number and now it's worked...
Thanks

 

[CrystalizeCanada]

Do you need help on the last part on filtering out the data?.

One option is to use a group selection formula. (not a record selection formula)

stringvar array DVSClients;
not (totext({IMFILE_.PATIENT_NO},0,"") in DVSClients)

That works on my machine.

Gordon BOCP
Crystalize

 

[Turkbear]

Hi,
Glad CrystalizeCanada's solution worked for you, but try the Command object one as well, it is probably more efficient in its access, depending on your database type:

Select PATIENT_NO ,VACCINE_CODE,Shot-Date
from IMFILE
where PATIENT_NO
NOT IN
(Select PATIENT_NO from IMFILE
  where VACCINE_CODE = 'DVS')

Create a new report and after setting your database connection, choose 'Add new Command' instead of your table.






To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[CrystalizeCanada]

Turkbear is right - if you know how to do a command its much better in terms of performance

Gordon BOCP
Crystalize

 

[lbass]

This could have been done by creating a formula like this:

//{@hasDVS}:
if {table.immucode} = "DVS" then 1

Then insert a group on {table.clientID} and go to report->selection formula->GROUP and enter:

sum({@hasDVS},{table.clientID}) = 0

-LB

 

[tnguyen315]

Thanks alots for all of your solution. However, my database is not access database, so I cannot use

Select PATIENT_NO ,VACCINE_CODE,Shot-Date
from IMFILE
where PATIENT_NO
NOT IN
(Select PATIENT_NO from IMFILE
where VACCINE_CODE = 'DVS')

But thank you very much for all your help.

Now I can use both "CrystalizeCanada" and "lbass" solutions and tried on my Crystal report 11.

Once, thank you very much. I'm very appreciated for all your help.