I am trying to create a report that is a bit complicated. Basically, I have a main report that pulls all the appointments for a given date range. The main report is grouped as follows:
Group 1: Department
Group 2: Doctor
Group 3: Location
--- Detail Section Here
In the group 3 header, I have a subreport that links via the department, doctor, and location to pull the employee and doctor hours for the given location. The results are passed back via shared variable back to the main report.
In the group 3 footer (directly below the records), I have a few running totals that calculate the appointments for the selected doctor. In addition, there are some shared variables that are shown here from the subreport. This works perfectly. For example:
Doctor: Dr. Smith
Due to the grouping, I receive multiple Group 3's for the different locations that had appointments for Dr. Smith. So, I could have 3 lines listed, for locations A, B, and C with the respective subreport date.
Now the issue. The above works fine for each doctor. However, I need to roll-up this data to department, and then a grand total. Basically, let's say we have Department A that has two doctors. The report will show each doctor individually, then below that, I need to perform the same calculations I did in Group 3 for Group 2. However, since the detail section is only in group 3, I can't do the same calculations in group 2. I thought about using a subreport for the department and grand total, but I am already using a subreport to calculate the employee / doctor hours which resides in a different database. Crystal reports does not support nested subreports.
Is there anyway to pull the records 3 times? I know this is not efficient, but I need to pull the data 3 times, so I can calculate for each doctor, then per department, and then per location (grand total).