OBIEE 11g - Excel Export Creates Additional Blank Columns For Some Sections Of Pivot Table

Scenario:

We have a report in Pivot table view where we are displaying the data in different sections. When i check the report on the dashboard it looks good but while exporting the same report in to Excel 2003 or 2007+ it creates some additional  blank columns for few or all sections. This issue exists in 11.1.1.7 + versions.

Root Cause:

 The difference in width of the columns used in the report are causing the issue.

Solution:

This issue is registered as bug in Oracle Support. The possible work around is, Identify the columns where you 

are getting the additional blank columns and change the size of the field in Columns, in the Criteria-->

Column Properties-->'Additional Formatting Options' to 200 (it is depending on the data size  in that column),

for instance, and then go to Results tab and export it, the .xlsx file does NOT contain any blank gap cell.

 

OBIEE11g: All the Column Values are not shown in Prompts drop down for Check Box, Radio Buttons and List Box

Scenario:

We have created a column prompt on a column which has about 450 values. When we click on the Prompt Drop down all the column values are not displayed in the prompt when we are using Check Box or Radio Buttons or List Box the same prompt is displaying all the values when we select the check boxes option. The prompt shows first 256 values only, there is no option to see or select the values other than these 256.
  

Solution:

1. Please make a backup copy of the instanceconfig.xml located in <INSTANCE_HOME>/config/OracleBIPresentationServicesComponent/coreapplication_obips1 folder.

2. Add or modify the following tag <MaxDefaultValues> into instanceconfig.xml and set a proper value (in the below example, it is set to 1000).
Also check the <MaxDropDownValues> value.

<Prompts>
<MaxDefaultValues>1000</MaxDefaultValues>
<MaxScrollValues>100</MaxScrollValues>
<MaxDropDownValues>10000</MaxDropDownValues>
</Prompts>

3. Restart Presentation Services.

OBIEE 11g: Issue with Excel 2007 Export - Merging Columns,Adding Extra Columns,Extra Blank Spaces, Cell Size Changes When Exporting to Excel 2007+

Scenario:

When you try to export a report in Excel 2007 or Excel 2007+ format, the report data and layout is not properly exported. We see difference when you compare the exported report with the report on the dashboard. Some of the formatting issues you will find are

1) Merged Columns
2) Cell format like large cell size
3) Some extra blank spaces
4) Extra columns which are not showing on the dashboard.

Root Cause:

This is a bug in 11.1.1.7 and later versions.

Solution:

We can fix this issue by making a small change to the config file xdo.cfg. Please follow the below steps to know how to make the changes.
 

1. Take a back up copy of <Middleware_Home>/instances/instance1/config/OracleBIJavaHostComponent/coreapplication_obijh1/xdo.cfg
   
2. Open xdo.cfg for editing.
   
3. Add the following: 
   
   <config version="1.0.0" xmlns="
     http://xmlns.oracle.com/oxp/config/">
         <properties>
          <property name="xlsx-keep-values-in-same-column">true</property>
         </properties>
     </config>
4.  Save your changes.
   
5. Restart all OBIEE Services (OPMN Services).

OBIEE 11g : Exporting Dashboard Page to Excel Error:"Excel found unreadable content in" FILE_NAME.xlsx. "Do you want to recover the contents of this workbook?"

Scenario:

When you try to export a dashboard page or entire dashboard from "Page Options" on a dashboard to excel 2007 or excel 2003 you can get the excel file with out any error, but when you try to open the exported excel file then you will get an error message related to unreadable content. The same happens when you create an agent to deliver the dashboard page. 

  Root Cause:

This issue may be caused when Dashboard Page name is using special characters that are not acceptable for Excel Sheet Name.
For example

 =

\
 /
 *
 ?
 :
 [
 ]  

& 

;

 Resolution:

Rename the dashboard page and avoid the special characters. Now export it to excel and test. You can see the excel file opening with out any errors and data will be shown as per the dashboard page.

OBIEE11g:PRINT TO PRINTABLE PDF ON THE DASHBOARD FAILS WITH ERROR CODES: ETI2U8FA

 

Scenario:

We are trying to Print a report which has around 1900 records from the dashboard using the option Printable PDF the process fails with below error message 

