Friday Feb 28, 2014

Waterfall Charts

Great question came through the ether from Holger on waterfall charts last night.

"I know that Answers supports waterfall charts and BI Publisher does not.
Do you have a different solution approach for waterfall charts with BI Publisher (perhaps stacked bars with white areas)?
Maybe you have already implemented something similar in the past and you can send me an example."

I didnt have one to hand, but I do now. Little known fact, the Publisher chart engine is based on the Oracle Reports chart engine. Therefore, this document came straight to mind. Its awesome for chart tips and tricks. Will you have to get your hands dirty in the chart code? Yep. Will you get the chart you want with a little effort? Yep. Now, I know, I know, in this day and age, you should get waterfalls with no effort but then you'd be bored right?

First things first, for the uninitiated, what is a waterfall chart? From some kind person at Wikipedia, "The waterfall chart is normally used for understanding how an initial value is affected by a series of intermediate positive or negative values. Usually the initial and the final values are represented by whole columns, while the intermediate values are denoted by floating columns. The columns are color-coded for distinguishing between positive and negative values."

We'll get back to that last sentence later, for now lets get the basic chart working.

Checking out the Oracle Report charting doc, search for 'floating' their term for 'waterfall' and it will get you to the section on building a 'floating column chart' or in more modern parlance, a waterfall chart. If you have already got your feet wet in the dark arts world of Publisher chart XML, get on with it and get your waterfall working.

If not, read on.

When I first starting looking at this chart, I decided to ignore the 'negative values' in the definition above. Being a glass half full kind of guy I dont see negatives right :)

Without them its a pretty simple job of rendering a stacked bar chart with 4 series for the colors. One for the starting value, one for the ending value, one for the diffs (steps) and one for the base values. The base values color could be set to white but that obscures any tick lines in the chart. Better to use the transparency option from the Oracle Reports doc.

<Series id="0" borderTransparent="true" transparent="true"/> 

Pretty simple, even the data structure is reasonably easy to get working. But, the negative values was nagging at me and Holger, who I pointed at the Oracle Reports doc had come back and could not get negative values to show correctly. So I took another look. What a pain in the butt!

In the chart above (thats my first BIP waterfall maybe the first ever BIP waterfall.) I have lime green, start and finish bars; red for negative and green for positive values. Look a little closer at the hidden bar values where we transition from red to green, ah man, royal pain in the butt! Not because of anything tough in the chart definition, thats pretty straightforward. I just need the following columns START, BASE, DOWN, UP and FINISH. 

START 200
BASE 0
UP 0
DOWN 0
FINISH 0
START 0
BASE 180
UP 0
DOWN 20
FINISH 0
START 0
BASE 150
UP 0
DOWN 30
FINISH 0

 Bar 1 - Start Value
 Bar 2 - PROD1
 Bar 3 - PROD2

and so on. The start, up, down and finish values are reasonably easy to get. The real trick is calculating that hidden BASE value correctly for that transition from -ve >> + ve and vice versa. Hitting Google, I found the key to that calculation in a great page on building a waterfall chart in Excel from the folks at Contextures.  Excel is great at referencing previous cell values to create complex calculations and I guess I could have fudged this article and used an Excel sheet as my data source. I could even have used an Excel template against my database table to create the data for the chart and fed the resulting Excel output back into the report as the data source for the chart. But, I digress, that would be tres cool thou, gotta look at that.
On that page is the formula to get the hidden base bar values and I adapted that into some sql to get the same result.

Lets assume I have the following data in a table:

PRODUCT_NAME SALES
PROD1 -20
PROD2 -30
PROD3 50
PROD4 60

The sales values are versus the same period last year i.e. a delta value.  I have a starting value of 200 total sales, lets assume this is pulled from another table.
I have spent the majority of my time on generating the data, the actual chart definition is pretty straight forward. Getting that BASE value has been most tricksy!

I need to generate the following for each column:

PRODUCT_NAME

STRT

BASE_VAL

DOWN

UP

END_TOTAL

