Warning after adding an Add Command

[RenaG]

I am using Crystal 10.

I added an Add Command to my Database Expert. The SQL statement only references tables in the datasource used for the report. Now when ever I leave the Database Expert I get this warning:

More than one datasource or a stored procedure has been used in this report.
Please make sure that no SQL Expression is added and no server-side group-by is performed.

Is this something to be concerned about? I not even exactly sure what it means .

TIA!
~RLG

 

[lbass]

How are you using the command? You can use a command to populate a parameter picklist and leave it unlinked (in fact, you should leave it unlinked), and you will get this message, which you can just ignore, but you should not reference any fields from the command in your main report.

Otherwise, you should use a command as your sole datasource, rather than linking it to tables in the main report, since the linking would occur locally, and slow the report.

-LB

 

[RenaG]

Hi LB,

I am using this to create a file of records I don't want. What I mean by that is, I am looking for patients that don't have a 'survivorship' appt. This 'file' contains all the patients who DO have a 'survivorship' appt. I linked it (the file is called SurvivorshipAppt) with a left outer join to the Patient file. In the Record Selection Formula there is an IsNull({SurvivorshipAppt.Pat_ID1}). I hope that makes sense.

I got the idea for this from another thread (which I can't find right now) but I am open to suggestions on a better way to do this.

Warmly,
~RLG

 

[lbass]

There is probably a simpler way. Not sure of your table structure, but let's say you have a main table and a survivor table. Use a left join FROM the main table TO the survivor table, and then use a record selection formula like this:

IsNull({SurvivorshipAppt.Pat_ID1})

-LB

 

[RenaG]

Unfortunately, there is just one table for all the appointments. We are looking for patients who don't have a survivorship appointment scheduled after their last chemo treatment. There is nothing to indicate that they have reached the last chemo treatment except that there aren't any more appointments scheduled. So I wanted to eliminate all the patients who have had the survivorship appointment scheduled and then all I have to do figure a way to determine if the patient is done with chemo and if they are then, they will be on the report. I hope that makes sense. So having an 'elimination' table seemed like the easiest way to go.

~RLG

 

[lbass]

How do you distinguish a survivorship appointment from a chemo treatment appointment? I think it would help if you referenced the actual table and field names.

-LB

 

[TeyBrady]

I think you should be writing one SQL statement in the command object that does everything you need it to do. There is no point in passing the results of two separate queries back to crystal to let it do the join unless the queries are coming out of different databases. Let the database do the database work. It seems like you should be able to take the crystal query and the command object query that you are outer joining to in crystal and make it one command object that does the same outer join in the database in one command object.

 

[RenaG]

There is one table that is mainly used for selecting patients: Schedule. There are a couple other tables for patient status (not inactive) and cancer type but those are very straight forward.

Schedule-(the fields I look at)
Pat_ID
Appt_DtTm
Activity
Notes

If Activity = SVRSHP then this is a survivorship appointment.
If Activity starts with INF then this is a chemo appointment.
If Notes contains HERCEPTIN then this is a chemo appointment to give herception - I don't want to count this chemo appointment and these appointments follow the last regular chemo appointment.

We want to make sure that every chemo patient has a survivorship appointment so what I need to do is report on all patients who have received chemo, are finished with chemo and have not had a survivorship appointment scheduled. It seemed like it would make the reporting process easier if I first eliminated all patients who do have a survivorship appointment. Then I just need to determine if they are finished with chemo at a certain point in time. For example, they would say "have all the patients who are finished with chemo by Dec 1, 2011 had a survivorship appt scheduled?" So the report would need to look at the last INF appt (that is not for herceptin), see if it is before 12/1/11. Since I have already eliminated all patients who have a survivorship appt then all I would need to do is write these patients to the report.

I hope that all made sense and I have covered all the bases. Is this what you wanted to know?

I have just been given another project to work on that I hope won't take too long. I will be back to this one because it is a much needed report. Any suggestion / advice / help you can offer will be appreciated!

Merry Christmas to you all and a very Happy New Year!

~RLG