how to use presentation variable in bi publisher

14 Jul 2021 Navigating from OAC to BI Publisher with Parameters

Here at ClearPeaks , we are always trying to understand how tools work so that we can squeeze the best out of them to meet our customers’ needs. In this article we’re going to explain how to set up an action link from an OAC to a BI Publisher report with the special particularity of passing a set of parameters, including a date filter. Even though this article deals with OAC, the method can also be applied to OBIEE or OAS.

Our starting point is an OAC dashboard that depicts the hiring cost by gender and nationality for a specific date range:

Figure 1: Hire Cost Analysis dashboard

When clicking on the Cost To Hire amount for any row, a navigation to BI Publisher is set in order to display the disaggregation of the hiring cost. In this case, the cost for French females will be broken down:

Figure 2: Cost to Hire dashboard – French females

Figure 3: BI Publisher – Hiring Cost Details Report

How can we pass dates that are not even shown from a report through a navigation? This will be explained in the article, with the implementation process divided into several steps:

STEP 1: Create your OAC Report

In this case, the report granularity might need aggregated data, so a drilldown to BI Publisher makes sense. Since the report is filtered by a dashboard prompt, it needs a date filter as prompted:

Figure 4: OAC report – Date filter

STEP 2: Create your BI Publisher Report

To create a BI Publisher report, you need to create a data model first, and then the report where the layout is defined. In this case, the query and the report parameters will be defined in the data model.

It is very important to set proper names to the parameters and use them in the query.

Figure 5: BI Publisher Report – Parameter definitions

Figure 6: BI Publisher Report – Query filters using parameters

STEP 3: Create an Action Link Between OAC and BI Publisher

You can learn how to guide user navigation in OAC dashboards and analysis using action links. An action link will allow users to access external web content in the context of your BI analysis, to use conditions and agents to automate the initiation of business processes, or to invoke a Web Service.

To set up an action link, go to your OAC report and click on the column properties of the field that you want to use to navigate. In our example, Cost To Hire will be the selected column to navigate to BI Publisher.

Figure 7: Set up an action link – Column properties

Figure 8: Set up an action link – Interaction tab

Figure 9: Set up an action link – Create action link – Navigate to a Web Page

The type of action link is crucial to determining the navigation possibilities. In this case, since BI Publisher has a different URL from OAC, it is important to set the action link as Navigate to a Web Page .

The first URL to enter is from BI Publisher, so navigate to your BI Publisher report and click on the settings icon to get the URL:

Figure 10: Set up an action link – Get BI Publisher URL

In our example, the URL is:

https://url_to_oac/ui/analytics/saw.dll?bipublisherEntry&Action=open&itemType=.xdo&bipPath=%2FBIP%2FHiring%20Cost%20Details% 20Report.xdo&bipParams{“_xmode”:”2″,”_sTkn”:”242d733617993ae0f2b”,”_xiasynch”:””, “_xpf”:””,”_xpt”:”0″,”_dFlag”:”false”,”_edIndex”:”0″,”_dIndex”:”0″,”_rToken”:””, “_ranDiag”:”false”,”_xdo”:”%2FBIP%2FHiring%20Cost%20Details%20Report.xdo”, “_paramspSTART_DATE”:””,”_paramspEND_DATE”:””,”_paramspNATIONALITY”:””, “_paramspGENDER”:””,”_xt”:”Hiring%20Cost%20Details%20Report”,”_xf”:”analyze”, “_xana”:”view”}&_linkToReport=true

Enter the URL in the text box shown below, prompted after setting the action link as a Navigate to a Web Page (Figure 9):

Figure 11: Set up an action link – Enter the BI Publisher URL here

Click on OK to complete.

STEP 4: Define Date Columns to Pass Through Navigation

This step is often the key to making this navigation work, considering the difficulty of passing the filter set to BI Publisher by the dashboard prompt, and how to split that date range into 2 different values to filter the SQL explained in step 2.

First, let’s see what value is returned by the dashboard prompt: to do so, assign a presentation variable to the date prompt and use it in the OAC report to visualise the format. Navigate to OAC and edit the dashboard prompt:

Figure 12: Edit dashboard prompt

Figure 13: Set up a Presentation Variable

Once the presentation variable has been set, add it to the report:

Figure 14: Add the Presentation Variable to the report

Figure 15: Presentation Variable to OAC report

Remember to add single quotes once the presentation variable has been added to the report:

Figure 16: Presentation Variable syntax

Once added, we can see its value from a dashboard perspective:

Figure 17: Presentation Variable date format when displayed in OAC dashboard

