Business Intelligence – Oracle

Oracle BI EE 10.1.3.3/2 – Varying Columns Dynamically using Session Variables

Posted by Venkatakrishnan J on February 29, 2008

If you had gone through my blog entry here, you would have known how to go about dynamically varying the columns using presentation variables and dashboard prompts. But say you have a scenario wherein you are integrating BI EE into an external application using GO URL, then the above procedure would not be valid since presentation variables would work only from dashboards. Now, lets look at an approach wherein we can dynamically vary the columns in a report using GO URL. This approach is based on yesterday’s blog entry here. Our idea is to create a simple report wherein there would be 2 columns. One column will be dynamic based on what we pass in the GO URL and the other would be static. In our example, we shall be making use of a session variable called DSN while shall be initialized to some value through an init block. Now, lets create a simple report with 2 columns. One column would have the below formula

CASE WHEN VALUEOF(NQ_SESSION.DSN) = ‘0’ THEN Products.Type WHEN VALUEOF(NQ_SESSION.DSN) = ‘1’ THEN Products.Brand ELSE Products.Color END

So, basically what this does is, it chooses the columns based on the value of the session variable. The value of the session variable will be varied from the GO URL.

The other column will be a static column (Sales).

      

      

Now, save this report. Ensure that you have added the Session variable to the instanceconfig.xml using the procedure here. Now, if you issue the below GO URLs the report columns will change automatically.

http://localhost:9704/analytics/saw.dll?Go&nquser=Administrator&nqpassword=Administrator&Path=/shared/Paint%20Demo/GO%20URL%20-%20Vary%20Session%20Variable/Report1&Options=rmf&DSN=0

      

http://localhost:9704/analytics/saw.dll?Go&nquser=Administrator&nqpassword=Administrator&Path=/shared/Paint%20Demo/GO%20URL%20-%20Vary%20Session%20Variable/Report1&Options=rmf&DSN=1

      

The disadvantage of this approach is that you will be losing the default hierarchy drills. You can still use Navigate to achieve the drills though.

Advertisements

One Response to “Oracle BI EE 10.1.3.3/2 – Varying Columns Dynamically using Session Variables”

  1. Karthik said

    Hi Venkat,

    This is regarding goURL functionality in SA 7.8.4, I know that we could pass any parameters through goURL functionality and based on that we have a dashboard which filters reports based on few prompts. We are able to pass parameters using goURL but reports dont automatically refresh after parameters are passed. It is only after a user hits go on the prompt, reports are refreshed with proper filter condition.

    Just want to know if we pass variables for filtering, how do reports in dashboard refresh automatically without user involvement?

    – Thanks
    Karthik

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: