6 Replies Latest reply on Jan 28, 2012 4:05 AM by comment

    Charting Related Data with Multiple Y-Axes

    jmci

      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.