As can be observed in Figure 17, the value shown in the dashboard is a string concatenation of the 2 dates selected in the prompt with a comma as a separator, so the idea is to apply string functions to extract both dates, the initial and the final.

For the former, apply the following formula:

CAST(to_datetime(SUBSTRING(‘@{PV_HIRE_DATE}’ FROM 0 FOR LENGTH(‘@{PV_HIRE_DATE}’) – LOCATE(‘,’,’@{PV_HIRE_DATE}’)) ,’YYYY-MM-DD’) AS DATE)

And for the latter:

CAST(to_datetime(SUBSTRING(‘@{PV_HIRE_DATE}’ FROM LOCATE(‘,’,’@{PV_HIRE_DATE}’)+1 FOR LENGTH(‘@{PV_HIRE_DATE}’) – LOCATE(‘,’,’@{PV_HIRE_DATE}’)+1),’YYYY-MM-DD’) AS DATE)

Bear in mind that both fields need to be casted to date as we want to pass these fields as date fields. In addition, as they are not relevant to the dashboard itself, they will be hidden from the OAC table. It is crucial not to hide the fields from the Criteria tab (column properties); go to the editing options and hide the fields from there.

Figure 18: Hide unused fields in OAC table

STEP 5: Parametrise the Action Link

Go back to the OAC report, click on Cost To Hire column properties and edit the action link as described in step 3.

Figure 19: Edit the action link to include parameters

Click on Define Parameters and the following window will appear:

Figure 20: Define parameters to use in the navigation

Now it’s time to parametrise the different variables that will be passed to BI Publisher. The first thing to note is that all parameters need to be hidden in order to avoid another window prompt once the user starts navigating.

The first 3 parameters will be kept by default, but the rest will be removed. Based on the image below, the following 4 parameters will be created:

Figure 21: Set up parameters

The key here is that the date filters have been included in the report in step 4 and so we can now select them.

The different options when defining a parameter are shown below:

Figure 22: Options to set up a parameter in an action link

By and large, Value will be used to filter a specific value like ‘Female’, and Column Value will be selected to filter the clicked value in the OAC report.

In our example, the user is not required to select any variable in the target BI Publisher report, so the number of parameters to be included in the navigation should match the number of parameters set in the BI Publisher.

Finally, once all the parameters have been set correctly, the URL needs to be modified according to the following logic:

https://url_to_oac/analytics/saw.dll?bipublisherEntry&Action=@{1}&itemType=@{2}&bipPath=@{3}&bipParams={“_xmode”:”2″,”_sTkn”:”78416170177d3ff717b”,”_xiasynch”:””,”_xpf”:””,”_xpt”:”0″, “_dFlag”:”false”,”_edIndex”:”0″,”_dIndex”:”0″,”_rToken”:””,”_ranDiag”:”false”, “_xdo”:”/BIP/Hiring Cost Details Report.xdo”,” _paramspSTART_DATE”:”@{4}”,”_paramspEND_DATE”:”@{5}”,”_paramspGENDER”: “@{6}”,”_paramspNATIONALITY”:”@{7} “,”_xf”:”analyze”,”_xana”:”view”}

As you can see, the number headed by ‘ @ ’ corresponds to the Name column in Figure 21.

STEP 6: Double-check your Navigation

Everything should work now, but since managing dates can be challenging sometimes, it is very important that we ensure that the date format matches the source field format in the database. In this case, since the date format in the OAC report is YYYY-MM-DD and the one required for the database is MM/DD/YYYY, the format of the date parameters in the BI Publisher data model needs to be changed.

Figure 23: Specify the date format to match the database

Now you can have a go!

In this article we have customised a navigation from an OAC report to a BI Publisher report and filtered the target report properly.

ClearPeaks are experts in Oracle technologies, always up-to-date with market trends and the latest features. If you believe our expertise can be of use to you, please contact us – we’ll be happy to help!

• Essential Cookies
• Analytics Cookies
• Additional Cookies
• Cookie Policy

This website uses cookies so that we can provide you with the best user experience possible. Cookie information is stored in your browser and performs functions such as recognising you when you return to our website and helping our team to understand which sections of the website you find most interesting and useful.

You can adjust your preferences below.

We use these cookies to ensure that our website works correctly and meet your expectations. They cannot be switched off. You can find out more in our privacy policy and cookie policy.

If you disable this cookie, we will not be able to save your preferences. This means that every time you visit this website you will need to enable or disable cookies again.

This website uses Google Analytics to collect anonymous information such as the number of visitors to the site and the most popular pages. Keeping these cookies enabled helps us to improve our website and give you a great experience.

