Main report depends on Subreport total

[LOgden]

Here is the problem I have been trying to solve:

Each time we see a client we call this an encounter.
We pay for encounters within various departments in our institution.
Each client has a cap amount that they must meet before we start paying for their encounters.

I need a report which prints out for each department which encounters we are paying for when we send them payment.

The report thus needs to first run a total for each client and if the client is over their cap, include the cost of their subsequent encounters on a report grouped by the department being reimbursed,

I think the answer includes a subreport that has a running total, but am having problems because the RT on the subreport needs to be grouped by client name and restarted with each client, while the main report must be grouped by our encounter sites. Any tips, hints, complete answers would be greatly appreciated as I have tried everything I can think of and haven't quite got it. Lucy

 

[SmpTraining]

Is this a fair interpretation of your requirements?

You have a CLIENT table and an ENCOUNTERS table, which is a one-to-many link (probably on client_id). In the CLIENT table you have a field holding the CAP amount.

In the encounters table you a FEE field.

You want to run the report Grouped by client, listing only the encounters over the CAP amount.

Questions:
1. What if the client does not reach the CAP amount - I assume you do not want to see the client at all.

2. If the client DOES reach the CAP amount, do you want to list every encounter or just those over the cap amount?

Steve Phillips, Crystal Consultant

 

[LOgden]

1. Yes, I only want to see clients who have encounters that are over the cap amount.

2. When the client does reach the cap, I want to list only the encounters that are over. (because we only need to reimburse for these)

Your interpretation of how my table & fields are put together is correct. However, for the final report output, I need the grouping to be Group By "Encounter Site". This is because the objective is to a check to each Encounter Site with a list of all the clients/encounters the check covers.

I have attemped to do this by setting up a main report grouped by encounter site then by client name, putting a subreport in the group header for the client name which does a running total of the client's encounters, suppressing those where the sum of the encounter fees minus the cap amount is less than the cap amount, thus listing only those encounters over the cap amount; each encounter in the database has an 'entry id' field, so the subreport is linked to pull data into the main report only where the entry id on the sub matches the entry id on the main. This seems to me like it would work (unless the suppressed data in the sub still is there, but is hidden), however, I get erroneous data when it runs. The page for each encounter site (they're set up to be on separate pages) has one client listed on it and not always a client who is over his or her cap.

Thanks for helping me think about this & figure it out. Lucy

 

[SmpTraining]

Sorry for the delay in responding, for some strange reason I wasn't notified you had replied. Anyway, I hope the wait was worth it....

Lets look at this from a basic level again.

You can write a main report and identify the IDs of the encounters you would like to report on (that is those encounters over the cap amount).

Problem is, you then need to report on these by encounter site which is a different grouping to that used to identify the encounter IDs required.

That being the case, I have just written a report that adds the IDs to a string field such that I end up with a list e.g. "1,2,3,4,5" at the end of the report.

I have then added a subreport, passing the string of IDs as a linked parameter and set the subreports selection to only select these IDs.

The brilliant thing is, you can then format the subreport with whatever grouping you want - doesn't have to be anything like the main report.

The subreports select criteria is a tiny bit complex as it needs to convert the string into an array before being used by the select criteria.

Tell me if you want more info on this, I can email the test report if you want. You can email me on Steve@SMPtraining.co.uk Steve Phillips, Crystal Consultant