1.
Requirement
Key in any value (say item number) in any case
(upper/lower/mixed etc..) in a dashboard prompt. OBIEE should take this prompt
value and pass as a filter condition by ignoring its case and display results
in the report regardless of the case the data is stored in the database.
Example: Assume the data is stored in the database as
below
|
Item Number
|
Item Name
|
|
abc123
|
Item
abc123
|
|
PQR100
|
Item
PQR 100
|
|
Xyz400
|
Item
Xyz400
|
If the user keys in the item number as “ABC123”, obiee
prompt, obiee report should display as
below
|
Item Number
|
Item Name
|
|
abc123
|
Item
abc123
|
If the user keys in the item number as ” ABC123 ; pQr100
; xyz400 ”, obiee prompt, obiee report
should display as below
|
Item Number
|
Item Name
|
|
abc123
|
Item
abc123
|
|
PQR100
|
Item
PQR 100
|
|
Xyz400
|
Item
Xyz400
|
Let’s see, how we can implement this
solution
2.1 OBIEE Reporting – Dashboard Prompt
As the first step, lets create a dashboard prompt.
In the below example, we created a prompt with 2 cols (DSN, Item Number). Here our focus is on the Item
Number Column prompt
- In the above prompt, I’ve limited the Item number values by DSN (doesn’t play any significance in our solution but just to restrict data)
- Set a Presentation variable to hold the selected/keyed in value(s) from the prompt. We have used the variable “V_ITEM_NUMBER”
- Also note that, we have enabled the “Enable user to type value” option for the prompt.
- Save the prompt
2.2
Create Report
Now, let’s create a simple report and make
the DSN as “is prompted” and Item Number as a filter.
- Pulled in few cols from the Item folder into the report
- Made a filter on DSN with as “is prompted” operator
- Crete an advanced SQL filter for the Item Number Column
Filter Code:
UPPER("Item"."Item
Number" ) IN
(@{V_ITEM_NUMBER}[UPPER('@')]{UPPER("Item"."Item Number"
)})
Here, on the left hand side of the advanced SQL filter we have
UPPER(column name)
- Lets see the right hand side, In the "IN" clause
1. we called
in the Variable name @{V_ITEM_NUMBER} which we set in the prompt.
2. Specified
to repeat the UPPER clause and the single opening and closing quotes “ ‘ “ for any number of values (@) in the variable.
Ie: Lets assume the users keyed in 2 values
in the prompt, the in clause we should have the values as IN(UPPER(‘value1’),UPPER(‘value2’))
If we don’t do this, the presentation
variable will put the single quote in the beginning and end only, not between
the values
3. At the
end, given the same colum name (here item.item_number) with an upper clause as
the default value of the variable {UPPER("Item"."Item
Number" )}. This is
done for a reason that, if the user selects no value in the prompt, the report
should not filter any values and should display all the data.
- Save the repot
- Pull in the report and prompt into a dashboard page for testing
3. Lets' test it...
Lets see how this will work
- Keyed in the item numbers as x9;X8;Y/e which are stored in the data base as X9,X8 and Y/E
- Applied the filter. The result is as below. The report displayed all filter specified item numbers in the report as expected
- Now lets look on the session log (physical query) created here
* See the query
which is highlighted in bold for the condition.
WITH
SAWITH0 AS (select
/*+ full(T542075) */ distinct
T542075.DSN as c1,
T542075.ITEM_ID as c2,
T542075.ITEM_NAME as c3,
T542075.ITEM_NUMBER as c4
from
XXLSR_ITEM_D
T542075 /* Dim_ITEM */
where (
T542075.DSN = 'XXXXXXXXX' and (upper(T542075.ITEM_NUMBER)
in (upper('X8'), upper('Y/e'), upper('x9'))) ) )
select D1.c1 as c1, D1.c2 as c2, D1.c3 as c3, D1.c4 as
c4, D1.c5 as c5 from ( select distinct 0 as c1,
D1.c1 as c2,
D1.c2 as c3,
D1.c3 as c4,
D1.c4 as c5
from
SAWITH0 D1
order by c5 desc, c2, c3, c4 ) D1 where rownum <=
10000000
- Removed all the item numbers from the prompt. This should return me all the items in the database for the Dsn filter as we are not filtering the item.
- Applied the condition. The result is as below. The report displayed all the values as expected
- Lets look on the session log (Logical , physical query) created here
See the query
which is highlighted in bold for the condition.
Logical Query: See the part of the query marked in
bold for the item number default condition. The Left hand side is equal to the
right hand side in the logical query for this condition.
SELECT
0 s_0,
"LSR - Services"."Item"."Dsn" s_1,
"LSR - Services"."Item"."Item Id" s_2,
"LSR - Services"."Item"."Item Name" s_3,
"LSR - Services"."Item"."Item Number" s_4
FROM "LSR - Services"
WHERE
((UPPER("Item"."Item Number" ) IN (UPPER("Item"."Item Number" ))) AND ("Item"."Dsn" = 'XXXXXXX'))
ORDER BY 1, 5 DESC NULLS FIRST, 2 ASC NULLS LAST, 3 ASC NULLS LAST, 4 ASC NULLS LAST
FETCH FIRST 10000000 ROWS ONLY
Physical Query: You will notice that there is no
condition passed for Item number in the physical query.
WITH
SAWITH0 AS (select /*+ full(T542075) */ distinct T542075.DSN as c1,
T542075.ITEM_ID as c2,
T542075.ITEM_NAME as c3,
T542075.ITEM_NUMBER as c4
from
XXLSR_ITEM_D T542075 /* Dim_ITEM */
where ( T542075.DSN = 'XXXXXXXX' ) )
select D1.c1 as c1, D1.c2 as c2, D1.c3 as c3, D1.c4 as c4, D1.c5 as c5 from ( select distinct 0 as c1,
D1.c1 as c2,
D1.c2 as c3,
D1.c3 as c4,
D1.c4 as c5
from
SAWITH0 D1
order by c5 desc, c2, c3, c4 ) D1 where rownum <= 10000000
-----------------------------------------------------------------------------------------------------------------
/*--------------------------------CREATED_BY : JJN ; CREATED_DATE : 01-Sep-2015---------------------------------*/
No comments:
Post a Comment