Hi. I have 3 tables that I am working with and will need to do several reports. The one I'm currently working on gets the numbers of procedures a hospital performed grouped by the specialty under which the procedure was listed (for example, how many cardiac procedures were performed). I know the logic behind what I am trying to do, but I'm new to CR and I'm not sure of the syntax. I have a table that holds the procedure info (times, surgeon ID, patient account #, etc.), one that lists surgeons and surgeon IDs and specialties (ID), and a third that lists specialty IDs and descriptions. I currently have a count set up on the ID field (Index field) of the table with the procedure info and a series of what would normally be "where" statements linking each case with a specialty that corresponds to one of the headers so that it is grouped in the right place. What do I use instead of "where" in order to associate fields in my formula? Thanks.
Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations TouchToneTommy on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
The equivalent of a "Where" in CR? 1
- Thread starter artyk
- Start date
-
1
- #2
Tough to follow from your description.
Sounds like a mixture of simple joins (database expert) and/or selection formulas (Report, Selection formula, Record...) and/or Select Case logic inside a formula.
- Ido
view, e-mail, export, burst, distribute, and schedule Crystal Reports.
Sounds like a mixture of simple joins (database expert) and/or selection formulas (Report, Selection formula, Record...) and/or Select Case logic inside a formula.
- Ido
view, e-mail, export, burst, distribute, and schedule Crystal Reports.
- Thread starter
- #3
Let me see if I can explain it better. I'm trying to set up a selection formula. Everything is already linked in the database relationship-wise. I just need to tell CR how to count it correctly. Right now it is basically just counting all the records and relisting the same total for each category(group header). As I stated, I know the logic, but I don't know the sytax. My three tables that I need for this report are CaseInput, Surgeons, and Specialties. The relevant fields are as follows:
Specialties->Specialty ID, Specialty (the description of the specialty)
Surgeons->SurgeonID, Specialty (a reference to the specialty ID of the specialty table)
CaseInput->ID, Surgeon (a reference to the surgeon ID field of the surgeon table.
Here is the logic that I need to use:
Count(CaseInput.ID) where Specialty.SpecialtyID = Surgeon.Specialty And Surgeon.SurgeonID = CaseInput.Surgeon And Surgeon.Specialty = GroupName(Specialty.Specialty) (a field from my report)
The logic may not be the best setup either, but I can't test that until I get the syntax down. I hope this is a better description. Any help you could provide would be appreciated. Don't worry about getting my particular example exactly right, the important thing is for me to get the general idea because there will be many reports that have to be done. Thanks.
Specialties->Specialty ID, Specialty (the description of the specialty)
Surgeons->SurgeonID, Specialty (a reference to the specialty ID of the specialty table)
CaseInput->ID, Surgeon (a reference to the surgeon ID field of the surgeon table.
Here is the logic that I need to use:
Count(CaseInput.ID) where Specialty.SpecialtyID = Surgeon.Specialty And Surgeon.SurgeonID = CaseInput.Surgeon And Surgeon.Specialty = GroupName(Specialty.Specialty) (a field from my report)
The logic may not be the best setup either, but I can't test that until I get the syntax down. I hope this is a better description. Any help you could provide would be appreciated. Don't worry about getting my particular example exactly right, the important thing is for me to get the general idea because there will be many reports that have to be done. Thanks.
1. Use the same logic to create a formula that returns 1 if the condition is true and 0 otherwise.
2. SUM (note: don't count) that formula to provide the desired conditional count.
- Ido
view, e-mail, export, burst, distribute, and schedule Crystal Reports.
2. SUM (note: don't count) that formula to provide the desired conditional count.
- Ido
view, e-mail, export, burst, distribute, and schedule Crystal Reports.
- Thread starter
- #5
What should I use instead of "where" to test the condition? Also, when I've tried to use "sum" before, since the ID field is numeric, it literally added the ID fields together. Instead of 1+1+1+1... I would get 1+2+3+4... The formula should have ended up with 5 as a result and it ended up totaling 51.
Read my message again.
- Ido
view, e-mail, export, burst, distribute, and schedule Crystal Reports.
- Ido
view, e-mail, export, burst, distribute, and schedule Crystal Reports.
There is no WHERE. It should look like this:
- Ido
view, e-mail, export, burst, distribute, and schedule Crystal Reports.
Code:
IF Specialty.SpecialtyID = Surgeon.Specialty And Surgeon.SurgeonID = CaseInput.Surgeon THEN 1 ELSE 0view, e-mail, export, burst, distribute, and schedule Crystal Reports.
- Thread starter
- #8
- Status
Similar threads
- Locked
- Question
- Locked
- Question
- Locked
- Question
- Locked
- Question