START
200
0
0
0
0
PROD1
0
180
20
0
0
PROD2
0
150 30 0
0
PROD3
0 150 0 50 0
PROD4
0 200
0 60 0
END
0 0 0 0 260

Ignoring the START and END values for a second. Here's the query for the PRODx columns:

 SELECT 2 SORT_KEY 
, PRODUCT_NAME
, STRT
, SALES
, UP
, DOWN
, 0 END_TOTAL
, 200 + (SUM(LAG_UP - DOWN) OVER (ORDER BY PRODUCT_NAME)) AS BASE_VAL
FROM
(SELECT P.PRODUCT_NAME
,  0 AS STRT
, P.SALES
, CASE WHEN P.SALES > 0 THEN P.SALES ELSE 0 END AS UP  
, CASE WHEN P.SALES < 0 THEN ABS(P.SALES) ELSE 0 END AS DOWN
, LAG(CASE WHEN P.SALES > 0 THEN P.SALES ELSE 0 END,1,0) 
      OVER (ORDER BY P.PRODUCT_NAME) AS LAG_UP
FROM PRODUCTS P
)

The inner query is breaking the UP and DOWN values into their own columns based on the SALES value. The LAG function is the cool bit to fetch the UP value in the previous row. That column is the key to getting the BASE values correctly.

The outer query just has a calculation for the BASE_VAL.

200 + (SUM(LAG_UP - DOWN) OVER (ORDER BY PRODUCT_NAME))

The SUM..OVER allows me to iterate over the rows to get the calculation I need ie starting value (200) + the running sum of LAG_UP - DOWN. Remember the LAG_UP value is fetching the value from the previous row.
Is there a neater way to do this? Im most sure there is, I could probably eliminate the inner query with a little effort but for the purposes of this post, its quite handy to be able to break things down.

For the start and end values I used more queries and then just UNIONed the three together. Once note on that union; the sorting. For the chart to work, I need START, PRODx, FINISH, in that order. The easiest way to get that was to add a SORT_KEY value to each query and then sort by it. So my total query for the chart was:

SELECT 1 SORT_KEY
, 'START' PRODUCT_NAME
, 200 STRT
, 0 SALES
, 0 UP
, 0 DOWN
, 0 END_TOTAL
, 0 BASE_VAL
FROM PRODUCTS
UNION
SELECT 2 SORT_KEY 
, PRODUCT_NAME
, STRT
, SALES
, UP
, DOWN
, 0 END_TOTAL
, 200 + (SUM(LAG_UP - DOWN) 
      OVER (ORDER BY PRODUCT_NAME)) AS BASE_VAL
FROM
(SELECT P.PRODUCT_NAME
,  0 AS STRT
, P.SALES
, CASE WHEN P.SALES > 0 THEN P.SALES ELSE 0 END AS UP  
, CASE WHEN P.SALES < 0 THEN ABS(P.SALES) ELSE 0 END AS DOWN
, LAG(CASE WHEN P.SALES > 0 THEN P.SALES ELSE 0 END,1,0) 
       OVER (ORDER BY P.PRODUCT_NAME) AS LAG_UP
FROM PRODUCTS P
)
UNION
SELECT 3 SORT_KEY 
, 'END' PRODUCT_NAME
, 0 STRT
, 0 SALES
, 0 UP
, 0 DOWN
, SUM(SALES) + 200 END_TOTAL
, 0 BASE_VAL
FROM PRODUCTS
GROUP BY 1,2,3,4,6
ORDER BY 1 

A lot of effort for a dinky chart but now its done once, doing it again will be easier. Of course no one will want just a single chart in their report, there will be other data, tables, charts, etc. I think if I was doing this in anger I would just break out this query as a separate item in the data model ie a query just for the chart. It will make life much simpler.
Another option that I considered was to build a sub template in XSL to generate the XML tree to support the chart and assign that to a variable. Im sure it can be done with a little effort, I'll save it for another time.

