presentation variable syntax in obiee 12c

TIMESTAMPS and Presentation Variables

TIMESTAMPS and Presentation Variables can be some of the most useful tools a report creator can use to invent robust, repeatable reports while maximizing user flexibility.  I intend to transform you into an expert with these functions and by the end of this page you will certainly be able to impress your peers and managers, you may even impress Angus MacGyver.  In this example we will create a report that displays a year over year analysis for any rolling number of periods, by week or month, from any date in time, all determined by the user.  This entire document will only use values from a date and revenue field.

The TIMESTAMP is an invaluable function that allows a user to define report limits based on a moving target. If the goal of your report is to display Month-to-Date, Year-to-Date, rolling month or truly any non-static period in time, the TIMESTAMP function will allow you to get there.  Often users want to know what a report looked like at some previous point in time, to provide that level of flexibility TIMESTAMPS can be used in conjunction with Presentation Variables.

To create robust TIMESTAMP functions you will first need to understand how the TIMESTAMP works. Take the following example:

Here we are saying we want to include all dates greater than or equal to 7 days ago, or from the current date.

• The first argument, SQL_TSI_DAY, defines the T ime S tamp I nterval (TSI) . This means that we will be working with days.
• The second argument determines how many of that interval we will be moving, in this case -7 days.
• The third argument defines the starting point in time, in this example, the current date.

So in the end we have created a functional filter making Date >= 1 week ago, using a TIMESTAMP that subtracts 7 days from today.

Note: it is always a good practice to include a second filter giving an upper limit like "Time"."Date" < CURRENT_DATE. Depending on the data that you are working with you might bring in items you don’t want or put unnecessary strain on the system.

We will now start to build this basic filter into something much more robust and flexible.

To start, when we subtracted 7 days in the filter above, let’s imagine that the goal of the filter was to always include dates >= the first of the month. In this scenario, we can use the DAYOFMONTH() function. This function will return the calendar day of any date. This is useful because we can subtract this amount to give us the first of the month from any date by simply subtracting it from that date and adding 1.

Our new filter would look like this:

For example if today is December 18 th , DAYOFMONTH(CURRENT_DATE) would equal 18. Thus, we would subtract 18 days from CURRENT_DATE, which is December 18 th , and add 1, giving us December 1 st .

(For a list of other similar functions like DAYOFYEAR, WEEKOFYEAR etc. click here .)

To make this even better, instead of using CURRENT_DATE you could use a prompted value with the use of a Presentation Variable (for more on Presentation Variables, click here ). If we call this presentation variable pDate, for prompted date, our filter now looks like this:

A best practice is to use default values with your presentation variables so you can run the queries you are working on from within your analysis. To add a default value all you do is add the value within braces at the end of your variable. We will use CURRENT_DATE as our default, @{pDate}{CURRENT_DATE}.  Will will refer to this filter later as Filter 1.

{Filter 1}:

As you can see, the filter is starting to take shape. Now lets say we are going to always be looking at a date range of the most recent completed 6 months. All we would need to do is create a nested TIMESTAMP function. To do this, we will “wrap” our current TIMESTAMP with another that will subtract 6 months. It will look like this:

Now we have a filter that is greater than or equal to the first day of the month of any given date (default of today) 6 months ago.

To take this one step further, you can even allow the users to determine the amount of months to include in this analysis by making the value of 6 a presentation variable, we will call it “n” with a default of 6, @{n}{6}.  We will refer to the following filter as Filter 2:

{Filter 2}:

For more on how to create a prompt with a range of values by altering a current column, like we want to do to allow users to select a value for n, click here .

Our TIMESTAMP function is now fairly robust and will give us any date greater than or equal to the first day of the month from n months ago from any given date. Now we will see what we just created in action by creating date ranges to allow for a Year over Year analysis for any number of months.

Consider the following filter set:

This appears to be pretty intimidating but if we break it into parts we can start to understand its purpose.

Notice we are using the exact same filters from before (Filter 1 and Filter 2).  What we have done here is filtered on two time periods, separated by the OR statement.

The first date range defines the period as being the most recent complete n months from any given prompted date value, using a presentation variable with a default of today, which we created above.

The second time period, after the OR statement, is the exact same as the first only it has been wrapped in another TIMESTAMP function subtracting 1 year, giving you the exact same time frame for the year prior.

This allows us to create a report that can run a year over year analysis for a rolling n month time frame determined by the user.

A note on nested TIMESTAMPS:

You will always want to create nested TIMESTAMPS with the smallest interval first. Due to syntax, this will always be the furthest to the right. Then you will wrap intervals as necessary. In this case our smallest increment is day, wrapped by month, wrapped by year.

Now we will start with some more advanced tricks:

• Instead of using CURRENT_DATE as your default value, use yesterday since most data are only as current as yesterday.  If you use real time or near real time reporting, using CURRENT_DATE may be how you want to proceed. Using yesterday will be valuable especially when pulling reports on the first day of the month or year, you generally want the entire previous time period rather than the empty beginning of a new one.  So, to implement, wherever you have @{pDate}{CURRENT_DATE} replace it with @{pDate}{TIMESTAMPADD(SQL_TSI_DAY,-1,CURRENT_DATE)}
• Presentation Variables can also be used to determine if you want to display year over year values by month or by week by inserting a variable into your SQL_TSI_MONTH and DAYOFMONTH statements.  Changing MONTH to a presentation variable, SQL_TSI_@{INT}{MONTH} and DAYOF@{INT}{MONTH}, where INT is the name of our variable.  This will require you to create a dummy variable in your prompt to allow users to select either MONTH or WEEK.  You can try something like this: CASE MOD(DAY("Time"."Date"),2) WHEN 0 'WEEK' WHEN 1 THEN 'MONTH' END

In order for our interaction between Month and Week to run smoothly we have to make one more consideration.  If we are to take the date December 1st, 2014 and subtract one year we get December 1st, 2013, however, if we take the first day of this week, Sunday December 14, 2014 and subtract one year we get Saturday December 14, 2014.  In our analysis this will cause an extra partial week to show up for prior years.  To get around this we will add a case statement determining if '@{INT}{MONTH}' = 'Week' THEN subtract 52 weeks from the first of the week ELSE subtract 1 year from the first of the month.

Our final filter set will look like this:

With the use of these filters and some creative dashboarding you can end up with a report that easily allows you to view a year over year analysis from any date in time for any number of periods either by month or by week.

That really got out of hand in a hurry! Surely, this will impress someone at your work, or even Angus MacGyver, if for nothing less than he or she won’t understand it, but hopefully, now you do!

Also, a colleague of mine Spencer McGhin just wrote a similar article on year over year analyses using a different approach. Feel free to review and consider your options.

Calendar Date/Time Functions

These are functions you can use within OBIEE and within TIMESTAMPS to extract the information you need.

• Current_Date
• Current_Time
• Current_TimeStamp
• Day_Of_Quarter
• Month_Of_Quarter
• Quarter_Of_Year
• TimestampAdd
• TimestampDiff
• Week_Of_Quarter
• Week_Of_Year

Back to section

Presentation Variables

The only way you can create variables within the presentation side of OBIEE is with the use of presentation variables. They can only be defined by a report prompt. Any value selected by the prompt will then be sent to any references of that filter throughout the dashboard page.

In the prompt:

From the “Set a variable” dropdown, select “Presentation Variable”. In the textbox below the dropdown, name your variable (named “n” above).

When calling this variable in your report, use the syntax @{n}{default}

If your variable is a string make sure to surround the variable in single quotes: ‘@{CustomerName]{default}’

Also, when using your variable in your report, it is good practice to assign a default value so that you can work with your report before publishing it to a dashboard. For variable n, if we want a default of 6 it would look like this @{n}{6}

