1 Reply Latest reply on May 7, 2015 8:40 AM by BrandonWoodward_1

    Dynamic Chart Filter by Date Range

    BrandonWoodward_1

      Title

      Dynamic Chart Filter by Date Range

      Post

      I am trying to create a chart with a dynamic date range on a products layout that includes the price purchased (y-axis) and the date purchased (x-axis) which are taken from a lineitems table. 

      - there are two global fields for the start date and the end date on the products layout which will determine the date range for data displayed on the chart

      - the Chart Data is set to Current Record (delimited data)

       

      Data (x-axis)

      ExecuteSQL("
          SELECT date_PO
          FROM PRO_ProLin
          WHERE date_PO >=?
          AND date_PO <=?
              "
          ; "" ; "";PRO_Products::g_startdate;PRO_Products::g_enddate )

      Data (y-axis)

      ExecuteSQL("
          SELECT Price
          FROM PRO_ProLin
          WHERE date_PO >=?
          AND date_PO <=?
              "
          ; "" ; "";PRO_Products::g_startdate;PRO_Products::g_enddate )

       

      This is where my problem occurs. I'm trying to return data on the chart that reflects the current record but the chart is showing price information for every record in the date range. 

       

      Anyone have any ideas?

        • 1. Re: Dynamic Chart Filter by Date Range
          BrandonWoodward_1

          For anyone interested here was the solution:

          x-axis:

          ExecuteSQL( "
              SELECT date_PO
              FROM LineItems
              WHERE date_PO >= ?
              AND date_PO <= ?
              AND ProductNumber = ?
                "
              ; ""; ""; Products::g_startdate; Products::g_enddate; Products::ProductNumber)

           

          y-axis:

          ExecuteSQL( "
              SELECT Price_Report
              FROM LineItems
              WHERE date_PO >= ?
              AND date_PO <= ?
              AND ProductNumber = ?
                "
              ; ""; ""; Products::g_startdate; Products::g_enddate; Products::ProductNumber)