Archive

Posts Tagged ‘concatenation’

Few Oracle’s support treasures

December 12th, 2010 No comments

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:

smiley%20face Few Oracles support treasures

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.

CONCAT function

September 29th, 2008 No comments

Concat- this useful function is often overlooked in BI. However, by using it – you can make your routine work much easier (at both BMM and Presentation layers of OBIEE).

There are two ways of using this function in OBIEE. The first method is to simply concatenate two character strings. The second method uses the character string concatenation character (||) to concatenate more than two character strings. You can also reuse the CONCAT function in OBIEE, but this might be an “excessive use of force”. Please note, you can only concatenate CHAR/VACHAR strings *you can use CAST function to convert from other data type*.

Method 1.

CONCAT (expression1, expression2)

where:

character_expression Expressions that evaluate to character strings (if numeric you can use CAST function).

For example – CONCAT (‘The year is ‘, CAST(TimeTable.FiscalYear AS CHAR))

Method 2:

CONCAT (expression1 || expression2 || expression3 and so forth)

where:

Very much like the previous one – expressions that evaluate to character strings, separated by the concatenation operator || (double vertical bars).

For example – CONCAT (‘Total is: $’ || SalesFact.TotalSales ||  ‘USD’)
I was having trouble using the CONCAT function type 2 expression in the Business Layer of the Analytics Admin Tool (using multiple expressions which evaluate to strings). The problem seemed to happen with functions such as
CONCAT (“DimTable”.”Org”.”STATE” ||”DimTable”.”Org”.”CITY” ||”DimTable”.”Org”.”ADDRESS”) and the error message is:   [nQSError: 27002] Near <)>: Syntax error [nQSError: 26012] .
The workaround seems to be using a nested CONCAT statement – CONCAT (CONCAT(“DimTable”.”Org”.”STATE” ,”DimTable”.”Org”.”CITY”),”DimTable”.”Org”.”ADDRESS”) Hopefully, it’ll be fixed in the OBIEE release 11g