Need help programming troublesome report...

[saturn2]

We're using Crystal 8.5 here with our custom health record software... Basically it's an Access database.. FYI there are no connection issues, but I just canNOT figure out how to make it give me the report I want... I tried fiddling around with SQL in there, but though it SAYS it can use SQL, I dunno what SQL language it thinks it's using -- nothing I normally use in PHP or ASP (for SQL) seems to be recognized... rather use the Crystal language anyhow...

I tried sending it home (I only have Crystal 6.0 that came with Simply Accounting , but it won't open the file, which means I can't show you what I have so far... regardless, what I have so far doesn't work for me anyhow...

So here's the scenario:

It's accessing 2 (linked) tables: Client, Program
These are the fields I need to display...
Client - FName, LName, ID
Program - ProgType, ProgName, Status, EndDate

--Descriptions:
ProgType is either "inpatient" or "outpatient"
ProgName is one of about 40 names (eg "Preschool", "Autism", etc etc)
Status is one of several names (eg "admitted", "discharged", "cancelled", etc)
EndDate is the DATE of discharge, cancel, withdrawn, etc
Fname, Lname are (obviously) the client's names
ID is the chart number for that client.


It needs to prompt the user for a date range (got that), and should pull records according to the following criteria:

First, it needs to ONLY pull records where <Status = "discharged"> and <EndDate is within the prompted date range>

The fields for the Client table will only be used in the output and so don't need to be checked in any way... would be nice to sort them numerically by ID at the end.

It needs to pull ONLY those records matching the above criteria, AS WELL AS, the ProgName needs to be one of about 9 specific items. I got this far, but...

-- Here's where I start running into problems:

I need the query to not only pull all those records, BUT I need to figure out how to add an IF statement that works: For every record it pulls up with the above criteria, I need the query to CHECK ALL the other ProgName records (if any) attached to that ID, which also match the same ProgName list as above. If there ARE any other records attached to that ID in the table, I need it to check the Status field for each to ensure they are all NOT EQUAL to "admitted".

I've monkeyed around with If statements in there, tried while loops, etc, but I must be missing something (I'm hardly new to programming)... I'm looking for a suggestion on how I can basically make it cross-check every hit that it gets against any other records for that same ID, to ensure that there are no <Status = "admitted"> records. If there ARE any admitted ProgName's, then I want that entire ID omitted from the end report..

I hope this is clear, and I know this probably isn't a "help me program" forum, but any suggestions are appreciated!

Thank you so much!!

Dave

 

[lbass]

How are the tables linked? You show no common field? If there is a clientID in the program table, then you probably need a left join from {Program.ClientID} to {Client.ClientID}. Please explain this first, and then I will try to help.

-LB

 

[saturn2]

The two tables are linked by ID fields. I should have made that clearer. In the Clients table, ID is the Key, while the Program table has a field that matches. It is a Many-to-One relationship (infinite Programs possible per one Client, and Clients are never duplicated).

Basically, once it finds any ID's that are linked to the specific list of ProgNames where each ProgName record also has Status = "discharged" ... this we have, but once it finds all those, I need it to sort of "go back" and check ALL ProgName (same list) records to ensure there are no Status = "admitted"...

The scenario is such that often there are multiple Programs Admitted for a child (these are outpatient.. they might have both Physiotherapy AND Occupational Therapy, and perhaps Autism program all open at once). The end result for this report is to give me a list of PATIENTS (by ID), where basically the LAST REMAINING admission has become Discharged at some point within the date range.

In other words, we need to know that they were discharged within the date range, but also DO NOT want any clients listed who are still actually Admitted to any OTHER ProgNames...

I feel in my gut this is gonna involve some sort of loop in the code, ie for each ID that comes up from the initial test, it will then need a subroutine to check the whole list of possible Program records for that ID, apply a condition to establish if there are any remaining Admitted Status fields in any Program records attached to that ID, and then either allow that client to be included in the report, or (somehow) to disregard that client from disply (if still Admitted somewhere).

It's this last bit (cross-checking and then selecting whether to display) that I can't quite figure out how to do in Crystal... Sure wish it was PHP or ASP =)

Any further ideas you have are definitely appreciated!

 

[lbass]

In the main report, use a record select on date, status, and program, and insert a group on {Client.ClientID}.

Then insert a subreport which uses the same tables, but without any selects. In the subreport, add fields for program and status, group on {client.clientID}, and also create a formula {@admitted}:

if {table.status} = "Admitted" then 1 else 0

Create a second formula {@exclude} and be sure to place it on the subreport in the group footer or header:

whileprintingrecords;
shared numbervar exclude;

if sum({@admitted},{client.clientID}) > 0 then exclude := 1 else exclude := 0;

Suppress all sections within the subreport, and link the subreport to the main report on {Client.ClientID}. Place the subreport in GHa for the ClientID group and minimize the subreport and the section. Your regular group fields should appear in GHb for the ClientID group. Go to format->section->GHb (Client group), details, AND GF(Client group)->suppress and enter:

whileprintingrecords;
sharednumbervar exclude := 1;

I didn't test this, but I think it should work. I'm not sure about the date range issue--whether you should have any date limitations in the subreport. What happens if there is an admission after the end date of your range?

-LB

 

[saturn2]

Thank you, I'll give your method a spin and see what happens =) I'm new to Crystal reports, tho well-versed in Access as well as an established programmer.. This Crystal language is a tad bizarre to me... =)