On the last leg, we have the data; now to build the chart. This is actually the easy bit. Sadly I have found an issue in the online template builder that precludes using the chart builder in those templates. However, RTF templates to the rescue!

Insert a chart and in the dialog set up the data like this (click the image to see it full scale.)

Its just a vertical stacked bar with the BASE_VAL color set to white.You can still see the 'hidden' bars and they are over writing the tick lines but if you are happy with it, leave it as is. You can double click the chart and the dialog box can read it no problem. If however, you want those 'hidden' bars truly hidden then click on the Advanced tab of the chart dialog and replace:

<Series id="1" color="#FFFFFF" />

with

<Series id="1" borderTransparent="true" transparent="true" />

and the bars will become completely transparent. You can do the #D and gradient thang if you want and play with colors and themes. You'll then be done with your waterfall masterpiece!

Alot of work? Not really, more than out of the box for sure but hopefully, I have given you enough to decipher the data needs and how to do it at least with an Oracle db. If you need all my files, including table definition, sample XML, BIP DM, Report and templates, you can get them here.

Monday Feb 24, 2014

Wildcard Filtering continued

I wrote up a method for using wildcard filtering in your layouts a while back here. I spotted a followup question on that blog post last week and thought I would try and address it using another wildcard method. 

I want to use the bi publisher to look for several conditions using a wild card. For example if I was sql it would look like this:

if name in ('%Wst','%Grt')

How can I utilize bi publisher to look for the same conditions.

This, in XPATH speak is an OR condition and we can treat it as such. In the last article I used the 'starts-with' function, its a little limiting, there is a better one, 'contains'. This is a much more powerful function that allows you to look for any string within another string. Its case insensitive so you do not need to do upper or lowering of the string you are searching to get the desired results.
Here it is in action:

For the clerks filter I use :

<?for-each-group:G_1[contains(JOB_TITLE,'Clerk')];./JOB_TITLE?>

and to find all clerks and managers, I use:

<?for-each-group:G_1[contains(JOB_TITLE,'Clerk') or contains(JOB_TITLE,'Manager')];./JOB_TITLE?>

Note that Im using re-grouping here, you can use the same XPATH with a regular for-each. Also note the lower case 'or' in the second expression. You can also use an 'and' too.

This works in 10 and 11g flavors of BIP. Sample files available here.

Monday Feb 03, 2014

Memory Guard

Happy New ... err .. Chinese Year! Yeah, its been a while, its also been danged busy and we're only in February, just! A question came up on one of our internal mailing lists concerning out of memory errors. Pieter, support guru extraordinaire jumped on it with reference to a support note covering the relatively new 'BI Publisher Memory Guard'. Sounds grand eh?

As many a BIP user knows, at BIP's heart lives an XSLT engine. XSLT engines are notoriously memory hungry. Oracle's wee beastie has come a long way in terms of taming its appetite for bits and bytes since we started using it. BIP allows you to take advantage of this 'scalable mode.' Its a check box on the data model which essentially says 'XSLT engine, stop stuffing your face with memory doughnuts and get on with the salad and chicken train for this job' i.e. it gets a limited memory stack within which to work and makes use of disk, if needed, think Windows' 'virtual memory'.

Now that switch is all well and good, for a known big report that you would typically mark as 'schedule only.' You do not want users sitting in front of their screen waiting for a 10,000 page document to appear, right? How about those reports that are borderline 'big' or you have a potentially big report but expect users to filter the heck out of it and they choose not to? It would be nice to be able to set some limits on reports in case a user kicks off a monster donut binge session. Enter 'BI Publisher Memory Guard'!

It essentially lets you set those limits on memory and report size so that users can not run a report that brings the server to its knees. More information on the support web site, search for 'BI Publisher Memory Guard a New Feature to Prevent out-of-memory Errors (Doc ID 1599935.1)' or you can get Leslie's white paper covering the same here.

Wednesday Aug 07, 2013

Siebel BIP Tuning

Another brain dump from John, the product manager for the Siebel-BIP integration.

