IDT - Alter mandatory filter options

[Former Member]

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

[Former Member]

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

[Former Member]

Hi Didier,

first of all thanks for the detailed reply .

Unfortunately I can't use the aggregate aware because the universe and the table has 1.000 dimension ad it is impossible to add the function for the objects.

I'm looking for a solution that can change the generated SQL filter without the usage of derived\alias table because the universe is really big and consolidated.

thanks!

Maurizio

[former_member207052]

Hi Maurizio,

From what I understand, you want to initially run the query with default parameters and then the user could change it as per his/her requirement.

If what I understood is right, why not just set the default value for your prompt in IDT? Posting a  screenshot or two might help here.

[Former Member]

Hi,

yes this is the scenario .

But I can't use a prompt because I don't want all users have to select the default value; they have many existing webi report that can't be affected.

the idea is to use a new object, like a Filter, when a user want to change the parameter just drag & drop this filter into the query .

thanks!

[former_member207052]

In universe (while using IDT or UDT, doesn't matter) you can define the below filter options

1. Filters with prompts and without prompts
2. Mandatory Filters and optional Filters

In your scenario, First you are going to build the report with an optional filter (without prompt). End user will open to run the report, no prompts needs to be filled and he will get the results.

When a user wants to change the filter, he goes to the query, removes the old one, inserts the new one(with/without prompt as per your choice) and runs the report to see the desired result.

[Former Member]

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