Presentation variables can be called in filters, formulas and even text boxes.

Dummy Column Prompt

For situations where you would like users to select a numerical value for a presentation variable, like we do with @{n}{6} above, you can convert something like a date field into values up to 365 by using the function DAYOFYEAR("Time"."Date").

As you can see we are returning the SQL Choice List Values of DAYOFYEAR("Time"."Date") <= 52.  Make sure to include an ORDER BY statement to ensure your values are well sorted.

Back to Section

Sign up for more like this.

Advanced Techniques: Referencing Stored Values in Variables

About session variables, about repository variables, about presentation variables, about request variables, about global variables, creating global variables, syntax for referencing variables.

You can reference stored values in variables in several areas of Oracle BI Enterprise Edition, including in analyses, dashboards, and actions. For example, suppose that you wanted to create an analysis whose title displays the current user's name. You can do this by referencing a variable. You can use fives types of variables: session, repository, presentation, request, and global.

A session variable is a variable that is initialized at login time for each user. When a user begins a session, Oracle BI Enterprise Edition creates a new instance of a session variable and initializes it. There are as many instances of a session variable as there are active sessions on Oracle BI Enterprise Edition. Each instance of a session variable can be initialized to a different value.

There are two types of session variables, as described in the following table.

A repository variable is a variable that has a single value at any point in time. A static repository variable has values that persist and do not change until the administrator changes it. A dynamic repository variable has values that are refreshed by data returned from queries.

A presentation variable is a variable that you can create as part of the process of creating one of the types of dashboard prompts that is described in the following table.

The value of a presentation variable is populated by the column or variable prompt with which it was created. That is, each time a user selects one or more values in the column or variable prompt, the value of the presentation variable is set to the value or values that the user selects.

A request variable enables you to override the value of a session variable but only for the duration of a database request initiated from a column prompt. You can create a request variable as part of the process of creating a column prompt.

A request variable that is created as part of a column prompt is associated with a column, and the values that it can take come from the column values.

To create a request variable as part of a column prompt, in the New Prompt dialog, you must select Request Variable in the Set a variable field. Enter the name of the session variable to override in the Variable Name field.

The value of a request variable is populated by the column prompt with which it was created. That is, each time a user selects a value in the column prompt, the value of the request variable is set to the value that the user selects. The value, however, is in effect only from the time the user presses the Go button for the prompt until the analysis results are returned to the dashboard.

A global variable is a column created by combining a specific data type with a value. The value can be a Date, Date and Time, Number, Text, and Time. The global variable is evaluated at the time the analysis is executed, and the value of the global variable is substituted appropriately. Only users with the BIAdvancedContentAuthor role can manage (add, edit, and delete) global variables.

You create a global value during the process of creating an analysis by using the Edit Column Formula dialog. The global variable is then saved in the catalog and made available to all other analyses within a specific tenant system.

You can save a calculation as a global variable then reuse it in different analyses.

To create a global variable:

Open the analysis for editing.

In the Selected Columns pane, click Options beside the column name.

Select Edit Formula to display the Column Formula tab. You can create a custom header for the global variable by using this tab.

Click Variable and select Global to display the Insert Global Variable dialog.

Click Add New Global Variable to display the New Global Variable dialog.

Enter the value for the Name . For example, gv_region, date_and_time_global_variable, or rev_eastern_region_calc_gv. See Syntax for Referencing Variables for syntax requirements.

"Base Facts"."1- Revenue"*@{global.variables.gv_qualified}

Enter values for the Type and Value .

If you are entering an expression or a calculation as a value, then you must use the Text data type, as in the following example: "Base Facts"."1- Revenue"*3.1415

Click OK . The new global variable is added to the Insert Global Variable dialog.

Select the new global variable that you just created, and click OK . The Edit Column Formula dialog is displayed with the global variable inserted in the Column Formula pane. The Custom Headings check box is automatically selected.

Enter a new name for the column to which you have assigned a global variable to more accurately reflect the variable.

You can reference variables in analyses and dashboards. How you reference a variable depends on the task that you are performing. For tasks where you are presented with fields in a dialog, you must specify only the type and name of the variable (not the full syntax), for example, referencing a variable in a filter definition.

For other tasks, such as referencing a variable in a title view, you specify the variable syntax. The syntax that you use depends on the type of variable as described in the following table.

You can also reference variables in expressions. The guidelines for referencing variables in expressions are described in the following sections.

Session Variables

The guidelines for referencing session variables in expressions are:

Include the session variable as an argument of the VALUEOF function.

Enclose the variable name in double quotes.

Precede the session variable by NQ_SESSION and a period.

Enclose both the NQ_SESSION portion and the session variable name in parentheses.

For example:

"Market"."Region"=VALUEOF(NQ_SESSION."SalesRegion")

Repository Variables

The guidelines for referencing repository variables in expressions are:

Include the repository variable as an argument of the VALUEOF function.

Refer to a static repository variable by name.

Refer to a dynamic repository variable by its fully qualified name.

CASE WHEN "Hour" >= VALUEOF("prime_begin") AND "Hour" < VALUEOF("prime_end") THEN 'Prime Time' WHEN ... ELSE...END

Presentation Variables

When referencing a presentation variable, use this syntax:

@{ variablename }{ defaultvalue }

where variablename is the name of the presentation variable and defaultvalue (optional) is a constant or variable reference indicating a value to be used if the variable referenced by variablename is not populated.

To type-cast (that is, convert) the variable to a string, enclose the entire syntax in single quotes, for example:

'@{user.displayName}'

