This discussion is archived
4 Replies Latest reply: Nov 20, 2012 2:49 AM by 207376 RSS

Two Stage Filters

207376 Newbie
Currently Being Moderated
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 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Journeyer
    Currently Being Moderated
    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
    207376 Newbie
    Currently Being Moderated
    Thx for all replies. Let me try the suggestions.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points