These docs focus on the out of the box integration. Where Siebel holds the reins and just sends the BI Publiser server publishing requests. For this you use Integration Objects to generate the data. This support doc contains a white paper on performance testing of the IOs and provides some standard tests that you can compare your system to.

Siebel CRM BI Publisher Integration Performance[Article ID 1466709.1]

I have pulled the white paper out of support to save you some time (cos Im kind like that.) Of course the latest and greatest will be on http://support.oracle.com

The following support doc covers the tuning of said objects to handle larger data sets.

Improving the performance of Siebel BI Publisher Report Generation (Doc ID 1392449.1)

I have linked a converted PDF of the doc as of today, access http://support.oracle com and search via the Doc Id for the latest and greatest.

For completeness of the post, heres a link to post on the Siebel-BIP Business Service Integration.

Friday Mar 23, 2012

Updating a database connection password using a script

An interesting customer requirement that I thought was worthy of sharing today. Thanks to James for the requirement and Bryan for the proposed solution and me for testing the solution and proving it works :0)

A customers implementation of Sarbanes Oxley requires them to change all database account passwords every 90 days. This is scripted leveraging shell scripts today for most of their environments. But how can they manage the BI Publisher connections?

Now, the customer is running 11g and therefore using weblogic on the middle tier, which is the first clue to Bryans proposed solution. To paraphrase and embellish Bryan's solution a little; why not use a JNDI connection from BIP to the database. Then employ the web logic scripting engine to make updates to the JNDI as needed? BIP is completely uninvolved and with a little 'timing' users will be completely unaware of the password updates i.e. change the password when reports are not being executed.

Perfect! James immediately tracked down the WLST script that could be used here, http://middlewaremagic.com/weblogic/?p=4261 (thanks Ravish)

Now it was just a case of testing the theory. Some steps:

  1. Create the JNDI connection in WLS
  2. Create the JNDI connection in BI Publisher pointing to the WLS connection
  3. Build new data models using or re-point data sources to use the JNDI connection.
  4. Create the WLST script to update the WLS JNDI password as needed.
  5. Test!

Some details.

  1. Creating the JNDI connection in web logic is pretty straightforward.
    1. Log into hte console and look for Data Sources under the Services section of the home page and click it
    2. Click New >> Generic Datasource
    3. Give the connection a name. For the JNDI name, prefix it with 'jdbc/' so I have 'jdbc/localdb' - this name is important you'll need it on the BIP side.
      Select your db type - this will influence the drivers and information needed on the next page. Being a company man, Im using an Oracle db.
      Click Next
    4. Select the driver of choice, theres lots I know, you can read about them I just chose 'Oracle's Driver (Thin) for Instance connections; Versions 9.0.1 and later'
      Click Next >> Next
    5. Fill out the db name (SID), server, port, username to connect and password >> Next
    6. Test the config to ensure you can connect. >> Next
    7. Now you need to deploy the connection to your BI server, select it and click Next.
      You're done with the JNDI config.
  2. Creating the JNDI connection on the Publisher side is covered here. Just remember to the connection name you created in WLS e.g. 'jdbc/localdb'
  3. Not gonna tell you how to do this, go read the user guide :0) Suffice to say, it works.
  4. This requires a little reading around the subject to understand the scripting engine and how to execute scripts. Nicely covered here. However a bit of googlin' and I found an even easier way of running the script.

    ${ServerHome}/common/bin/wlst.sh updatepwd.py

Where updatepwd.py is my script file, it can be in another directory. As part of the wlst.sh script your environment is set up for you so its very simple to execute.

The nitty gritty:

  1. Need to take Ravish's script above and create a file with a .py extension.
  2. Its going to need some modification, as he explains on the web page, to make it work in your environment. I played around with it for a while but kept running into errors.
    The script as is, tries to loop through all of your connections and modify the user and passwords for each. Not quite what we are looking for. Remember our requirement is to just update the password for a given connection.

    I also found another issue with the script. WLS 10.x does not allow updates to passwords using clear type ie un-encrypted text while the server is in production mode. Its a bit much to set it back to developer mode bounce it, change the passwords and then bounce and then change back to production and bounce again.
    After lots of messing about I finally came up with the following:

#############################################################################
#
#  Update password for JNDI connections
#
#############################################################################



print("*** Trying to Connect.... *****")
connect('weblogic','welcome1','t3://localhost:7001')
print("*** Connected *****")
edit()
startEdit()


print ("*** Encrypt the password ***")
en = encrypt('hr')
print "Encrypted pwd: ", en

print ("*** Changing pwd for LocalDB ***")
dsName = 'LocalDB'

print 'Changing Password for DataSource ', dsName
cd('/JDBCSystemResources/'+dsName+'/JDBCResource/'+dsName+'/JDBCDriverParams/'+dsName)
set('PasswordEncrypted',en)

save()
activate()


Its pretty simple and you can expand on it to loop through the data sources and change each as needed. I have hardcoded the password into the file but you can pass it as a parameter as needed using the properties file method. Im not going to get into the detail of that here but its covered with an example here.

Couple of points to note:
1. The change to the password requires a server bounce to get the changes picked up. You can add that to the shell script you will use to call the script above.
2. The script above needs to be run from the MW_HOME\user_projects\domains\bifoundation_domain directory to get the encryption libraries set correctly. My command to run the whole script was:

d:\oracle\bi_mw\wlserver_10.3\common\bin\wlst.cmd updatepwd.py

- where wlst.cmd is the scripting command line and updatepwd.py was my update password script above.

I have not quite spoon fed everything you need to make it a robust script but at least you know you can do it and you can work out the rest I think :0)

Wednesday Feb 29, 2012

Conditional Charting II

A follow up post on yesterdays efforts. After pinging a few colleagues Klaus came up with a much much neater approach that appeals to my sense of easy, straightforward and neat, when it comes to code anyway. A by product of the approach, it can handle, none, one or multiple conditonal bars.

So, no variables ... a big plus, some chart xml editing, not quite so good but the benefits far outweigh the costs. Its a case of digging into the chart code and maybe Klaus (who is in charge of the template builders) will one day, provide this conditionality via the chart dialogs ... pleeeease Klaus :0)

Heres the relevant snippet of the chart code:

<Graph seriesEffect="SE_AUTO_GRADIENT">
 <LegendArea visible="false" />
 <ExceptionalRisers>
  <xsl:for-each select=".//Row"  xmlns:xsl="
http://www.w3.org/1999/XSL/Transform">
  <xsl:if test="number(number(.//Value) div number(.//Target))&lt; number($pLim)">
   <ExceptionalRiser series="0" group="{position()-1}" fill border/> 
  </xsl:if>
  </xsl:for-each>
 </ExceptionalRisers>
 <LocalGridData>
...
</Graph> 

We essentially, build the multiple ExceptionalRiser entries in the graph code itself rather than try and build out a concatentated string variable. The string approach was my first thought but 1. XSL does not allow XML strings to be built dynamically and 2. the chart does not break if there are no conditional columns to be rendered.
Dealing with the code a line at a time
  <xsl:for-each select=".//Row"  xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
Loop over the data as we did before
  <xsl:if test="number(number(.//Value) div number(.//Target))&lt; number($pLim)">
Look for percentages less than a specified value. I externalized this value into a variable (pLim) to make things easier to test, you can play with it in the template.
   <ExceptionalRiser series="0" group="{position()-1}" fill border/> 
If we find a row of data that meets the criteria we create an ExceptionalRiser entry and use the current record number (position()) minus 1 because out bars are numbered from zero.
Then we just close out the if, the for-each and we're done.
If the if statement does not return true at all i.e. no bars need to be colored; the chart just renders as normal, nice!

Klaus took his example a little further than mine. The one thing that my approach suffers from is that it requires the engine to loop over the data twice for the chart. Once to search for ExceptionalRisers and again to render the chart itself. Klaus took an aproach to create a variable to hold the chart data first and then  run the riser code and chart rendering against that. Check that out in the second template (ConditionalChart2-1.rtf) please note, that its an 11g template and will need the 11g plugin to see it running. Both available and with sample datasets here.