"An error occurred during execution of ""send"". Broken pipe [Socket:8]
Error Details
Location: saw.rpc.variablemos.write, sawfopProxy,saw.subsystem.portal.pdf 

Error Codes: ETI2U8FA"

When i try to pint a small report which has 150 records it processed printing successfully to PDF

 When i am doing the research on this error, i found that this error is most common with exporting PDF and Printing PDF. Especially when we try to export large Dashboards and Large reports.Printing reports with a smaller number of rows works successfully and No issues in exporting the same to CSV or Excel.This issue can be fixed by modifying the Java host Config file.

Please follow the steps as mentioned below.

Update the <obiee_home>\instances\instance1\config\OracleBIJavaHostComponent\coreapplication_obijh1\config.xml file with:

<MessageProcessor>
     <SocketTimeout>300000</SocketTimeout>
</MessageProcessor>

Along with:

 <XMLP>

<InputStreamLimitInKB>0</InputStreamLimitInKB>
<ReadRequestBeforeProcessing>true</ReadRequestBeforeProcessing>
</XMLP>

 

Note: After modifying the Config.xml file we should restart OPMN Services.

  

Useful TIMESTAMP Codes in OBIEE11g

Below Timestamp related codes are very very useful in Obiee. These codes are to be placed in the Column Formula (fx tab) or Can be used in Filter conditions in Criteria tab.

Current Date:

TIMESTAMPADD(SQL_TSI_DAY, 0, CURRENT_DATE)

Previous Date:


TIMESTAMPADD(SQL_TSI_DAY, -1, CURRENT_DATE)

Next Date:

TIMESTAMPADD(SQL_TSI_DAY, 1, CURRENT_DATE)

Current Month Number:

MONTH(CURRENT_DATE)

* Shows the results in Number format like 1,2,3 etc. Shows the month number.

Current Quarter Number:

QUARTER_OF_YEAR(CURRENT_DATE)


* Shows the results in Number format like 1,2,3 etc. Shows the month number.

Current Year Number:

YEAR(CURRENT_DATE)


* Shows the results in Number format like 2012,2013,2014 etc. Shows the month number.

Current Month Name:

MONTHNAME(CURRENT_DATE)

 * Shows the results in character format like Jan,Feb,Mar etc. Shows the month number.

Last Month Start Date:

TIMESTAMPADD(SQL_TSI_MONTH, -1, TIMESTAMPADD( SQL_TSI_DAY , DAYOFMONTH( CURRENT_DATE) * -(1) + 1, CURRENT_DATE))

Current Month Start Date:

TIMESTAMPADD(SQL_TSI_MONTH, 0, TIMESTAMPADD( SQL_TSI_DAY , DAYOFMONTH( CURRENT_DATE) * -(1) + 1, CURRENT_DATE))

Next Month Start Date:

TIMESTAMPADD(SQL_TSI_MONTH, 1, TIMESTAMPADD( SQL_TSI_DAY , DAYOFMONTH( CURRENT_DATE) * -(1) + 1, CURRENT_DATE))

Current Year Start Date:

TIMESTAMPADD( SQL_TSI_DAY , EXTRACT( DAY_OF_YEAR FROM CURRENT_DATE) * -(1) + 1, CURRENT_DATE)

 Last Year Start Date:

TIMESTAMPADD( SQL_TSI_YEAR , -1, TIMESTAMPADD( SQL_TSI_DAY , EXTRACT( DAY_OF_YEAR FROM CURRENT_DATE) * -(1) + 1, CURRENT_DATE))

Current Month End Date:

 TIMESTAMPADD( SQL_TSI_DAY , -1, TIMESTAMPADD( SQL_TSI_MONTH , 1, TIMESTAMPADD( SQL_TSI_DAY , DAYOFMONTH( CURRENT_DATE) * -(1) + 1, CURRENT_DATE)))

Current Year End Date:

TIMESTAMPADD(SQL_TSI_YEAR, 1, TIMESTAMPADD( SQL_TSI_DAY , -1, TIMESTAMPADD( SQL_TSI_DAY , EXTRACT( DAY_OF_YEAR FROM CURRENT_DATE) * -(1) + 1, CURRENT_DATE)))

Last Year End Date:

TIMESTAMPADD( SQL_TSI_DAY , -1, TIMESTAMPADD( SQL_TSI_DAY , EXTRACT( DAY_OF_YEAR FROM CURRENT_DATE) * -(1) + 1,CURRENT_DATE))

How to work with multiple value select prompt in obiee11g Direct Database Request

Scenario:

You have a dashboard prompt with multi-select option and a presentation variable set. You have a report with a field using a filter formula based on the presentation variable.  When multiple values are selected in the dashboard prompt, the query generated is incorrect. The query should select the multiple values selected by a comma. Instead of the results, you get this message:

No Results

The specified criteria didn't result in any data. This is often caused by
applying filters and/or selections that are too restrictive or that contain
incorrect values. Please check your Analysis Filters and try again. The
filters currently being applied are shown below.

This occurs when the Filter SQL function is used in a column formula within a request; for example:

FILTER("Base Facts"."1- Revenue" USING ("Products"."P4 Brand" IN ('@{var_mlt}{FunPod}')))

It does not occur if a standard filter is applied to the analysis.  The incorrect SQL produced is, for example

[2012-02-07T14:47:34.000+00:00] [OracleBIServerComponent] [TRACE:3] [USER-18]
[] [ecid: 20f12d143003324e:d29df33:13558086f8a:-8000-000000000000084b] [tid:
a30] [requestid: 33990013] [sessionid: 33990000] [username: weblogic]
-------------------- Sending query to database named 01 - Sample App Data
(ORCL) (id: <>), connection pool named Sample Relational Connection:
[[
WITH
SAWITH0 AS (select sum(T42442.Revenue) as c1,
T42406.PER_NAME_YEAR as c2
from
SAMP_PRODUCTS_D T42409 /* D10 Product (Dynamic Table) */ ,
SAMP_TIME_QTR_D T42406 /* D03 Time Quarter Grain */ ,
SAMP_REVENUE_FA2 T42442 /* F21 Rev. (Aggregate 2) */
where ( T42406.QTR_KEY = T42442.Bill_Qtr_Key
and T42409.Brand = 'BizTech,FunPod,HomeView'
and T42409.Prod_Key = T42442.Prod_Key )
group by T42406.PER_NAME_YEAR)
select distinct 0 as c1,
D1.c2 as c2,
D1.c1 as c3
from
SAWITH0 D1
order by c2

The problem is that the multiple values are passed in a single string to the physical SQL, and the operator used is "=", when it must be "IN":

and T42409.Brand = 'BizTech,FunPod,HomeView'

 instead of:

and T42409.Brand in ('BizTech','FunPod,HomeView')

 

However, if you create a report filter like this, you get the correct values:

P4 Brand is equal to / is in @{var_mlt}{FunPod}

 

Root Cause of the Issue:

 

Incorrect syntax used in the 'Filter' option, 'Criteria' tab. There is a specific format of syntax that should be used when you want to work with Presentation Variable.

Solution:

You need to make sure the syntax is correct.


Review the correct syntax to use in the Advanced SQL in a filter to reference a presentation variable with potentially multiple values. The syntax differs slightly depending on the column type.

Text column:

Select T1.C1 from SA where T1.TextColumn in (@{myTextVar}['@']{'Default'})

Numeric column:

Select T1.C1 from SA where T1.NumberColumn in (@{myNumVar}{1000})

Date Time column:

Select T1.C1 from SA where T1.DateTimeColumn in (@{myDateTimeVar}{timestamp '2000-01-21 00:00:00'})

Date-Only column:

Select T1.C1 from SA where T1.DateColumn in (@{myDateVar}{date '2000-01-21'})

Time-Only column:

Select T1.C1 from SA where T1.TimeColumn in (@{myTimeVar}{time '00:00:00'})

 

Applies to:

Business Intelligence Suite Enterprise Edition - Version 11.1.1.5.0 [1308] and later

Business Intelligence Server Enterprise Edition - Version 11.1.1.5.0 [1308] and later

Applies to any platform. 

 

References

OBIEE 11g: Presentation Variable With Multi Value Selected Gives 'No Results' and Wrong Query Generated. (Doc ID 1418868.1)

BUG:13696529 - INCORRECT PHYSICAL QUERY WHEN USING FILTER AND A PRESENTATION VARIABLE MULTIPLE