Tag Archives: obiee variables

Few interesting Q&As from Oracle

Good evening,

I’ve located some more interesting items from Oracle:

Q:How to remove the empty space between sections in dashboards?

A:To remove the spaces between the reports try the following:

You have to modify the css of the style you apply to the dashboard. In particular to modify the space under the answers you have to modify the portalcontent.css and the views.css

Try to modify the .EmbeddedItem class in the portalcontent.css , and the .ResultsTable in the view.css
You can find this .css in the s_STYLENAME/b_mozilla_4/ folder of you BIEE deploy.

Try to Add or modify to this class:

Suggest you to create a new style (the s_ ans sk_).

Q:How to Remove [nQSError: 22047] “The Dimension used in AGO function..”

A: To remove the following error :

Steps to reproduce the error:

1. Create an Answer Request based on (Year, Chg AGO metric)
2. For table view, set Report-Based Total.

The error can be reproduced on any calculation of AGO metric, not only Chg
AGO, for example, AGO Metric + 1.

The error will be gone if we put ANY filter on the Date dimension, for
example Year > 2000.

Detailed error msg:
State: HY000. Code: 10058. [NQODBC] [SQL_STATE: HY000] [nQSError: 10058] A
general error has occurred. [nQSError: 22047] The Dimension used in AGO
function must be referenced in the query.

1.Look for instanceconfig.xml under the following path :

* Windows Operating Systems: OracleBIData_HOME\web\config
* Linux Operating Systems: OracleBIData_HOME/web/config

2. Add the following in instanceconfig.xml , restart BIEE instance and re-test :


Q: How to display session variables in static text View ?

A: Correct syntax in Static View is

Q: How to Sort a date column of varchar type as if it was a date datatype in answers.

A:1. Create another logical column in the Dim-Date table in the business layer based on the logical column ” Month ”

Note : Dim-Date table is the logical table in business layer which has the “Month ” column of varchar data type but has date values.

2. Give the expression for the new column as

CAST (Core.”Dim – Date”.”Month Name” AS DATE )

This would cast the original Month column to date datatype.

3. Use this column in the report thorugh answers and change its format as needed .

shown below are the steps to display the date as “MMMM – yyyy”

a. click on column properties.

b. Navigate to Data format tab.

c. Select Date Format – Custom

d. Give the Custom Date Format – MMMM – yyyy

4. See that this new column now gives the date format as needed and sort this column on the dashboard date wise not alphabetically.

URL-variable not passed thru to session variable

This one I found to be interesting:

The goal is to set session variables using url variables, but can you also do this for the user and password ?
url variable (&Upwd) is not passed to session variable USER_PWD.
The variable USER is correctly passed, the variable USER_PWD is not

The steps to set an OBI session variable via a URL call utilizing the

instanceconfig.xml tag should be as follows

1. Create a session init block that will act as a ‘placeholder’ for the
session variable to be set via the url call – the variable can be set to

2. Set the ‘Enable any user to set the value’ option for the variable.

3. Add the following tag block to the instanceconfig.xml file anywhere
between the <ServerInstance></ServerInstance> tags:

<UserIdPassword enabled=”true”>
“TEST_VAR” should match the session variable name (case sensitive).

4. The following option will need to be appended to the OBI url passed –
&SETVAR=’variable value to pass. So a full example would be:

However, note that you cannot set the value of any System Security Session variable (specifically USER, PROXY, GROUP and WEBGROUPS) using any source method (e.g.: url, cookie, httpHeader) by design. Having this ability would open possible security breaches.

If you attempt to set the USER variable with the following instanceconfig.xml setting:

<Param name=”NQ_SESSION.USER” source=”url” nameInSource=”nquser” />

You will get the following error when using the url: http://localhost:9704/analytics/saw.dll?Dashboard&nquser=user1&nqpassword=public

nQSError: 10018: Access for the requested connection is refused

OBIEE variable

Parameter For Max Number Of Rows Not Taken Into Account

This is probably helpful for someone who migrated from OBIEE 10G to 11G and need to retain the same settings. I’m still wondering who needs to see reports with 500k rows, but still.