The "back story" for the curious... every 3 months or so, we want to pull out a report (as above) to know what charts to pull from the main stacks and send to Archives. As long as there's even one active Admitted program, the chart stays in the Main Stacks, but should not stay there if all Programs (for that area) are completed... We don't "care" about after or before the date range --- BEFORE should theoretically have already been done on the "last run" and AFTER will be caught on the "next run"... (This is why we were using a pop-up prompt for the date range, so the report is "dynamic"

Thanks again, will let you know how I make out with it (will hopefully have a chance to try this tomorrow).

Dave

 

[saturn2]

LBass -- Having one minor problem with the instructions you gave above... Took me a little while to figure out how to do everything up there (I'm new to this software), but we eventually (I think) managed to get MOST of it put together right... The one thing we just couldn't figure out was your last instruction.... perhaps you can let me know more specifically how I do it:

----Quote----
and enter:

whileprintingrecords;
sharednumbervar exclude := 1;
----End----

Not sure how/where to enter those two lines... Apparently it's not one of those @formulas but I can't seem to figure out what other method to use... Thanks in advance!!!

 

[lbass]

There was an omitted space in the formula that I fixed below. You want to go to the section expert. In 8.0 you would go to format->section. Then highlight Group Header b (for your Client group--not sure whether this is your Group 1 or Group 2)-> click on the x+2 button next to "suppress" and enter:

whileprintingrecords;
shared numbervar exclude := 1;

Repeat for the details section AND the Group Footer(Client group) by highlighting and then in the suppression formula area entering the above formula. The formula area for each section allows you to conditionally use the specific section formatting option.

Let me know if you are still having trouble with this.

-LB

 

[saturn2]

LBass --

OK I've given it a try, but no matter which place I put it (using your method above), it tells me:

"The formula result must be a boolean"

Since I'm not entirely sure what we're asking it to do here (I have an idea but not enough knowledge of the language), I'm not sure what needs to be different...

It won't let me save it as is, all it does is tell me that error. So I cleared it again to close the report.. Any ideas?

Dave

 

[lbass]

So sorry. The formula should be:

whileprintingrecords;
shared numbervar exclude = 1;

I tested my post and this method works, but it leaves a lot of blank space because you can't suppress the section the subreport is in in the main report.

So, let me apologize again, as there is a simpler solution that will display nicely. Instead of using a subreport, add the Programs table a second time so you have three tables, Client, Program, Progam_1 (the automatic alias created when you choose it again). Link Program_1 to Client on ClientID also.

Group on {Client.ClientID}, and in the record selection formula add:

{progam.status} = "discharged" and
{program.date} = {?date} //your date range parameter and
{program.name} in ["Name1","Name2",..."Name9"]

Be sure the selects are all using the first Program table.

Add fields from Client and Progam to your report as necessary. Then create a formula {@Admitted}:

if {program_1.status} = "Admitted" then 1 else 0 //using the second table status field

Then go to report->edit selection formula->GROUP and enter:

sum({@Admitted},{Client.ClientID} = 0

This will result in a list of clients for the specified programs and date range who have no status = "Admitted" in any programs.

Sorry to take you down a garden path...

-LB

 

[saturn2]

I'll give this a go tomorrow morning (hopefully) .. I don't have constant access to Crystal at work, so I have to borrow a coworker's machine (they're too cheap to buy multiple licenses)....

Thank you again, I'll let you know how I make out!

 

[saturn2]

LBass....

My god, man, you're amazing with Crystal! Your last method worked so well!! It does a few odd things with SOME records (IE when it does display them, it tends to duplicate them 2, 4... in one case filled up 2 full pages -- always even numbers though)...

I don't have any way to check if it's perhaps a glitch in the source data (our system is a bit haywire some days, so wouldn't surprise me), or what... We decided the easiest solution is to simply NOT try to display the program name, status and date on the actual output.. just the client ID and names. Don't need the ProgName and whatnot on the end result anyhow, just needed to query using them =)

After that change, it's working perfectly! I bow before your greatness and thank you SO much for all your kind help!

You've managed to save me (on just ONE report run for all of 2003) an estimated 50-80 hours of work doing it the "old way"...

Up till now, we had to print off reports for discharges a much more labour-intense way... generate a report for discharges during the date range, for ONE PROGNAME AT A TIME (so total of 22 reports in all, each averaging about 60 pages for 1 year)... and it didn't cross-check them, so then you have to take all 22 reports, weed out the dupes, THEN go into the normal database system and look up every single one to check if they are still admitted anywhere else... UGH!!

You can see this new report saves me SO much time.. I just began this job in January and I don't know HOW they got along with this in the past... it was just stupid! Probably explains why my predecessor hadn't done it in about a year, though.... =)

So, thank you again!!! If there's ever anythign I can do for you, just let me know! I'm a pretty decent coder in mIRC script, Pascal, Perl, ASP, PHP, and can usually sort out most other languages if I have the time and resources (I learned the bulk of Pascal in a weekend)... I'm also an establish Access Database coder as well... I've written 12 databases commercially (big buggers, some of them, including a "dayplanner" appointment book module for 8 people to share, all 100% coded in Access...)... If any of my knowledge can assist you in future, please send me an email!

Thanks again

Dave

http://www.TitaniumWebs.com

 

[lbass]

Dave,

Yes, I was assuming you didn't need to display data from the second Programs table.

Thanks for your offer...

-LB