Skip to Content
avatar image
Former Member

IDT - Alter mandatory filter options

Hi experts,

We're using IDT 4.1 over SAP Hana.

The scenario to be created is an IDT universe over a simple table.

- Using the universe from WEBI without condition the system must automatically SELECT data WHERE FILED1 = 'A' .

- When the user insert a specific Filter Object, system must change the SELECT , transforming the condition into FILED1 = 'B' .

are there any way to implement it using IDT ?

thanks

Maurizio

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

1 Answer

  • Apr 12, 2016 at 12:28 PM

    Hi Maurizio,

    You can achieve your requirement by using @aggregate_aware function in the business objects definition.

    Aggregate awareness is a feature that has been introduced in the universes a long time ago. This feature is especially designed to dynamically define a measure on top of the most appropriated aggregated tables in a data warehouse.

    But in your case we can also use @aggregate_aware function on top of dimensions or attributes definition to select the appropriate table for query.

    Here are the steps you have to achieve. To illustrate the purpose I used a sample to validate your requirements.

    1. Create 1 or 2 alias tables in the data foundation for the table concerned by the filter you want to push in the query.
    2. Add a filter on the first alias table: FILED1 = 'A'
    3. Add a filter on the second alias table or the original table: FILED1 = 'B'
    4. Create a business object for each element you want to be used in the query.
    5. If the object is concerned by the first/default condition (FILED1 = 'A')
      The select statement must look like this: @Aggregate_Aware(TPR_1.Name,TPR_2.Name)
    6. If the object is concerned by the second condition (FILED1 = 'B ')
      The select statement must look like this: @Aggregate_Aware(TPR_2.Name,TPR_1.Name)
    7. Last step you need to define the “aggregate navigation”.
      Click on menu “Actions” then “Set Aggregate Navigation” and the following panel will show up
    8. Now you have to select the first alias table on the left hand side and click on objects incompatible with this alias tables: all objects concerned by the second condition (FILED1 = 'B ')

    That’s done for the data foundation and business layer definition.

    Now when I build this query:

    The generated Select statement will be:

    SELECT

      TPR_1.Name,

      TPR_1.Nationality,

      TPR_1.Rank

    FROM

      "Tennis Players Ranking"  TPR_1

    WHERE

      ( TPR_1.Nationality > 'F'  )


    You can notice that the table used in the FROM statement is the first alias table.

    And if I add the object(s) concerned by the second condition in the query.

    The generated Select statement will be:

    SELECT

      TPR_2.Name,

      TPR_2.Nationality,

      TPR_2Rank,

      TPR_2."Previous rank"

    FROM

      "Tennis Players Ranking"  TPR_2

    WHERE

      ( TPR_2.Nationality < 'G'  )


    You can notice that the table used in the FROM statement is the second alias table, so the second condition is pushed to the where clause.

    This solution fits exactly what you want to do.

    Didier

    Add comment
    10|10000 characters needed characters exceeded

    • According to my first  undertsanding, there is always a filter named 'X'.

      When the user add a given object in the query then this filter is replaced by a filter named 'Y'.

      This is done automatically by the query engine, not by the user.

      Am I right?

      Didier