In OBIEE 10g the ResultRowLimit was working with a value of 5,000,000 (65,000 is the default value).

We have tried to do the same in 11g through EM > BI > Core application > Capacity Management >

Performance > Maximal number of rows in a tabular view set from 65,000 to 5,000,000.

The Servers have been restarted and the report is still not running.

The SQL query fetches less than 200,000 rows. i.e. one of our reports is 172,310 rows.

Impact on Business:
This problem impacts a lot of reports through the National Healthcare agency.
These reports used to work in 10g.

View Display Error

Exceeded configured maximum number of allowed input records.
Error Details
Error Codes: EKMT3FK5:OI2DL65P
Location: saw.httpserver.processrequest, saw.rpc.server.responder, saw.rpc.server,

saw.rpc.server.handleConnection, saw.rpc.server.dispatch, saw.threadpool, saw.threadpool, saw.threads
SQL Issued: 858~k1vsg0egoo7i60qhn13qv8lj52

Moreover, the same matter occurs in a report with 49220 lines fetched from Database.

Could it be linked to the fact that these reports use a pivot table to perform a second aggregation phase?

The final Pivot view is about 10 columns by 10 lines.
The Number of Rows setting was verified within OBIEE

Capacity Management > Performance

Maximum Number of Rows Processed when Rendering a Table View

This setting limits how much data is retrieved from the BI Server and processed. The default value is 65000.

Reducing the maximum number of rows processed can significantly improve performance by reducing the system resources that can be consumed by a given user session.

Number of Rows 65000

It was found that the cause was the Pivot Table (which generally have more performance overheads when compared to a standard Table).
Within the instanceconfig.xml file in 10g you would have had to adjust the limits in your file for the parameters below.

In OBIEE 11g, these values below are discussed in the following Guides.

Oracle Fusion Middleware System Administrator’s Guide for Oracle Business Intelligence Enterprise Edition 11g Release 1 (11.1.1) E10541-02

18.3.1 Manually Configuring for Data in Views

If changes are made to the instanceconfig.xml file please carefully refer to the section stated below:
(Section 3.4, “Using a Text Editor to Update Configuration Settings.”)

When the following changes were made to the instanceconfig.xml file, this fixed the issue.


Oracle suggests that “These values were specific to this environment and the reports being run by that organisation. Other readers should note that increasing these values to too high a level may have an impact on performance (which is hardware dependent).”

Unable to configure “ACT AS” proxy feature

If you’re unable to configure proxy “Act as” feature maybe this can help you.

The customer had OID/SSO users in lowercase but had the users in the proxy table in upper case.

Also the string (‘VALUEOF(NQ_SESSION.RUNAS)’) was not in uppercase in both the proxyblock and proxylevel init blocks

Initially, on selecting ‘act as’ the following error was reported:
“This functionality has not been correctly configured by your administrator (Can’t retrieve the list of target users).”

The customer had OID/SSO users in lowercase but had the users in the proxy table in upper case. Advised the customer to add upper function to proxy related init block queries and in the XML template file queries as well. This resolved this error message and the customer was able to see the list of target users.

Then on selecting a particular user from the list of target users it failed. It gave the error message : “Not logged in”.

Since the customer had OID/SSO setup, the customer was advised to test with rpd users.

Even with rpd users the functionality still failed to work and reported the following error:
“Access for requested connection is refused. Failed to get the value of PROXY session variable”.

Finally, what seemed to resolved the problem for the customer was changing the string (‘VALUEOF(NQ_SESSION.RUNAS)’) in all uppercase in both the proxyblock and proxylevel init blocks.

Is it possible to use Dashboard Prompts to override Session Variables?

This is a two step process:

1) Tick “enable any user to set variable” checkbox in the RPD for the session variable
2) When you create a dashboard prompt, in the Set Variable list, choose to populate a variable for the dashboard prompt using a server request variable.

If you set a server variable it will override explicitly the value of this variable set via the initialization block.

The variable is changed in the dashboard that uses the prompt( where the variable is set).