4 Replies Latest reply: Nov 20, 2012 4:49 AM by lkuchibh-Oracle RSS

    Two Stage Filters

    lkuchibh-Oracle
      We have the following requirement

      Say, I have 1000 unpicked Sales Order lines. I need to come up with a criteria (a set of Sales Order line attributes) to pick the sales order lines.

      STEP-1, I want to establish a broad set of criteria and ignore all other lines that do not meet the criteria. For Ex: I want to consider only the unpicked Sales Order lines whose 'Ship To' Destination is within 'USA'. Let's say the number of unpicked lines come down to 750 with this filter. I want to set this as base chart (For Ex: If the data is being shown in a pie chart, the unpicked Sales Order lines should show up as 100%)

      STEP-2, I want to apply more filters (say, 'Shipment Priority':High, 'Date Scheduled':between today's date and 7 days before today's date, etc.). Once I choose these filters, I want the base chart to show the % of lines that fullfill these filters with respect to the data set in STEP-1(For Ex: 65% as 'Meet the criteria' and 35% as 'Do not meet the Criteria'. Note: The 100% should still correspond to the 750 lines that I came-up in STEP-1).

      If I am satisfied with the % of lines that meet the critieria, then I would go to the EBS page and create a batch (with criteria specified in Step 1 and Step 2) to pick the unpicked lines.

      I couldn't figure out on how we can achieve the requirement mentioned in STEP-2. Appreciate any help/pointers.
        • 1. Re: Two Stage Filters
          958853
          There are probably many ways to solve this, one way i can think of you will need to define to two source record sets to do your final percentage calculation against. First we need to define the

          DEFINE AllUnpicked AS SELECT
          COUNT(orderNum) AS AllCNT
          FROM AllBaseRecords
          WHERE ShipToDestination = 'USA' AND orderStatus = 'Unpicked';

          This query will return the count of all unpicked orders that are being shipped to the USA, by using the FROM AllBaseRecords the query is against the data source without any refinements from the user's navigation state.

          DEFINE NavStateUnpicked AS SELECT
          COUNT(orderNum) AS navStateCNT
          FROM NavStateRecords
          WHERE ShipToDestination = 'USA' AND orderStatus = 'Unpicked';

          In this query we are returning the count of unpicked orders from the USA and including any refinements that from user's navigation state. This define includes FROM NavStateRecords , that is option I included it to help in understanding what is the source for each query.

          DEFINE UnpickedPieChart AS SELECT
          100 * navStateUnpicked.navStateCNT / AllUnpicked.AllCNT as PercentMeetCriteria
          100 - PercentMeetCriteria as PercentNotMeetCriteria

          The above is computing the percent of the basecount and the current navigation states count. So as user use the guided navigation and search to narrow down the number of orders that meet the criteria the navStateUnpicked.navStateCNT will decrease while the AllUnpicked.AllCNT will always be the same. The final query you would put in to the 2.3 view manager would be something like this:


          DEFINE AllUnpicked AS SELECT
          COUNT(orderNum) AS AllCNT
          FROM AllBaseRecords
          WHERE ShipToDestination = 'USA' AND orderStatus = 'Unpicked';

          DEFINE NavStateUnpicked AS SELECT
          COUNT(orderNum) AS navStateCNT
          FROM NavStateRecords
          WHERE ShipToDestination = 'USA' AND orderStatus = 'Unpicked';

          DEFINE UnpickedPieChart AS SELECT
          100 * navStateUnpicked.navStateCNT / AllUnpicked.AllCNT as PercentMeetCriteria
          100 - PercentMeetCriteria as PercentNotMeetCriteria



          If you want to eliminate the where clauses, you can create a new datasource with a recordFilter base function of the two filters you want, and use that as the datasource for your chart.

          I would also suggest reviewing the Query Language Reference guide, there is a section that discusses calculating percentages:
          Calculating percent change between most recent month and previous month
          http://docs.oracle.com/cd/E29805_01/server.230/es_eql/src/ceql_use_case_percent_change.html

          Cheers,

          Pete Freeman
          RealDecoy - We thrive on Complexity. We deliver Simplicity.
          Boston, Jamaica, Ottawa, Toronto, Vancouver
          www.realdecoy.com
          www.endecacommunity.com

          Edited by: PeteFreemanRD on Nov 15, 2012 1:27 PM
          • 2. Re: Two Stage Filters
            920244
            PeteFreemanRD is correct, although a few details need tweaking.

            First, you may only use an aggregator like COUNT in a grouping EQL statement, so the first two statements become

            DEFINE AllUnpicked AS SELECT
            COUNT(orderNum) AS AllCNT
            FROM AllBaseRecords
            WHERE ShipToDestination = 'USA' AND orderStatus = 'Unpicked'
            GROUP;

            DEFINE NavStateUnpicked AS SELECT
            COUNT(orderNum) AS navStateCNT
            FROM NavStateRecords
            WHERE ShipToDestination = 'USA' AND orderStatus = 'Unpicked'
            GROUP;

            Finally, to refer to one statement from another, you'll need to use EQL's "lookup" syntax:

            DEFINE UnpickedPieChart AS SELECT
            100 * NavStateUnpicked[].navStateCNT / AllUnpicked[].AllCNT as PercentMeetCriteria,
            100 - PercentMeetCriteria as PercentNotMeetCriteria

            Alternatively, you can use a join:

            DEFINE UnpickedPieChart AS SELECT
            100 * NavStateUnpicked.navStateCND / AllUnpicked.AllCNT as PercentMeetCriteria,
            100 - PercentMeetCriteria as PercentNotMeetCriteria
            FROM NavStateUnpicked CROSS JOIN AllUnpicked

            Richard

            Richard
            • 3. Re: Two Stage Filters
              Branchbird - Pat
              Are the criteria in Step 1 dynamic?

              For example:

              1) Do you know ahead of time that step 1 of the chart should show USA and "unpicked"?
              2) Or do you only know that the user will pick a country (could be CAN, could be ESP, etc.) and/or a status (picked/unpicked) but could be any of the available values?
              3) Or is even more complex where there's a whole set of attributes that should only apply to Step 1 and not Step 2?

              If the answer to #1 is no, you may need a custom charting component to help the user through this with advanced refinement restriction on a per clause basis.

              If the answer to #1 is yes, forget I mentioned it and Richard's got you covered. :-)

              Regards,

              Patrick Rafferty
              http://branchbird.com
              • 4. Re: Two Stage Filters
                lkuchibh-Oracle
                Thx for all replies. Let me try the suggestions.