obiee use presentation variable in filter

Real World OBIEE: Demystification of Variables Pt. 2

Matthew Walding

In part one of this blog series, I went over using bins and presentation variables to dynamically create groups and switch between them in a report and on a dashboard. In part two, I am going to talk about making reports dynamic for periods of time using repository, system and presentation variables. Before I dive into an example, there are a couple of things I would like to cover first.

The sysdate function returns the current datetime set by the system where the database resides. Sysdate is a really useful function for creating repository variables for use with date dimensions. If I go into SQL Developer, I can write a query to return the current sysdate :

CURRENT_DATE

The current_date functions returns the current datetime set by the system where the bi server resides. This datetime may differ from sysdate depending on the geographical location of the database vs. the system that OBIEE resides on. I can write a query using sql developer to return the datetime using the current_date function:

Since my database and OBIEE instance are on the same system, sysdate and current_date are the same.

When using sysdate or current_date to create repository variables for dates (which I am going to show in an upcoming example), you have to keep something in mind. While the date may match, the time may not. To show an example of this, I am going to join one of my date columns with sysdate.

If I run this query, I don't get an error but I get no results.

Why? To answer this, I need to write a query to inspect my date column.

As you can see by the results of my query, the DIM_DATE_KEY column does have the same format as sysdate but all the times are set to 00:00:00 (or midnight). To further demonstrate the difference between my date column and sysdate , I am going to write a new query and use the TRUNC (or TRUNCATE ) function.

As you can see, the query runs successfully but notice how sysdate and DIM_DATE_KEY still have different times. How is the join possible? Because I used the truncate function in the where clause in my query for sysdate . Without going into too much detail, using truncate on a date function without any formatting (which I will cover later) will set (or truncate) the datetime to the start (or midnight) of the current day. For example, if I run another query that just selects the truncated sysdate from dual, I get this result.

Now, lets dive into an example.

Note: For all of the examples in this blog series I am using OBIEE 12.2.1.2.0

The Scenario

In this example, I have been asked to create a report that is going to reside on a products dashboard. It needs to have the same product grouping as the report I used part one of this series, needs to contain Gross Rev $, Net Rev $ and # of Orders and have a prompt that can select between the first and current day of the month and every day in-between. The person who requested the report wants the prompt to change dynamically with each month and does not want users to be able to select future dates.

There are two foreseeable challenges with this report. The first, and probably the most obvious, is how to make the date prompt for the current month and have it change dynamically with each month. The second is how to pass the dates into the report.

There is one more challenge that I will have to tackle. There is a gap in the data loads for # of Orders. Data does not update until the 2nd or 3rd of each new month. This wouldn't be a big deal except the person who requested the report wants a summary of the previous months # of Orders to be shown until the data is updated for the current month.

Fortunately, by using Repository, System and Presentation Variables, I can accomplish all of the requirements of this report.

The Example

For this example, I am going to start by creating Repository Variables to use with my date column in order to make the dates dynamic. There are other ways to make dates dynamic using functions within Answers but they are a little bit trickier to use and are less common. I am going to go over some of those functions in part three of this blog series.

Repository Variables are created using the Admin Tool. By launching the Admin Tool and opening my RPD in online mode (can also be created offline), I can go to Manage > Variables to start creating my first Repository Variable.

From the Variable Manager window, I can create a Repository Variable by selecting Action > New > Repository > Variable.

I am going to start by creating the Repository Variable for the current date. Since this variable will be dynamic, I need to make sure I select the option 'Dynamic' and I am going to give it the name USCurDate .

Now I need to create a new init block. I can do this by clicking New...

Once in the Repository Variable Initialization Block screen, I need to give the init block a name, set the schedule for when variable or variables will be refreshed then click Edit Data Source to define the connection pool the init block will use as well as the initialization string (query) the init block will use to populate the Repository Variable.

In the data source window, I am going to set my connection pool to one I have created just for my init blocks and then type in the following into the initialization string window:

If I click Test , the query will execute and will return a result.

Notice how the result is the same as the query I ran using SQL Developer earlier.