Please enable Strictly Necessary Cookies first so that we can save your preferences!

This website uses the following additional cookies:

More information about our Cookie Policy

Adding Parameters to a Data Model in Oracle Business Intelligence Publisher

Before you begin.

This 15-minute tutorial shows you how to add parameters to a data model and test the parameters.

Using parameters in a data model enables filtering the source data. The filtered data is used in reports.

In this tutorial, use the Demo data available in the Oracle Business Intelligence installation directory. You create a data set of products, and then use the text and menu type parameters to filter the product list based on the price limit and the supplier.

What Do You Need?

• Access to Oracle Business Intelligence Publisher as a BI Content Author
• Access to samples and demo data source installed and configured in the ${xdo.server.config.dir}/repository/DemoFiles directory when you installed Oracle Business Intelligence Publisher

Create a Data Model

In this section, create a data model, develop a SQL query against the Products table, and then create a data set in the data model.

• Sign in to Oracle Business Intelligence Publisher with your credentials.
• On the Home page, click New, and then select Data Model.

• In the New Data Set - SQL Query dialog box, enter Product_Supplier_Price in the Name field, select demo from the Data Source list, and then select Standard SQL from the Type of SQL list.
• Click Query Builder.
• In Query Builder, select OE from the Schema list, and then select PRODUCTS.
• From Products, select the PRODUCT_ID, PRODUCT_NAME, SUPPLIER_ID, LIST_PRICE columns, and then click Save.

• In the New Data Set - SQL Query dialog box, view the SQL query you built in the Query Builder dialog box, and then click OK.

• In the Save As dialog box, from the Catalog pane, select My Folders, enter Product_dm as the data model name, and then click Save.

Add Parameters to the Data Model

In this section, add a text type parameter and a menu type parameter to the data model. Use a list of values (LOV) for the menu type parameter.

• Name: Supplier_Lov
• Type: SQL Query
• Data Source: demo
• SQL Query: Select supplier_id from products

• Name: P_SupplierID
• Data Type: String
• Default Value: 102059
• Parameter Type: Menu
• Display Label: Select a Supplier ID
• List of Values: Supplier_Lov
• Number of Values to Display in List: 20

• Name: P_PriceLimit
• Data Type: Integer
• Default Value: 223
• Parameter Type: Text
• Display Label: Price Limit
• Text Field Size: 6

Test the Parameters in the Data Model

In this section, test the parameters in the data model.

• In the Product_dm data model, click View Data.
• In the Data tab, click View, and then click Table View.

Want to Learn More?

• Creating Data Sets
• Adding Parameters and Lists of Values

Working with Session Variables

Learn about session variables and how to create them.

This section provides information about working with session variables, and contains the following topics:

About Session Variables

Creating session variables.

Session variables obtain their values from initialization blocks.

Unlike dynamic repository variables, however, the initialization of session variables is not scheduled. When a user begins a session, the Oracle BI Server creates new instances of session variables and initializes them.

Unlike a repository variable, 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.

Session variables are primarily used when authenticating users against external sources such as database tables or LDAP servers. If a user is authenticated successfully, session variables can be used to set filters and permissions for that session. For information about using session variables when setting up security, see Managing Session Variables in Security Guide for Oracle Business Intelligence Enterprise Edition .

This section contains the following topics:

About System Session Variables

About Nonsystem Session Variable

System session variables are used by the Oracle BI Server and Oracle BI PresentationServices use for specific purposes.

System session variables have reserved names that cannot be used for other kinds of variables such as static or dynamic repository variables and nonsystem session variables.

When you use these variables for Oracle BI Presentation Services , preface their names with NQ_SESSION . For example, to filter a column on the value of the variable LOGLEVEL , set the filter to the variable NQ_SESSION.LOGLEVEL .

The table describes the available system session variables.

About Nonsystem Session Variables

You use the same procedure to define nonsystem session variables as for system session variables.

A common use for nonsystem session variables is setting user filters. For example, you could define a nonsystem variable called SalesRegion that would be initialized to the name of the sales region of the user.

You could then set a security filter for all members of a group that would allow them to view only data pertinent to their region.

When you use these variables for Oracle BI Server , preface their names with NQ_SESSION . For example, to filter a column on the value of the variable SalesRegion , set the filter to the variable NQ_SESSION.SalesRegion .

Learn how to create session variables.

To create a session variable:

• In the Administration Tool , select Manage , then select Variables .
• In the Variable Manager dialog, select Action > New > Session > Variable .

Names for all variables should be unique. The names of system session variables are reserved and cannot be used for other types of variables.

