A Summary of Tests

[tmozer]

Using Crystal Reports 8.5 and Oracle Tables.....

One of the tables that becomes populated as tests are completed (ARRESULTS) contains fields that relate the tests to a "CASE" (CASE KEY), the test type, the test and the test result (in character form). If there were five test results entered for a particular "CASE" there would be five records in ARRESULTS with the same CASE KEY field and the five different tests information, including the result entered.

I am trying to figure out how to write a Crystal Report that tells me how many cases have a certain combination of test run.

For example, TEST1, TEST2 and TEST3 are possible test entries. If run and entered into the system, they will have a character numeric entry indicating how many of the samples were either tested or found to be positive. I want a report that will look at any CASE where TEST1 was run (numeric value of 1 or more) tell me the total of those CASES that were negative for both TEST2 and TEST3, the total where TEST2 was positive and TEST3 was negative and finally where TEST2 was negative and TEST3 was positive. Remember, if the test was negative (or not performed), there would be no record in ARRESULTS for that case and that test.

Any suggestions??

 

[lbass]

I was with you until you said:

Remember, if the test was negative (or not performed), there would be no record in ARRESULTS for that case and that test.

Does this mean, for example, that in your scenario where test2 and test3 are both negative that there will be no results for test2 and test3? Or are you saying that if there is a negative on Test1 there will be no results in the table? Why do we need to "remember" this?

I think you need to provide some sample data.

-LB

 

[tmozer]

The way the table is supposed to work is that if the test is not run, there would be no record in the table for that case/test.

Let me give a more specific - real world example:

If a CASE has items that are tested for semen, the table will be populated with "EXAMSEM" in the ARRESULT.Test Type field and the number of item examined for semen in the ARRESULT.Result field. Semen samples have to be confirmed by identifying spermatozoa microscopically or, if sperm are not found, by testing for the presence of a protein, P30.

So a CASE that was examined for semen and where sperm was found would have two records in the ARRESULT table (EXAMSEM and SPERM+). If sperm was not found but a positive P30 test was performed, the two records would contain EXAMSEM and P30+. If neither P30 for spermatozoa were found, there would only be one record containing EXAMSEM.

I am trying to figure out how to summarize the three possible outcomes from the above test for cases examined over certain times periods or other possible filters.

If I only use the ARRESULT table I would have to test to determine if a CASE (ARRESULT.Case Key) had a record that contained ARRESULT.Exam Type = EXAMSEM, then determine if there were any other records for that Case Key that contained SPERM+ or P30+, total and summarize the numbers.

 

[tmozer]

By filtering and suppressing I have the cases separated and listed the way I need. However, if I do any type of counting (of just the records printed) I also get a count of the suppressed records.

Is there anyway to get a summary of just the records printed/showing on the screen?

 

[lbass]

I'm not sure whether you want a count of total cases, but if you do, you should have a left join from the Cases table to the Arresult table. Insert a group on {Cases.CaseID} and then create two formulas:

//{@sperm+}:
if {Arresult.result} = "SPERM+" then 1 else 0

//{@P30+}:
if {Arresult.result} = "P30+" then 1 else 0

Then create three running totals, using the running total expert:

//{#negative}: Select {Cases.CaseID}, distinctcount, evaluate based on a formula:

isnull({Arresult.result}) and
{Arresult.examtype}= "EXAMSEM"

Reset never.

//{#Sperm+only}: Select {Cases.CaseID}, distinctcount, evaluate based on a formula:

sum({@Sperm+},{Cases.CaseID}) > 0 and
sum({@P30+},{Cases.CaseID}) = 0

Reset never.

//{#P30+only}:
Select {Cases.CaseID}, distinctcount, evaluate based on a formula:

sum({@Sperm+},{Cases.CaseID}) = 0 and
sum({@P30+},{Cases.CaseID}) > 0

Reset never.

For total cases, regardless of testing, insert a distinct count on {Cases.CaseID}.

-LB

 

[tmozer]

That looks good and I will give it a try. But to complicate matters, I have to check the value field in the ARRESULT table also. If a test like P30+ has a value of either NULL or "0" then that would count as a negative also.

Basically a negative (or not-run) test can be indicated by no record with that test type or a record with that test type, but a value of NULL or "0".

 

[tmozer]

I am getting an error with this running total formula:

//{#Sperm+only}:
sum({@Sperm+},{ARRESULT.Case Key})>0 and
sum({@P30+},{ARRESULT.Case Key})=0

("The summary/running total field could no be created").

Since the syntax of the next one is the same, I guess it will also error-out. Anything obviously wrong???

 

[lbass]

You have to have a group on {ARREST.Case Key} for your formulas to work. Not sure why you didn't use the {Cases.Case ID} field (from the first table). Use the field you are grouping on for case...

-LB

 

[tmozer]

Tried it and got it all to run. Got numbers for #negative that could be meaningful, #Sperm+only and #P30+only just give zeros.

I know what I need to do (I think), but am not sure how to do it. LABCASE is kind of the master table. There is only one record per case. ARRESULT will have several records per case. I need to evaluate ARRESULT for different tests and test values left joined to LABCASE by Case Key... Hopefully you know what I need......

Is there a way to evaulate something like:

if ({LABCASE.Case Key},{ARRESULT.Test Type Code}) = "EXAMSEM" then 1 else 0

??????

Obviously I'm syntax challenged.......

 

[lbass]

Please confirm how you have joined the tables--I would think you would have left join FROM Labcase to Arresult, and please explain how you created the running totals, particularly what field you summarized and what the summary was, and what you finally used as the evaluation formula. Did you implement the original formulas {@sperm+} and {@P30+} as I suggested? I think the running totals should have worked without specifying the exam type, unless "Sperm+" and "P30+" are valid results for other tests.

For further help, please supply some sample data for the following columns:

Labcase.CaseKey Arresult.ExamType Arresult.Result

-LB

 

[tmozer]

lbass:


Thanks so much for you help.