Category Archives: Bugs and Issues

Some interesting decimal problem

Someone was using the AVG function in a column. The result should be 4,7 but answers displayed only 4. He modified the data format and added to the configuration to show 2 decimal places but he saw this value 4,00. He was using the formula like this AVG([Column_name]).

Oracle’s support decided figured out that:

The AVG function works as a division (in a Math context).
If the number you divide is an integer, the result will be an integer.
If the number you divide is a double, the result will be a double, this is how answers works.

In this case, the column had integer values, so the result should be an integer that’s the reason why customer saw a 4 instead 4,7.

If you divide an integer you will get an integer as a result. If you divide a double you will get a double as a result.

And provided the instructions:

1.- Go to column and click on the “Edit Formula” option.
2.- Click on the Function button.
3.- Select the CAST function in the Conversion section and click on the OK button.
4.- The formula should be looked like this CAST(“[column_name]” AS Double).
5.- Click on the Function button again.
6.- Select the AVG function in the Aggregation section and click on the OK button.
7.-The formula should be looked like this Avg(CAST(“[column_name]” AS Double)).
8.- Click on the “Column properties” option.
9.- Click on the “Data Format” tab and check in the “Override Default Data Format”.
10.- Choose the value 2 in the “Decimal Places” section.
11.- Click on the Ok button and click on the “Display Results” button.

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:
margin:0px;
padding:0px;

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 :

<ReportAggregateEnabled>true</ReportAggregateEnabled>

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

A: Correct syntax in Static View is

@{biServer.variables['NQ_SESSION.variablename']}
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.

Few Oracle’s support treasures

Normally, I like to browse through Oracle’s support in search of hidden gems and to get new ideas. This is how I smile when I find solution in the knowledge base for my problem:

I’m also almost used to new flash interface and it doesn’t irk me anymore.

It’s amazing how they deal with all kinds of issues and requests. I’ve found few things I’d like to share today:

How To Concatenate or Merge Amount and Currency Columns?

Your report displays transactions amount and currency in two separate columns. The requirement is to display the amount and currency as one column. For example:
100USD 100EUR etc….

Currently you have a numeric (double) column – Amount, and a second column – currency (alpha code format, such as:USD,EUR,….) in the report. The currency column is placed on the right side of the
amount column, as below:

date | merchant | amount | currency

The currency might be different for each transaction

Currently this cannot not be achieved because when casting the amount to char, in order to concat it with the currency, the 1000s separator is lost (15,321 becomes 15321).  Also when casting the amount to char, the sum agg is no longer available.  Formatting the amount as currency is also not possible because the amount is
of different currencies.
There is an Enhancement Request Bug 8680024 logged to address this matter.

Multiselect Prompt Displays ‘No Choices Available’ When Used With A Presentation Variable

You have configured a dashboard prompt with two columns, where the value selected by the user in the first prompt column is stored in a presentation variable, and the values in the second prompt column depend on
the value in this presentation variable.

When both column prompts are defined as type ‘drop down’ all works fine. The second column correctly displays in the drop down the values. However when the second column prompt control is defined as ‘multi select’, no data is displayed in the prompt. The prompt displays ‘No Choices Available’.

The multi-select prompt column should correctly display values in the prompt as it does when it is defined as a drop down.

This is bug 7571682

There are three possible work-arounds:
1. Click the ‘Go’ button will bring back data
2. Enter a value in the match box and it returns data
3. Use the vanilla constrain option for the dashboard prompts, so you can have column 2 constrained by column 1

Integrating BI Office with OBIEE that has SSO

It is possible to integrate BI Office with OBIEE that has SSO?

BI Office is similar to BI Publisher in integrating with OBIEE that has SSO.

Which is documented in the Oracle® Business Intelligence Enterprise Edition Deployment Guide and “Additional Configuration When SSO is Enabled for Oracle BI and BI Publisher” section.

Basically you need to deploy a second saw bridge (analytics.ear) that dedicates for web service calls and bypasses the SSO:
* Locate analytics.ear in the directory OracleBI_HOME/web, deploy and name the new Plug-in analyticsSOAP.  See the Oracle Business Intelligence Infrastructure Installation and Configuration
Guide for more information on Deploying an Ear file on your Web Application Server.
* Locate the Office Server config file (bioffice.xml), update the SawBaseURL property to point the new analyticsSOAP deployed above.
Config file bioffice.xml can be found in the directory OAS_HOME\j2ee\home\applications\bioffice\bioffice\WEB-INF
And the SawBaseURL property should look like this:
http://[host]:[port]/analyticsSOAP/saw.dll
The BI Office add-in would require the user to enter their SSO credentials to login from within Excel or PowerPoint.

Without these steps, and when attempting to login to Excel plugin to OBI\SSO environment, the error message “Unknown error” is likely to occur.

Oracle’s answer to OBIEE’s case sensitivity

Someone asked Oracle’s support  the following question: “Why some of the prompt case sensitive, some of them are case insensitive”

You noticed that some prompts are case sensitive and some are not. User selects a multi-select prompt, with the “BEGIN WITH” matching functionality, ie, User enters “We” (without quotes) and presses the Go button, the user receives “No Choices Available.” message. User then enters “We%” the users receives results.