Enable any user to set the value. Select this option to set session variables after the initialization block has populated the value (at user login) by calling the ODBC stored procedure NQSSetSessionValue() . For example, this option lets non-administrators to set this variable for sampling.

Note that the NQSSetSessionValues() stored procedure is not supported for use through the Issue SQL page in Oracle BI Presentation Services Administration.

If this option is not selected, then the variable cannot be set.

Security Sensitive. Select this option to identify the variable as sensitive to security when using a row-level database security strategy, such as a Virtual Private Database (VPD). When filtering cache table matches, the looks at the parent database object of each column or table that is referenced in the logical request projection list. If the database object has the Virtual Private Database option selected, the Oracle BI Server matches a list of security-sensitive variables to each prospective cache hit. Cache hits would only occur on cache entries that included and matched all security-sensitive variables.

To create a new initialization block, click New . See Creating Initialization Blocks for more information.

• To add a Default Initializer value, type the value in the Default Initializer box, or click the Expression Builder button to use Expression Builder.

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

• 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.

How to display BI Publisher multi-select parameter items in a bi publisher report

I have a requirement to display the items chosen in a multi select parameter in a bi publisher report. My Data Model's sample data does include the chosen parameter values but I only see the first item selected after placing the parameter in the bi pub report layout and then viewing the report (this includes when I place the parameter in a list object). How do I display in my report all of the items that were selected in my multi-select parameter?

• bi-publisher

• Please provide the sample XML. You'll probably use <?for-each:?> to loop multiple nodes. –  DaveStSomeWhere Commented May 12, 2017 at 4:07
• Unfortunately, I have been asked to not include the sample XML due to the sensitive nature of our data. As noted I am new to BI Publisher; please provide an example of how we could use <?for-each:?> to display all items of a multi-select parameter. –  dman6750 Commented May 12, 2017 at 18:18
• 1 You could always sanitize your example XML showing the basic element relationships and key attributes - this would make it easier using your example. I'll put an answer below with a generic <?for-each:?> example if that would be helpful (too much to put into a comment). Providing the input and desired output is necessary to get a useful answer. –  DaveStSomeWhere Commented May 12, 2017 at 19:25
• Agree with @DaveCoast, please provide more details. What are the parameters of the concurrent request (and how would they look when the user enters them?), what does your XML look like (at the very least the parameter section), what do you want the output to look like? –  EdHayes3 Commented May 11, 2018 at 20:30

Know someone who can answer? Share a link to this question via email , Twitter , or Facebook .

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 .

Browse other questions tagged bi-publisher or ask your own question .

• The Overflow Blog
• Where does Postgres fit in a world of GenAI and vector databases?
• Featured on Meta
• We've made changes to our Terms of Service & Privacy Policy - July 2024
• Bringing clarity to status tag usage on meta sites
• Feedback requested: How do you use tag hover descriptions for curating and do...
• What does a new user need in a homepage experience on Stack Overflow?
• Staging Ground Reviewer Motivation

Hot Network Questions

• How to assess whether it is imposter syndrome or actual low quality work during Ph.D.?
• Get WEBP via FFmpeg/thumbnails utility
• Why are AAVs not a replacement for through-roof vent pipe?
• Sci-fi short story about a dystopian future where all natural resources had been used up and people were struggling to survive
• What is the name of the book about a boy dressed in layers of clothes who is actually a mouse?
• How do I alter a table by using AFTER in hook update?
• My enemy sent me this puzzle!
• What is the difference between using a resistor or a capacitor as current limiter?
• Which battery clamp is positive?
• How to allocate memory in NASM without C functions (x64)?
• What are the risks of a compromised top tube and of attempts to repair it?
• What is the significance of bringing the door to Nippur in the Epic of Gilgamesh?
• Are quantum states like the W, Bell, GHZ, and Dicke state actually used in quantum computing research?
• Submitting a paper as a nonacademic practitioner in a field
• My collegeagues and I are travelling to UK as delegates in an event and the company is paying for all our travel expenses. what documents are required
• 24.04 Ubuntu crashes periodiocally, screen, keyboard and mouse freeze
• Why do National Geographic and Discovery Channel broadcast fake or pseudoscientific programs?
• Purpose of burn permit?
• Why does my PC take a long time to start, then when it's at the login screen it jumps to the desktop instantly?
• How are notes named in Japan?
• What issues are there with my perspective on truth?
• Can a 2-sphere be squashed flat?
• A string view over a Java String
• Where did Geordi's eyes go?

• 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.

Variable in BI publisher