You can now conditionally color your bar charts, horizontal or vertical. I have tried the same with pie charts but no banana so I'm assuming its only going to work for bar charts. I have tested on 10.1.3.4.x and 11g releases. 

Wednesday Feb 22, 2012

11.1.1.6 is out ...

I know, I'm a day or two late ... blame my travel schedule. Get 11.1.1.6 here.

Some highlights

  • Improved Create Report Flow - really neat UI to walk you through the report creation process. Old pros dont worry you can circumvent it.
  • Enhancements to Parameter Display Options
    • Support for radio button and check box display of values
    • New options for placement of parameters in the report viewer
    • New report viewer toolbar button to show or hide parameters
    • Search added to menus
    • Option to remove the Apply button
  • Support for HTML Formatting in Data - out of the box support to automatically format your XHTML data inside your BIP output
  • Support for PDF/A and PDF/X Output
  • Edit Scheduled Jobs - what it says on the tin
  • Event-Driven Schedules - ohhhh yeah! This is a great step for creating triggers to automatically execute a BIP job. Looking forward to playing with this.

 For more details check out the New Features sections in the 11.1.1.6 documents here.

Monday Jan 09, 2012

BI Publisher January patch for 11.1.1.5.0

The BI Publisher January patch for 11.1.1.5.0 is now available on Metalink; look for patch 13554951. Thanks to Shinji and team, this is one of the easiest posts of the year so far :0) Contents of the readme below, enjoy!