Now I need to create a Repository Variable for the first day of every month. I am going to use the same method as before and name it USMoBeginDate. The query I am going to use is slightly different from the previous query. I still need to use the TRUNC function but I also need to apply formatting so that it truncates to the start of the month. I am going to enter the following into the initialization string window:

Some other useful queries I can use are:

First Day of the Current Year

Last Day of the Previous Year

Previous Year Date

Now I need to create a Repository Variable for the previous month to use with my # of Orders measure column. Upon inspection, I discover that the column I need to use is called Calendar Year Month and is a VARCHAR or character type. If I go into Answers and pull in the Calendar Year Month column, I can see the format is ' YYYYMM '

To create the Repository Variable, I am going to use the same method as with the current date and first day of the current month Repository Variables and issue a new query. Because the Calendar Year Month column is a VARCHAR, I need to use the to_char function to change sysdate from a date type to a character type, use some formatting syntax and use some basic arithmetic. The query is as follows:

To break down each part of this query, lets start with the year. In order to use the 'YYYY' format I must first cast sysdate to a character ( to_char(sysdate, 'YYYY' )). Then I need to cast that result back to and int so that I can multiply by 100. This will give me the result 201500.00. The reason for this is when I add the month number to my yearx100 , there will always be a leading 0 for month numbers 1-9. To get the previous month number, I have to first cast sysdate to a character and use the formatting 'MM'. I then have to cast it back to an int and subtract 1 to get the previous month number ( to_number(to_char(sysdate, 'MM') -1 ) then cast the entire statment back to a character type so that it matches the type for the Calendar Year Month column. When I run the query, I get this result.

Now that I have my three repository variables (USCurDate, USMoBeginDate and Prev_Month) I can start to create the report.

Im going to fast forward a little bit to the part of the report creation process where I will use my Repository Variables I created using the Admin Tool. Since I am using virtually the same report as part one of this blog series, please refer back for how to create custom groups using bins and presentation variables and custom value prompts.

Because of the delay in the data load for the # of Orders at the beginning of the month, I can not use a global report filter. Instead, I am going to have to use something called a Filter Expression within each measure column formula.

About Filter Expressions

Unlike global report filters, column formula level filter expressions are used when you need to specify a particular constraint within the column formula itself. Because the filter is at the column formula level, it is independent of any subsequent column filters.

Note: When using a column formula filter for a measure, you can not add a global filter of the same data subject on top of it. For example, if using a column level filter for a particular Year and Month, I can not add a global filter for a particular year. The two filters contradict each other and the result will be null .

To add a filter in the column formula, go to Edit formula, make sure the column syntax is highlighted and click Filter.

From here the Insert Filter window will pop up and I can select the attribute column to filter the measure by. Here, I want to use the column Day Date to filter Gross Rev $ by the day.

I can add a column by double clicking it in the the Subject Areas pane. When a column is added, I will be prompted with a New Filter window and from here, everything is exactly the same process as adding a global report filter.

Here I need to define the operator as is between since we are dealing with date ranges. I could call my Repository Variables for current_date and first day of the month here but, because the request is for a prompt to select between date ranges, I am going to have to call Presentation Variables and use the prompt to populate the actual values.

Note: If you are unsure about the functionality of Presentation Variables, see part one of this blog series

To add Presentation Variables to the filter expression, click Add More Options and select Presentation Variable from the dropdown.

When a Presentation Variable is added to the filter, two new text boxes appear. The Variable Expr box is where you define the variable to be used and the (default) box is used to add a default value. The default value is optional but, when defining a Presentation Variable within a filter, you have to specify a default value in order to get any results. The reason for this is because, when the report is run, the query issued will use the Presentation Variable placeholder that is defined unless a default value is specified. In other words, the default value will always be used unless the Presentation Variable is populated with a value or a list of values.

Because I want the users to be able to specify a date range, I need to define two Presentation Variables: one for the start date and one for the end date. I can add another place for a Presentation Variable by simply clicking Add More Options again and selecting Presentation Variable.

Now I need to add both my start and end date Presentation Variables in the Variable Expr boxes. I’m going to call my start date presentation variable pv_start_dt and my end date presentation variable pv_end_dt . I am also going to specify a default date range from the beginning of the current month (10/01/2015) to yesterday's date (10/15/2015).

If I click OK, I will be taken back to the Insert Filter screen where I can see the filter expression previously defined.

Clicking OK again will return me to Edit Column Formula which shows the column formula with the filter expression defined in the previous steps.

Now I have to do the exact same thing for the Net Rev $ column. Since the filter expression is identical, I can simply copy and paste the column formula for Gross Rev $ and replace the column name in the expression.

Now I need to take care of the # of Orders column. This column is tricky because of the gap between the 1st and the 2nd or 3rd of every month. I could use a filter expression that defaults to the previous month by using the previous month repository variable I created in a previous step, but this alone wouldn’t switch over when the data became available.

So how can we fulfill the requirement of the report if we don’t know the exact date in which the data will be available? This can be accomplished by using a CASE statement as shown previously in part one of this series. We can break the Case statement down into two parts or two conditions:

1. When the day for the current month is less than or equal to 2 OR if # of Orders is null, then filter # of Orders by Calendar Year Month using the value of the Prev_Month Repository Variable.

2. When condition one is not true, then filter # of Orders by Day Date between the values of the pv_start_date and the pv_end_date Presentation Variables

Putting both conditions together and using the correct syntax for Column Formula results in the following formula:

Note that I am using CURRENT_DATE in my column formula. In this case, I am extracting the day number from the current date by using the extract day function ( DAY(CURRENT_DATE) ). I am going to talk about this in further detail when I talk about using built in functions in Answers to make reports dynamic in part 3 of this series.

There is one problem with this, however. Because of the arithmetic I am using to put current year and current month together, there will be a problem when the month number is 01 for January. The function will subtract 1 from 01 and put the month number at 00 and not 12 of the previous year. I can solve this problem using a CASE statement to switch to another Repository Variable that returns the last month of the previous year when the month is equal to January.

Going back to the Admin Tool, i'm going to create another Repository Variable and call it PREV_YR_LAST_MO

In the initialization string window, I am going to use this select statement:

Going back to my column formula, I need to insert a case statment to switch to my PREV_YR_LAST_MO Repository Variable when MONTH(CURRENT_DATE) = 1 .

Now I need to create my dashboard prompt. I am going to start by clicking on New > Dashboard Prompt.

I need to create two prompts: One for the start date and one for the end date. Because I am using presentation variables as placeholders for the date between values, I have to use a Variable Prompt instead of a Column Prompt. Variable Prompts allow us to define a presentation variable and then define a list of values for the users to select from.

To create a Variable Prompt for Start Date, I can click on the new prompt icon and select Variable Prompt.

There a few things I need to do in order to make this prompt function for the report. First, I have to define the same presentation variable name ( pv_start_dt ) that I used in the filter expressions for the Gross Rev $, Net Rev $ and # of Orders columns.

Because this is not a column prompt, I have to manually specify the values I want the user to be able to select from. Rather than typing in each value, I can use the SQL Results option from the Choice List Values dropdown and use a SQL statement to select the exact values that I want.

This may seem daunting at first but there is a very straightforward way to accomplish this. Rather than manually writing out a SQL query, we can make use of the Advanced Tab within a new report.

I’m going to start by clicking New > Analysis and selecting the column that I want values for: Day Date.

I need to add a filter to Day Date so that it returns only the values I want to user to select from.

Now I need to select the operator to be is between and add two Repository Variables that I have set up: one for the first date of the current month and one for the current date of the current month.

If I go to results, I can see the data returned with the filter I have specified.

As you can see, the Day Date column only contains the values from the first of the month to the current date (October, 16th 2015 in this example)

Now for the good stuff. I can navigate to the Advanced Tab and copy the SQL statement used to generate these values and paste them into the SQL Results text box in my prompt.

You will notice that within the SQL Statement generated by OBI, there are numbers and s_# between the SELECT and Day Date column, after the Day Date column and there is also an order by clause that uses a number “2”. Without going into too much detail, this what OBI uses to make the query more efficient when retrieving results from the database. In order to allow the values to populate the prompt, these have to be removed in OBIEE 12c and the “ORDER BY” clause has to be rewritten in order to make it work.

Changed to this

This can be a bit confusing if you are not very familiar with SQL but just remember:

When populating a prompt using an SQL statement in OBIEE 12c, take out any number and anything that begins with “s_” between the SELECT and first column and anything that begins with “s_” after any subsequent columns and make sure the “ORDER BY” clause contains the actual column name of the column you want to order by.

Note: If you do not require any values to be in order, you can omit the “ORDER BY” clause all together .

If I expand Options in the Edit Prompt window, I can add a default selection or a default value that the prompt will start with. I can use the USMoBeginDate here as well so that the prompt always starts with the first date of every month as the start date.

Note: You will notice that under Options in the Edit Prompt window there is a Variable Data Type option with a dropdown selector. This can be used if the data type needs to be specified to something other than the default which is ‘text’ or character type. If you are getting an error when running the report that says “Selected value does not match datatype. Expected [this value] but got [this value]” you need to change the Variable Data Type to the datatype of the column you are prompting on. In this example, we are prompting a date datatype so therefore it needs to be set to date .

If I click OK, I can check the values in the display window by clicking the dropdown for the Start Date prompt I just created.

The blue checkmark indicates the value that is selected which, because the first date of every month was set by using the USMoBeginDate Repository Variable as the default value, defaults to the first date of the current month (October, 1st 2015) in this example.

Now I need to create another Variable Prompt for the End Date. The SQL statement used for Start Date can be reused for the values as we want the exact same values to be available for selection. I am going to specify the presentation variable to be named pv_end_dt, and the default value to be the USCurDate Repository Variable so that the End Date prompt always defaults to the current date.

Now all that’s left to do is put the prompt and report on the Dashboard. Here is the result.

So that concludes part 2 of Demystification of Variables. Please feel free to ask questions or leave me a comment! In part 3, I am going to talk about using built in front end functions and presentation variables to make reports dynamic for any series of time. Until next time.

Sign up for more like this.

Oracle Business Information

• All Categories
• 12.2K Forums
• User Classes
• 1 Announcements
• Find Partners
• Used Partners
• Hall of Fame: Spotlights
• Hall of Fame: Monthly Highlights
• Hall of Fame: Leaderboard

Filter Analysis based on substring of presentation capricious

I have a speaker varying in one prompt. The variable is a select statement which your picking up a field that remains concatenated so that which user is a clue out what they are picking up. What I'm trying to do is intercept one presentation variable consequently i can do a substring on it and one grab the first two characters.  Can i do this ? In my filter Ive tried doing this OBIEE: Concern with Lecture Unstable

"xxx"."xxxx Code Leveling 1" BY ( LEFT(@{ACCT_TYPE}['@'],2){''}   )

on grab one first two characters of the read, not still it keeps failing. If I removes the "left" function, everything works but I get the full string.

The there random way at done this?

I essentially just wanted to grab the first two characters of the presentation variable.

QUIT isn't applied to every single elements of the variable (if multiple values) pre to add ' ' also add them as options include the IN but it is applied to the final string itself, which means you are breaking it.

To use it using REMAINING you must make your variable single value only and alter your IN into a = .

Gratitude Gianni Ceresa ,

That is very helpful! I'm much nearest to the results I'm looking for, however, is there anyway that I can use anything substring or something similar to get the first two characters for either text while employing which "in" clause. I really appreciate your online. Here is very helpful! IODIN have a question concerning the use of presentation variables: 1) What's the correct syntax for filtering turn one speaker vary is used? You allow a user till selected multiple values in ampere filte...

