I have been losing my sanity trying to solve or find a work-around for a problem summarizing data in a cross tab report.
I have a report that consists of multiple crosstabs grouped by agency. I have it worked out so that each agency has a one-page set of crosstabs that summarize three years of data from patient demographics to financial information...and it works beautifully. Included in these crosstabs are summarized fields that are formulas to calculate percentages (for instance, the percentage of all patients who are uninsured). Since there is only one entry for each agency, by year, the "average" option works here and correctly displays the percentage.
However, in the report header of that same report, I am trying to reproduce the same one-page set of crosstabs FOR ALL AGENCIES. Sort of a "Status of the Program." The crosstabs work great for summarizing the basic fields and giving totals (sums) of all information. The percentage forumulas, on the other hand, are a nightmare. If I choose the "average" option in the summarized field, then I do get an average of all the programs percentages. However, that average is NOT the average calculated if you take the total sums and divide them. (For instance, taking the summary fields for total uninsured patients divided by the total patients DOES NOT give you the "average" calculated here).
Is there a solution for having both the summarized totals AND the actual percentages for the entire program displayed here on the report header.
Also, keep in mind that this needs to be easily updatable for each year once new data is available.
Suggestions? Have I completely missed something here?