AnsweredAssumed Answered

Charting Related Data with Multiple Y-Axes

Question asked by jmci on Jan 27, 2012
Latest reply on Jan 28, 2012 by comment

I’m having a terrible time creating a particular chart and I could use some advice.

 

I’ve created a simple test file in an attempt to reduce my problem to its essential elements. I have a table of surgical procedures (Procedure) which has the following fields:

 

ID

FacilityTypeID

PaymentAmount

Year

 

There are nine records in the Procedure table. The Year field in each record contains one of the following values: 2006, 2007, or 2008.

 

I also have a table of Facility Types (FacilityType) at which the above-mentioned Procedures were performed. It contains the following fields:

 

ID

Name

cPaymentAmtByType

 

 

The field FacilityType::ID is related to the field Procedure::FacilityTypeID. For each FacilityType there may be 0, 1, or many related Procedures.

The field FacilityType::cPaymentAmtByType is a calculation field. The formula for the calculation is Sum ( Procedure::PaymentAmount ). There are three records in the Facility Type table.

 

My objective is to create a layout containing a bar chart. This chart should show Payment Amounts by Facility Type, by Year. The X-axis (the horizontal) axis of the chart must be the Procedure::Year field. There must a Y-axis for each of the three records in the FacilityType table. The legend for the Y-Axis should be the field FacilityType::Name. For each of the three Years for which there are Procedure records I need to see a bar showing PaymentAmounts for each of the three FacilityTypes.

 

I have attached my sample file if you're curious.

 

I would be ashamed to admit how much time I’ve spent on this problem already trying different approaches. If I need summary fields I can add them. On which table should the layout be based and how should it be sorted? On which layout part should the chart be positioned? I could use some advice.

Attachments

Outcomes