Aren't you having a "modelling" issue?

Autochthonous reason to concatenating matters in the prompt seems go be "the user has a reference of what they are assort up": why is it likes that?

Can't you use the "Descriptor ID column" in your RPD? So that which human-readable post has the "technical" 2 characters column set as describe ID and you can use this one as filter?

That's how OBIEE specials with technical coding opposite human readable added (knowing that with a database one technical password column works better with indexing and sort at the DB level than long human legibly strings).

404 Not found

404 Not found

Oracle Business Intelligence

• All Categories
• 12.4K Forums
• User Groups
• 1 Announcements
• Find Partners
• For Partners
• Hall of Fame: Spotlights
• Hall of Fame: Monthly Highlights
• Hall of Fame: Leaderboard

OBIEE Presentation Variable Not working in Filter

Respected Members,

Can someone please resolve my issue trying to built MTD report(Date select from prompt):

1- I have Date column "Calendar Date"."Calendar Date" presentation variable @{P_DATE}

2- When I select Date from front end Prompt it should run for all dates in the condition

"Calendar Date"."Calendar Date" BETWEEN TIMESTAMPADD(SQL_TSI_MONTH, 0, TIMESTAMPADD( SQL_TSI_DAY , DAYOFMONTH(date '@{P_DATE}{2016-03-25}') * -(1) + 1,date '@{P_DATE}{2016-03-25}'))

