Graph in a Report

[RayMilhon]

I have a Report that Counts all Authorizations for a PCP by the Specialty of the Referral. For Example:



PCP Line of Business Speccode Auths mbrs_months grpauths grpmbr_months
Dr Jones Senior ANESTH 3 2400 150 50000
Dr Jones Commercial ANESTH 15 12000 560 120000


The above is a minimal results of the query the report is based on.

There are 2 computed fields on the report for each Line of Business PCP auths per 1000 = Auths/mbrs_months * 12000 or 3/2400*12000 and group auths per 1000 = grpauths/grpmbr_months * 12000 or 150/50000*12000

The report works perfectly so far. Which PCP the report is run for is obtained from a form with a combo box showing all PCP's one is selected and the report is generated.

Now there is an additional requirement for a graph in the Line of Business Footer That shows the Specialty PCP Auths per 1000 and grp Auths per 1000 so the PCP can be compared to the group. I use the same query for the graph as I used for the report but I get an error saying that the Forms!frm_generate_report!cbo_pcp does not exist. But it does exist it is open the rest of the report works perfectly it's just the graph that errors?????

[June7]

I analyze graph issues best when I can work with data. If you want to provide db for analysis, follow instructions at bottom of my post.

However, I would probably have the chart RowSource reference field on the report, not the form, as criteria.

The query is a saved object and it has dynamic parameter that references the form control? I never use dynamic parameterized query.

[RayMilhon]

The Report is an ad-hoc report that can be run at anytime. The report is specific to a single provider and compares that provider to the rest of our PCP's. The provider is selected at the time the report is run. if you have a better way to accomplish this I'd appreciate hearing it.

[June7]

Doesn't quite clarify. Is the report based on a saved query object? Does that query have dynamic filter parameter that references form?

As stated, I don't use dynamic parameterized queries. I prefer to pass filter criteria to report when it is opened:

DoCmd.OpenReport "report name", , , "ID=" & Me.ID

[RayMilhon]

Sorry I wasn't clear. Yes the report is based on a query. Which is also based on a couple of other queries.

There are 2 base queries the first get's all authorizations for that specific PCP and does an accumulation of all approved authorizations by Specialty for that PCP. The second gets all authorizations for all other PCP's and does the same accumulation

The Report query Then combines those 2 queries So we have the necessary data for the report. That works without a problem. I then have a graph that uses the same query as the report for it's data so show those elements in a bar chart. That's where it error's. What I don't get is why does the query work fine for the report and then fail for the graph?

[June7]

My comments still apply. Didn't answer question - does the query have dynamic parameter?

Again, best to provide db for analysis.

[RayMilhon]

I'm unable to provide the DB there is proprietary elements that cannot be removed. I'm obviously not being very clear.

There is a form in the Database with 2 controls. the first is a combobox that lists all PCP's. The second is a command button to generate the report. The user selects the PCP in the combo box and then generates the report.

The report is 2 parts. part 1 is a list by specialty code of the number of all referrals for that PCP and a per 1000 number for that PC and a total of all other PCP's with a per 1000 number calculated as follows

TotalReferrals/TotalMembership*12000

Displayed as in my original post.

Part 2 is the same data in a bar graph with the Specialty for the X Axis and the per1000 calculation for the Y Axis. For each Specialty there are 2 bars 1 for the PCP and 1 for the other PCP's

Part 1 works fine. Part 2 gives me an error that the combobox doesn't exist. What I can't figure out is why the combobox exists for the Report but not for the graph both use the same query.

[June7]

Don't know. I've never had report graph reference control on form. My graphs reference field of report as parameter.

Sorry, doubt I can help any further if you can't or won't provide a sanitized db.