3 Replies Latest reply on Oct 3, 2017 12:38 PM by Hilary Farrell-Oracle

    APEX 5 - Bar chart data incorrect

    Shas

      I see a discrepancy between what my bar chart shows and the data when looked at in the Database (Both SQL Developer and within SQL Workshop with Apex - These 2 are in sync) - I am perplexed as to how this is happening.

       

      This is my bar chart screenshot (below),

      When the SQL query is executed in both SQL Developer and SQL Workshop (within APEX) - I am getting the below dataset,

       

       

      And from this its clear that the discrepancy is on "Pending Return" status that database shows it's against EQ TEC DE INF (Asset Type) and Chart shows it's against EQ AGRICOLA (Asset Type)

       

      Note: Caching is disabled both at the application level and also at the chart/region level and I have tried logging out and logging back multiple times just to make sure if its down any session cache?

       

      My SQL query which is in the series is as below with ASSET_RETURN_STATUS being the "Series", ASSET_TYPE being the "Label" and COUNT(ASSET_SERIAL) being "Value"

       

      SELECT ASSET_RETURN_STATUS
             ,ASSET_TYPE
             ,COUNT(ASSET_SERIAL)
        FROM (SELECT DISTINCT
                     FAC.SEGMENT1                                                  ASSET_TYPE
                     ,ASR.ARS_CODE                                                 ARS_CODE
                     ,ASR.ASSET_RETURN_STATUS                                      ASSET_RETURN_STATUS
                     ,NVL(CSI.SERIAL_NUMBER,ASR.ASSET_NUMBER)                      ASSET_SERIAL
                FROM APPS.CSI_ITEM_INSTANCES           CSI
                     ,APPS.OKC_K_ITEMS                 CIM_IB
                     ,APPS.OKC_K_LINES_B             CLE_IB
                     ,APPS.OKC_LINE_STYLES_B           LSE_IB
                     ,APPS.OKC_K_LINES_B             CLE_INST
                     ,APPS.OKC_K_LINES_B               CLE_FIN
                     ,APPS.OKL_TXL_ASSETS_B     OFA
                     ,APPS.OKC_K_LINES_TL     CLE_ASSET
                     ,APPS.FA_ADDITIONS_B              FAA
                     ,APPS.FA_CATEGORIES_B             FAC
                     ,APPS.OKL_AM_ASSET_RETURNS_UV     ASR
               WHERE 1 = 1
                 AND CSI.INSTANCE_ID             = CIM_IB.OBJECT1_ID1
                 AND CIM_IB.CLE_ID        = CLE_IB.ID
                 AND CIM_IB.DNZ_CHR_ID         = CLE_IB.DNZ_CHR_ID
                 AND CLE_IB.LSE_ID           = LSE_IB.ID
                 AND CLE_IB.CLE_ID        = CLE_INST.ID
                 AND CLE_IB.DNZ_CHR_ID        = CLE_INST.DNZ_CHR_ID
                 AND CLE_FIN.CHR_ID        = CLE_FIN.DNZ_CHR_ID
                 AND CLE_INST.CLE_ID        = CLE_FIN.ID
                 AND CLE_INST.DNZ_CHR_ID        = CLE_FIN.DNZ_CHR_ID
                 AND OFA.DNZ_KHR_ID        = CLE_FIN.DNZ_CHR_ID
                 AND OFA.ASSET_NUMBER             = CLE_ASSET.NAME
                 AND OFA.ASSET_NUMBER             = FAA.ASSET_NUMBER
                 AND FAA.ASSET_CATEGORY_ID       = FAC.CATEGORY_ID
                 AND CLE_FIN.CLE_ID        IS NULL
                 AND OFA.TAL_TYPE                 = 'CFA'
                 AND CLE_FIN.ID            = CLE_ASSET.ID
                 AND ASR.KLE_ID            = CLE_FIN.ID
                 AND CIM_IB.JTOT_OBJECT1_CODE     = 'OKX_IB_ITEM'
                 AND CIM_IB.OBJECT1_ID2           = '#'
                 AND ASR.ARS_CODE            IN ('SCHEDULED','PENDING_RETURN','RETURNED','RET_INSPECTED','AVAILABLE_FOR_SALE')
                 AND CLE_ASSET.LANGUAGE      = 'US'
             )
             GROUP BY ASSET_RETURN_STATUS
             ,ASSET_TYPE
      
        • 1. Re: APEX 5 - Bar chart data incorrect
          Hilary Farrell-Oracle

          Hi Shas,

           

          This topic has been raised on a number of other threads, such as one updated just a few hours ago: Oracle APEX 5.1 stacked bar chart .  As you'll see referenced in all of the other associated threads, JET requires each series of a multi-series query to contain a data point - even if that data point be the value null - for every x-axis label.  Otherwise, it will not render the data points correctly.  With APEX 5.1, the densification of the data needs to be done via the chart query.  With our upcoming 5.1.3 patch set release we have added logic to automatically inject the missing points into the resulting data set.  Until 5.1.3 is production, you will need to handle this via your query.  You'll see various examples in the associated forum threads on this topic.

           

          Regards,

          Hilary

          • 2. Re: APEX 5 - Bar chart data incorrect
            Shas

            I see now that 5.1.3 is available for us to consume - this would have been fixed there correct?

            • 3. Re: APEX 5 - Bar chart data incorrect
              Hilary Farrell-Oracle

              Hi Shas,

               

              Yes - applying 5.1.3 should should automatically handle the filling in of any gaps in your data, to ensure the multi-series chart is rendered correctly.  There are 2 exceptions to this: if your chart is using the Time Axis Type attribute, and it is set to 'Mixed Frequency' or 'Skip Gaps'.

               

              Regards,

              Hilary