AND date '@{P_DATE}{2016-03-25}'

But unfortunately it gives result of only 1 row of the date which I have selected from PROMPT.

Whereas, if I replace the presentation variable with CURRENT_DATE it gives the correct result.

Please help.

Have you checked the physical SQL generated by the BI Server? Is that generating the correct BETWEEN clause?

Thank you Joel for your reply I am using correct SQL statement. Column prompt variable is not working for this filter whereas a standard presentation variable worked. But I want to integrate Column prompt presentation variable for this.

You maybe misread what Joel was saying: what does the physical SQL looks like?

It must be a valid query as you get something back, but what is it like? Because if the physical query is correct the result can't be wrong (ok, it can if you have selection steps or other things happening at the BI Server level, but you didn't say anything letting us believe something like that could exist).

404 Not found

OBIEE - Date Format in presentation variable, dashboard prompt and logical SQL

This article talk about the manipulation of a presentation variable with a date datatype .

Starting with OBIEE 10.1.3.4.1 and higher versions, Dashboard Prompt input formats and presentation variable values for DATE & DATETIME columns are standardized to YYYY-MM-DD & YYYY-MM-DD HH24:MI:SS

Articles Related

• OBIEE - Date Datatype and Functions
• OBIEE - Cast as date - Which date format to use ?