If the @ sign is not followed by a {, then it is treated as an @ sign. When using a presentation variable that can have multiple values, the syntax differs depending on the column type.

Use the following syntax in SQL for the specified column type in order to generate valid SQL statements:

Text — (@{ variablename }['@']{' defaultvalue '})

Numeric — (@{ variablename }{ defaultvalue })

Date-time — (@{ variablename }{timestamp ' defaultvalue '})

Date (only the date) — (@{ variablename }{date ' defaultvalue '})

Time (only the time) — (@{ variablename }{time ' defaultvalue '})

Scripting on this page enhances content navigation, but does not change the content in any way.

Social Buttons

Monday, May 12, 2014

Obiee 12c or obiee 11g : using multiple value for a presentation variable in criteria.

148 comments

Thank You, very nice tip.

Thanks for Information Flax IT online training began online training for Software program and SAP courses. Online training is the better option to study software products as well as programs, which are SAP modules, like SAP HR, SAP FSCM, SAP BASIS, SAP ABAP, SAP BW, SAP ED ADMIN, SAP SECURITY, SAP SRM, SAP SCM, SAP GRC etc., and Software Courses like JAVA-J2EE, PeopleSoft, Tibco courses, Microsoft Courses, Network and System Admin, Oracle Courses, SAS, Testingtools, PMP, Cognos, AB Initio, ORACLE and Database etc..

This comment has been removed by the author.

Thanks for this useful information. This also worked in prompt SQL. I was using LOCATE function in sql to achieve same.

Thanks for sharing this niche useful informative post to our knowledge, Actually SAP is ERP software that can be used in many companies for their day to day business activities it has great scope in future. Regards, SAP courses in chennai | SAP institutes in chennai | sap training in Chennai | sap course in Chennai

This is my first time visit on your site and i have bookmark this for again visit. thanks a lot of for share a appreciable post .. sas online training

Thank you very much! I had tried a million ways. Cheers

interesting information. This is just the kind of information that i had been looking for, i'm already your rss reader now and i would regularly watch out for the new posts,Thanks a million once again, Regards sap hana training in hyderabad

I really enjoyed while reading this post.The information that you have shared is really great.If anyone interested to do a SAS course then joinfita to choose your better career. Joinfita offers a good training with experienced professionals. Regards, SAS Training chennai

I had recently visited to this site and found it to be very nice. Thanks a lot for the wonderful site. It was very useful for me. I really appreciate the work. SAP Simple Finance Online Training

Truly an awesome post.Keep up your astonishing work and continue sharing. Hadoop Training in Chennai

I am not sure that where you are getting this ideas.I have read it fully and it is really awesome. Thanks for your wonderful information. ccna Training in Chennai | ccna courses in Chennai | ccna institutes in Velachery

Thanks for sharing your creative ideas.It is really helpful and informative.Keep sharing more with efficient new like this. SAS Training in Chennai | SAS Course in Chennai

A debt of gratitude is in order for taking an ideal opportunity to talk about this, I feel upbeat about it and I adore adapting more about this theme. I utilize your manual for teach my understudies. DOTNET Training in Chennai | DOTNET course in Chennai | .net training Chennai | .net course in Chennai

I saw your site its really good and appreciated.. I got more information from your site. Thanks a lot and Keep on Updating. sap hybris online training

Was a perfect solution for my issue Thank you so much

Much obliged to you for requiring significant investment to give us a portion of the valuable and restrictive data with us. Regards, ccna Training in Chennai | ccna courses in Velachery | ccna institutes in Velachery

thanks for this information sap hana is the mighty field of sap module and also this is the field where sap consultant are getting high salary package . It is a revolutionary platform that’s best suited for performing real-time analytics, and we also provide sap training in bangalore if any one wants training in sap hana then just click here and get detail sap training institutes in bangalore and sap hana training in bangalore

Amazing records, i and allow me to assist you to apprehend, your internet website online gives the top notch and the maximum interesting data. that is virtually the form of records that i have been seeking out, i am already your reader now and i'd regularly be careful with the ultra-current posts, all over again hats off to you! thanks a ton once more, regards, oracle fusion procurement on line schooling some of the oracle fusion procurement in hyderabad. test room education in hyderabad india thank for sharing oracle fusion procurement online training oracle fusion procurement training

Excellent post! The strategy you have posted on this technology helped me to get into the next level and had lot of information in it. SAP Simple Logistic Training in Bangalore

oracle financials sector having huge openings for finance background students. here we have oracle fusion hcm and oracle fusion financials online training institute for all the Indian and foreigner students. we have 10+ years of experience in handling oracle technology related projects. we have proven skill in online training and giving job assistance after the training. for more information please visit: Oracle Fusion HCM Training

You post explain everything in detail and it was very interesting to read. Thank you. Web design institute chennai

CALFRE handles oracle fusion financials online training and its modules maintaining classroom based training with the self-paced videos. An expert having ten plus years of self-experience handles the training period through online and explains each and every point perfectly. We recently launched our institute in the USA and getting the best reputation over there. Oracle fusion Financials Training in hyderabad Oracle Fusion Financials online Training in hyderabad

Wow...What an excellent informative blog, really helpful. Thank you so much for sharing such a wonderful post with us.keep updating.. AWS Certifications in Chennai | AWS Exam Centers in Chennai | AWS Certification Exams in Velachery | AWS Exams in Velachery | AWS Online Exam Center in Velachery

It is really very awesome and wonderful to visit your site.Thanks for sharing your informative blog with us.keep updating such a wonderful post.. MicroSoft Azure Certification in Chennai | Azure Exam Centers in Velachery | Azure Exam Centers in Madipakkam

Excellent Post! Thank you so much for sharing this pretty post, it was so good to read and useful to improve my knowledge. Embedded System Training in Chennai | Embedded Training in Velachery | Embedded Courses in Pallikaranai

This is useful post for me. I learn lot of new information from your article. keep sharing. thank you for share us. MCSE Training Institute in Chennai | MCSE Training in Velachery | MCSE Training Center in Chrompet

Thanks for giving nice information from your blog...It's really an amazing post.. Selenium Training Institute in Chennai | Selenium Training Center in Velachery

Thanks for your informative article. Your post helped me to understand the future and career prospects. Keep on updating your blog with such awesome article. PCB Designing Training Institute in Chennai | PCB Training in Velachery

Really nice post. Thank you for sharing your amazing information and informative article,its really useful for us.keep updating such a wonderful blog.. Embedded Training Institute in Chennai | Embedded Training Center in Velachery

Very informative and interesting blog, it was so good to read and useful to improve my knowledge as updated one,keep updating..This Concepts is very nice Thanks for sharing.. Selenium Training Institute in Chennai | Selenium Training Center in Velachery | Selenium Courses in T.Nagar

Amazing blog. Thank you for sharing. The information you shared is very effective for learners I have got some important suggestions from it.. Blue Prism Training Institute in Chennai | Blue prism Certification Training in Velachery | Blue Prism Training Center in Adyar

Pretty article! I found some useful information in your blog, it was amazing to read, thanks for sharing this great content to my vision... Embedded Training Institute in Chennai | Embedded Training in Velachery | Embedded Certification Training in Velachery

Amazing blog. Thank you for sharing. The information you shared is very effective for learners I have got some important suggestions from it.. Embedded System Training Institute in Chennai | Embedded Training in Velachery | Embedded Courses in T.nagar

Great post.Thanks for one marvelous posting! I enjoyed reading it;The information was very useful.Keep the good work going on!! Tally Training Institute in Chennai | Tally Training in Velachery | Best Tally Courses in Guindy | Tally Training Center in Pallikaranai

I am reading your post from the beginning,it was so interesting to read & I feel thanks to you for posting such a good blog,keep updates regularly.. Web Designing and Development Training in Chennai | Web Designing Training Center in Velachery | Web Design Courses in Pallikaranai

Awesome post.. Really you are done a wonderful job.thank for sharing such a wonderful information with us..please keep on updating.. PCB Designing Training Institute in Chennai | PCB Training Center in Velachery | PCB Design Courses in Thiruvanmiyur

Thanks for making me this Blog. You have done a great job by sharing this content here.Keep writing blog this like. MatLab Training Institute in Chennai | MatLab Training Center in Velachery | MatLab Courses in Tambaram

Pretty blog, so many ideas in a single site, thanks for the informative article, keep updating more article. Software Testing Training Institute in Chennai | Software Testing Training Institutes in Velachery

I am really enjoying reading your well-written articles. It looks like you spend a lot of effort and time on your blog.. MCSE Certification Training Institute in Chennai | MCSE Training Center in Velachery

MBA Project Center in Chennai | MBA Project Center in Velachery | MBA HR Projects in Pallikaranai | MBA Finance Projects in Taramani

Thanks for Sharing the valuable information and thanks for sharing the wonderful article.. Embedded Training Institute in Chennai | Embedded Training Center in Velachery | Embedded Courses in Pallikaranai

Your post is very nice .this post is very useful to us… Selinium Course Training In Chennai | Selinium Course TrainingIn Velachery | Selinium Course TrainingIn Tambaram

Pretty blog, so many ideas in a single site, thanks for the informative article, keep updating more article. Oracle Training Institute in Chennai | Oracle Certification Training in Velachery | Oracle Courses in Pallikaranai

You Posted a Nice post This post is very useful and Easy To Understand .. Cloud Computing Training in Chennai | Cloud Computing Training in Chennai | Cloud Computing Training in Chennai .

Really Very happy to see this blog. thanks for sharing such a amazing blog... Mobile Computing Project Center in Chennai | Mobile Computing Projects in Velachery | Mobile Computing Projects in Medavakkam | Mobile Computing Projects in Pallikaranai

Nice and interesting blog to read..... keep updating Android Project Center in Chennai | Android Project Center in Velachery | Android Projects for BE in Velachery | Android Projects for ME in Chennai

As we know there are many companies which are converting into Google cloud big data services. with the right direction we can definitely predict the future.

Pretty article! I found some useful information in your blog, it was awesome to read, thanks for sharing this great content to my vision... Java Project Center in Chennai | Java Project Center in Velachery | Java Projects in Velachery

Really nice and good post. Thank you for sharing amazing information. PHP Project Center in Chennai | PHP Project Center in Velachery | PHP Projects in Velachery

Good Post! Thank you so much for sharing this pretty post, it was so good to read and useful to improve my knowledge. VLSI Project Center in Chennai | VLSI Project Center in Velachery | VLSI Projects in Pallikaranai | VLSI Projects in Guindy | VLSI Projects in Taramani

Really Very happy to see this blog. thanks for sharing such a amazing blog... Final Year Project Center in Chennai | Final Year Projects in Velachery

This is really very impressive article with useful content,thanks for sharing your amazing post. MatLab Project Center in Chennai | MatLab Project Center in Velachery | MatLab projects in Perungudi

Nice and interesting article to read..... keep updating MBA Project Center in Chennai | MBA Project Center in Velachery | MBA Projects in Velachery | MBA Projects in Taramani

Excellent blog with lovely information. really very useful article for us thanks for sharing such a wonderful blog... Final Year Project Center in Chennai | Final Year Projects in Velachery

I read this article. I think You put a lot of effort to create this article. I appreciate your work. Embedded System Training Institute in Chennai | Embedded Training Center in Velachery | Embedded Training in Guindy

I wanted to thank you for this great read!! I definitely enjoying every little bit of it I have you bookmarked to check out new stuff you post.is article. Thanks for sharing nice article Salesforce Training | Online Course | Certification in chennai | Salesforce Training | Online Course | Certification in bangalore | Salesforce Training | Online Course | Certification in hyderabad | Salesforce Training | Online Course | Certification in pune

The first point is that ML algorithms can assist people by helping them to find patterns or dependencies, which are not visible by a human. machine learning and ai courses in hyderabad

Great article Glad to find your blog. Thanks for sharing. PCB Design Training Institute in Chennai | PCB Training Center in Velachery | PCB Design Course in Velachery

trung tâm tư vấn du học canada vnsava công ty tư vấn du học canada vnsava trung tâm tư vấn du học canada vnsava uy tín công ty tư vấn du học canada vnsava uy tín trung tâm tư vấn du học canada vnsava tại tphcm công ty tư vấn du học canada vnsava tại tphcm điều kiện du học canada vnsava chi phí du học canada vnsava #vnsava @vnsava

It is very awesome and wonderful to visit your site.Thanks for sharing this information,this is helpful to me a lot... Java Training Institute in Chennai | java Training Center in Velachery | Java Training in Velachery | Online Training Institute in Velachery

Awesome Blog with Smart Content, Thanks for sharing such a nice blog.. Embedded System Training in Chennai | Embedded Training Center in Velachery | Online Training Institute in Velachery

This post is really nice and informative. The explanation given is really comprehensive and informative.. CCNA Training Institute in Chennai | CCNA Training Center in Velachery | CCNA Training Courses in Chennai | CCNA Training in Velachery | CCNA Online Training in Velachery

Thanks for giving nice information from your blog...It's really an amazing post... Tally Training Institute in Chennai | Tally Training Center in Velachery | Tally Training with GST Training in Velachery | Online Training Center in Velachery

Amazing Blog with Smart Content, Thanks for sharing such a nice blog.. Embedded Training Center in Chennai | Embedded System Training in Velachery | Embedded System Courses in Velachery

Brilliant article. The information I have been searching precisely. It helped me a lot, thanks. Keep coming with more such informative article. C and C++ Training Institute in Chennai | C and C++ Training Center in Velachery | C & C++ Training in Velachery | Online Training in Velachery

Excellent post... Thank you for sharing such a informative and information blog with us.keep updating such a wonderful post.. MicorSoft Azure Training Institute in Chennai | Azure Training Center in Chennai | Azure Certification Training in velachery | Online Azure training in Velachery

Excellent information with unique content and it is very useful to know about the information based on blogs... ISTQB Certification Course in Chennai | ISTQB Certification Course in Tharamani

Excellent article.It is really very helpful for us.keep sharing such a amazing post DOT NET Training Institute in Chennai | online DOT NET training | DOT NET Training Center in Velachery

Amazing article. Thanks for sharing such a excellent blog.it is very useful for us. PCB Training Institute in Velachery | PCB online training | PCB offline training

Really excellent blog.It is very useful for us. Thaks for for such amazing blog. Keep sharing such excellent blog. PMP Certification Center in Chennai | PMP Certification Online Training | PMP Certification Offline Training

Nice information .It is very useful for all.keeping sharing such excellent blogs.It is useful for us. JAVA Training Institute in Chennai | JAVA Online Training Institute in Chennai | JAVA Training Offline Institute in Chennai

Really amazing informative blog.Excellent blog with unique content.It is very useful for us.Thanks for sharing such a wonderful blog. C and C++ Training Institute in Chennai | C and C++ Online Training Institute in Chennai | C and C++ Offline Training Institute in Chennai

Great post! I am actually getting ready to across this information, is very helpful my friend. Also great blog here with all of the valuable information you have. Keep up the good work you are doing here. business analytics course

Liên hệ Aivivu, đặt vé máy bay tham khảo lịch bay singapore hà nội vé máy bay từ quy nhơn vào sài gòn vé bay huế hà nội vé máy bay huế đà lạt vé máy bay đi huế giá rẻ

Nice and good article. It is very useful for me to learn and understand easily. Thanks for sharing your valuable information and time. Please keep updating... CCNA Exam Center in Chennai and Velachery | AWS Training Center in Chennai & Velachery | Java Training center in velachery | Microsoft Azure Training Center in Chennai & Velachery | Best ISTQB Exam center in velachery |

Such a cute blog. Thank you for blogging. Keep adding more blogs. Very nicely presented. CCNA Exam Center in Chennai and Velachery | AWS Training Center in Chennai & Velachery | Java Training center in velachery | Microsoft Azure Training Center in Chennai & Velachery | Best ISTQB Exam center in velachery |

Thank you so much for sharing this worth able content with us. Keep blogging article like this. Python Training in Chennai and Velachery | AWS Training Center in Chennai & Velachery | Java Training center in velachery | Microsoft Azure Training Center in Chennai & Velachery | Best ISTQB Exam center in velachery |

Pretty article! I found some useful information in your blog, it was awesome to read, thanks for sharing this great content to my vision, keep sharing. GRE Test Center in Chennai and Velachery | Software Testing Training Center in Chennai and Velachery | CCNA Training Center in Chennai & Velachery | Java Training center in velachery | Microsoft Azure Training Center in Chennai & Velachery | Best ISTQB Exam center in velachery |

Awesome blog. Your articles really impressed for me, because of all information so nice and unique... IELTS Test Center in Chennai and Velachery | Software Testing Training Center in Chennai and Velachery | CCNA Training Center in Chennai & Velachery | Java Training center in velachery | Microsoft Azure Training Center in Chennai & Velachery | Best ISTQB Exam center in velachery |

It is a one of the great Explanation, which is very essential for me as well. Web Designing and Development Training in velachery | Dot Net Trainig in Chennai and Velachery | Python Training Center in Chennai and Velachery | CCNA Training Center in Chennai & Velachery Java Training center in velachery | Microsoft Azure Training Center in Chennai & Velachery | Best ISTQB Exam center in velachery |

Great article, your blog was really unique... thanks for sharing… Selenium Training Center in Velachery | Tally Training center in velachery | Software Testing Training Center in Velachery | Web Designing and Development Training in velachery | Dot Net Trainig in Velachery | Python Training Center in Velachery | Java Training center in velachery |

Wonderful Article. Thank you for updating such an informative content. Python Training Center in Velachery | Java Training center in velachery | Selenium Training Center in Velachery | Tally Training center in velachery | Software Testing Training Center in Velachery | Web Designing and Development Training in velachery | Dot Net Trainig in Velachery |

Nice Post. Thanks for sharing. Keep on updating. Software Testing Training Center in Velachery | Web Designing and Development Training in velachery | Dot Net Trainig in Velachery | Python Training Center in Velachery | Java Training center in velachery | Selenium Training Center in Velachery | Tally Training center in velachery |

Good Post! Thank you so much for sharing this pretty post, it was so good to read and useful to improve my knowledge. Java Training Institute in Chennai | Java Training Institute in Velachery

This blog is really useful and it is very interesting thanks for sharing, it is really good and exclusive. Tally Training center in velachery | Software Testing Training Center in Velachery | Web Designing and Development Training in velachery | Dot Net Trainig in Velachery | Python Training Center in Velachery | Java Training center in velachery | Selenium Training Center in Velachery |

Thanks for sharing such a wonderful blog here... Dot Net Trainig in Velachery | Python Training Center in Velachery | Java Training center in velachery | Selenium Training Center in Velachery | Tally Training center in velachery | Software Testing Training Center in Velachery | Web Designing and Development Training in velachery |

Impressive blog with lovely information. Really very useful article for us thanks for sharing such a wonderful blog... Python Training Center in Velachery | Java Training center in velachery | Selenium Training Center in Velachery | Tally Training center in velachery | Software Testing Training Center in Velachery | Web Designing and Development Training in velachery | Dot Net Trainig in Velachery |

Awesome post. Really you are shared very informative concept... Thank you for sharing. Keep on updating... JAVA Training in Chennai | Summer Courses Training in Chennai | Software Testing Training in Chennai | ISTQB Training in Chennai | Selenium Automation Training in Chennai |

Your Blog is really an amazing content to read, its very useful for everyone. thanks for sharing such an wonderful post.. PMP Exam Center in Chennai | PMP Certification in Velachery | PMP Exams in Velachery | Online Certification in Chennai

Awesome article and Unique Words. Thanks for sharing such nice article……… IELTS Exams in Velachery | IELTS Exams in Chennai | IELTS Exams in Tharamani | IELTS Exams in Perungudi | IELTS Exams in Thambaram

This Article is really helpful for me. I like it. Thanks for sharing. Hardware and Networking Training in Chennai | AWS Training in Chennai | Web Designing Training Center in Chennai | Python Training in Chennai | Tally Training in Chennai | Dot Net Training in Chennai |

The Blog Content was Awesome !Thanks for sharing this unique blog keep posting more….. Python Training in Chennai | Tally Training in Chennai | Dot Net Training in Chennai | Hardware and Networking Training in Chennai | AWS Training in Chennai | Web Designing Training Center in Chennai |

Wonderful information and really very much useful. Thanks for sharing and keep updating More like this……... AWS Training in Chennai | Python Training in Chennai | Tally Training in Chennai | Dot Net Training in Chennai | Hardware and Networking Training in Chennai | Web Designing Training Center in Chennai

excellent blog.... PMP Exam Center in Chennai | PMP Exam Center in Velachery | PMP Exam Center in medavakkam

Wow such an Amazing Blog!we need more info like this so we can learn something new. PCB Training Institute In Chennai. | PCB Training Institute In velachery. | PCB Training Institute In medavakkam | PCBTraining Institute Pallikaranai. | PCB Training Institute In Tharamani. | PCB Training Institute In Thiruvanmiyur. | PCB Training Institute In perungudi. | PCB Training Institute In Thambaram. .

Excellent post.. Thank you so much for sharing your nice blog with useful content with us.... ITIL Exam Center in Chennai | ITIL Exams in Velachery | ITIL Exam Center in Velachery | ITIL Exams in Perungudi | Online Certification in Chennai

Valuable information shared with us . Thanks for your excellent information. AWS Training Institute in Chennai | AWS online Training Institute in Velachery | AWS offline Training Institute in velachery | AWS Training Institute in Tharamani | AWS Training Institute in Medavakkam

Excellent blog with valuable information.. MCSA Training Institute in Chennai | MCSA Training Institute in Velachery | MCSA Training Institute in Medavakkam | MCSA Training Institute in Tharamani

This is useful post for me. I learn lot of new information from your post. keep sharing. thank you for share us... GGRE Test Center in Chennai | GRE Test Center in Velachery | GRE Test in Chennai | Online GRE Exams in Velachery | Online GRE Certification in Velachery

unique content and it is very useful to know about the information based on blogs... Python Training in Tharamani | CCNA Training in chennai | Dot Net Training in thambaram | Hardware and Networking Training in perungudi | Web Designing Training Center in guindy AWS Training in Velachery |

Good information and really its very much useful Blog . Thanks for sharing and keep updating like this ……. IELTS Exams in Chennai | IELTS Exams in Velachery | IELTS Exams in Tharamani | IELTS Exams in Perungudi | IELTS Exams in Thambaram

Very informative blog. Helps to gain knowledge about new concepts and techniques. Thanks a lot for sharing this wonderful blog.keep updating such a excellent post with us. PMP Exam Center in Chennai | PMP Certification in Velachery | PMP Online Certification in Chennai | PMP Exams in Chennai | PMP Exams in Chennai

Really very useful article for us thanks for sharing such a wonderful blog... Data Science Training in Chennai | Data Science Training in Velachery | Data Science Training in Tharamani | Data Science Training in Perungudi | Data Science Training in Thambaram

Good Niche ! Thanks for sharing such a wonderful blog here... AWS Training in Velachery | Hardware and Networking Training in Velachery | Web Designing Training Center in Velachery JAVA Training in Velachery | CCNA Training in Velachery | CCNP Training in Velachery | Python Training in Velachery |

I am reading your post from the beginning, it was so interesting to read & I feel thanks to you for posting such a good blog, keep updates regularly. Linux Training Institute in Velachery | Linux Training Institute in Chennai | Linux Training Institute in Tambaram | Linux Training Institute in Taramani | Linux Training Institute in Medavakkam

Nice post.. Its really an amazing with informative information and useful for everyone. Thanks for sharing your wonderful article.. AWS Certification in Chennai | AWS Exam Center in Chennai | AWS Exams in Velacheri | AWS Online Exams in Velachery | Online Certification in Chennai

Excellent information. Thanks for sharing such a great blog with us. Linux Training Institute in Chennai | Linux Training Institute in Tharamani | Linux Training Institute in Medavakkam | Linux Training Institute in Velachery

Awesome article you have shared, thank you so much for posting such a useful information.. PCB Design Training Institute in Chennai | PCB Designing Training Center in Velachery | Online PCB Design Courses in Chennai | PCB Courses in Velachery

Wonderful Article. Thank you for updating such an informative content. Linux Training in chennai | Dot Net Training in chennai | AWS Training in chennai | Certified Ethical Hacking Training in Chennai | Hardware and Networking Training in chennai | Python Training in chennai | Web Designing Training Center in chennai

Thanks for giving nice information from your blog...It's really an amazing post... ISTQB Certification in Chennai | OISTQB Exam Center in Velachery | ISTQB Certification in Velachery | Online Certification in Chennai

“Superb!” Very Creative.... You have done really great job. It’s really useful to everyone .Thanks for share MCSA Training Institute in Velachery | MCSA Training Institute in Chennai | MCSA Training Institute in Tambaram | MCSA Training Institute in Taramani |

I have read your blog, it's really very attractive and impressive. I like your content. Thanks for sharing such amazing information with us... Software Testing Training Institute In Chennai. | Software Testing Android Training Institute In velachery. | Software Testing Training Institute In medavakkam | Software TestingTraining Institute Pallikaranai. | Software Testing Training Institute In Tharamani. | Software Testing Training Institute In Thiruvanmiyur. | Software Testing Training Institute In perungudi. | Software Testing Training Institute In Thambaram. .

The Information that is shared is very nice and interesting. Linux Training Institute in Chennai | Linux Training Institute in Tharamani | Linux Training Institute in Medavakkam | Linux Training Institute in Velachery

I have read your blog, its really very attractive and impressive. I like your content. Thanks for sharing such an amazing information with us... CCNA Training Institute In Chennai. | CCNA Training Institute In velachery. | CCNA Training Institute In medavakkam | CCNA Training Institute Pallikaranai. | CCNATraining Institute In Tharamani. | CCNA Training Institute In Thiruvanmiyur. | CCNA Training Institute In perungudi. | CCNA Training Institute In Thambaram. .

Nice post. It was really effective. Thank you for sharing. AWS Training in Chennai & velachery | Certified Ethical Hacking Training in Chennai & velachery | Linux Training in Chennai & velachery | Hardware and Networking Training in Chennai & velachery | JAVA Training in Chennai & velachery | Python Training in Chennai & velachery | Dot Net Training in Chennai & velachery | Web Designing Training Center in Chennai & velachery |

Amazing Information my sincere thanks for sharing this post Please Continue to share this kind of blog.. Final Year Project Center in Chennai | Final Year Projects in Velachery | Final Year IEEE Projects in Velachery | Online Project Center in Chennai | BE Projects in Chennai | ME Projects in Velachery

Excellent blog with excellent content... PCB Design Training in Chennai | PCB Design Training in perungudi | PCB Design Training in tharamani | PCB Design Training in velachery

Excellent content with useful information. I am looking forward for your future posts. Keep up the Good work. CCNA & CCNP Training Institute in Velachery | CCNA & CCNP Training Institute in Chennai | CCNA & CCNP Training Institute in Tambaram |

very interesting article... Embedded system Training Institute in Chennai | Embedded system Training Institute in Chennai | Embedded system Training Institute in Medavakkam | Embedded system Training Institute in perungudi | Embedded system Training Institute in Velachery

Pretty article! I found some useful information in your blog, it was awesome to read, thanks for sharing this great content to my vision, keep sharing. AWS Training in Chennai & velachery | Certified Ethical Hacking Training in Chennai & velachery | Linux Training in Chennai & velachery | Hardware and Networking Training in Chennai & velachery | Dot Net Training in Chennai & velachery | Web Designing Training Center in Chennai & velachery | JAVA Training in Chennai & velachery | Python Training in Chennai & velachery |

AlltechZSolution in Chennai provides Microsoft Azure Training supplies the advanced stage course equal to the modern IT Industry. Nowadays Microsoft Azure is one of the emerging guides wherein students and specialists are taking on. Microsoft Azure is the business enterprise's public cloud computing carrier that has millions of users globally. It provides exceptional offerings in terms of networking, garage, and analysis. The Scope and opportunities for Microsoft Azure certified aspirants have dramatically increased within the modern-day years, owing to the growing demand through large corporate agencies. It growing the competencies inside the aspirants to transport an existing. Each online and offline training is to be had .NET ASP MVC software to Azure along with its functionality. The rookies will get maintain of the methods to optimize the software program improvement life cycle.

Really Very amazing blog with useful information... Thanks for sharing such a nice post.. Final Year Project Center in Chennai | Final Year Projects in Velachery

Really nice blog.. Thanks for sharing.. GMAT Test Center in Chennai | GMAT Test Center in velachery

Nice Blog. Thank you for Sharing.. Python Training Institute in Chennai | python Training in Velachery

Interesting post. This is really helpful for me. I like it. Thanks for sharing. JAVA Training in Chennai | Python Training in Chennai | AWS Training in Chennai | Certified Ethical Hacking Training in Chennai | Linux Training in Chennai | Hardware and Networking Training in Chennai | Dot Net Training in Chennai | Web Designing Training Center in Chennai |

Nice post... Really you are done a wonderful job. Thanks for sharing such wonderful information with us. Please keep on updating... Advanced & Core JAVA Training in Chennai | Python Training in Chennai | AWS Training in Chennai | Certified Ethical Hacking Training in Chennai |

Impressive blog with lovely information. Really very useful article for us thanks for sharing such a wonderful blog... Advanced & Core JAVA Training in Chennai | Python Training in Chennai | AWS Training in Chennai | Certified Ethical Hacking Training in Velachery | Linux Training in Chennai | Hardware and Networking Training in Chennai | Dot Net Training in Chennai | Web Designing Training Center in Chennai |

Nice post. It was really effective. Thank you for sharing. Certified Ethical Hacking Training in Velachery | Linux Training in Velachery | Advanced & Core JAVA Training in Velachery | Python Training in Velachery | Hardware and Networking Training in Velachery | Dot Net Training in Velachery | Web Designing Training Center in Velachery | AWS Training in Velachery |

Thank you so much for sharing this worth able content with us. The Niche taken here will be useful for my future programs and I will surely implement them in my study. Hardware and Networking Training in Chennai and Velachery | Dot Net Training in Chennai and Velachery | Web Designing Training Center in Chennai and Velachery | Certified Ethical Hacking Training in Chennai and Velachery | Linux Training in Chennai and Velachery | Advanced & Core JAVA Training in Chennai and Velachery | Python Training in Chennai and Velachery |

Thank you so much for sharing this worth able content with us. Keep blogging article like this. Advanced & Core JAVA Training in Velachery | Python Training in Velachery | Hardware and Networking Training in Velachery | Dot Net Training in Velachery | Web Designing Training Center in Velachery | Certified Ethical Hacking Training in Velachery | Linux Training in Velachery | AWS Training in Velachery |

This post is very simple to read and appreciate without leaving any details out. Great work! data scientist course in aurangabad

Interesting post. This is really helpful for me as a career Growth. Thanks for sharing…… Certified Ethical Hacking Training in Guindy | Linux Training in Guindy | AWS Training in Guindy | Advanced & Core JAVA Training in Guindy | Python Training in Guindy | Hardware and Networking Training in Guindy | Dot Net Training in Guindy | Web Designing Training Center in Guindy |

Wow!!... Superb blog with wonderful information.. PCB Design Training Institute in Chennai | PCB Design Training Center in Velachery

very excellent blog . . . AWS Training Institute in Chennai | CCNA Training Institute in Chennai | Linux Training Institute in Chennai | CCNA Training Institute in Velachery

ShiftEasy is India's end to end Real Estate Property Management Platform. Find the Best Property in India , UK & Abroad. Buy/Sell/Rent/Lease your Property with Shifteasy becomes more easy. Best Residential Property in India

Excellent goods from you, man. I’ve remember your stuff prior to and you’re just extremely fantastic. I really like what you have got right here, certainly like what you are stating and the way in which by which you assert it. You’re making it entertaining and you still take care of to keep it sensible. I cant wait to read much more from you. That is really a wonderful site.

A good blog always comes up with new and exciting information and while reading I have felt that this blog really has all those quality that qualify a blog to be one.Kindly take time to check my Blogs too which is about SEO Questions and Answers

excellent blog with informative content... Selenium Training Institute in Chennai | Selenium Training Institute in Chennai | Selenium Training Institute in Medavakkam | Selenium Training Institute in Tharamani

Thanks for this awesome blog post....Its very useful and interesting blog articles ......We provide projects for final year matlab projects chennai

Nik Collection by DxO Full Crack Add the power of the Nik Collection by DxO to your workflow today and create stunning images faster. The most . Nik Collection By Dxo V4 30 0 X64

Thank you so much for sharing this information. Do visit free internship in chennai

Such a great information, Graphic designing course in Chandigarh Tally training in Chandigarh

AllTechZ is the best Search Engine Optimization Training Course in Chennai a term that suggests the development of goodness Web composition upgrade is the demonstration of additional fostering a webpage's arranging in unique web search devices like a Nation individual, Google, Bing, and others picked various watchwords or statutes nearby to the webpage's work or wonderful. Its extra benefits incorporate favored control of districts over updating site traffic and search arranging. We have recorded amazing and first-rate Site improvement arranging foundations in Chennai with every one of the normal subtleties like Course Stake, Plan, Staff, and extensively less. Both are online classes and offline classes available. Presently Alltechz Guides Situation helps moreover.

AllTechZ offered Embedded Training In Chennai. The Embedded Readiness in Chennai at AllTechZ first familiarizes the understudies with the pieces of Embedded System Programming and its headway cycle. The Expert Embedded Mentors at AllTechZ get you acquainted with the Memory System, Cycle Plan, Microcontroller Peripherals, and significant level upgrades and use of the compilers persistently with license. Close to the completion of the Embedded Arrangement in Chennai at AllTechZ, you will end up being more familiar with Embedded System Development, its Applications, and the capacities that are supposed to work as an Introduced Subject matter expert. Online Classes are available. Weekdays and weekend Classes are available.

AllTechZ Offered Photoshop Training In Chennai. The Photoshop Classes in Chennai at AllTechZ is a specialist program that engages even the fledgling to foster their image-making capacities further and make them functional with Photo changing abilities, for instance, adjusting, modifying, various reviews, building covering, and further developing pictures. Around the completion of Photoshop Getting ready in Chennai at AllTechZ, you will sort out some way to make clear, assortment, layers, and thing changes unusually and basically under the mentorship of Master Photoshop experts progressively. Online Classes are Available. Weekdays and Weekend Classes Are Available.

AllTechZ Offered Advanced Excel Training In Chennai. Join Advanced Succeed Getting ready in Chennai at AllTechZ and value the Microsoft Succeed Office suite from its basics to the significant level under the course of Fitness. Succeed Getting ready Framework at AllTechZ enables you to proficiently research the data on any level. It sets you up to use computation sheets with capacities, conditions, and charts, perform data mashups, concentrate or pull the information, envision the eliminated data, turn tables, turn diagrams, test, and Prevail in VBA under the mentorship of asserted specialists.Online Classes Are Available. Weekdays And Weekend Classes are Available

Total Visits

Feature video.

• Conversion Functions OBIEE 12c Conversion Functions OBIEE Conversion Functions The conversion functions convert a value from one form to another.  ...

Join the Club

Oracle Business Intelligence

• Privacy Policy

Tuesday, April 10, 2012

Using presentation variable in obiee.

No comments:

Post a comment.

Thanks for your comment.

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

• Aggregate Tables
• DataBase Schema
• Fusion Middleware
• New Features
• OBIEE Admin
• OBIEE Developer
• Performance
• Report Creation
• Repository Upload Issue
• Rpd Download Issue

23 February 2012

Using variables in obiee.

• Conversion Functions OBIEE Conversion Functions The conversion functions convert a value from one form to another. You can also use the VALUEOF functio...
• Using Variables in OBIEE You can reference variables in several areas of Oracle BI Enterprise Edition, including in analyses, dashboards, KPIs, actions, age...
• Database (Evaluate) Function Database Functions ( EVALUATE ) Users and administrators can create requests by directly calling database functions from either Oracle...

Blog Archive

• March 2020 (1)
• July 2019 (1)
• December 2016 (1)
• April 2015 (2)
• December 2014 (1)
• November 2014 (1)
• January 2013 (1)
• March 2012 (7)
• February 2012 (2)

About Blogger

• OBIEE Tutorial
• OBIEE - Home
• OBIEE - Data Warehouse
• OBIEE - Dimensional Modeling
• OBIEE - Schema
• OBIEE - Basics
• OBIEE - Components
• OBIEE - Architecture
• OBIEE - Repositories
• OBIEE - Business Layer
• OBIEE - Presentation Layer
• OBIEE - Testing Repository
• OBIEE - Multiple Logical Table
• OBIEE - Calculation Measures
• OBIEE - Dimension Hierarchies
• OBIEE - Level-Based Measures
• OBIEE - Aggregates
• OBIEE - Variables
• OBIEE - Dashboards
• OBIEE - Filters
• OBIEE - Views
• OBIEE - Prompts
• OBIEE - Security
• OBIEE - Administration
• OBIEE Useful Resources
• OBIEE - Questions Answers
• OBIEE - Quick Guide
• OBIEE - Useful Resources
• OBIEE - Discussion
• Selected Reading
• UPSC IAS Exams Notes
• Developer's Best Practices
• Questions and Answers
• Effective Resume Writing
• HR Interview Questions
• Computer Glossary

OBIEE – Variables

In OBIEE, there are two types of variables that are commonly used −

• Repository variables
• Session variables

Apart from this you can also define Presentation and Request variables.

Repository Variables

A Repository variable has a single value at any point of time. Repository variables are defined using Oracle BI Administration tool. Repository variables can be used in place of constants in Expression Builder Wizard.

There are two types of Repository variables −

• Static repository variables
• Dynamic repository variables

Static repository variables are defined in variable dialog box and their value exists until they are changed by the administrator.

Static repository variables contain default initializers that are numeric or character values. In addition, you can use Expression Builder to insert a constant as the default initializer, such as date, time, etc. You cannot use any other value or expression as the default initializer for a static repository variable.

In older BI versions, the Administrator tool did not limit value of static repository variables. You may get warning in consistency check if your repository has been upgraded from older versions. In such case, update the static repository variables so that default initializers have a constant value.

Dynamic repository variables are same as static variables but the values are refreshed by data returned from queries. When defining a dynamic repository variable, you create an initialization block or use a preexisting one that contains a SQL query. You can also set up a schedule that the Oracle BI Server will follow to execute the query and refresh the value of the variable periodically.

When the value of a dynamic repository variable changes, all cache entries associated with a business model are deleted automatically.

Each query can refresh several variables: one variable for each column in the query. You schedule these queries to be executed by the Oracle BI server.

Dynamic repository variables are useful for defining the content of logical table sources. For example, suppose you have two sources for information about orders. One source contains current orders and the other contains historical data.

Create Repository Variables

In the Administration Tool → Go to Manage → Select Variables → Variable Manager → Go to Action → New → Repository > Variable.

In the Variable dialog, type a name for the variable (Names for all variables should be unique) → Select the type of variable - Static or Dynamic.

If you select dynamic variable, use the initialization block list to select an existing initialization block that will be used to refresh the value on a continuing basis.

To create a new initialization block → Click New. To add a default initializer value, type the value in the default initializer box, or click the Expression Builder button to use Expression Builder.

For static repository variables, the value you specify in the default initializer window persists. It will not change unless you change it. If you initialize a variable using a character string, enclose the string in single quotes. Static repository variables must have default initializers that are constant values → Click OK to close the dialog box.

Session Variables

Session variables are similar to dynamic repository variables and they obtain their values from initialization blocks. When a user begins a session, the Oracle BI server creates new instances of session variables and initializes them.

There are as many instances of a session variable as there are active sessions on the Oracle BI server. Each instance of a session variable could be initialized to a different value.

There are two types of Session variables −

• System session variables
• Non-system session variables

System session variables are used by Oracle BI and Presentation server for specific purposes. They have predefined reserved names which can’t be used by other variables.

Non-system session variables are used for setting the user filters. Example, you could define a non-system variable called Sale_Region that would be initialized to the name of the sale_region of the user.

Create Session Variables

In the Administration Tool → Go to Manage → Select Variables.

In the Variable Manager dialog, click Action → New → Session → Variable.

In the Session Variable dialog, enter variable name (Names for all variables should be unique and names of system session variables are reserved and cannot be used for other types of variables).

For session variables, you can select the following options −

Enable any user to set the value − This option is used to set session variables after the initialization block has populated the value. Example - this option lets non-administrators set this variable for sampling.

Security sensitive − This is used to identify the variable as sensitive to security when using a row-level database security strategy, such as a Virtual Private Database (VPD).

You can use the initialization block list option to choose an initialization block that will be used to refresh the value regularly. You can also create a new initialization block.

To add a default initializer value, enter the value in the default initializer box or click the Expression Builder button to use Expression Builder. Click OK to close the dialog box.

The administrator can create non-system session variables using Oracle BI Administration tool.

Presentation Variables

Presentation variables are created with creation of Dashboard prompts. There are two types of dashboard prompts that can be used −

Column Prompt

Presentation variable created with column prompt is associated with a column, and the values that it can take comes from the column values.

To create a presentation variable go to New Prompt dialog or Edit Prompt dialog → Select Presentation Variable in the Set of a variable field → Enter the name for the variable.

Variable Prompt

Presentation variable created as variable prompt is not associated with any column and you need to define its values.

To create a presentation variable as part of a variable prompt, in the New Prompt dialog or Edit Prompt dialog → Select Presentation Variable in the Prompt for field → Enter the name for the variable.

The value of a presentation variable is populated by the column or variable prompt with which it is created. Each time a user selects a value in the column or variable prompt, the value of the presentation variable is set to the value that the user selects.

Initialization Blocks

Initialization blocks are used to initialize OBIEE variables: Dynamic Repository variables, system session variables and non-system session variables.

It contains SQL statement that are executed to initialize or refresh the variables associated with that block. The SQL statement that are executed points to physical tables that can be accessed using the connection pool. Connection pool is defined in the initialization block dialog.

If you want the query for an initialization block to have database-specific SQL, you can select a database type for that query.

Initialize Dynamic Repository Variables using Initialization Block

Default initiation string field of initialization block is used to set value of dynamic repository variables. You also define a schedule which is followed by Oracle BI server to execute the query and refresh the value of variable. If you set the logging level to 2 or higher, log information for all SQL queries executed to retrieve the value of variable is saved in nqquery.log file.

Location of this file on BI Server −

ORACLE_INSTANCE\diagnostics\logs\OracleBIServerComponent\coreapplication_obisn

Initialize Session Variables using Initialization Block

Session variables also take their values from initialization block but their value never changes with time intervals. When a user begins a new session, Oracle BI server creates a new instance of session variables.

All SQL queries executed to retrieve session variable information by BI server if the logging level is set to 2 or higher in the Identity Manager User object or the LOGLEVEL system session variable is set to 2 or higher in the Variable Manager is saved in nqquery.log file.

Create Initialization Blocks in Administrator Tool

Go to Manager → Variables → Variable Manager Dialog box appears. Go to Action menu → Click New → Repository → Initialization Block → Enter the name of initialization block.

Go to Schedule tab → Select start date and time and refresh interval.

You can choose the following options for Initialization Blocks −

Disable − If you select this option, initialization block is disabled. To enable an initialization block, right-click an existing initialization block in the Variable Manager and choose Enable. This option enables you to change this property without opening the initialization block dialog.

Allow deferred execution − This allows you to defer the execution of the initialization block until an associated session variable is accessed for the first time during the session.

Required for authentication − If you select this, initialization block must execute for users to log in. Users are denied access to Oracle BI if the initialization block doesn’t execute.

• Stack Overflow for Teams Where developers & technologists share private knowledge with coworkers
• Advertising & Talent Reach devs & technologists worldwide about your product, service or employer brand
• OverflowAI GenAI features for Teams
• OverflowAPI Train & fine-tune LLMs
• Labs The future of collective knowledge sharing
• About the company Visit the blog

Collectives™ on Stack Overflow

Find centralized, trusted content and collaborate around the technologies you use most.

Q&A for work

Connect and share knowledge within a single location that is structured and easy to search.

Get early access and see previews of new features.

Using OBIEE Presentation Variables in Column Formula

I have a dashboard prompt setting a presentation variable, based on months.

I am then trying to use the presentation variable in column formulae, however I am getting the following error when I try to preview it by selecting the month of February:

COlumn Formula :

However when I use the same variable in the analysis filter (not in the column formula, but whole analysis) it seems to run fine. But I need the presentation variable applied on particular columns and not on all of them.

Could anyone please advise how to achieve this or what I am doing wrong when using the presentation variable in the column formula.v

• presentation

• we don't know what you are doing wrong when using the variable in the column formula, as you haven't told us how you are doing it. If you could edit your question to include the column formula that would be helpful. –  jackohug Commented Mar 31, 2016 at 8:34

2 Answers 2

--Create a prompt with sql query which will fetch all month name or month number --Assign a presentation variable to that prompt --use that variable as a filter condition in analysis for that particular column.

So how it works is--when u select any month name from prompt that will be assigned to variable.And same month name in that variable will be used as filter condition for your column and and it will fetch data accordingly and report will run properly. PFB a blog related to presentation Variable.

" https://blogs.oracle.com/ExalyticsOBIEE/entry/how_to_pass_presentation_variable

I assume you have setup a presentation variable already called MonthName, yo will need to add the formulae like this (@{MonthName}['@']{'January'}) . in between mothname and default January.

Hope that helps.

Your Answer

Reminder: Answers generated by artificial intelligence tools are not allowed on Stack Overflow. Learn more

Sign up or log in

Post as a guest.

Required, but never shown

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy .

Not the answer you're looking for? Browse other questions tagged variables presentation obiee or ask your own question .

• Featured on Meta
• We've made changes to our Terms of Service & Privacy Policy - July 2024
• Introducing an accessibility dashboard and some upcoming changes to display...
• Tag hover experiment wrap-up and next steps

Hot Network Questions

• Earliest example of space travel involving interdimensional 'shortcuts'
• how to replace Info with Emacs
• Delexing a finitely complete category
• How to find a simplified sinogram in a paper dictionary
• How do I resolve license terms conflict when forking?
• In Europe, are you allowed to enter an intersection on red light in order to allow emergency vehicles to pass?
• How much does flight help the ranger?
• XAct website not working
• MPs assuming office on the day of the election
• Reduce spacing between letters in equations
• Inverse relationship between Stirling numbers of the first and second kind via generating functions
• How to open a single app in a particular language while the system language is English?
• Why do I see different declension tables for the same noun in different sources?
• Is there mutable aliasing in this list of variable references?
• Statistically, which is more of a severe penalty on a d20 roll, Disadvantage or a -10 penalty?
• Use of initialisms such as DMG, PHB and MM in third-party content
• What was R' Chanina's Technique to Revive R' Yochanan?
• Cycloheptatrienyl anion is antiaromatic or non-aromatic?
• LaTeX error when formatting external data with siunitx
• 16 of them stay the same, some of the rest are "questionable"
• Weird lines in the Aeneid (Book I, lines 444-445)
• Do comets ever run out of water?
• Utilising Paired T-test but data is not normally distributed and there are outliers
• Flashlight and 6 batteries

• Install App

Analytics Software

For appeals, questions and feedback about Oracle Forums, please email [email protected] . Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

Oracle has updated its online  Terms of Use  and  Community Guidelines  and introduced a  Community Integrity Policy . These changes document Community specific rules and Oracle’s content moderation practices including use of automated tools, appeals process, and Oracle’s contact details. If you object to any changes, you may request that your account be closed by contacting  [email protected] . Your continued use of Oracle Communities means that you are consenting to the updated terms.

Presentation Variable in Evaluate Function in obiee 12c

I am trying to use presentation variable in evaluate function.getting below error.

Cannot function ship the following expression evaluate

If I use date column it is working fine.

Please suggest