On other dashboards the steps describes above return results by entering “We” OR “we” into the “BEGIN WITH” matching functionality.

By default, Oracle database is case sensitive, this can be easily verified by run a query to test it, for example:

SELEECT PROD_NAME FROM PRODUCTS WHERE PROD_NAME LIKE ‘p%’
ORDER BY PRODNAME;

This query should not return any result as the first character for product name stored in database is upper case.

However, when using BI Answers, you may find some of the prompt with condition IS EQUAL TO/LIKE is case insensitive. This happens when

1. CASE_SENSITIVE_CHARACTER_COMPARISON = OFF is set (BIEE default setting) for Oracle Database in NQConfig.ini file.

2. The table is set cachable in BIEE Physical model.

So the data is cached in BI server is case insensitive, consequently the prompts retriving data from cacheable table is Case Insesitive, and the prompts retriveing from Non-cacheable table is Case Sesitive.

You also can turn off the cache in BIEE Administrator for the table in question, this can be done by double click on the table in Physical Model, click on General tab, uncheck Cacheable option for the table in question, save it. Restart BI Server. You then will find it is case sensitive, but for those tables set cacheable, they are still case insensitive.

It is recommended to set CASE SENSITIVE_CHARACTER_COMPARISON according to database searching behavior, so SENSITIVE_CHARACTER_COMPARISON=ON is set for Oracle Database in NQConfig.ini file to enforce consistent Case Sensitive searching behavior.
REF: [ID 862731.1]

Fmap Image Not Shown When Printing to PDF only – Fmap Function Showing White Space Instead – Image Can be Seeing in Dashboard

I also had this problem (Thank you very much people at Oracle’s support):

PDF OBIEE

When using the function @{fmap:images/my_image.jpg} no image is being returned on the PDF version of the report.

Image is properly shown in the dashboard screen.

It only happens with one Presentation Service and Web Server. The other one works fine.

No custom skins/styles used.

War file has already been redeployed.

Already have tested many things: paths, permissions, fmap syntax, war/ear webserver redeployment, invoking image url, copying image file from one server to the other, testing with different image files, smaller and bigger ones, testing with less data, using the fmap in different areas of the Dashboard (footer, narrative, title), verifying there is no custom style in Dashboard properties, etc.

Results are always the same: the image is shown in the dashboard but not in PDF print.

No error message reported in sawlog0.log.

Cannot see images when printing to pdf only. The image can be seen in the dashboard, and its direct URL shows the image fine too.

Is there a particular setting to get the images in PDF? A particular path PDF format uses that is different to the one used to show the image in the dashboard? Any additional path where the image needs to be copied? Something particular to Websphere?

Could you point us to where to look or how to get better detailed information on what is happening when printing to pdf when looking for the instance of the image? We have tried with different image files, very small ones, and copying the image that works from one server to the other. Customer also redeployed his war file after adding the image.

Could you give us any pointer to where to look or how to get better log info or how the printing to pdf looks for the image?

1. OBIEE Development provided the following answer for you:

Make sure the path to the file used by fmap is fully qualified (i.e. a complete path). The problem is likely because the PDF generation process which is being generated in the Javahost process, doesn’t have access to the same path.

The path to the file must be the same for both the Presentation Server, and also available to the Javahost process. If they’re on different servers then this will need to be a path that is common to both systems.

A fully qualified path would typically satisfy this requirement, but this really is dependent on the customers environment, and the permissions granted to each process. A webserver doesn’t necessarily have access to a fully qualified path, and the Javahost process may well have the same problem.

So the easy solution may be to duplicate the image file on the javahost server, and try to figure out a path where both the Presentation Server and Javahost server see the “same” file.

Please, review and follow it.

They also suggested to verify this image be downloaded to other file types. That is, it is shown when printing to html, or downloading to other file types.

Also, make sure adobe version to download to pdf is the same in both the environments. the one that works and the one that does not.

2. Additionally, you could, access the working environment via a web browser in Answers, open the HTML source code (like right-click and View Page Source (or View Image Info) in FireFox or View Source in MS IE) and search for the image.
you will see the HTML source like:
<img border=0 src=”analyticsRes/s_customSkin/mydirectory/my_image.jpg”> or
<img border=”0″ src=”res/s_oracle10/images/my.gif”>

Compare the values in both footers, for your working and not working environment.
The source of the image in the working environment should give a clue of the relative directory there. You could try to place the image in the same location on the non-working environment (see point 1 first).

3. Additionally, as a test, you could place the image in an accessible virtual directory via websphere (i.e. http://server/domain:port/virtualdir/../../mi_image.gif) and then in Answers, test with a URL mapping instead of the fmap. That is, try pointing the image as a URL instead of an fmap.

4. You could also look in the javahost log to search for errors thrown when the issue is reproduced.
They are all the log files in the following directory:

<install_drive>\OracleBIData\web\log\javahost

They are multiple and OBIEE rotates then.

5. Given that one environment is working and another is not, please, review your webserver/oc4j  mappings of both environments (in this case they were using Websphere). Moreover in relation to point 1. Websphere mappings and access given to process, fall outside of OBIEE scope.