The big mistake

One big mistake that is made with the date, is that people may confuse between :

• the format of a date
• and the data type of a date.

Why ? Because a lot of database include an implicit datatype transformation from a string into a date. See this example below on Oracle :

Oracle take the string '01-JAN-95' transform it as a date and perform the query.

But what happen if you change the format of the date with the NLS_DATE_FORMAT parameter because you are in a multi-language environment :

You fired an error because Oracle expected an other date format to be able to transform it as a date data type.

To be able to support the localization , you must send to the database not a string but a real value with a date data type. You can do that with the TO_DATE function in Oracle.

Then especially when you work in a multi-language environment, you always must set in a filter not a formatted string but a real value with a date data type. The DATE function of the OBIEE logical Sql have this purpose.

To understand more the difference between the data type and the date format, check out this article : Toad - The date format with null and decode

The date function and its date format

In OBIEE, an equivalent of the function TO_DATE is the DATE function which has this syntax

The date format is unique where :

• YYYY is the Year with 4 numbers
• MM is the Month of year 01, 02…12
• DD is the Day of the month in numbers (i.e. 28)

And you use it with a presentation variable (for instance in a filter ) as

See the paragraph examples below to have more insights

In fact, with Oracle, you will receive :

Understanding the datatype of a presentation variable

Before going further, you have to be sure that you pass the date data type to your presentation variable. See this paragraph which show you how to verify it : understanding the datatype of a presentation variable

The localization and the filter

When you set up a filter on a date, you see a string but in background, Oracle BI Presentation Service see it as a real date data type.

To demonstrate it, below is a little report in a dashboard, the first one with the LOCALE value as English and the second one as French.

In Edit-Box Dashboard prompt

In all language configuration (french, english, …) , if you use a edit-box dashboard prompt, you must use this format :

In a formula

In a filter.

To transform the default value as a date data type, you have to use this statement :

or this one :

of in the advanced Sql (Advanced / Convert this filter in Sql):

Documentation / Reference

• For the date example, Forum Thread with Goran
• epmos/faces/ui/km/DocumentDisplay.jspx