One-off patch for Oracle Business Intelligence Publisher 11.1.1.5.0 READ ME File ==================================================
This patch contains fixes of following bugs for Oracle Business Intelligence Publisher 11.1.1.5.0.

 9102877 - QA: QUERY BUILDER GENERATES INCORRECT SQL FOR MANY DBS
 10071370 - IF 'USE SECURE FTP' IS CHECKD ON ADMIN PAGE, ITS SERVER IS NOT LISTD WHEN SCHED
 10402890 - SETTINGS FROM "DELIVERY CONFIGURATION" PAGE NOT BEING HONORED WHEN REPORTS RUN
 11725468 - ER: RTE HTML SUPPORT IN DATA XML
 11828672 - QA: INCORRECT INACTIVE WARNING MESSAGE
 11835845 - 11G SQL DATA SET CREATION ORA-01756 ERROR BECAUSE COLON IS READ AS BIND VARIABLE
 11850902 - SHARE REPORT LINK "NO PARAMETERS" AND "DOCUMENT ONLY" RENDER INCORRECTLY
 11870449 - BI PUBLISHER XDOXSLT:SDIV() FUNCTION RESULTS NOT CORRECT
 12312539 - QA: REPORT VIEWER SCREEN FREEZES FOR A FLEX REPORT WHEN SESSION EXPIRES
 12376386 - REMOVETEMPLATEFORREPORT API DOESN'T REMOVE TEMPLATE
 12405904 - CANNOT DEPLOY BIP WHEN DEPLOYMENT PATH INCLUDES SPACE CHARACTERS
 12406034 - CANNOT USE JAVA DB - DERBY - FOR SCHEDULER DATABASE
 12570248 - PROJECTS : -GENERATE REV/INV PROCESS ERRORING OUT
 12588342 - DELETEOBJECT RANDOM FAILURE
 12595931 - WHEN PRINTING CHARTS TO PDF, NUMBERS BECOME ARABIC NUMBERS
 12615821 - MISSING MEMBERS ON THE XML GENERATED BY BIP
 12634030 - RE-PACKAGING MLR#11893189 TO XDOPARSER11G.JAR
 12646493 - ORA-22902 CURSOR EXPRESSION NOT ALLOWED
 12666066 - ER: EXCEL 2007 SUPPORT IN REST API
 12705244 - JOINED MDX DATA SETS DO NOT PRODUCE CORRECT DATA - DATA MISSING
 12726163 - NUMBER OF SESSIONS INCREASES AFTER RUNNING SCHEDULED JOBS
 12737670 - GROUP BY FOR MDX DATA SET MISSING IN 11.1.1.5 - WAS PRESENT IN 11.1.1.3
 12741858 - BI ASNWERS ANALYSIS IMPLEMENTING CASE WHEN STATEMENTS CAN NOT BE CONSUMED IN BIP
 12750192 - ER: GENERATE NATIVE COMBINATION DUAL Y GRAPH IN PPTX OUTPUT FROM RTF AND XPT
 12766932 - VERTICAL DUAL Y COMBO CHART MISSING SETTINGS TO ASSIGN VALUES TO AXIS
 12775400 - DBFW:GENERATING IN XLXS OUTPUT IS THROWING AN ERROR
 12822514 - SQL WITH CLAUSE - DATASET DOESN'T WORK (AND DIAGNOSTIC ISN'T HELPFUL)
 12828845 - ISSUE WITH XDO_USER_NAME PARAMETER ON BIP 11.1.1.5.0
 12835431 - INTRANSIT VALUATION REPORT QUERY GIVING ERROR IN EDITOR
 12847306 - BI PUBLISHER DATA SET - WON'T SAVE
 12872831 - EXCEL TEMPLATES GENERATING INCORRECT RESULTS FROM NESTED GROUPS
 12879801 - ALLOW SAVING DATA MODEL WITH SPECIAL CHARACTER
 12945367 - NOT ABLE TO EDIT DATA MODEL WHEN LINKS EXIT AMONG DATA SETS
 12945475 - IE: NOT ABLE TO EDIT DATA MODEL WITH MANY DATA SET AND LINKS
 12956901 - IN SQL SERVER CLOB IN DATA MODEL UI MARKED AS STRING DATA TYPE
 12956915 - INCORRECT DATA WHEN PROCESSING OF CLOB COLUMNS FOR SQLSERVER
 12956934 - DATA MODEL SHOULD RESPECT CASE SENSITIVITY FOR COLUMN AND TABLE NAMES
 12972559 - XML DATA FILE TRUNCATED
 12972852 - DYNAMIC IMAGES BROKEN FOR FO/PDF
 12989467 - NEED TO CONVERT DOCUMENT FORMAT "EXCEL" TO "EXCEL2000"
 13005659 - PSR:PERF:BIP:POOR PERF ACCESSING CATALOG FOLDER IN BIP 11G WITH SECURITY ENABLED 
 13007341 - DISPLAY ISSUES IN PARAMETER TAB OF OBI PUBLISHER 11G
 13095050 - STRESS: OUTOFMEMORY ERRORS WITH VIEW REPORT STRESS
 13100021 - BI PUBLISHER: QUERY BUILDER RESULTS RETURN COLUMN TYPE (APPEARS) INSTEAD VALUE
 13100220 - HOW TO USE 11.1.1.5 WITH THE IDENTITY ASSERTER
 13250009 - FOR METADATA LOGICAL SQL BIND WITH DUMMY VALUES INSTEAD OF NULL
 13321449 - RESOURCELIMITEXCEPTION DURING ANY OPS THAT USE JNDI DATA SOURCE
 13346737 - MULTIPLE VALUES IN A DASHBOARD PROMPT DOESN'T WORK WITH BI PUBLISHER INTEGRATED
 13351305 - QUERY BUILDER DOES NOT BUILD CORRECT SYNTAX FOR SQL SERVER
 13393658 - PRINTER DELIVERY [0] STATUS: FAILED PRINTING FROM WEB SERVICE CALL FROM EXTERNAL
 13421264 - ADDING PARAMETER TO SQL BI SERVER DATA SET CORRUPTS DATA MODEL
 13481659 - CAN'T SAVE ON BI DEV ENV RC2
 13501838 - ORA-00923 ISSUE WHEN CREATING A BI PUBLISHER DATASET 
 13521951 - BIP UPGRADE FROM 10G TO 11.1.1.5.0 IS NOT SUCCESSFULL FOR TIAA-CREF
 13529494 - BI REPORT NOT GENERATED WHEN HTML CONTAINS INVALID ATTRIBUTES
