on 04-12-2016 9:40 AM
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
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.
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
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!
In universe (while using IDT or UDT, doesn't matter) you can define the below filter options
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.
User | Count |
---|---|
84 | |
10 | |
10 | |
9 | |
7 | |
6 | |
5 | |
5 | |
4 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.