----------------------------- INSTALLATION FOR ENTERPRISE -----------------------------
1. Login to BI Domain Weblogic Administration Console.
2. Find the location of the "bipublisher" application.
3. Stop the bipublisher application.
4. Open a terminal and go to the directory.
5. Back up the existing xmlpserver.ear to xmlpserver.ear.org.
6. Copy new xmlpserver.ear from this patch.
7. In Admin Console, select "Lock & Edit".
8. Select the application "bipublisher" and "Update".
9. Leave all options same and proceed to Finish.
10. Select "Activate Changes".
11. Start the bipublisher application.

------------------------- MDX Query Limitation -------------------------
Joins of two MDX queries on a dimension that uses a MDX function in the Select Clause that requires the stipulation of the same dimension, such as Intersect, MemberRange, Union are not supported. Example)
SELECT
  NON EMPTY Hierarchize([Product].Generations(3).Members) ON Axis(0),
  Hierarchize(Union([Market].Generations(3).Members, [Market].Generations(4).Members, ALL)) ON Axis(1)
FROM Demo.Basic
It should be written using a With Set member:
with set [UnionMarket] as '{Union([Market].Generations(3).Members, [Market].Generations(4).Members, ALL)}'
SELECT
  NON EMPTY Hierarchize([Product].Generations(3).Members) ON Axis(0),
  Hierarchize(([UnionMarket]) ON Axis(1)
FROM Demo.Basic 
This MDX set is recreated for the Join clause to:
with set [UnionMarket] as '{Union([Market].Generations(3).Members, [Market].Generations(4).Members, ALL)}'
SELECT
  NON EMPTY Hierarchize([Product].Generations(3).Members) ON Axis(0),
  Hierarchize(Filter([UnionMarket], [Market].CurrentMember.Member_Name = "Boston")) ON Axis(1)
FROM Demo.Basic
----------------------------------------- Query Builder limitation with Microsoft SQL Server -----------------------------------------
Query Builder doesn't support a cross join that does not have a WHERE clause and produces the Cartesian product of the tables involved in the join. The size of a Cartesian product result set of such join is the number of rows in the first table multiplied by the number of rows in the second table. A cross join has a very limited use in everyday data retrieval.

----------------------------- HTML text support in XML -----------------------------
This patch supports HTML embedded in XML. The following new layout command is added to retain HTML format from data in the final output. <?html2fo: xpath?> Example: html code in xml data: user should input the html code to xml data file as CDATA. sample, html code is embedded in field RTECODE.
<?xml version="1.0" encoding="UTF-8"?>
<RTECODE>
<![CDATA[
<font style="font-style: italic; font-weight: bold;" size="3"><a
href="http://www.oracle.com">oracle</a></font> <br/>
<font size="6"><a href="www.oracle.com">www.oracle.com</a></font><br/><br/>
]]>
</RTECODE>


Sample usage in rtf template (assume the data.xml is as above).
<?html2fo:RTECODE?>
Supported html formats: Paragraph Font style ( bold, italic, plain, underline, subscript, superscript and strikes-through) Font size Font family Background color Foreground color Paragraph alignment (center, left, right and justify) Paragraph indent URL link List Bullet List Number List Non-Supported formats: Nested List (List with Indent) Some HTML tags/attributes which manually inserted like Table, Image, etc... in stead of HTML editor Sample Report: XML: html_test.xml RTF tempalte: html_test.rtf
About

Follow bipublisher on Twitter Find Us on Facebook BI Publisher Youtube ChannelDiscussion Forum

Join our BI Publisher community to get the most and keep updated with the latest news, How-to, Solutions! Share your feedback and let us hear your voice @bipublisher on Twitter, on our official Facebook page, and Youtube!

Search

Archives
« July 2014
SunMonTueWedThuFriSat
  
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
  
       
Today