X

Recent Posts

Adding Native Pivot Charts and Tables to your Excel Reports!!

A report in Excel format is a very common requirement and BI Publisher can generate excel output using RTF, XSL or Excel Template. Excel template is recommended when the requirement is to create pixel perfect column width, to use built in excel functions, to create multi-sheet output, to handle preceding zeroes in data, to maintain data formatting, to manage high number of columns of data, etc. How about adding native charts and pivot tables in the excel report ? Well, excel templates can handle that too.   There is no wizard in the Excel Template Builder to create charts or pivot table, but you can certainly include Excel Pivot Charts and Pivot Tables in your report using MS Excel features. Here is a step-by-step guide:   Step 1: Create Excel Template to build data for Pivot Chart & Pivot Table Use Excel Template Builder to create Excel Template Load a sample XML data. Add data column header names. Use "Insert Field" option from BI Publisher Ribbon Menu and create data place holders as shown below. You will see an interim dialog box from the Template Builder that a metadata sheet will be created. Click OK on it.   Add looping of data using Insert Repeating Group. Select the For Each entry at the repeating node level   Preview the output. This will bring all records in the excel sheet in a separate .xls output file.   Step 2: Create Pivot Chart & Pivot Table You can close the output .xls file and stay in the Excel Template. Now select all the data columns to be used in the Pivot Chart and table. You can click on column headers and select the entire column to be included or you can just select the table with column headers and single row of data placeholders. From Excel Menu Insert, select Pivot Chart & Pivot Table option.   In the dialog box "Create PivotTable", you can keep selected the option "Select a table or range" and leave the Table/Range that appears by default based on the selection. You can choose to create the Pivot Chart and Pivot Table in a new work sheet (recommended).Click OK. This will add a new Sheet in the Excel file and insert a Pivot Table and Chart place holder, with Pivot Table fields on the right panel Here you can select the fields for the Pivot table and chart, to be depicted as Axis, Legend and Values. In this example we have included Product Type, Product, LOB and Brand as Axis and Revenue as Values. Please note that by default the function selected under Values is Count. Therefore, select the drop down next to Count function and choose Value Field Settings, where you can change this to Sum function.     One more thing to note is the presence of Field Buttons in the chart. You can hide these Field Buttons. With Pivot Chart selected, go to Analyze Menu in the Ribbon style Menu, and under Show/Hide section choose "Hide all Field Buttons". Finally the template will look like this   Step 3: Include dynamic data generated by BI Publisher for Pivot Chart & Pivot Table Right click on Pivot Chart, select PivotChart Options, select Data tab. Here select the option "Refresh data when opening the file". This will bring the data dynamically into the PIvot Chart and Pivot Table.     You can run preview of the excel output and you will see the pivot table and chart displaying dynamic data. You will notice blank data appearing in the Pivot Table and Chart. This is due to the way the looping works against the dynamic data. You can hide this blank data by filtering the blank data from the parent field in the pivot table of the output excel file. In this example, we will remove the blank data from Product field and the complete blank section will be removed without affecting rest of the data. To do this, just hover over Product in the right side pane under Pivot Chart Fields and click on the down arrow. This will open the filter options for Product field. Uncheck the Blank value from filter list.    So, this completes the template design and the final output will look as shown below You can further include excel functions and formula within these pivot table and charts as necessary for your requirement. You can even change the chart type, style etc. to create the most appropriate visual representation of the data. You can upload the excel template on BI Publisher server and run it against live data. You can include as many sheets with different pivot charts and tables, as required for your report.  Also note that excel template can be run against any data source type in BI Publisher Data Model. Therefore you can use BI Analysis or even run a BIJDBC SQL query against RPD layer, and bring complex calculations, aggregations as a part of your data.    Hope this was helpful. If you want to check the sample template and data, download it from here. Have a great day !!

A report in Excel format is a very common requirement and BI Publisher can generate excel output using RTF, XSL or Excel Template. Excel template is recommended when the requirement is to create pixel...

Introducing Pixel Perfect Reporting in Oracle Analytics Cloud

  For all you BI Publisher fans, here is the good news - BI Publisher is now available with Oracle Analytics Cloud !! Oracle Analytics Cloud (OAC) is a scalable and secure public cloud service that provides a full set of capabilities to explore and perform collaborative analytics for your enterprise. You can take data from any source, explore with Data Visualization and collaborate with real-time data. It is available in three flavors - Standard Edition, Data Lake Edition and Enterprise Edition, with Standard Edition giving the base ability to explore data, Data Lake Edition allowing insights into big data, and Enterprise Edition offering the full platter of data exploration, big data analytics, dashboard, enterprise reporting, Essbase etc. Refer to this documentation for additional details on different editions. With OAC 17.4.5 Enterprise Edition, now you can create pixel perfect report and deliver to a variety of destinations such as email, printer, fax, file server using ftp or WebDAV, Webcenter Content and Content & Experience Cloud. The version of BI Publisher here is 12.2.4.0. If you have used BI Publisher On-prem, the experience will be very similar feature wise and look-and-feel wise, and therefore you will find it easy to get on-board. If you are new to BI Publisher, you will now be able to create pixel perfect and highly formatted business documents in OAC such as Invoices, Purchase Orders, Dunning Letters, Marketing Collateral, EFT & EDI documents, Financial Statements, Government Forms, Operational Reports, Management Reports, Retail Reports, Shipping Labels with barcodes, Airline boarding passes with PDF417 barcode, Market to Mobile content using QR code, Contracts with fine-print on alternate page, Cross-tab reports, etc. You can connect to a variety of data sources including BI Subject Areas, BI Analysis and RPD; Schedule your report to run once or as a recurring job; and even burst documents to render in multiple formats and be delivered to multiple destinations.   Can we move from BI Publisher on-prem to BI Publisher on OAC? Well yes, you can. You will have to understand your on-prem deployment and plan accordingly. If your data can be migrated to OAC, that will be the best otherwise you can plan to extend your network to Oracle Cloud allowing OAC to access your on-prem data. The repository can be migrated by archiving and unarchiving mechanism. User data management will be another task where application roles from On-prem will need to be added to OAC application roles. Details on this will be coming soon.   Benefits of BI Publisher on OAC First of all OAC comes with many great features around data exploration and visualization with advanced analytics capabilities. BI Publisher compliments this environment for pixel perfect reporting. So now you have an environment that is packed with Industry leading BI products providing an end-to-end solution for an enterprise.  Managing Server instances will be a cake walk now, with just few clicks you will be able to scale up/down to a different compute shape or scale out/in to manage nodes in the cluster, saving you both time and money. Many self service features to manage reports and server related resources.   What's new in BI Publisher 12.2.4.0? BI Publisher in OAC includes all features of 12.2.1.3 and has the following new features in this release: Accessible PDF Support (Tagged PDF & PDF/UA-1) New Barcodes - QR Code and PDF417 Ability to purge Job History Ability to view diagnostic log for online report Widow-orphan support for RTF template   So why wait? You can quickly check this out by creating a free trial account here. Once you login, you are in OAC home page. To get to BI Publisher you need to click on the Page Menu on right side top of the page and then select option "Open Classic Home". BI Publisher options are available under Published Reporting in the classic home page. For further details on pixel perfect reporting, check the latest Oracle Analytics Cloud Documentation.   Stay tuned for more updates on upgrade and new features !

  For all you BI Publisher fans, here is the good news - BI Publisher is now available with Oracle Analytics Cloud !! Oracle Analytics Cloud (OAC) is a scalable and secure public cloud service that...

Delivery

Delivery to Oracle Document Cloud Services (ODCS) Like A Boss

p { margin-bottom: 0.1in; direction: ltr; color: rgb(0, 0, 10); line-height: 120%; text-align: left; }p.western { font-family: "Liberation Serif",serif; font-size: 12pt; }p.cjk { font-family: "WenQuanYi Micro Hei"; font-size: 12pt; }p.ctl { font-family: "Lohit Devanagari"; font-size: 12pt; } We have moved to a new blogging platform. This was a post from Pradeep that missed the cut over ... In release 12.2.1.1, BI Publisher added a new feature - Delivery to Oracle Document Cloud Services (ODCS). Around the same time, BI Publisher was also certified against JCS 12.2.1.x and therefore, today if you have hosted your BI Publisher instance on JCS then we recommend Oracle Document Cloud Services as the delivery channel. Several reasons for this: Easy to configure and manage ODCS in BI Publisher on Oracle Public Cloud. No port or firewall issues. ODCS offers a scalable, robust and secure document storage solution on cloud. ODCS offers document versioning and document metadata support similar to any content management server Supports all business document file formats relevant for BI Publisher When to use ODCS? ODCS can be used for all different scenarios where a document need to be securely stored in a server that can be retained for any duration. The scenarios may include: Bursting documents to multiple customers at the same time. Invoices to customers HR Payroll reports to its employees Financial Statements Storing large or extremely large reports for offline printing End of the Month/Year Statements for Financial Institutions Consolidated department reports Batch reports for Operational data Regulatory Data Archival Generating PDF/A-1b or PDF/A-2 format documents How to Configure ODCS in BI Publisher? Configuration of ODCS in BI Publisher requires the  URI, username and password. Here the username is expected to have access to the folder where the files are to be delivered.   How to Schedule and Deliver to ODCS? Delivery to ODCS can be managed through both - a Normal Scheduled Job and a Bursting Job. A Normal Scheduled Job allows the end user to select a folder from a list of values as shown below \ In case of Bursting Job, the ODCS delivery information is to be provided in the bursting query as shown below: Accessing Document in ODCS Once the documents are delivered to ODCS, they can be accessed by user based on his access to the folder, very similar to FTP or WebDAV access. That's all for now. Stay tuned for more updates !  

We have moved to a new blogging platform. This was a post from Pradeep that missed the cut over ... In release 12.2.1.1, BI Publisher added a new feature - Delivery to Oracle Document Cloud Services...

RTF

BIP and Mapviewer Mash Up V

The last part on maps, I promise ... its been a fun ride for me at least :0) If you need to catch up on previous episodes: BIP and Mapviewer Mash Up I BIP and Mapviewer Mash Up II BIP and Mapviewer Mash Up III BIP and Mapviewer Mash Up IV In this post we're looking at map quality. On the left a JPG map, to the right an SVG output. If we ignore the fact that they have different levels of features or layers. Imagine getting the maps into a PDF and then printing them. Its pretty clear that the SVG version of the map is going to render better on paper compared to JPG. Getting the SVG output from mapviewer is pretty straightforward, getting BIP to render it requires a little bit of effort. I have mentioned the XML request that we construct and then do a variable substitution in our servlet. All we need do is add another option to the requested output. Mapviewer supports several flavors of SVG: If you specify SVG_STREAM, the stream of the image in SVG Basic (SVGB) format is returned directly; If you specify SVG_URL, a URL to an SVG Basic image stored on the MapViewer host system is returned. If you specify SVGZ_STREAM, the stream of the image in SVG Compressed (SVGZ) format is returned directly; If you specify SVGZ_URL, a URL to an SVG Compressed image stored on the MapViewer host system is returned. SVG Compressed format can effectively reduce the size of the SVG map by 40 to 70 percent compared with SVG Basic format, thus providing better performance. If you specify SVGTINY_STREAM, the stream of the image in SVG Tiny (SVGT) format is returned directly; If you specify SVGTINY_URL, a URL to an SVG Tiny image stored on the MapViewer host system is returned. (The SVG Tiny format is designed for devices with limited display capabilities, such as cell phones.) Dont panic, Ive looked at them all for you and we need to use SVGTINY_STREAM. This sends back a complete XML file representation of the map in SVG format. We have a couple of issues: We need to strip the XML declaration from the top of the file: <?xml version="1.0" encoding="utf-8"?> If we don't BIP will choke on the SVG. Being lazy I just used a string function to strip the line out in my servlet:dd We need to stream the SVG back as text. So we need to set the CONTENT_TYPE for the servlet as 'text/javascript' We need to handle the SVG when it comes back to the template. We do not use the

The last part on maps, I promise ... its been a fun ride for me at least :0) If you need to catch up on previous episodes: BIP and Mapviewer Mash Up I BIP and Mapviewer Mash Up II BIP and Mapviewer Mash...

RTF

Supporting Font Encoders on 11g and 12c

We had a couple of requests for some help this past week with customers' custom font encoders. With 11g/12c BIP its not straightforward to embed your code with the BIP code. In fact; its really simple but it took me a while and some trial and error to get the encoder classes to the right spot and in the right format so that BIP could 'see' and use them. In 11g/12c the BIP application is exploded out under the servers below the DOMAIN_HOME, in my 11.1.1.7.x install thats ./oracle/BI/user_projects/domains/bifoundation_domain/servers/AdminServer/tmp/_WL_user/bipublisher_11.1.1/6uc731 Under the last directory (which will be different for you) will be a lib directory. Thats where your encoder classes need to sit wrapped in a jar file. Its not the best place to have them as upgrades to the code line may not preserve them but its the only place I found where the classes get loaded that BIP can access the encoder. There are other more standard places under the WLS server but BIP can not access the class and the encoder is not reached. Just to lay out some of the things I have found out during this investigation: 1. When you compile your class make sure the target java version is appropriate. For the 11.1.1.7.x codeline the version needs to be 1.6. For 12g, its 1.8. If you dont do this, you'll get errors when calling the class. 2. You need to wrap your class up in a jar file. If you use JDeveloper, you can create a deployment profile that will build the jar for you. Its then a case of copying it into the appropriate directory. 3. Make sure your RTF barcode register field command matches the 'package' line in your class. In my class I have: package xx.bip.fonts; So my template command is:  <?register-barcode-vendor:'xx.bip.fonts.BarcodeUtil';'xxBarVendor'?> make sure they match. 4. In the encode command in the template. Make sure the encoding routine you want to use case matches the one in your encoding class.In my class: ENCODERS.put("pdf417", mUtility.getClass().getMethod("pdf417", arrayOfClass)); In my RTF template: <?format-barcode:TRANSACTION_NUMBER;'pdf417';'xxBarVendor'?> 5. When you are testing the RTF template on the server. Forget the barcode font for now and use a clear text font. That way you can make sure the encoder is being called. Once you are happy. Just update the template with the barcode font you need. 6. When JDeveloper creates the jar file it will create the directory structure that matches your package path in the jar. With all of the steps followed above. You are now ready to deploy the jar file into the server. Depending on how you have configured your server it is going to influence the location of the jar file. On my server, I have a simple install with just an Admin server. You may have multiple managed servers. The jar file will need to reside in all locations for the BIP server to find it. On my 11g server, I copied the jar file to: user_projects/domains/bifoundation_domain/servers/AdminServer/tmp/_WL_user/bipublisher_11.1.1/6uc731/lib On my 12c server: user_projects/domains/bifoundation_domain/servers/bi_server1/tmp/_WL_user/bipublisher_11.1.1/f45tyd/lib Once copied, you need to bounce the WLS server to get the class loaded. You will then be able to see the encoder at work in the output. Make a post upgrade note for yourself and others to ensure, that after a patch application. You check that the encoder jar is still present in the above directories. It'll save some head scratching later :0) Here are my files from testing the solution on 11g.7.x and 12c My JDeveloper project - BIPFonts.zip My jar file - bipfontsupport.zip Update: we have had a couple of customers get through this now and they have helpfully noted a couple of points that helped them out. 1.  When moving from 10g to 11g or 12c. On compiling  the custom class For example, from 10G to 11G, the location of XDOBarcodeEncoder changes from oracle.apps.xdo.template.rtf.util to oracle.xdo.template.rtf.util.  so you will need to update your class to reflect the new import location.2. A reference to "Oracle® Fusion Middleware Developer's Guide for Oracle Business Intelligence Publisher 11g Release 1 (11.1.1) E22259-04     Section 7.13, "Advanced Barcode Formatting" It provides an example of source code for a custom class.and for 12c - Fusion Middleware Report Designer's Guide for Oracle Business Intelligence Publisher

We had a couple of requests for some help this past week with customers' custom font encoders. With 11g/12c BIP its not straightforward to embed your code with the BIP code. In fact; its really simple...

BI Publisher Enterprise

Up in the JCS Clouds !!

Hello Friends, Oracle BI Publisher has been in the cloud for quite sometime ....as a part of Fusion Applications or few other Oracle product offerings. We now announce certification of BI Publisher in the Java Cloud Services!!  Oracle Java Cloud Service (JCS) is a part of the platform service offerings in Oracle Cloud. Powered by Oracle WebLogic Server, it provides a platform on top of Oracle's enterprise-grade cloud infrastructure for developing and deploying new or existing Java EE applications. Check for more details on JCS here. In this page, under "Perform Advanced Tasks" you can find a link to "Leverage your on-premise licenses". This page cites all the products certified for Java Cloud Services and now we can see BI Publisher 11.1.1.9 listed as one of the certified products using Fusion Middleware 11.1.1.7. How to Install BI Publisher on JCS? Here are the steps to install BI Publisher on JCS. The certification supports the Virtual Image option only. Step 1: Create DBaaS Instance Step 2: Create JCS Instance To create an Oracle Java Cloud Service instance, use the REST API for Oracle Java Cloud Service. Do not use the Wizard in the GUI. The Wizard does not allow an option to specify the MWHOME partition size, whereas REST API allows us to specify this. The default size created by the Wizard is generally insufficient for BI Publisher deployments. The detailed instructions to install JCS instance are available in the Oracle By Example Tutorial under "Setting up your environment", "Creating an Oracle Java Cloud Service instance". Step 3:  Install and Configure BI Publisher Set up RCU on DBaaS Copy RCU Run RCU Install BI Publisher in JCS instance Copy BI Installer in JCS instance Run Installer Use Software Only Install Configure BI Publisher Extend Weblogic Domain Configure Policy Store Configure JMS Configure Security You can follow the detailed installation instructions as documented in "Oracle By Example" tutorial.  Minimum Cloud Compute and Storage Requirements: Oracle Java Cloud Service: 1 OCPU, 7.5 GB Memory, 62 GB Storage To install Weblogic instance To Install BI Publisher To set Temp File Directory in BI Publisher Oracle Database Cloud Service: 1 OCPU, 7.5 GB Memory, 90 GB Storage To install RCU To use DBaaS as a data source Oracle IaaS (Compute & Storage): (Optional - Depends on sizing requirements) To Enable Local & Cloud Storage option in DBaaS (Used with Full Tooling option) So now you can use your on-premise license to host BI Publisher as a standalone on the Java Cloud Services for all your highly formatted, pixel perfect enterprise reports for your cloud based applications. Have a great Day !!

Hello Friends, Oracle BI Publisher has been in the cloud for quite sometime ....as a part of Fusion Applications or few other Oracle product offerings. We now announce certification of BI Publisher in...

BI Publisher Enterprise

Oracle BI Publisher 12c released !!

Greetings !! We now have Oracle BI Publisher 12c (12.2.1.0.0) available. You will be able to get the download, documentation, release notes and certification information in BI Publisher OTN home page. The download is also available from Oracle Software Delivery Cloud. This release is part of Fusion Middleware 12c release that includes Oracle WebLogic Server 12c (12.2.1.0.0) Oracle Coherence 12c (12.2.1.0.0) Oracle TopLink 12c (12.2.1.0.0) Oracle Fusion Middleware Infrastructure 12c (12.2.1.0.0) Oracle HTTP Server 12c (12.2.1.0.0) Oracle Traffic Director 12c (12.2.1.0.0) Oracle SOA Suite and Business Process Management 12c (12.2.1.0.0) Oracle MapViewer 12c (12.2.1.0.0) Oracle B2B and Healthcare 12c (12.2.1.0.0) Oracle Service Bus 12c (12.2.1.0.0) Oracle Stream Explorer 12c (12.2.1.0.0) Oracle Managed File Transfer 12c (12.2.1.0.0) Oracle Data Integrator 12c (12.2.1.0.0) Oracle Enterprise Data Quality 12c (12.2.1.0.0) Oracle GoldenGate Monitor and Veridata 12c (12.2.1.0.0) Oracle JDeveloper 12c (12.2.1.0.0) Oracle Forms and Reports 12c (12.2.1.0.0) Oracle WebCenter Portal 12c (12.2.1.0.0) Oracle WebCenter Content 12c (12.2.1.0.0) Oracle WebCenter Sites 12c (12.2.1.0.0) Oracle Business Intelligence 12c (12.2.1.0.0) For BI Publisher this is primarily an infrastructure upgrade release to integrate with WebLogic Server 12c, Enterprise Manager 12c, FMW infrastructure 12c. There are still some important enhancements and new features in this release:  Scheduler Job Diagnostics: This feature is primarily to help with custom report designs and for production job analysis. A report author during design time can view SQL Explain Plan and data engine logs to diagnose report performance and other issues. This will also help in diagnostics of a job in production.   Improved handling of large reports online: Large reports are always recommended to be run as scheduled job. However, there are scenarios where in a few reports vary in size from one user to another. For most end users the report may be just a few pages, but for few end users the same report may run into thousands of pages. Such reports are generally designed to be viewed online and sometimes such large reports end up causing stuck thread issue on Weblogic Server. This release enhances the user experience by providing the user an ability to cancel the processing of a large report. Also, the enhanced design will no longer cause any stuck thread issue. Schedule Job Output view control: Administrators can now hide the "make output public" option from the report job schedulers (Consumer Role) to prevent public sharing of report output. The installation of BI Publisher will be a very different experience in this release. The entire installation effort has been divided into the following steps: Prepare Install Java Developers Kit 8 (JDK8) Run Infrastructure installer fmw_12.2.1.0.0_infrastructure.jar. This will install Web Logic Server 12c Install BI Launch installation by invoking executable ./bi_platform-12.2.1.0.0_linux64.bin Configure BI Run Configuration Assistant Post Installation Tasks Setting up Datasources Setting up Delivery Channels Updating Security - LDAP, SSO, roles, users, etc. Scaling out Upgrade from the 11g environment to the 12c environment is an out-of-place migration, where you would basically migrate the Business Intelligence metadata and configuration from the Oracle 11g instance to the new 12c instance. For the migration procedure, see Migration Guide for Oracle Business Intelligence. For rest of the details please refer to the documentation here. Happy exploring BI Publisher 12c !!

Greetings !! We now have Oracle BI Publisher 12c (12.2.1.0.0) available. You will be able to get the download, documentation, release notes and certification information in BI Publisher OTN home page....

Delivery

WebCenter Content - A new Delivery Channel in 11.1.1.9.0 !!

Hi Everyone One of the new features introduced in 11.1.1.9.0 is WebCenter Content (WCC) Server as a delivery channel. Prior to this release, we could manage delivery to WCC (formerly UCM) server using webDAV as explained by Tim in his blog "BI Publisher and WebDAV... done!". However, there were few restrictions No way to include standard or custom metadata. Therefore, there was no description for the documents submitted and searching these documents in WCC was not convenient. WebDAV uses folders to store the document and access to these folders have to be pre-configured by WCC Administrator. User can not select security group or account at the time of scheduling. Moreover, with WCC as delivery channel you have the ability to use idc(s), http(s) and JAX-WS protocols. Refer to the documentation on RIDC protocols for more details on these protocols. We have now videos in BI Publisher Youtube Channel to demonstrate how BI Publisher integration with WCC works. The videos are split in two parts: Part 1 (Title: BIPublisherWCC Part1) covers an overview and explains some of the WCC concepts, while Part 2 (Title: BIPublisherWCC Part2) walks you through all the steps necessary to make the integration work. You can find additional details in the documentation guide. Navigate to the Books link and check the Administrators Guide for setting up delivery destinations and the Data Modeling Guide for Custom Metadata & Bursting related information.  I am sure you will find this new feature very easy to configure and very useful for maintaining documents in WCC. Have a nice day !! 

Hi Everyone One of the new features introduced in 11.1.1.9.0 is WebCenter Content (WCC) Server as a delivery channel. Prior to this release, we could manage delivery to WCC (formerly UCM) server using...

General

Oracle BI Publisher 11.1.1.9.0 is available !!!

Hi Everyone, I am happy to announce that Oracle BI Publisher 11.1.1.9.0 is released, although I admit that this is almost a week old news now and I am sure some of you may have already known this by now from the BI Publisher homepage in OTN or from other sources. My sincere apologies for the delay here. Thank you Tim for helping me to get back into this blog membership and being patient to allow me put this word out. My activity in the blog has been so less in the past that I am as good as a new member here. When I tried to login last week, I was greeted with this message - "Sorry, you do not have the privileges necessary to access the page you requested. This system is available to Oracle Employees only. Oracle Employees who would like to request a blog account should click here." So I had to start all over and get a fresh access created. Thanks to Tim and Phil from IT support for helping me with this. I will now make sure to use this space more often and share more features, tips and tricks. Oracle BI Publisher 11.1.1.9.0 was GA on May 19th and you can get the download, documentation, certification matrix and release notes here at the BI Publisher home page in OTN. Here is a quick snapshot of new features in this release. The download is also available at Oracle Software Delivery Cloud site. The documentation page has also been given a fresh new structure where in the left navigation you will notice "Task" and "Books" as two menu items. The task will provide quick reference to role based activities under different sub menu items such as "View & Publish", "Design Reports" etc. The "Books" menu will take you to the complete set of books. You can select Administrator's guide, or Developer's guide, Data Modelling Guide, Report Designer's guide and User's Guide for BI Publisher here. If you are looking for any feature, and do not find information under "Tasks" then check for the same under "Books" or use the search option. Stay tuned for more updates on new features. Wish you have a good time exploring the new features!!

Hi Everyone, I am happy to announce that Oracle BI Publisher 11.1.1.9.0 is released, although I admit that this is almost a week old news now and I am sure some of you may have already known this by...

BI Publisher Enterprise

BIP scheduleReport with Parameters

I have just spent an hour or so working on getting a sample scheduleReport web service working with parameter values. There are a lot of examples out there but none I have found have the parameters being set. Our doc is a little light on details on how to set them up :) In lieu of that, here's this!         // Set the parameter values for the report. In this example we have        // 'dept' and 'emp' parameters. We could easily query the params dynamically        //Handle 'dept' parameter        ParamNameValue deptParamNameVal = new ParamNameValue();        deptParamNameVal= new ParamNameValue();        deptParamNameVal.setName("dept"); // Create the string array to hold the parameter value(s)        ArrayOfXsdString deptVal = new ArrayOfXsdString();        // For individual values or multiples, add values to the // string array e.g. 10,20,30        deptVal.getItem().add("10");        deptVal.getItem().add("20");        deptVal.getItem().add("30");        // Asterisk used for a null value ie 'All'        //deptVal.getItem().add("*"); // add the array to the parameter object        deptParamNameVal.setValues(deptVal);        //Handle 'emp' parameter        ParamNameValue empParamNameVal = new ParamNameValue();        empParamNameVal= new ParamNameValue();        empParamNameVal.setName("emp");        ArrayOfXsdString empVal = new ArrayOfXsdString();        // For individual values or multiples, add values to the string array         // empVal.getItem().add("Jennifer Whalen");        // empVal.getItem().add("Michael Hartstein");        // Asterisk used for a null value ie 'All'        empVal.getItem().add("*");        empParamNameVal.setValues(empVal);        // add parameter values to parameter array                ArrayOfParamNameValue paramArr = new ArrayOfParamNameValue();        paramArr.getItem().add(deptParamNameVal);        paramArr.getItem().add(empParamNameVal);        //Now add array to values obj        ParamNameValues pVals = new ParamNameValues();        pVals.setListOfParamNameValues(paramArr);  The pVals object can then be added to the report request object.         req.setParameterNameValues(pVals); Hopefully, you can extrapolate to your code. JDev application available here, unzip and open the application. Just the schedule report class is available here.

I have just spent an hour or so working on getting a sample scheduleReport web service working with parameter values. There are a lot of examples out there but none I have found have the parameters...

BI Publisher Enterprise

How do I ...

An email came in this morning to an internal mailing list, We have an Essbase customer with some reporting requirements and we are evaluating BI Publisher as the potential solution. I'd like to ask for your help with any document, blog or white paper with guidelines about using BI Publisher with Essbase as the main data source.Is there any tutorial showing how to use BI Publisher with Essbase as the main data source? There is not one to my knowledge but trying to be helpful I came up with the following response I'll refer to the docs ...First set up your connection to Essbase http://docs.oracle.com/cd/E28280_01/bi.1111/e22255/data_sources.htm#BIPAD294Then create your data model using that Essbase connection http://docs.oracle.com/cd/E28280_01/bi.1111/e22258/create_data_sets.htm#BIPDM404Use the MDX query builder to create the query or write it yourself (lots of fun :) http://docs.oracle.com/cd/E28280_01/bi.1111/e22258/create_data_sets.htm#BIPDM431Add parameters (optional) http://docs.oracle.com/cd/E28280_01/bi.1111/e22258/add_params_lovs.htm#BIPDM306Then build layouts for your Essbase query http://docs.oracle.com/cd/E28280_01/bi.1111/e22254/toc.htmannnnd your're done :) Simple, right? Well simple in its format but it required me to know the basic steps to build said report and then where to find the appropriate pages in the doc for the links. Leslie saw my reply and commented on how straightforward it was and how our docs are more like reference books than 'how to's.' This got us thinking. I have noticed that the new 'cloud' docs have How do I ... sections where a drop down will then show maybe 10 tasks associated with the page Im on right now in the application. Getting that help functionality into the BIP is going to take a while. We thought, in the mean time, we could carve out a section on the blog for just such content. Here's where you guys come in. What do you want to know how to do? Suggestions in the comment pleeeease!

An email came in this morning to an internal mailing list, We have an Essbase customer with some reporting requirements and we are evaluating BI Publisher as the potential solution. I'd like to ask for...

BI Publisher Enterprise

Paginated HTML is here and has been for some time ... I think!

We have a demo environment in my team and of course things get a little beaten up in there. Our go to, 'here's Publisher' report was looking really bad. Data was not returning or being rendered correctly on the five templates we have for it. So, I spent about a half hour cleaning up the report; getting things working again; clearing out the rubbish. I noticed that one of the layouts when rendered in HTML was repeatedly showing a header down the screen. Oh, I know where to get rid of that and off I click to the report properties to fix it. But what is this I see? Is it? Can it be? Are my tired old eyes deceiving me? Yes, Dexter, you see that right, 'View Paginated'! I nervously changed the value to 'true' and went back to the HTML output. Holy Amaze Balls Batman, paginated HTML, the holy grail of HTML rendered reports, the Mount Everest of ... no, thats too easy, the K2 of html output ... its fan-bloody-tastic! Can you tell Im excited? I was immediately on messenger to Leslie (doc writer extraordinaire)  Obviously not quite as big a deal in the sane, real world outside of my head. 'Oh yeah, we have that now ...' Leslie is so calm and collected, however, she does like Maroon 5 but, we overlook that :) I command you 11.1.1.6+'ers to go find the property and turn it on right now and bask in the glory that is, 'paginated html.!' I cannot stop clicking back and forth and then to the end and then all the way back to the beginning. Its fantastic! Just look at those icons, just click em, you know you want to!

We have a demo environment in my team and of course things get a little beaten up in there. Our go to, 'here's Publisher' report was looking really bad. Data was not returning or being...

RTF

Bordering Text

A tough little question appeared on one of our internal mailing lists today that piqued my interest. A customer wanted to place a border around all data fields in their BIP output. Something like this: Naturally you think of using a table, embedding the field inside a cell and turning the cell border on. That will work but will need some finessing to get the cells to stretch or shrink depending on the width of the runtime text. Then things might get a bit squirly (technical term) if the text is wide enough to force a new line at the page edge. Anyway, it will get messy. So I took a look at the problem to see if the fields can be isolated in the page as far as the XSLFO code is concerned. If the field can be siolated in its own XSL block then we can change attribute values to get the borders to show just around the field. Sadly not. This is an embedded field YEARPARAM in a sentence. translates to  <fo:inline height="0.0pt" style-name="Normal" font-size="11.0pt" style-id="s0" white-space-collapse="false" font-family-generic="swiss" font-family="Calibri" xml:space="preserve">This is an embedded field <xsl:value-of select="(.//YEARPARAM)[1]" xdofo:field-name="YEARPARAM"/> in a sentence.</fo:inline>If we change the border on tis, it will apply to the complete sentence. not just the field.So how could I isolate that field. Well we could actually do anything to the field. embolden, italicize, etc ... I settled on changing the background color (its easy to change it back with a single attribute call.) Using the highlighter tool on the Home tab in Word I change the field to have a yellow background. I now have:  This gives me the following code. <fo:block linefeed-treatment="preserve" text-align="start" widows="2" end-indent="5.4pt" orphans="2" start-indent="5.4pt" height="0.0pt" padding-top="0.0pt" padding-bottom="10.0pt" xdofo:xliff-note="YEARPARAM" xdofo:line-spacing="multiple:13.8pt"> <fo:inline height="0.0pt" style-name="Normal" font-size="11.0pt" style-id="s0" white-space-collapse="false" font-family-generic="swiss" font-family="Calibri" xml:space="preserve">This is an embedded field </fo:inline> <fo:inline height="0.0pt" style-name="Normal" font-size="11.0pt" style-id="s0" white-space-collapse="false" font-family-generic="swiss" font-family="Calibri" background-color="#ffff00"> <xsl:attribute name="background-color">white</xsl:attribute> <xsl:value-of select="(.//YEARPARAM)[1]" xdofo:field-name="YEARPARAM"/> </fo:inline> <fo:inline height="0.0pt" style-name="Normal" font-size="11.0pt" style-id="s0" white-space-collapse="false" font-family-generic="swiss" font-family="Calibri" xml:space="preserve"> in a sentence.</fo:inline> </fo:block>  Now we have the field isolated we can easily set other attributes that will only be applied to the field and nothing else. I added the following to my YEARPARAM field: <?attribute@inline:background-color;'white'?> >>> turn the background back to white <?attribute@inline:border-color;'black'?> >>> turn on all borders and make black <?attribute@inline:border-width;'0.5pt'?> >>> make the border 0.5 point wide <?YEARPARAM?> >>> my original field The @inline tells the BIP XSL engine to only apply the attribute values to the immediate 'inline' code block i.e. the field. Collapse all of this code into a single line in the field. When I run the template now, I see the following: Its a little convoluted but if you ignore the geeky code explanation and just highlight/copy'n'paste, its pretty straightforward.

A tough little question appeared on one of our internal mailing lists today that piqued my interest. A customer wanted to place a border around all data fields in their BIP output. Something...

BI Publisher Enterprise

Multi Sheet Excel Output

Im on a roll with posts. This blog can be rebuilt ... I received a question today from Camilo in Colombia askinghow to achieve the following. ‘What are my options to deliver excel files with multiple sheets? I know we can split 1 report in multiplesheets in with the BIP Advanced Options, but what if I want to have 1 report /sheet? Where each report in each sheet has a independent data model ….’ Well, its not going to be easy if you have to havecompletely separate data models for each sheet. That would require generatingmultiple Excel outputs and then merging them, somehow. However, if you can live with a single data model withmultiple data sets i.e. queries that connect to separate data sources.Something like this: Then we can help. Each query is returning its own data setbut they will all be presented together in a single data set that BIP can thenrender. Our data structure in the XML output would be: <DS> <G1> ... </G1> <G2> ... </G2> <G3> ... </G3></DS> Three distinct data sets within the same data output. To get each to sit on a separate sheet within the Exceloutput is pretty simple. It depends on how much native Excel functionality youwant. Using an RTF template you just create the layouts for eachdata set on a page(s) separated by a page break (Ctrl-Enter.) At runtime, BIPwill place each output onto a separate sheet in the workbook. If you want toname each sheet you can use the <?spreadsheet-sheet-name: xpath-expression?> command. More info here. That’s assophisticated as it gets with the RTF templates. No calcs, no formulas, etc.Just put the output on a sheet, bam! Using an Excel template you can get more sophisticated withthe layout. This time thou, you create the layout for each data model onseparate sheets. In my example, sheet 1 holds the department data, sheet 2, theemployee data and so on. Some conditional formatting has snuck in there. I have zipped up the sample files here. FIN!

Im on a roll with posts. This blog can be rebuilt ... I received a question today from Camilo in Colombia asking how to achieve the following.‘What are my options to deliver excel files with...

Data Extraction

Database Links

Yeah, its been a while, moving on ... I got a question a week back asking about how BI Publisher could handle dblinks. The customer currently has db links from DB1 to DB2 and uses them in their queries. Could BIP handle the syntax and pass it on to the database in its SQL or could it handle the link another way? select e1.emp_name, e1.emp_id,e2.managerfrom emps e1, emps@db2 e2where e1.manager_id = e2.id Well, there is the obvious way to create the join in BIP. Just get rid of the db link alttogether and create two separate database connections (db1 and db2). Write query A against db1 and query B against db2. Then just create a join between the two queries, simple.  But, what if you wanted to use the dblink? Well, BIP would choke on the @db2 you would have in the sql. Some silly security rules that, no, you can not turn off if you want to. But there are ways around it, the choking, not the security. Create an alias at the database level for the emp@db2, that way BIP can parse the resulting query. Lets assume I create an alias in the db for my db linked table as 'managers'. Now my query becomes:select e1.emp_name, e1.emp_id,e2.managerfrom emps e1, managers e2where e1.manager_id = e2.id BIP will not choke, it will just pass the query through and the db can handle the linking for it.Thats it, thats all I got on db links. See you in 6 months :)

Yeah, its been a while, moving on ... I got a question a week back asking about how BI Publisher could handle dblinks. The customer currently has db links from DB1 to DB2 and uses them in their...

RTF

Internal Links

Another great question today, this time, from friend and colleague, Jerry the master house re-fitter. I think we are competing on who can completely rip and replace their entire house in the shortest time on their own. Every conversation we have starts with 'so what are you working on?' He's in the midst of a kitchen re-fit, Im finishing off odds and ends before I re-build our stair well and start work on my hidden man cave under said stairs. Anyhoo, his question! Can you create a PDF document that shows a summary on the first page and provides links to more detailed sections further down in the document? Why yes you can Jerry. Something like this? Click on the department names in the first table and the return to top links in the detail sections. Pretty neat huh? Dynamic internal links based on the data, in this case the department names. Its not that hard to do either. Here's the template, RTF only right now. The important fields in this case are the ones in red, heres their contents. TopLink <fo:block id="doctop" /> Just think of it as an anchor to the top of the page called doctop Back to Top <fo:basic-link internal-destination="doctop" text-decoration="underline">Back to Top</fo:basic-link> Just a live link 'Back to Top' if you will, that takes the user to the doc top location i.e. to the top of the page. DeptLink <fo:block id="{DEPARTMENT_NAME}"/> Just like the TopLink above, this just creates an anchor in the document. The neat thing here is that we dynamically name it the actual value of the DEPARTMENT_NAME. Note that this link is inside the for-each:G_DEPT loop so the {DEPARTMENT_NAME} is evaluated each time the loop iterates. The curly braces force the engine to fetch the DEPARTMENT_NAME value before creating the anchor. DEPARTMENT_NAME <fo:basic-link  internal-destination="{DEPARTMENT_NAME}" ><?DEPARTMENT_NAME?></fo:basic-link> This is the link for the user to be able to navigate to the detail for that department. It does not use a regular MSWord URL, we have to create a field in the template to hold the department name value and apply the link. Note, no text decoration this time i.e. no underline. You can add a dynamic link on to anything in the summary section. You just need to remember to keep link 'names' as unique as needed for source and destination. You can combine multiple data values into the link name using the concat function. Template and data available here. Tested with 10 and 11g, will work with all BIP flavors.

Another great question today, this time, from friend and colleague, Jerry the master house re-fitter. I think we are competing on who can completely rip and replace their entire house in the shortest...

BI Publisher Enterprise

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 200BASE 0UP 0DOWN 0FINISH 0 START 0BASE 180UP 0DOWN 20FINISH 0 START 0BASE 150UP 0DOWN 30FINISH 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_VALFROM(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_UPFROM 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_VALFROM PRODUCTSUNIONSELECT 2 SORT_KEY , PRODUCT_NAME, STRT, SALES, UP, DOWN, 0 END_TOTAL, 200 + (SUM(LAG_UP - DOWN) OVER (ORDER BY PRODUCT_NAME)) AS BASE_VALFROM(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_UPFROM PRODUCTS P)UNIONSELECT 3 SORT_KEY , 'END' PRODUCT_NAME, 0 STRT, 0 SALES, 0 UP, 0 DOWN, SUM(SALES) + 200 END_TOTAL, 0 BASE_VALFROM PRODUCTSGROUP BY 1,2,3,4,6ORDER 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.

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...

BI Publisher Enterprise

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.

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...

Charting

Filtered Charts

A customer question this week regarding filtering a chart. They have a report with a bunch of criteria with monetary values but, rather than show all of the criteria in a pie chart, they just want to show a few. For example:  This ...  rather than this  There are a couple of ways to tackle this: 1. Filter the chart data in the chart definition. Using an XPATH expression you can filter out all of the criteria you do not want to see. Open the chart definition and update the definition. You will need to update the RowCount, RowLabels and DataValues attributes in the chart definition. Adding in the following XPATH expression:     [DEPARTMENT_NAME='Accounting' or DEPARTMENT_NAME='Marketing' or DEPARTMENT_NAME='Executive'] so the DataValues value becomes:     <DataValues><xsl:for-each-group select=".//G_1[DEPARTMENT_NAME='Accounting' or DEPARTMENT_NAME='Marketing' or DEPARTMENT_NAME='Executive']" ... 2. Create a variable in the template to hold just the values you want to chart.     <?variable: filterDepts; /DATA_DS/LIST_G_1/G_1[DEPARTMENT_NAME='Accounting' or DEPARTMENT_NAME='Marketing' or DEPARTMENT_NAME='Executive']?> Then update the chart definition with the variable for the same three attributes above, the RowCount, RowLabels and DataValues. For example:     <DataValues><xsl:for-each-group select="$filterDepts" ... These both work admirably, but they both require some manual updating of the chart definition which can get fiddly and a pain to maintain. I'm also just filtering for three departments, when you get up to 5 or 6 then the XPATH starts to become a pain to maintain. Option 2 alleviates this somewhat because you only need to define the filter once to create the filtered variable. A better option may be ... 3. Force the effort down into the data layer. Create another query in the report that just pulls the data for the chart. LIST_G2/G_2 holds the data for the chart. Then all you need do is create a vanilla chart on that particular section of the data. Yes, there is some overhead to re-fetch the data but this is going to be about the same if not less than the extra processing required in the template with options 1 and 2. This has another advantage, you can parametrize the criteria for the user. You can create a parameter to allow the user to select, in my case, the department(s) they want to chart. Its simple enough to create the multi-select parameter and modify the query to filter based on the values chosen by the user. Sample report (including data model and layout template here) just un-archive into your catalog. RTF Template plus sample data available here.

A customer question this week regarding filtering a chart. They have a report with a bunch of criteria with monetary values but, rather than show all of the criteria in a pie chart, they just want to...

BI Publisher Enterprise

Alternate Tray Printing

Since we introduced support for check printing PCL escape sequences in 11.1.1.7 i.e. being able to set the micr font or change the print cartridge to the magnetic ink for that string. I have wanted to test out other PCL commands, particularly, changing print trays. Say you have letter headed paper or pre-printed or colored paper in tray 2 but only want to use it for the first page or specific or for a separator page, the rest can come out of plain ol Tray 1 with its copier paper.I have had a couple of inquiries recently and so, I finally took some time to test out the theory. I should add here, that the dev team thought it would work but were not 100%. The feature was built for the check printing requirements alone so they could not support any other commands. I was hopeful thou!In short, it works! I can generate a document and print it with embedded PCL commands to change from Tray 1 (&l4H) to Tray 2 (&l1H ) - yep, makes no sense to me either. I got the codes from here, useful site with a host of other possibilities to test.For the test, I just created a department-employee listing that broke the page when the department changed. Just inside the first grouping loop I included the PCL string to set Tray 1.<pcl><control><esc/>&l4H </control> </pcl>Note, this has to be in clear text, you can not use a formfield.I then created a dummy insert page using a template and called it from just within the closing department group field (InsertPAGE field.) At the beginning of the dummy page I included the PCL string to get the paper from Tray 2:<pcl><control><esc/>&l1H</control> </pcl>When you run this to PDF you will see the PCL string. I played with this and hid it using a white font and it worked great, assuming you have white paper :)When you set up the printer in the BIP admin console, you need to ensure you have picked the 'PDF to PCL Filter' for the printer. If you dont want to have PCL enabled all the time, you can have multiple definitions for the same printer with/with out the PCL filter. Users just need to pick the appropriate printer instance. Using this filter ensures that those PCL strings will be preserved into the final PCL that gets sent to the printer. Example files here. Official documentation on the PCL string here.Happy Printing!

Since we introduced support for check printing PCL escape sequences in 11.1.1.7 i.e. being able to set the micr font or change the print cartridge to the magnetic ink for that string. I have wanted to...

BI Publisher Enterprise

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.

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...

Templates

Conditional Borders

How can you conditionally turn cells borders on and off in Publishers RTF/XSLFO templates? With a little digging you'll find what appears to be the appropriate attributes to update in your template. You would logically come up with using the various border styling options: border-top|bottom|left|right-widthborder-top|bottom|left|right-styleborder-top|bottom|left|right-colorBuuuut, that doesnt work. Updating them individually does not make a difference to the output. Not sure why and I will ask but for now here's the solution. Use the compound border formatter border-top|bottom|left|right. This takes the form ' border-bottom="0.5pt solid #000000". You set all three options at once rather than individually. In a BIP template you use: <?if:DEPT='Accounting'?><?attribute@incontext:border-bottom;'3.0pt solid #000000'?><?attribute@incontext:border-top;'3.0pt solid #000000'?><?attribute@incontext:border-left;'3.0pt solid #000000'?><?attribute@incontext:border-right;'3.0pt solid #000000'?><?end if?> 3pt borders is a little excessive but you get the idea. This approach can be used with the if@row option too to get the complete row borders to update. If your template will need to be run in left to right languages e.g. Arabic or Hebrew, then you will need to use start and end in place of left and right. For the inquisitive reader, you're maybe wondering how, did this guy know that? And why the heck is this not in the user docs? Other than my all knowing BIP guru status ;0) I hit the web for info on XSLFO cell border attributes and then the Template Builder for Word. Particularly the export option; I generated the XSLFO output from a test RTF template and took a look at the attributes. Then I started trying stuff out, Im a hacker and proud me!  For the users doc updates, I'll log a request for an update.

How can you conditionally turn cells borders on and off in Publishers RTF/XSLFO templates? With a little digging you'll find what appears to be the appropriate attributes to update in your template....

XSL/XPATH

Desktop Testing XSL

Bit of a corner case this week but I wanted to park this as much for my reference as yours. Need to be able to test a pure XSL template against some sample data? Thats an XSL template that is going to generate HTML, Text or HTML. The Template Viewer app in the BI Publisher Desktop group does not offer that as an option. It does offer XSL-FO proccesing thou. A few minutes digging around in the java libraries and I came up with a command line solution that is easy to set up and use. 1. Place your sample XML data and the XSL template in a directory2. Open the lib directory where the TemplateViewer is installed. On my machine that is d:\Oracle\BIPDesktop\TemplateViewer\lib3. Copy the xmlparserv2.jar file into the directory created in step 1.4. Use the following command in a DOS/Shell window to process the XSL template against the XML data. java -cp ./xmlparserv2.jar oracle.xml.parser.v2.oraxsl fileX.xml fileY.xsl > fileX.xls The file generated will depend on your XSL. For an Excel output, you would instruct the process to generate fileX.xls in the same folder. You can then test the file with Excel, a browser or a text editor. Now you can test on the desktop until you get it right without the overhead of having to load it to the server each time. To be completely clear, this approach is for pure XSL templates that are designed to generate text, html or xml. Its not for the XSLFO templates that might be used at runtime to generate PDF, PPT, etc. For those you should use the Template Viewer application, it supports the XSLFO templates but not the pure XSL templates. If your template still falls into the pure XSL template category. This will be down to you using some BIP functionality in the templates. To get it to work you'll need to add in the Publisher libraries that contain the function e.g. xdo-core.jar, i18nAPI_v3.jar, etc to the classpath argument (-cp.) So a new command including the required libraries might look like: java -cp ./xmlparserv2.jar;./xdo-core.jar;./i18nAPI_v3.jar oracle.xml.parser.v2.oraxsl fileX.xml fileY.xsl > fileX.xls  You will need to either move the libraries to the local directory, my assumption above or include the full path to them. More info here on setting the -cp attribute.

Bit of a corner case this week but I wanted to park this as much for my reference as yours. Need to be able to test a pure XSL template against some sample data? Thats an XSL template that is going to...

Delivery

BIP 11g Dynamic SQL

Back in the 10g release, if you wanted something beyond the standard query for your report extract; you needed to break out your favorite text editor. You gotta love 'vi' and hate emacs, am I right? And get to building a data template, they were/are lovely to write, such fun ... not! Its not fun writing them by hand but, you do get to do some cool stuff around the data extract including dynamic SQL. By that I mean the ability to add content dynamically to your your query at runtime. With 11g, we spoiled you with a visual builder, no more vi or notepad sessions, a friendly drag and drop interface allowing you to build hierarchical data sets, calculated columns, summary columns, etc. You can still create the dynamic SQL statements, its not so well documented right now, in lieu of doc updates here's the skinny. If you check out the 10g process to create dynamic sql in the docs. You need to create a data trigger function where you assign the dynamic sql to a global variable that's matched in your report SQL. In 11g, the process is really the same, BI Publisher just provides a bit more help to define what trigger code needs to be called. You still need to create the function and place it inside a package in the db. Here's a simple plsql package with the 'beforedata' function trigger. Spec create or replace PACKAGE BIREPORTS AS whereCols varchar2(2000); FUNCTION beforeReportTrig return boolean;end BIREPORTS; Body create or replace PACKAGE BODY BIREPORTS AS  FUNCTION beforeReportTrig return boolean AS   BEGIN      whereCols := ' and d.department_id = 100';    RETURN true;  END beforeReportTrig;END BIREPORTS; you'll notice the additional where clause (whereCols - declared as a public variable) is hard coded. I'll cover parameterizing that in my next post. If you can not wait, check the 10g docs for an example. I have my package compiling successfully in the db. Now, onto the BIP data model definition. 1. Create a new data model and go ahead and create your query(s) as you would normally. 2. In the query dialog box, add in the variables you want replaced at runtime using an ampersand rather than a colon e.g. &whereCols. select     d.DEPARTMENT_NAME,... from    "OE"."EMPLOYEES" e,    "OE"."DEPARTMENTS" d  where   d."DEPARTMENT_ID"= e."DEPARTMENT_ID" &whereColsNote that 'whereCols' matches the global variable name in our package. When you click OK to clear the dialog, you'll be asked for a default value for the variable, just use ' and 1=1' That leading space is important to keep the SQL valid ie required whitespace. This value will be used for the where clause if case its not set by the function code. 3. Now click on the Event Triggers tree node and create a new trigger of the type Before Data. Type in the default package name, in my example, 'BIREPORTS'. Then hit the update button to get BIP to fetch the valid functions.In my case I get to see the following: Select the BEFOREREPORTTRIG function (or your name) and shuttle it across. 4. Save your data model and now test it. For now, you can update the where clause via the plsql package. Next time ... parametrizing the dynamic clause.

Back in the 10g release, if you wanted something beyond the standard query for your report extract; you needed to break out your favorite text editor. You gotta love 'vi' and hate emacs, am I right?...

BI Publisher Enterprise

Mobile App Designer

Back in August a new Oracle mobile solution jumped out of the gate, the Mobile App Designer (MAD). I seem to have been on the road every week for the last, goodness knows how many weeks. I have finally found some time this week to get down and work with it. Its pretty cool and above all, its capable of providing a mobile platform independent reporting solution. But you already have a mobile application! Yep, and I think they both sit quite comfortably together. The Oracle BI Mobile Application is available from the App Store for Apple users. Its a great app, build reports, dashboards and BIP reports for your browser based users and your Apple app users can take advantage of them immediately. MAD takes the next step forward. Maybe you don't use or can not use Apple mobile devices? Maybe you need to build something more specific around a business area that provides users with a richer experience, beyond what Answers and Dashboards can offer. However, you do not want to have to rely of the tech folks to build the mobile application, thats just piling more work on them. You also want to be platform agnostic, you might have a mix of mobile platforms. MAD can help. For those of you that have already used the Online Template layout editor with BI Publisher, you already know how to build a mobile application. The MAD interface is essentially the online template builder user interface, tweaked for a mobile destination ie a phone or tablet. You build your data model as you would normally including the newer direct data model build on a subject area from OBIEE.Then start to create the 'pages' of your application and the content to sit on those pages. All the normal stuff, tables, pivot tables, charts, images plus accordians, filters and repeating objects. On top of that is the ability to then extend the visual objects that are available to users. Maps (google or oracle), D3 visuals, gantt charts, org charts, if you can either write the code or leverage an existing javascript library, MAD has the extension framework to support it.You can build and test in a browser and then deploy to your own BI App Store. Users, on their mobile devices, can then subscribe to an application. They can open and interact with your app using their phone or tablet's interactive features just as they would with a native application.  As you update your app and add new features the changes will be picked up the next time your users open the application. Interested? Want to know more? The Oracle MAD home page has a ton of content including tutorials, etc. We are planning to dig into MAD in forthcoming posts. The geek in me wanted to be able to build plugins using the D3 and other visuals. I have been working with Leslie on some of the documentation and we'll be sharing some of that 'plugin' doc and how tos in the coming weeks.

Back in August a new Oracle mobile solution jumped out of the gate, the Mobile App Designer (MAD). I seem to have been on the road every week for the last, goodness knows how many weeks. I have...

RTF

Working the Chart Percentages

Charting in BIP is such fun, well sometimes it is. Not so much today, at least not for Ron in San Diego. He needed a horizontal bar chart showing values plotted for various test areas with value labels at the end of the bars. Simple enough right? The wrinkle, they were percentage values so he needed to see '56%' not '56'! Still, it should be simple enough but the percentage formatting has a requirement for your values to be in a decimal format i.e. 0.56 not 56.0. 56.0 gets formatted as 5600%. OK, so either pull the values out as decimals or use the div function to divide the values in the chart by 100 e.g. <xsl:value-of select="myval div 100)" /> Now I can use the following the chart XML to format the percentages as I need them: <Graph ... >...<MarkerText visible="true"><Y1ViewFormat><ViewFormat numberType="NUMTYPE_PERCENT" decimalDigit="0" numberTypeUsed="true" leadingZeroUsed="true" decimalDigitUsed="true"/></Y1ViewFormat></MarkerText>...</Graph> That gets me the values shown the way I want but the auto axis formatting gets me from 0 >> 1. I now need to go in and add the formatting for the axis too. <Graph ...>...<Y1Axis axisMinAutoScaled="false" axisMinValue="0.0" axisMaxAutoScaled="false" axisMaxValue="1.0" majorTickStepAutomatic="true"><ViewFormat numberType="NUMTYPE_PERCENT" decimalDigit="0" scaleFactor="SCALEFACTOR_NONE" numberTypeUsed="true" leadingZeroUsed="true" decimalDigitUsed="true" scaleFactorUsed="true"/></Y1Axis>Now I have a chart that's showing the percentage values and formatting axis scale correctly for me too. You can of course mess with the attributes above to get more decimal points on your labels, etc. Happy Charting!

Charting in BIP is such fun, well sometimes it is. Not so much today, at least not for Ron in San Diego. He needed a horizontal bar chart showing values plotted for various test areas with...

BI Publisher Enterprise

Building on Subject Areas

The new release of BI Publisher 11.1.1.7 has a very nice new feature for those of you wanting to build reports on top of the BI Server data model. In previous releases you would need to either write the logical sql yourself or build an Answer request and copy the SQL from the Advanced tab and paste it into the BIP data modeler. With the new release comes the ability to create reports without the need for a data model at all. You have the option when creating a new report to use a subject area directly.  Once you have selected the subject area you are interested in you can decide on whether to continue into the wizard to help you build the layout. Or to strike out on your own and build the layout yourself. If you go for the latter and load up the layout editor, you get to see all of the data items you would see in the Answers builder in the data tree. Its then a case of dragging and dropping the columns into the layout, just as you would normally with a sample data source. Once you are back to the report editor, the final step is to add some parameters.  This is a little different to a conventional BIP report. There is no data model definition per se i.e the logical SQL is not stored but rather, the columns you added to the layout and the subject area(s) you pulled them from. Yes' you can go across subject areas, but you need to know if its going to make sense or even work before you add more. You add more subject areas click on the subject area name where the data model name normally resides. You'll then get a shuttle dialog that lets you add more subject areas. You can then add columns in the layout builder. Getting back to the parameters, on the report editor page, click the Parameters link (top right.) This will open the parameters dialog. You can add parameters and set how they will be displayed; whether folks can select all; do they see check boxes, a drop box or text box; whether other parameters should be limited by the choice made for this box. Everything you get with a regular BIP parameter. Finally, the report rendered with the parameters. If you have a need to build a more highly formatted report on the BI Server data then this is definitely the way to go. This approach really does open up BIP reporting to business users. No need to write SQL, just pick the columns you want and format them in a simple to use interface. Before you ask, you can not build report layouts in MSWord or Excel for this type of data source, not yet anyhoo :0)

The new release of BI Publisher 11.1.1.7 has a very nice new feature for those of you wanting to build reports on top of the BI Server data model. In previous releases you would need to either write...

RTF

Get yourself Organized!

A request from Leslie today to help her out on the user docs. In them we state that we support the MSWord organization charts but we do not give any detail. Use the organization chart functionality in the templates and the chart that is rendered in the output. Figure 4-18 shows an example of an organization chart. Figure 4-18 Sample Organization Chart Its been a  while since I have looked at them but we mean just that. You build an org chart with names in the boxes, BIP will render it, simple. Oh, you wanted it to load the names into the chart dynamically from the dataset? Sorry, no dice, at least not with the MSWord Org Chart object. However, you can create your own org chart structure using MSShapes and use BIP's ability to fill those shapes with text from your data. Thats documented pretty well and is very easy to do. Taking it to obvious final step; completely data driven org chart structure and text. Thats a bit tougher. It can be done with the shape copy and move commands but its going to take some planning. You need to think about how wide your 'page' is, what to do when you reach the edge and need to continue with the same level in the hierarchy, etc. To get you started, I have created a sample template and data for the first two scenarios. They will work with all releases of BIP and XMLP. The third will take me a little longer :0)

A request from Leslie today to help her out on the user docs. In them we state that we support the MSWord organization charts but we do not give any detail. Use the organization chart functionality in...

RTF

Variable Numbers to Words

Satyender posted a comment to the Numbers to Words post asking:How can I store the result of &lt;?xdofx:to_check_number(TOTAL_INV_AMOUNT,'USD','CASE_UPPER','DECIMAL_STYLE_WORDS')?&gt; inside a variable. Checking this out, BIP chokes on the assigning to the variable with a nice error:  Namespace prefix 'xdofx' used but not declared Turning to BIP RTF template guru in residence Hok-Min, he suggested avoiding the xdofx: wrapper altogether in this case and calling the function more directly. The underlying function in java is: public static String toCheckNumber(String locStr, String amount, String preOrCurCode, String caseStyle, String decimalStyle) Applying that to Satyender's needs we end up with: <?variable@incontext:salval; xdoxslt:toCheckNumber($_XDOLOCALE,.//SALARY,'USD' ,'CASE_UPPER','DECIMAL_STYLE_WORDS')?> We still need the xdoxslt prefix but we can now assign the value to a variable. There is a caveat from Hok Min. Note that the amount has to be in string format.  If it is not a string, it has to be converted to a string, e.g. string($CALCULATED_SALARY).  If you use XML element name directly (like in this case SALARY), then it is already a string, so no need to do conversion. I know this raises the question of why do we need the xdofx: prefix at all? Im discussing that with Hok Min as I write and will get back to you.

Satyender posted a comment to the Numbers to Words post asking: How can I store the result of &lt;?xdofx:to_check_number(TOTAL_INV_AMOUNT,'USD','CASE_UPPER','DECIMAL_STYLE_WORDS')?&gt; inside a...

Excel

All hail the Excel Queen

An excellent question this past week from dear ol Blighty; actually from Brian at Nextgen Clearing Ltd in the big smoke (London). Brian was developing an excel template and wanted to be able to reference the data fields multiple times inside the Excel template. Damn good question and I of course has some wacky solutions, from macros and cell referencing in Excel to pre-processing the data with an XSL stylesheet to copy the data multiple times so it could be referenced multiple times. All completely outlandish, enter our Queen of Excel, Shirley from the development team. Shirley is singlehandedly responsible for the Excel templates, I put her through six months of hell a few years back, with a host of Excel template requirements. She was more than up to the challenge and has developed some great features. One of those, is the ability to use the hidden XDO_METADATA sheet to map the data to custom named fields so they can be used multiple times in the template. So simple and very neat! Excel template and regular Excel users will know that you can only use the naming function once ie the names have to be unique across the workbook so you can not reuse a cell/group name. To get around this you can just come up with as many cell names as you want and map them in the XDO_METADATA sheet to the data columns/fields in your XML data set:. For example: XDO_?DEPTNO_SUMMARY?  <?DEPTNO?> XDO_?DNAME_SUMMARY?  <?DNAME?> XDO_GROUP_?G_D_DETAIL? <xsl:for-each-group select=".//G_D" group-by="./DEPTNO"> XDO_?DEPTNO_DETAIL? <?DEPTNO?> As you can see DEPTNO has been referenced twice and mapped to different named values in the left hand column. These values can then be used to name individual cells in the Excel template. You'll also notice a mix of Publisher <? ...?> and native XSL commands. So the world is your oyster on the mapping and the complexity you might need for calculations or string manipulation. Shirley has kindly built out a sample Excel template, data and result here so you can see how it all hangs together. the XDO_METADATA sheet is hidden, just right click on the sheet names and use the Unhide command to show it.

An excellent question this past week from dear ol Blighty; actually from Brian at Nextgen Clearing Ltd in the big smoke (London). Brian was developing an excel template and wanted to be able to...

Charting

Chart Filtering

Interesting question from a colleague this week. Can you add a filter to a chart to just show a specific set of data? In an RTF template, you need to do a little finagling in the chart definition. In an online template, a couple of clicks and you're done. RTF Build your chart as you would normally to include all the data to start with. Now flip to the Advanced tab to see the code behind the chart. Its not very pretty but with a little effort you can get it looking a little more friendly. Here's my chart showing employees and their salaries. <Graph depthAngle="50" depthRadius="8" seriesEffect="SE_AUTO_GRADIENT"> <LegendArea visible="true"/> <Title text="Executive Department Only" visible="true" horizontalAlignment="CENTER"/> <LocalGridData colCount="{count(.//G_2)}" rowCount="1">  <RowLabels>   <Label>SALARY</Label>  </RowLabels>  <ColLabels>   <xsl:for-each select=".//G_2">    <Label><xsl:value-of select="EMP_NAME"/></Label>   </xsl:for-each>  </ColLabels>  <DataValues>   <RowData>    <xsl:for-each select=".//G_2">     <Cell><xsl:value-of select="SALARY"/></Cell>    </xsl:for-each>   </RowData>  </DataValues> </LocalGridData></Graph> Note the emboldened text. Its currently grabbing all values in the G_2 level of the data. We can use an XPATH expression to filter the data to the set we want to see. In my case I want to only see the employees that are in the Executive department. My  data is structured thus: <DATA_DS>    <G_1>        <DEPARTMENT_NAME>Accounting</DEPARTMENT_NAME>        <G_2>            <MANAGER>Higgins</MANAGER>            <EMPLOYEE_ID>206</EMPLOYEE_ID>            <HIRE_DATE>2002-06-07T00:00:00.000-04:00</HIRE_DATE>            <SALARY>8300</SALARY>            <JOB_TITLE>Public Accountant</JOB_TITLE>            <PARAS>11000</PARAS>            <EMP_NAME>William Gietz</EMP_NAME>        </G_2>So the XPATH expression Im going to use to limit the data to the Executive department would be .//G_2[../DEPARTMENT_NAME='Executive'] Note the ../ moves the parser up the XML tree to be able to test the DEPARTMENT_NAME value. I added this XPATH expression to the three instances that need it ColCount, ColLabels and RowData. Its simple enough to do. Testing your XPATH expression is easier to do using a table of data. Please note, as soon as you make changes to the chart code. Going back to the Builder tab, you'll find that everything is grayed out. I recommend you make all the changes you can via the chart dialog before updating the code.Online TemplateImplementing the filter is much simpler, there is a dialog box to help you out. Add you chart and fill out the various data points you want to show. then hit the Filter item in the ribbon above the chart. That will pop the filter dialog box where you can then add a filter to the chart.   You can add multiple filters if needed and of course you can use the Manage Filters button to re-open and edit the filters.Pretty straightforward stuff!

Interesting question from a colleague this week. Can you add a filter to a chart to just show a specific set of data? In an RTF template, you need to do a little finagling in the chart definition. In...

Barcoding

Quick Quips on QR Codes

Yes, I'm an alliterating all-star; I missed my calling as a newspaper headline writer. I have recently received questions from several folks on support for QR codes. You know them they are everywhere you look, even here! How does Publisher handle QR codes then? In theory, exactly the same way we handle any other 2D barcode font. We need the font file, a mapping entry and an encoding class. With those three pieces we can embed QR codes into any output. To test the theory, I went off to IDAutomation, I have worked with them and many customers over the years and their fonts and encoders have worked great and have been very reliable. They kindly provide demo fonts which has made my life so much easier to be able to write posts like this. Their QR font and encoder is a little tough to find. I started here and then hit the Demo Now button. On the next page I hit the right hand Demo Now button. In the resulting zip file you'll need two files: AdditionalFonts.zip >> Automation2DFonts >> TrueType >> IDAutomation2D.ttf Java Class Encoder >> IDAutomation_JavaFontEncoder_QRCode.jar - the QRBarcodeExample.java is useful to see how to call the encoder. The font file needs to be installed into the windows/fonts directory, just copy and paste it in using file explorer and windows will install it for you. Remember, we are using the demo font here and you'll see if you get your phones decoder to looks a the font above there is a fixed string 'DEMO' at the beginning. You want that removed? Go buy the font from the IDAutomation folks. The Encoder Next you need to create your encoding wrapper class. Publisher does ship a class but its compiled and I do not recommend trying to modify it, you can just build your own. I have loaded up my class here. You do not need to be a java guru, its pretty straightforward. I'd recommend a java IDE like JDeveloper from a convenience point of view. I have annotated my class and added a main method to it so you can test your encoders from JDeveloper without having to deploy them first. You can load up the project form the zip file straight into JDeveloper.Next, take a look at IDAutomation's example java class and you'll see: QRCodeEncoder qre=new QRCodeEncoder(); String DataToEncode = "IDAutmation Inc."; boolean ApplyTilde = false; int EncodingMode = 0; int Version = 0; int ErrorCorrectionLevel = 0; System.out.println( qre.FontEncode(DataToEncode, ApplyTilde, EncodingMode, Version, ErrorCorrectionLevel) ); You'll need to check what settings you need to set for the ApplyTilde, EncodingMode, Version and ErrorCorrectionLevel. They are covered in the user guide from IDAutomation here. If you do not want to hard code the values in the encoder then you can quite easily externalize them and read the values from a text file. I have not covered that scenario here, I'm going with IDAutomation's defaults and my phone app is reading the fonts no problem. Now you know how to call the encoder, you need to incorporate it into your encoder wrapper class. From my sample class:       Class[] clazz = new Class[] { "".getClass() };        ENCODERS.put("code128a",mUtility.getClass().getMethod("code128a", clazz));      ENCODERS.put("code128b",mUtility.getClass().getMethod("code128b", clazz));      ENCODERS.put("code128c",mUtility.getClass().getMethod("code128c", clazz));      ENCODERS.put("qrcode",mUtility.getClass().getMethod("qrcode", clazz)); I just added a new entry to register the encoder method 'qrcode' (in red). Then I created a new method inside the class to call the IDAutomation encoder. /** Call to IDAutomations QR Code encoder. Passing the data to encode     Returning the encoded string to the template for formatting **/public static final String qrcode (String DataToEncode){  QRCodeEncoder qre=new QRCodeEncoder();   boolean ApplyTilde = false;   int EncodingMode = 0;   int Version = 0;   int ErrorCorrectionLevel = 0; return qre.FontEncode(DataToEncode, ApplyTilde, EncodingMode, Version, ErrorCorrectionLevel); } Almost the exact same code in their sample class. The DataToEncode string is passed in rather than hardcoded of course. With the class done you can now compile it, but you need to ensure that the IDAutomation_JavaFontEncoder_QRCode.jar is in the classpath. In JDeveloper, open the project properties >> Libraries and Classpaths and then add the jar to the list. You'll need the publisher jars too. You can find those in the jlib directory in your Template Builder for Word directory.Note! In my class, I have used package oracle.psbi.barcode; As my package spec, yours will be different but you need to note it for later. Once you have it compiling without errors you will need to generate a jar file to keep it in. In JDeveloper highlight your project node >> New >> Deployment Profile >> JAR file. Once you have created the descriptor, just take the defaults. It will tell you where the jar is located. Go get it and then its time to copy it and the IDAutomation jar into the Template Builder for Word directory structure. Deploying the jars On your windows machine locate the jlib directory under the Template Builder for Word install directory. On my machine its here, F:\Program Files\Oracle\BI Publisher\BI Publisher Desktop\Template Builder for Word\jlib. Copy both of the jar files into the directory. The next step is to get the jars into the classpath for the Word plugin so that Publisher can find your wrapper class and it can then find the IDAutomation encoder. The most consistent way I have found so far, is to open up the RTF2PDF.jar in the same directory and make some mods. First make a backup of the jar file then open it using winzip or 7zip or similar and get into the META-INF directory. In there is a file, MANIFEST.MF. This contains the classpath for the plugin, open it in an editor and add the jars to the end of the classpath list. In mine I have: Manifest-Version: 1.0Class-Path: ./activation.jar ./mail.jar ./xdochartstyles.jar ./bicmn.jar ./jewt4.jar ./share.jar ./bipres.jar ./xdoparser.jar ./xdocore.jar ./xmlparserv2.jar ./xmlparserv2-904.jar  ./i18nAPI_v3.jar ./versioninfo.jar ./barcodejar.jar ./IDAutomation_JavaFontEncoder_QRCode.jarMain-Class: RTF2PDF I have put in carriage returns above to make the Class-Path: entry more readable, make sure yours is all on one line. Be sure to use the ./ as a prefix to the jar name. Ensure the file is saved inside the jar file 7zip and winzip both have popups asking if you want to update the file in the jar file.Now you have the jars on the classpath, the Publisher plugin will be able to find our classes at run time. Referencing the Font The next step is to reference the font location so that the rendering engine can find it and embed a subset into the PDF output. Remember the other output formats rely on the font being present on the machine that is opening the document. The PDF is the only truly portable format. Inside the config directory under the Template Builder for Word install directory, mine is here, F:\Program Files\Oracle\BI Publisher\BI Publisher Desktop\Template Builder for Word\config. You'll find the file, 'xdo example.cfg'. Rename it to xdo.cfg and open it in a text editor. In the fonts section, create a new entry:       <font family="IDAutomation2D" style="normal" weight="normal">             <truetype path="C:\windows\fonts\IDAutomation2D.ttf" />       </font> Note, 'IDAutomation2D' (in red) is the same name as you can see when you open MSWord and look for the QRCode font. This must match exactly. When Publisher looks at the fonts in the RTF template at runtime it will see 'IDAutomation2D' it will then look at its font mapping entries to find where that font file resides on the disk. If the names do not match or the font is not present then the font will not get used and it will fall back on Helvetica. Building the Template Now you have the data encoder and the font in place and mapped; you can use it in the template. The two commands you will need to have present are: <?register-barcode-vendor:'ENCODER WRAPPER CLASS'; 'ENCODER NAME'?> for my encoder I have: <?register-barcode-vendor:'oracle.psbi.barcode.BarcodeUtil'; 'MyBarcodeEncoder'?> Notice the two parameters for the command. The first provides the package 'path' and class name (remember I said you need to remember that above.)The second is the name of the encoder, in my case 'MyBarcodeEncoder'. Check my full encoder class in the zip linked below to see where I named it. You can change it to something else, no problem.This command needs to be near the top of the template. The second command is the encoding command: <?format-barcode:DATAT_TO_ENCODE;'ENCODER_METHOD_NAME';'ENCODER_NAME'?> for my command I have <?format-barcode:DATATEXT;'qrcode';'MyBarcodeEncoder'?>DATATEXT is the XML element that contains the text to be encoded. If you want to hard code a piece of text just surround it with single quotes. qrcode is the name of my encoder method that calls the IDAutomation encoder. Remember this.MyBarcodeEncoder is the name of my encoder. Repetition? Yes but its needed again. Both of these commands are put inside their own form fields. Do not apply the QRCode font to the second field just yet. Lets make sure the encoder is working. Run you template with some data and you should get something like this for your encoded data: AHEEEHAPPJOPMOFADIPFJKDCLPAHEEEHABNFFFNBPJGMDIDJPFOJGIGBLMPBNFFFNBAPIBOHFJCFBNKHGGBMPFJFJLJBKGOMNIIOANKPJFFLEPLDNPCLMNGNIJIHFDNLJFEHFPLFLHFHFILKFBLOIGMDFCFLGJGOPJJMECPIACDFJPBGDODOJCHALJOBPECKMOEDDFMFFNFNEPKKKCHAIHCHPCFFLDAHFHAGLMKAPBBBPAPLDKNKJKKGIPDLKGMGHDDEPHLNHHHHHHHPHPHHPHPPHPPPPHHPHHPHPHPHP Grooovy huh? If you do not get the encoded text then go back and check that your jars are in the right spot and that you have the MANIFEST.MF file updated correctly. Once you do get the encoded text, highlight the field and apply the IDAutomation2D font to it. Then re-run the report and you will hopefully see the QR code in your output. If not, go back and check the xdo.cfg entry and make sure its in the right place and the font location is correct. That's it, you now have QR codes in Publisher outputs. Everything I have written above, has been tested with the 5.6.3, 10.1.3.4.2 codelines. I'll be testing the 11g code in the next day or two and will update you with any changes.One thing I have not covered yet and will do in the next few days is how to deploy all of this to your server. Look out for a follow up post. One note on the apparent white lines in the font (see the image above). Once printed they disappear and even viewing the code on a screen with the white lines, my phone app is still able to read and interpret the contents no problem. I have zipped up my encoder wrapper class as a JDeveloper 11.1.1.6 project here. Just dig into the src directories to find the BarcodeUtil.java file if you just want the code. I have put comments into the file to hopefully help the novice java programmer out. Happy QR'ing!

Yes, I'm an alliterating all-star; I missed my calling as a newspaper headline writer.I have recently received questions from several folks on support for QR codes. You know them they are everywhere...

Barcoding

Barcodes and Bugs

A great mail from Mike at Browning last week. He has been through the ringer getting his BIP barcoding sorted out but he's now out of the woods. Here's the final result. By way of explanation, an excerpt from Mike's email:   This is an example of the GS1_128 carton shipping labels we are now producing with BIP in our web application for our vendors who drop ship products to our dealers. It produces 4 labels per printed page, in PDF format, on peel & stick label paper. Each label has a unique carton number, and a unique carton serial number in the SSCC-18 barcode. This example is for Cabelas (each customer has slightly different GS1-128 label format requirements – custom template for each - a pain!). I am using custom java encoders I wrote for the UPC and SSCC-18 barcodes, and a standard encoder (code128b) for the ShipTo zip barcode. Is there any way yet to get around that SUPER ANNOYING bug when opening the rtf template in MS Word, and it replaces my xsl code text in the barcode fields with gibberish??? Every time I open it I have to re-enter all the xsl code. Not only to be able to read & edit it, but also to get it to work in BIP (BIP doesn’t like the gibberish if I upload the template that has it). Mike's last point, regarding the annoying bug in the template builder, is one that I have experienced occasionally. The development team have looked at it and found it to be an issue with MSWord and not a plugin problem. That's all well and good but how can you get around it? Well, you can take advantage of the font mapping that BIP offers to get the barcodes into the PDF output. As many of you know, getting a barcode font to appear in the PDF output, you need employ the use of the xdo.cfg file in the template builder config directory.You would normally have an entry such as this:       <font family="Code 128" style="normal" weight="normal">       <truetype path="C:\windows\fonts\128R00.TTF" />       </font>to map a barcode font to get it to render in the PDF output when testing from the template builder plugin.Mike's issue is only present when the formfield is highlighted with a barcode font. The other fields in the template are OK. What you can do to get around the issue is to bend the config entry to get around having to use the barcode font in the template at all. Changing the entry to something like:       <font family="Calibri" style="normal" weight="normal">       <truetype path="C:\windows\fonts\128R00.TTF" />       </font>Note that we are mapping the Calibri; a humanly readable and non 'erroring' font in the template, to the code 128 barcode font. Where you used to highlight the field with the barcode in MSWord, you now use the Calibri font instead. At run time, BIP will go look for the Calibri font mapping and will drop in the Code128 font. Of course, Calibri is an example; you need to pick a font that you are not going to use any where else in the layout.

A great mail from Mike at Browning last week. He has been through the ringer getting his BIP barcoding sorted out but he's now out of the woods. Here's the final result. By way of explanation, an...

BI Publisher Enterprise

Ubiquitous BIP

The last number I heard from Mike and the PM team was that BIP is now embedded in more than 40 oracle products. That's a lot of products to keep track of and to help out with new releases, etc. Its interesting to see how internal Oracle product groups have integrated BIP into their products. Just as you might integrate BIP they have had to make a choice about how to integrate. 1. Library level - BIP is a pure java app and at the bottom of the architecture are a group of java libraries that expose APIs that you can use. they fall into three main areas, data extraction, template processing and formatting and delivery. There are post processing capabilities but those APIs are embedded withing the template processing libraries. Taking this integration route you are going to need to manage templates, data extraction and processing. You'll have your own UI to allow users to control all of this for themselves. Ultimate control but some effort to build and maintain. I have been trawling some of the products during a coffee break. I found a great post on the reporting capabilities provided by BIP in the records management product within WebCenter Content 11g. This integration falls into the first category, content manager looks after the report artifacts itself and provides you the UI to manage and run the reports. 2. Web Service level - further up in the stack is the web service layer. This is sitting on the BI Publisher server as a set of services, runReport and scheduleReport are the main protagonists. However, you can also manage the reports and users (locally managed) on the server and the catalog itself via the services layer.Taking this route, you still need to provide the user interface to choose reports and run them but the creation and management of the reports is all handled by the Publisher server. I have worked with a few customer on this approach. The web services provide the ability to retrieve a list of reports the user can access; then the parameters and LOVs for the selected report and finally a service to submit the report on the server. 3. Embedded BIP server UI- the final level is not so well supported yet. You can currently embed a report and its various levels of surrounding  'chrome' inside another html based application using a URL. Check the docs here. The look and feel can be customized but again, not easy, nor documented. I have messed with running the server pages inside an IFRAME, not bad, but not great. Taking this path should present the least amount of effort on your part to get BIP integrated but there are a few gotchas you need to get around. So a reasonable amount of choices with varying amounts of effort involved. There is another option coming soon for all you ADF developers out there, the ability to drop a BIP report into your application pages. But that's for another post.

The last number I heard from Mike and the PM team was that BIP is now embedded in more than 40 oracle products. That's a lot of products to keep track of and to help out with new releases, etc.Its...

BI Publisher Enterprise

Dynamic Bursting ... no really!

If any of you have seen me or my colleagues present BI Publisher to you then we have hopefully mentioned 'bursting.' You may have even seen a demo where we talk about being able to take a batch of data, say invoices. Then split them by some criteria, say customer id; format them with a template; generate the output and then deliver the documents to the recipients with a click. We and especially I, always say this can be completely dynamic! By this I mean, that you could store customer preferences in a database. What layout would each customer like; what output format they would like and how they would like the document delivered. We (I) talk a good talk, but typically don't do the walk in a demo. We hard code everything in the bursting query or bursting control file to get the concept across. But no more peeps! I have finally put together a dynamic bursting demo! Its been minutes in the making but its been tough to find those minutes! Read on ... It's nothing amazing in terms of making the burst dynamic. I created a CUSTOMER_PREFS table with some simple UI in an APEX application so that I can maintain their requirements. In EBS you have descriptive flexfields that could do the same thing or probably even 'contact' fields to store most of the info. Here's my table structure: Name                           Type------------------------------ -------- CUSTOMER_ID                    NUMBER(6)TEMPLATE_TYPE                  VARCHAR2(20)TEMPLATE_NAME                  VARCHAR2(120)OUTPUT_FORMAT                  VARCHAR2(20)DELIVERY_CHANNEL               VARCHAR2(50)EMAIL                          VARCHAR2(255)FAX                            VARCHAR2(20)ATTACH                         VARCHAR2(20)FILE_LOC                       VARCHAR2(255) Simple enough right? Just need CUSTOMER_ID as the key for the bursting engine to join it to the customer data at burst time. I have not covered the full delivery options, just email, fax and file location. Remember, its a demo people :0) However the principal is exactly the same for each delivery type. They each have a set of attributes that need to be provided and you will need to handle that in your bursting query. On a side note, in EBS, you use a bursting control file, you can apply the same principals that I'm laying out here you just need to get the customer bursting info into the XML data stream so that you can refer to it in the control file using XPATH expressions. Next, we need to look up what attributes or parameters are required for each delivery method. that can be found in the documentation here.  Now we know the combinations of parameters and delivery methods we can construct the query using a series a decode statements: select distinct cp.customer_id "KEY", cp.template_name TEMPLATE, cp.template_type TEMPLATE_FORMAT, 'en-US' LOCALE, cp.output_format OUTPUT_FORMAT, 'false' SAVE_FORMAT, cp.delivery_channel DEL_CHANNEL, decode(cp.delivery_channel,'FILE', cp.file_loc , 'EMAIL', cp.email , 'FAX', cp.fax) PARAMETER1, decode(cp.delivery_channel,'FILE', c.cust_last_name||'_orders.pdf' ,'EMAIL','cc@BIPublisher.com' ,'FAX', 'faxserver.com') PARAMETER2, decode(cp.delivery_channel,'FILE',NULL ,'EMAIL','customer_service@bipublisher.com' ,'FAX', null) PARAMETER3, decode(cp.delivery_channel,'FILE',NULL ,'EMAIL','Your current orders' ,'FAX',NULL) PARAMETER4, decode(cp.delivery_channel,'FILE',NULL ,'EMAIL','Please find attached a copy of your current orders with BI Publisher, Inc' ,'FAX',NULL) PARAMETER5, decode(cp.delivery_channel,'FILE',NULL ,'EMAIL','false' ,'FAX',NULL) PARAMETER6, decode(cp.delivery_channel,'FILE',NULL ,'EMAIL','reply@bipublisher.com' ,'FAX',NULL) PARAMETER7from cust_prefs cp, customers c,orders_view ovwhere cp.customer_id = c.customer_idand cp.customer_id = ov.customer_idorder by cp.customer_id Pretty straightforward, just need to test, test, test, the query and ensure it's bringing back the correct data based on each customers preferences. Notice the NULL values for parameters that are not relevant for a given delivery channel. You should end up with bursting control data that the bursting engine can use:  Now, your users can run the burst and documents will be formatted, generated and delivered based on the customer prefs. If you're interested in the example, I have used the sample OE schema data for the base report. The report files and CUST_PREFS table are zipped up here. The zip contains the data model (.xdmz), the report and templates (.xdoz) and the sql scripts to create and load data to the CUST_PREFS table.  Once you load the report into the catalog, you'll need to create the OE data connection and point the data model at it. You'll probably need to re-point the report to the data model too. Happy Bursting!

If any of you have seen me or my colleagues present BI Publisher to you then we have hopefully mentioned 'bursting.' You may have even seen a demo where we talk about being able to take a batch...

BI Publisher Enterprise

Trigger Happy

Its been a while, I know, we’ll say no more OK? I’ll just write …In the latest BIP 11.1.1.6 release and if I’m really honest; the release before this (we'll call it dot 5 for brevity.) The boys and gals in the engine room have been real busy enhancing BIP with some new functionality. Those of you that use the scheduling engine in OBIEE may already know and use the ‘conditional scheduling’ feature. This allows you to be more intelligent about what reports get run and sent to folks on a scheduled basis. You create a ‘trigger’ analysis (answer) that is executed at schedule time prior to the main report. When the schedule rolls around, the trigger is run, if it returns rows, then the main report is run and delivered. If there are no rows returned, then the main report is not run. Useful right? Your users are not bombarded with 20 reports in their inbox every week that they need to wade throu. They get a handful that they know they need to look at. If you ensure you use conditional formatting in the report then they can find the anomalous data in the reports very quickly and move on to the rest of their day more quickly. You could even think of OBIEE as a virtual team member, scouring the data on your behalf 24/7 and letting you know when its found an issue.BI Publisher, wanting the team t-shirt and the khaki pants, has followed suit. You can now set up ‘triggers’ for it to execute before it runs the main report. Just like its big brother, if the scheduled report trigger returns rows of data; it then executes the main report. Otherwise, the report is skipped until the next schedule time rolls around. Sound familiar?BIP differs a little, in that you only need to construct a query to act as the trigger rather than a complete report. Let assume we have a monthly wage by department report on a schedule. We only want to send the report to managers if their departmental wages reach and/or exceed a certain amount. The toughest part about this is coming up with the SQL to test the business rule you want to implement. For my example, its not that tough: select d.department_name, sum(e.salary) as wage_totalfrom employees e,departments dwhere d.department_id = e.department_idgroup by d.department_namehaving sum(e.salary) > 230000 We're looking for departments where the wage cost is greater than 230,000 Dexter Dollars! With a bit of messing I found out you can parametrize the query. Users can then set a value at schedule time if they need to. To create the trigger is straightforward enough. You can create multiple triggers for users to select at schedule time. Notice I also used a parameter in the query, :wamount. Note the matching parameter in the tree on the left. You also dont need to return multiple columns, one is fine, the key is if there are rows returned. You can build the rest of your report as usual. At scheduling time the Schedule tab has a bit more on it. If your users want to set the trigger, they check the Use Trigger box. The page will then pop fields to pick the appropriate trigger they want to use, even a trigger on another data model if needed. Note it will also ask for the parameter value associated with the trigger. At this point you should note that the data model does not make a distinction between trigger and data model (extract) parameters. So users will see the parameters on the General and Schedule tabs. If per chance you do need to just have a trigger parameters. You can just hide them from the report using the Parameters popup in the report designer, just un-check the 'Show' box I have tested the opposite case where you do not want main report parameters seen in the trigger section. BIP handles that for you! Once the report hits its allotted schedule time, the trigger is executed. Based on the results the report will either run or be 'skipped.' Now, you have a smarter scheduler that will only deliver reports when folks need to see them and take action on the contents. More official info here for developers and here for users.

Its been a while, I know, we’ll say no more OK? I’ll just write …In the latest BIP 11.1.1.6 release and if I’m really honest; the release before this (we'll call it dot 5 for brevity.) The boys...

BI Publisher Enterprise

14540059 - UPDATE FOR BI PUBLISHER ENTERPRISE 11.1.1.6.0 AUGUST

Its been a while, I know :( I have posts in the pipe just gotta smoke em out!The latest update for BIP 11.1.1.6 was released last week. A bunch of defects have been addressed as you can see below. 13473493 - XMLP TRANSLATION ISSUE OF MILLION (ENG) TO MILLIONES (SPANISH) 13521951 - BIP UPGRADE FROM 10G TO 11.1.1.5.0 IS NOT SUCCESSFULL FOR TIAA-CREF  12542914 - ACC: REPORT VIEWER STRUCTURE HAS ERRORS - NO IFRAME AND NO LANG ATTRIBUTE  13562801 - XML TAG DISPLAY SHOULD DEFAULT TO 'FOLLOW THE DATA 13568043 - BIP QUERY FAILING VALIDATION DUE TO 'COALESCE' KEYWORD 13592901 - THE REPORT IS THROWING AN SQL ERROR THAT REFERENCES CHECKING FOR NULL VALUES 13836696 - BI PUBLISHER REPORT NOT GENERATED WHEN A TEXT FIELD START WITH "E.<SPACE>"  13879206 - DM MIGRATION ISSUES 13888939 - DM: LOV SEARCH CAUSING DB CONNECTION LEAK 13904225 - XSLX ERROR DUE TO URL LINK AND USE OF LIST 13930795 - RTF TEMPLATE GIVING DIFFERENT RESULTS IN DIFFERENT  13942064 - XDOEXCEPTION THROWN WHEN RUNNING PEOPLESOFT TEMPLATES AND XML FILE 13981523 - BI PUBLISHER ON 64-BIT WINDOWS CAN'T CONNECT TO MS ANALYSIS SERVICES CUBE 14039229 - BIP 11.1.1.5.0 REPORTS ARE NOT WORKING ON BIP 11.1.1.6.0  14055793 - BIP 11.1.1.6.0: DATE TYPE INPUT PARAMTER IS NOT DISPLAYING THE CORRECT VALUE USI  14059851 - UNABLE TO GRANT PRIVILEGES TO ROLE: DOMAIN USERS; THE ROLE DOES NOT EXIST 14109967 - LARGE OUTPUT CAUSES OUT OF MEMORY DUE TO LEFT OVER DEBUG CODE 14163973 - ISSUES USING DATA MODEL EDITOR IN BIP 11.1.1.6  14167915 - ORG.XML.SAX.SAXEXCEPTION: DATE FORMAT CANNOT BE NULL  14240045 - EDITING SCHEDULED REPORTS DOES NOT REFLECT VALID VALUES FOR UPGRADED SCHEDULES 14304427 - SEARCH DIALOG NOT BINDING PARAMETER VALUE - INVALID PARAMETER BINDING(S). 14338158 - PASSWORD FIELD SHOULD NOT BE DISPLAYED FOR FMW SECURITY MODEL 14393825 - OBIEE11G: LARGE NUMBER OF OBIPS SESSIONS CREATED WHEN USING SSO AND BI PUB 14558377 - CONT. BUG 14240045:EDITING SCHEDULES IN BI PUBLISHER IS DEFAULTING TO 'ALL' This patch is just for BI Publisher standalone installs. For those of you using BIP within the wider BIEE suite there is the 11.1.1.6.2 BP1 patchset. More details on that here.

Its been a while, I know :( I have posts in the pipe just gotta smoke em out! The latest update for BIP 11.1.1.6 was released last week. A bunch of defects have been addressed as you can see below.  ...

BI Publisher Enterprise

Secrets Revealed to Advanced Charting

We get a lot of emails and questions here at Publisher Tower concerning charts and how to do X. I write about some of the solutions here if I think they could be useful to a wider audience but its tough to document everything for everyone's specific features. The chart dialog in the template builder gets you so far but there are cases where you are going to have to get into the code to make things work the way you want them to. I have documented a bunch which I have pulled together as links below. But if you do venture into the chart code, where do you start? I have re-documented the location of the chart DTD document recently as it disappeared from OTN and having bugged a few people about it, its still not there, c'est la vie. But those of you with the Template Builder for Word (TB) have your own copy you can refer to. Just dig into your TB install directory and look for the dvt-jclient.jar (11g) or bipres.jar (10g) files open them with a zip utility a dig down through the directories to oracle\dss\graph\. There you will find the fabled and rare, graph.dtd ... this is the golden fleece of the BIP charting world. In it, you will find secrets beyond your imagination, treasures beyond compare ...  OK, its not that exciting but there is a lot of charting info to be gleaned. There is not much in the way of comments but you can at least look up features and then see what attributes they will need to achieve your needs.Just remember, Word has almost unlimited undo's, just get stuck in a try stuff out you are not going to break anything! Some blogged chart solutions via google.

We get a lot of emails and questions here at Publisher Tower concerning charts and how to do X. I write about some of the solutions here if I think they could be useful to a wider audience but its...

Applications

Collaborate 2012 wrap up - A Few Presentations Worth a Second Look

It's been a few weeks and hopefully everyone is fully recovered.  Here are a few presentations that really stood out.  No offense to any not mentioned, there were too many to attend and describe here. Adam Crigger of Preferred Strategies presented "Using Oracle BI Publisher for JD Edwards Operations and Financial Reporting" He unveiled some fabulous layouts he built using BI Publisher 11.1.1.6 on top of JD Edwards EnterpriseOne data.John Peters of JRP JR, Inc showed us some very useful tips when modifying data templates in E-Business Suite in his presentation, "Adding Data Elements to BI Publisher Documents" He outlined very clearly the different types of data sources and best practices for updating the seeded E-Business Suite data extracts.In her presentation, "Using Oracle BI Publisher to E-mail Customer ACH Payment Confirmations" Kristina Boone of MediaNews Group went over the step-by-step procedure she followed to create custom payment confirmations using an RTF template in BI Publisher. She then showed how to configure her JD Edwards EnterpriseOne system to run it. Great Stuff!Glen T. Ryen, from Prisio Technologies presented "Oracle Payments in Release 12 – Take It To the Bank!" on Thursday to a nearly SRO crowd - impressive for get away day.  Those who stayed for this presentation were well rewarded.  Perfect balance between Functional and Technical and it gave enough detail and pointers to go deeper.  Several of the attendees around me commented that it was exactly what they were looking for. I see that the Collaborate site is no longer hosting the presentations and white papers - bummer.  I'm told that the user groups - OAUG, Quest and IOUG will be making those available to members on their sites in a few weeks.  Please check with your user group or the presenter if you're interested in the material.

It's been a few weeks and hopefully everyone is fully recovered.  Here are a few presentations that really stood out.  No offense to any not mentioned, there were too many to attend and describe here. A...

RTF

Beyond the Conditional Dialog

Interesting question today, asking how to conditionally underline and align text in an RTF template?Your first thought, the conditional dialog box in the Template Builder for Word right? Mine too, but I know that the dialog is limited to setting the background color, font color and font style. However, the XSLFO standard has a bunch of attributes that can be set. There is going to an intersect of what the standard offers and what BI Publisher has implemented. Ots also going to depend on what version of BIP you are running too as the boys and gals in the back room constantly add to the list of supported attributes for give objects. If you're just getting to grips with the language and want to know what attributes are available, the W3c Schools site is a good place to start - http://www.w3schools.com/xslfo/xslfo_reference.asp.  There you can find the object you want to change and its attributes. The easiest way to create the conditional code is to go ahead and create a condition using the dialog and choosing one of the supported attributes. Note that the dialog only works when you are inside a table. Thats jus tthe dilog box talking, you re not limited to changing attributes only inside tables. Just use the dialog to get the code. All you then need to do is substitute in the attribute name and the value you want it to be into the code. So: <?if:@Name='Tim'?><?attribute@incontext:color;'red'?><?end if?>can then be changed to <?if:@Name='Tim'?><?attribute@incontext:text-align;'right'?><?end if?>and of course you have make multiple changes inside the 'if' statement. One thing to note here, the @incontext might need to be changed to get the desired changes to be applied. Check the documentation for details on the various @ levels you can use. Don't be scared, play a little until it does what you want it to do. Its useful to export the RTF to XSL:FO and see where your code is being applied if the output is not what you were expecting.

Interesting question today, asking how to conditionally underline and align text in an RTF template? Your first thought, the conditional dialog box in the Template Builder for Word right?Mine too,...

BI Publisher Enterprise

Port 80 Goodness for Publisher

Do you ever tire of remembering what port BIP is running on when you enter the URL? Ever wish you could just type: http://tdexter-lap/xmlpserver and be done with it? Well its really not that hard to do. Its documented how to front the web logic server with a web server whether that be Microsoft's IIS or maybe Apache. Well with my success at getting BIP on to my Ubuntu laptop I thought I would give Apache a whirl being as I dont think IIS would run on my machine :) I did start off with the docs but as usual Googling got me faster, more concise instructions with some help if things went awry. Of course, BIP has nothing to do with the configuration other than to tell Apache where the xmlpserver app is running and on what port. So its really just an exercise in installing Apache and then configuring it. Rather than re-invent the wheel, check out Vikas' blog post here - http://vikashazrati.wordpress.com/2009/10/10/apache-weblogic-ubuntu/.  Yes, its specific to Ubuntu but can be applied to any flavor of Linux in this case. For you 'windowers' out there, this will get you on the road to non port typing bliss, http://docs.oracle.com/cd/E21764_01/web.1111/e14395/toc.htm. For linux folks, just follow Vikas' excellent post and just replace the references to the 'medrec' application with 'xmlpserver' and change the port from 7011 to 7001. You can of course add the /console app to the list as well. I have covered web logic server installs ie 11.x. If you re running earlier versions on OC4J the Oracle AS documentation is pretty good on how to get BIP on port 80. In no time you'll have forgotten that BIP even runs on a specific port.

Do you ever tire of remembering what port BIP is running on when you enter the URL? Ever wish you could just type: http://tdexter-lap/xmlpserver and be done with it? Well its really not that hard to do....

BI Publisher Enterprise

Running BIP on Ubuntu!

I am a complete Ubuntu (Linux) convert ... my work laptop runs 64 bit Ubuntu and Im very happy with it. Yes I do still need windblows for a few things and for testing but I would say 80-90% of my time is spent in Ubuntu. Not so good is Oracle's support for Ubuntu, some of that I guess, is around Ubuntu being seen primarily for the desktop and not for servers and also Ubuntu's interesting at times, implementation of Linux. Case in point, the recent release of the BI Publisher Trial Edition (see here) we state support for Linux but to be fair, we narrow that down to Oracle Linux Redhat and SLES. OK, but as its so lightweight it would be great to be able to fire it up on my laptop without the need for a winddoze VM. Trying the installer or more appropriately the 'configurer' it falls at the first hurdle complaining about the script.Thats easily fixed, the pain point in question is the -s switch for the password entry for the admin user you create. Just open the configure.sh and remove the -s references (x2). read -s -p "Enter a password for the Administrator user: " WLS_ADMIN_PWDecho ""read -s -p "Confirm password: " WLS_ADMIN_PWD2 Rerunning and it falls over a little further into the configuration.At this point I want to thank Juergen in Germany for digging into the sub scripts and figuring out what to do. I have to admit, I had put the investigation on to the back burner while I have been traveling. Juergen found the problem in the ../server/wls/configure.sh file. It checks for the MW_HOME and JAVA_HOME environment variables and even if you have set them it still complains. The easiest fix is to just comment out the checks for the variables. # Users must set $MW_HOME variableif [[ -z "$MW_HOME" || ! -d $MW_HOME ||  ! "$(ls -A $MW_HOME)" ]]; then  echo "ERROR: You must set MW_HOME and it must point to a directory".  echo "       where an installation of WebLogic exists. Ensure you point"  echo "       this variable to the extract location of the zip distribution."  exit 1;fi# Users must set $JAVA_HOME variableif [[ -z $JAVA_HOME || ! -d "${JAVA_HOME}/bin" ]]; then  echo "ERROR: You must set JAVA_HOME and point it to a valid location"  echo "       of where your JDK has been installed"  exit 1;fi Just comment out the red text with a # at the beginning of the line. Now when you run the main configureBIP script it completes and successfully starts the WLS server and consequently BIP. Its real fast to start and fast to run and best of all I have another reason I can avoid windblows! Yes I have boring windows colors but what the heck, its not windows!

I am a complete Ubuntu (Linux) convert ... my work laptop runs 64 bit Ubuntu and Im very happy with it. Yes I do still need windblows for a few things and for testing but I would say 80-90% of my time...

E Business Suite

Proxy Images in EBS

Gareth Roberts, founder of Virtuate, a consulting outfit based in New Zealand, came up with a doh! moment for me this week. He has been working with Publisher and trying to get images into his reports that are sitting behind a proxy server. This is all inside E Business Suite so no means to use the JVM proxy settings when the concurrent manager starts up. The support folks found the solution that I had completely forgotten about ... I have been away from EBS too long :( When the concurrent manager (CM) starts a java based job (concurrent program) such as a Publisher one. It fires up a new JVM instance for it. In their wisdom the CM development folks left an 'options' field on the concurrent program definition where you can add JVM start up parameters. So to allow Publisher to reach the proxied images adding the following to the options field: -Dhttp.proxyHost=<proxyhost> -Dhttp.proxyPort=<proxyport> did the trick. Why a Doh! moment? Because we also used the same field when we knew a report was going to be BIG and wanted to increase the memory heap available to that particular report at runtime i.e. -Xmx, -Xmn, etc. You can catch Gareth over on his blog waxing lyrical about all things EBS. Pieter from support pointed out that if the application requires a proxy server then it can be done at a more global level, that is via the context file using these CP context variables: AFJSMARG="%s_afjsmarg%" AFJCPARG="%s_afjcparg%" [vispb12@pbreugel4 ~]$ grep s_afjsmarg $CONTEXT_FILE             <AFJSMARG oa_var="s_afjsmarg" osd="Linux">-server -Xmx384m -XX:NewRatio=2 -XX:+UseSerialGC -Doracle.apps.fnd.common.Pool.leak.mode=stderr:off -verbose:gc</AFJSMARG> [vispb12@pbreugel4 ~]$ grep s_afjcparg $CONTEXT_FILE             <AFJCPARG oa_var="s_afjcparg" osd="Linux">-client</AFJCPARG> More on that approach here - 

Gareth Roberts, founder of Virtuate, a consulting outfit based in New Zealand, came up with a doh! moment for me this week. He has been working with Publisher and trying to get images into his...

BI Publisher Enterprise

BI Publisher has hit the gym

As Pieter reminded me this morning a sleek, slim, new installer is available for BIP 11g. The Oracle BI Publisher 11g Trial Edition has been released on OTN. In comparison to the fatty monster that is, OBIEE Suite, tipping the scales at more than 7Gb; BIP Trial Ed is a svelte, muscular beauty a tad under 600 Mb. A fully functional, free server provided for evaluation purposes. It is not intended for production deployments thou. For that you have to go start the BIEE download and go for a cup of joe. The installer includes the Derby database and BIP is configured out of the box with a bunch of new demos and samples so just download, unpack, run the config script and you're ready to roll. You can of course hook it up to your own data sources and get building. Check the links below. http://www.oracle.com/technetwork/middleware/bi-publisher/downloads/index.html BI Publisher Trial Edition 11g for Windows 64 bit (587 MB) BI Publisher Trial Edition 11g for Windows 32 bit (582 MB)Windows installers include Java SE 6 Runtime Environment BI Publisher Trial Edition 11g for Linux 32 and 64 bit (533 MB) Linux installer requires Java SE 6 Runtime Environment (download)Install Guide | Quick Start Guide | Getting Started TutorialBIG NOTICE ... right here, read this! BI Publisher Trial Edition is not supported for production deployments.  You can save any reports developed on the Trial Edition and upload to your production environment.

As Pieter reminded me this morning a sleek, slim, new installer is available for BIP 11g. The Oracle BI Publisher 11g Trial Edition hasbeen released on OTN. In comparison to the fatty monster that...

BI Publisher Enterprise

Fusion Middleware Innovation Awards 2012

Is your organization using Oracle BI  to deliver unique business value? If so, submit a nomination today for the Oracle Fusion Middleware Innovation Awards. These awards honor customers for their cutting-edge solutions using Oracle Fusion Middleware. Winners are selected based on the uniqueness of their business case, business benefits, level of impact relative to the size of the organization, complexity and magnitude of implementation, and the originality of architecture. The awards will be presented during Oracle OpenWorld 2012 (September 30-October 4) in San Francisco.Thats the official blurb out of the way, I know you are out there.. I have seen and heard about some very cool uses of Oracle BI. I'm not limiting things to BI Publisher here, nor even just Oracle BI but any use of Oracle middleware. Maybe you have integrated BIP into your ERP solution, or are allowing users to run reports via web center or retrieve BIP documents from a content management system. Maybe, you have built a kick ass billing system that integrates with BIP for invoice generation and OBIEE to do the ad-hoc reporting on payments, debtors, etc. Anything qualifies, just get yourself over to the Awards site and fill out an application. You have until July 17th 2012 to get your solution into the spotlight. Good luck!

Is your organization using Oracle BI  to deliver unique business value? If so, submit a nomination today for the Oracle Fusion Middleware Innovation Awards. These awards honor customers for theircuttin...

Applications

Collaborate 2012 update - additional Hands On Lab sessions

Hello Collaborators. Great first 2 days at Collaborate.  On Sunday saw Anthony Isele from McKesson tell a wonderful success story on how they converted critical contracts from Actuate to BI Publisher within Siebel CRM - A Case Study of a Migration to BI Publisher From Actuate at a Leading Healthcare Company.  Earlier Mark Andrezjewski and Shawn Scanlon touted the benefits of the Oracle Red Stack for JD Edwards EnterpriseOne customers - I Bleed Red, Oracle Red Stack that Is... Big news on Monday from our friends on the JD Edwards team who introduced EnterpriseOne 9.1 with One View Reporting Press Release -- bringing the power and ease of use of BI Publisher 11g to EnterpriseOne customers. Also saw Edelweiss Kamermann from the UYOUG share the good news about BI Publisher 11g for BI Publisher Enterprise customers and customers of BIEE -- BI Publisher 11g: Only Good News Thanks again to the BIWA SIG of the IOUG and to Vlamis Software Solutions for again preparing servers and client machines to host the Hands On Labs. Like the past 2 years we had more people show up than we could accommodate. For those of you who couldn't get a seat at the BI Publisher Hands On Lab on Monday, the IOUG has generously arranged for a repeat sessions on Thursday 8:30 - 9:30 and 9:45 - 10:45 in Palm B  Please come by for this opportunity to get your hands on experience with BI Publisher 11g.  Unfortunately the lab handouts are too large to upload to the blog site.  Please visit Building Reports and Data Models in Oracle Business Intelligence Publisher Hands On Lab and you can download the lab handouts from there. I'm sure there were other wonderful presentations these past two days.  There are so many it's hard to get to all of them. Looking forward to the rest of the show.

Hello Collaborators. Great first 2 days at Collaborate.  On Sunday saw Anthony Isele from McKesson tell a wonderful success story on how they converted critical contracts from Actuate to BI Publisher...

Applications

BI Publisher at Collaborate 2012

Wow - There are over 30 fantastic presentations that cover BI Publisher at Collaborate this year.  Interest and use of BI Publisher continues to spread across the growing family of Oracle Applications and products. See Focus On BI Publisher at Collaborate 2012 for a day by day view of all the sessions.Come and visit us at the Oracle Business Intelligence Foundation demo station (kiosk 14) to chat and see BI Publisher 11g live. If you want to try BI Publisher 11g, show up for the Hands On Lab - early ! Last year we had over 200 people show up for 40 seats.We are running 2 sessions this year to accommodate more people but still expect this to be a sold out event.Here is a breakdown of BI Publisher sessions organized by Application area of interest. Follow the link or see Focus On BI Publisher for time, location and speaker info.General Interest for All Building Reports and Data Models in Oracle Business Intelligence Publisher Hands On Lab -- 2nd sessionBI Publisher 11g: Only Good NewsOracle Analytics & Performance Management Strategy and Road MapOracle BI Publisher: The Reporting Platform for Oracle Applications OBIEE 11g - Using the New Features of Answers and BI PublisherOracle BI Publisher: Anatomy of an RTF TemplateMigrating Discoverer to Oracle BIEE: Take Your Reporting to the Next LevelThe Right Information, The Right Tool, the Right TimeProject and HR Analytics + OBIEE 11g + Exadata = Operational Efficiency E-Business Suite Jump Start Your Custom Reports with E-Business Suite R12's Seeded BI Publisher TemplatesUpgrading to R12 Payments Funds Disbursement: An iRobot Case StudyIntroduction to BI Publisher in Oracle EBS R12- Getting StartedLook Before You Leap! Issues to Consider When Migrating Discoverer Reports to BI Publisher 11gMaking Sense of Parent-Child Relationships – The Oracle Kind, Not the Human KindAdding Data Elements to BI Publisher DocumentsPrinting PDF Documents in Self Service Made Easy By Integrating BI Publisher With OAFOracle Payments in Release 12 - Take it to the Bank! JD Edwards Enterprise One I Bleed Red, Oracle Red Stack that Is... Using Oracle BI Publisher for JD Edwards Operational and Financial Reporting JD Edwards: Let's Get Going with BI Publisher JD Edwards EnterpriseOne XE to 9.0 in Less Than 100 Days Choosing the Right Solution to Transform Data to Actionable Information - Oracle EPM, BI and Reporting Using Oracle BI Publisher to E-mail Customer ACH Payment Confirmations Primavera Transitioning From InfoMaker to BI Publisher Introduction to Primavera Contract Management, Business Intelligence Publisher Edition Understanding the Px Schema and Writing Reports for Primavera P6 Using Oracle BI Publisher Oracle Primavera and BPM - The Power of Integration Reporting from Contract Management 14.0 Using BI Publisher 11g Primavera P6 Release 8 - Web Innovation & So Much More JD Edwards Enterprise World Import/Export – Electronic Document Delivery – BI Publisher Gateway BI Publisher and World Delivering Value through EDD and BI Publisher in World A9.2 PeopleSoft XML Publisher - Become an Expert in an HourSiebel A Case Study of a Migration to BI Publisher From Actuate at a Leading Healthcare Company Hope to see you in Vega$,Mike, Leslie and Nikos

Wow - There are over 30 fantastic presentations that cover BI Publisher at Collaborate this year.  Interest and use of BI Publisher continues to spread across the growing family of Oracle Applications...

Siebel

All things Siebel

John from the Siebel product management team just shared a neat list of resources for all things Siebel-BIP. Too good to park in my email folders, here it is for everyone. OpenWorld presentations: http://www.oracle.com/us/rednetwork/index.html 2010 - Siebel Reporting: Oracle Business Intelligence Publisher Customer Experiences2011 - Building More Insightful Reports with Siebel CRM Reporting Solutions BI Publisher forum (public) http://forums.oracle.com/forums/forum.jspa?forumID=245 Oracle BI Publisher http://www.oracle.com/technetwork/middleware/bi-publisher/overview/index.html Oracle BI Publisher Best Practices http://www.oracle.com/technetwork/middleware/bi-publisher/overview/oracle-bi-publisher-best-practices-133345.pdf The following are notes available on Oracle Support Siebel CRM Reports - Statement of Direction / Planned Features (Doc ID 1226744.1) Siebel 8 BI Publisher Reports - List of Siebel Reports (Doc ID 876284.1) Links to Documentation for Using Siebel Reports (Doc ID 1172844.1) Siebel BI Publisher 8 Integration Concepts (Doc ID 811104.1) Siebel Reports integration with Oracle BI Publisher (BIP) Information Center (Doc ID 1147910.1) Siebel Reporting Upgrading BI Publisher Server (Doc ID 1262585.1)Siebel BI Publisher 8 Reports Business Service (Doc ID 823360.1) Siebel BI Publisher Reports Business Service (8.1.1.7+) (Doc ID 1425724.1) Using Oracle BI Publisher for Siebel Proposals (Doc ID 1362460.1) Siebel Reporting 8.x Migration FAQ (Doc ID 764097.1) Supporting Documentation for Siebel Reports by Product Version (Doc ID 1172844.1) Improving the performance of Siebel BI Publisher Report Generation (Doc ID 1392449.1) Siebel Bookshelf, Siebel Reports Guide 8.1.x http://download.oracle.com/docs/cd/E14004_01/books/Reports/ReportsTOC.html

John from the Siebel product management team just shared a neat list of resources for all things Siebel-BIP. Too good to park in my email folders, here it is for everyone. OpenWorld presentations: http:/...

BI Publisher Enterprise

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: Create the JNDI connection in WLS Create the JNDI connection in BI Publisher pointing to the WLS connection Build new data models using or re-point data sources to use the JNDI connection. Create the WLST script to update the WLS JNDI password as needed. Test! Some details. Creating the JNDI connection in web logic is pretty straightforward. Log into hte console and look for Data Sources under the Services section of the home page and click it Click New >> Generic Datasource 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 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 Fill out the db name (SID), server, port, username to connect and password >> Next Test the config to ensure you can connect. >> Next Now you need to deploy the connection to your BI server, select it and click Next.You're done with the JNDI config. 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' Not gonna tell you how to do this, go read the user guide :0) Suffice to say, it works. 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: Need to take Ravish's script above and create a file with a .py extension. 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: ", enprint ("*** Changing pwd for LocalDB ***")dsName = 'LocalDB'print 'Changing Password for DataSource ', dsNamecd('/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)

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...

RTF

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. 

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...

RTF

Conditional Charting

Something I have never been asked for with BIP until recently, conditional charts. Not whether they appear or not but being able to highlight a specific bar on a bar chart if it meets some certain criteria. The chart to the right is simple enough showing sales by month. The April bar is being highlighted in red because the value is falling outside of some limit.So how can you do it? Its not documented in the BIP docs, it's not in the BIBeans (charting engine) docs, so until I finish this post you will need to know and have friends in high places at Oracle. Thank you Klaus and especially Katia for pointing me in the right direction.I have tested this approach with 10.1.3.4.x and 11g, I have not tested with 5.6.3 thou.To get the bar to change color you are going to have to dig into the chart XML and there will be a bit of effort to identify the column to change color but its not that onerous to do or manage. Note thou, if you re-open the chart dialog with the 10g Template Builder it will over write you conditional code.The new object you need to add into your graph XML is an ExceptionalRiser, you can add multiple risers but more on that later. It needs to appear somewhere inside the Graph tag.chart:<Graph seriesEffect="SE_NONE" graphType="BAR_HORIZ_CLUST"><ExceptionalRisers><ExceptionalRiser series="0" group="9" borderColor="#0fffff" fillColor="#ff0000" /></ExceptionalRisers>...</Graph>The attributes can be described as:  - series - the data series that contains the data point to be re-colored. For the majority of charts this will be "0" ie only a single data series- group - the number of the data point or bar to be re-colorded. Starting from 0- borderColor - line around outside color- fillColor  - errr ... the fill color.If you know the value thats erroneous up front then you can hard code the group attribute as above but thats going to be rare. I guess you could work it all out in the data extract and create an element to hold the exception data row number but you can equally work out the value in the template layer.I have struggled a bit with this and have had to resort to my nemesis updatable variables. My first use case is where I know I only want to color a single bar on my chart.Here's my data:<Graph><Sales>    <Row> <Month>Jan</Month> <Value>450</Value> <Target>420</Target></Row>    <Row> <Month>Feb</Month> <Value>500</Value> <Target>550</Target></Row>    <Row> <Month>Mar</Month> <Value>490</Value> <Target>490</Target></Row>    <Row> <Month>Apr</Month> <Value>400</Value> <Target>520</Target></Row>    <Row> <Month>May</Month> <Value>680</Value> <Target>650</Target></Row></Sales></Graph>Simple stuff as usual but I hope easy to understand. I can create a calculation looking for Values that miss the Target value and build an 'if' statement oround it to highlight values in red.<?if:(number(Value) div number(Target))<number(0.8)?><

Something I have never been asked for with BIP until recently, conditional charts. Not whether they appear or not but being able to highlight a specific bar on a bar chart if it meets some certain...

BI Publisher Enterprise

Oracle Policy Automation and BI Publisher

Yep, found another integration with BIP. I get asked about Oracle Policy Automation quite a lot; I do not profess to know much; enough to be dangerous I guess. However, I was not aware that they had been working with BIP to help automate the generation of documents from the system. For the uninitiated, here's the blurb care of : Oracle Policy Modeling is a Windows desktop application for transforming legislation and policy documents into executable business rules. Rules are written in Word and Excel documents using phrases in languages such as English, Chinese and French. These rule documents can be shared amongst business and information technology stakeholders, and commentary can be added into the documents without affecting the structure of the rules themselves. Other features of Oracle Policy Modeling include integrated test case execution and debugging capabilities, and the definition of interviews for interactiverule-based assessments. Interview screen order and branching logic can be defined using visual flow diagrams. In other words, a cool piece of technology, but tell us Tim how is it used? Well, it has been publicly deployed within several government web-sites. Australia's Department of Immigration uses it for visitors to check their eligibility for visas. The UK uses it for their Employment Status Indicator assessment tool, the UK governments online portal for businesses also uses OPA for over 60 interactive tools while the United States uses the software for guidance on tax law. The last one might be pertinent for me this weekend as W-2s have arrived and I have a night of misery trying to get a refund. How does BIP fit in? Pictures are worth a thousands words and Fiona from the development team down under has recorded a great YouTube session explaining the integration and how to take advantage of it. I know I'm becoming more American cos I found it a little hard to place her accent at first, not to be rude Fiona but was it Brit, Aussie or maybe New Zealand? I plumped for Aussie, 10 points to me ... phew! Direct link to the OPA YouTube channel - some other great vids on use cases and features. You can find the OPA Homepage here lots of info on the tool and its possible applications.

Yep, found another integration with BIP. I get asked about Oracle Policy Automation quite a lot; I do not profess to know much; enough to be dangerous I guess. However, I was not aware that they had...

APEX

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.BasicIt 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 rowsin the first table multiplied by the number of rows in the second table. A cross join hasa 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"><ahref="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 ListNon-Supported formats: Nested List (List with Indent) Some HTML tags/attributes which manually inserted like Table, Image, etc... in stead of HTML editorSample Report: XML: html_test.xml RTF tempalte: html_test.rtf

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...

BI Publisher Enterprise

Siebel Tips and Tricks

Good tidings for the season to all of you. Its been erratic I know but we have stumbled along this year ... I have been working with Eric, a Siebel sales consultant whiz on the BIP integration. He shared some really useful tips with a Siebel-BIP customer yesterday which I thought were well worth sharing. Date Formatting Firstly, some help with formatting dates when you are using a Siebel integration objects to generate the data. Siebel IOs do not generate the XSD date format that BIP needs to let you format dates to your hearts content.  Attached are a couple files that provide some examples on how to manipulate dates to get the desired formats. Some are easy to get working and some need a little effort. MSWord Plugin Testing Siebel development have shipped  some Siebel-BIP functions that are deployed to the server. However, to be able to test them locally on your desktop you need to get the java libraries into your classpath prior to starting MSWord. Here is the code Eric uses for starting Word with the Siebel classes in a batch file.  It assumes that the jar files are available locally on the client machine at C:\811DQSSIA\Client\CLASSES.  This will make the classes used by some Siebel report templates available to test directly in Word.  I have two versions, one for Word 2003 and one for Word 2007. Word 2003: echo %1 set_JAVA_OPTIONS=-Xbootclasspath/a:C:\811DQSSIA\Client\CLASSES\SiebelXMLP.jar;C:\811DQSSIA\Client\CLASSES\XMLP.jar;C:\811DQSSIA\Client\CLASSES\Siebel.jar;C:\811DQSSIA\Client\CLASSES\XSLFunctions.jar;C:\811DQSSIA\Client\CLASSES\SiebelCustomXMLP.jar;C:\811DQSSIA\Client\CLASSES\SiebelCustomXMLP_SIA.jar; "C:\Program Files\Microsoft Office\Office12\Winword.exe" %1 Word 2007: echo %1 set_JAVA_OPTIONS=-Xbootclasspath/a:C:\811DQSSIA\Client\CLASSES\SiebelXMLP.jar;C:\811DQSSIA\Client\CLASSES\XMLP.jar;C:\811DQSSIA\Client\CLASSES\Siebel.jar;C:\811DQSSIA\Client\CLASSES\XSLFunctions.jar;C:\811DQSSIA\Client\CLASSES\SiebelCustomXMLP.jar;C:\811DQSSIA\Client\CLASSES\SiebelCustomXMLP_SIA.jar; "C:\ProgramData\Microsoft\Windows\Start Menu\Programs\Microsoft Office\Microsoft Office Word 2007.lnk" %1 Happy Holidays!

Good tidings for the season to all of you. Its been erratic I know but we have stumbled along this year ... I have been working with Eric, a Siebel sales consultant whiz on the BIP integration. He...

Excel

Excel 2007 Warnings!

As many of you have found out, everything is not as it seems with the Excel output that gets generated from an RTF template. It has non-Excel like limitations and the files are quite big. Those of you using Excel 2007 will have noticed another niggle. When you open an 'Excel' output you get a warning along the lines of: The file you are trying to open, '*.XLS', is in a different format than specified by the file extension. Verify that the file is not corrupted and is from a trusted source before opening the file. Do you want to open the file now? This is down to BIP actually generating HTML and setting the mime type and extension to get Excel to open it. This is the cause of the limitations and the cause of the warnings. The limitations can be addressed by using an Excel template more on those in the user docs and elsewhere in this blog. Addressing the warning is going to require a modification to your users' registries. Huge thanks to Abhishek Gupta for digging up a note from Oracle Support on it and thanks to whoever wrote the original. The note number is 1077728.1. I have uploaded Abhishek's snippet form the note here. Update: Leslie has reminded me; for she is my Jimney Cricket to my Pinocchio, constantly prodding my conscious :0) As an additional option in 11.1.1.5 Excel 2007 is a new output type from RTF that generates .xlsx. Excel 2007 is a new output type available for reports. When selected, BI Publisher generates the output in Excel XML format (.xlsx). If you have Excel 2007 or later installed, this option provides the best preservation of layout and formatting.As noted in the comments, the Excel templates generate binary xls output not xlsx format and therefore have xls limitations in their abilities.

As many of you have found out, everything is not as it seems with the Excel output that gets generated from an RTF template. It has non-Excel like limitations and the files are quite big. Those of you...

BI Publisher Enterprise

11.1.1.5 December 2011 Update Available

Latest and greatest from the boys and girls at the coal face for December. Bug fixes and some interesting enhancements that I hope to dig into to here soon. Search for patch 13366758: UPDATE FOR BI PUBLISHER ENTERPRISE 11.1.1.5.0 DECEMBER 2011 on Oracle Support. Make sure you check out the readme for patch installation instructions. 9102877 - QA: QUERY BUILDER GENERATES INCORRECT SQL FOR MANY DBS 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 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 13421264 - ADDING PARAMETER TO SQL BI SERVER DATA SET CORRUPTS DATA MODELEnjoy!

Latest and greatest from the boys and girls at the coal face for December. Bug fixes and some interesting enhancements that I hope to dig into to here soon. Search for patch 13366758: UPDATE FOR BI...

Templates

Dynamic Content using Sub Templates

I have written about sub templates in the past on a few occasions; the principle behind them is pretty simple. If you have common report components that can be shared across reports; be they blocks of text like standard contract clauses or maybe some common calculation or function, drop them into a sub template and share the love. Develop once, use everywhere!A colleague was recently tasked with conditionally bringing into a report output, paragraphs of static text based on some user preferences. That’s an ideal candidate for a sub template approach; drop all of the paragraphs in to an RTF subtemplate and then just conditionally pull them in based on some boolean expressions.You might, quite naturally think about conditionally importing a series of sub templates rather than defining one, with all the content. However, XSL does not allow the conditional import of sub templates so you must take the single template approach. You can of course, import multiple sub templates if you have a lot of content to bring in but in most cases I would expect a single sub template will suffice. BIP does need to know what those paragraphs need to be for each user whether that’s as a set of parameter values or a data element in the incoming data set. For this example I have used both approaches and they work all flavors of BIP. Implementation of the sub template onto the servers is going to be a little different but the main principle is the same. I have mercilessly ripped out a nice graphic from Leslie’s (doc writer extraordinaire) documentation.This is for the 11g version that supports loading sub templates into the report catalog as objects.  They can then be referenced in your main template using the import statement:<?import:xdoxsl:///subtemplatefolder/subtemplatename.xsb?>The subtemplate folder is going to be from the /SharedFolders  or /My Folders root. For instance, I have a sub template ‘paragraphs’ loaded into a ‘test’ folder under  Shared Folders. The import statement in my main template is ‘<?import:xdoxsl:///Test/ParaSubTemplate.xsb?>’ Update from LeslieFor those of you testing using your own My Folder area. The syn tax is <?import:xdoxsl:///~username/path to subtemplate.xsb?> where username is your user name. For example: <?import:xdoxsl:///~tdexter/Subtemplates/Template1.xsb?> Recommend you move them into the shared folder area in production. For 10g you will either need to drop them into an accessible directory and use the file URI or mount them into the web server directory structure and access them via an http URI. I normally mount them in a directory under the ‘xmlpserver’ directory e.g J2EE_HOME\applications\xmlpserver\xmlpserver\subtemplates, a template is then accessible via the URI ‘http://server:port/subtemplates/template.rtf’ Make sure you set the Allow External References property to true for the report so that the sub template can be accessed. The actual content of the sub template is pretty straight forward. It’s a series of paragraphs bounded by the ‘template’ command e.g. <?template:para1?>……<?end template?><?template:para2?>……<?end template?><?template:para3?>……<?end template?>Now we have the dynamic content defined it’s a case of conditionally bringing it into the main template. For this example I have demonstrated two approaches; both rely on the required paragraph information to be in the main dataset:1.    Using parameters to allow the user to select the appropriate paragraphs to be brought in. This means creating the parameters and ensuring that you have set the property on the data model to include the parameter values in the XML result set.Once that’s done its just a simple case of using id statements to check if a given paragraph should be included: <?if:.//PARA1='1'?><?call:para1?><?end if?>This assumes my parameter is called PARA1 and that a ‘1’ means include it, it could easily be a ‘Y’ or ‘True’ value, you are just testing for it. 2.    Including a value in the data to define what paragraphs should be included. If you have stored what paragraphs should be included for a given entity i.e. customer, supplier, employee, etc. Then you can extract those values into the data set and test for them. For this demo I have a 5 character set of ‘1’s and ‘0’s to represent the paragraphs that need to be included e.g. 10110. I just use a substring command to find out if a particular paragraph needs to be included. <?if:substring(.//PARAS,1,1)='1'?><?call:para1?><?end if?> Where PARAS is the element holding the ‘1’s and ‘0’s string to be parsed.You can of course use some other means of marking whether a paragraph needs to be included or not. It’s just a case of parsing out the values with a substring or similar command.You should be able to generate dynamic content such as this:  Notice that I have rendered the code so that I can see that the paragraphs are coming in i.e 10001 means we get paragraphs 1 and 5. I have built out demo files for the second method, available here.

I have written about sub templates in the past on a few occasions; the principle behind them is pretty simple. If you have common report components that can be shared across reports; be they blocks of...

BI Publisher Enterprise

UPDATE FOR BI PUBLISHER ENTERPRISE 10.1.3.4.2 NOVEMBER 2011

It's Friday, that means its patch release time. Why do we do this to ourselves, 'we'll release on Friday!' It might 11.59 on Friday but by golly we'll have released on Friday. I can remember a release of BIP years ago that for some reason we went for 12/31 as a release date ... were we mad? I seem to remember we made it but talk about ridiculous pressure! The latest 10g rollup is out in the wild and available from Oracle support. A bug fixing rollup but worth getting to and know that support will want you to get to it and re-test before going forward on an SR. One simple but very useful fix or enhancement:[Cause of the bug] @ ================== @ Customer reports that despite the clock being shown, end users are clicking @ on the View button repeatedly as the initial generation is taking some time.   @ If the button were to be grayed out then  this would prevent the users @ requesting the report more than  once.  Repeated requests are causing a @ system overload and as this is their Production  instance this is extremely @ important to the customer. @ . @ [The Fix] @ ========= @ Added the logic to disable the button after the user clicks on the "view" @ button and re-enable it when the report is loaded. I told a group of customers once that they have a headache and we have a non-steroidal anti-inflammatory drug, alright, I actually said 'aspirin'. This little gem of a fix helps relieve another little headache that our aspirin was causing. The patch number for all this BIP pain killing is 13399232, enjoy!

It's Friday, that means its patch release time. Why do we do this to ourselves, 'we'll release on Friday!' It might 11.59 on Friday but by golly we'll have released on Friday. I can remember a release...

BI Publisher Enterprise

Time for a Timeout

Couple of inquiries this week around the default timeout for BIP 11g installs and being able to change it. Its not currently documented (Leslie is working on that :) and when you see what you need to do you might understand why. It's ugly, very ugly but in the interest of getting the information out there, here we go. The BIP session timeout is controlled in a section in the web.xml file under the WEB-INF directory. <session-config> <session-timeout>30</session-timeout></session-config> The weblogic server (WLS) has a couple of features that are one, a pain and two useful. The first is the inability to update the web application's web.xml and have the changes take effect when you restart WLS. So far in my web diggings, I have found that you have to completely delete the application from WLS, update the web.xml and then re-deploy it with your new settings. Here's the steps: Locate the xmlpserver.war under the MW home and back it up somewhere. Log into the WLS console >> Deployments Delete the BIPublisher entry. BIP is now gone from the server Back to the war file. Unpack it, preserving directories Go into the WEB-INF directory and edit the web.xml file and increase the timeout value and save the file. The setting is in the session-config section:<session-config>    <session-timeout>60</session-timeout>  </session-config> Zip the war back up preserving the directories Back in the console UI goto Deployments and 'Install' the BIP war file. Just accept all the defaults. You can now log into the BIP application and the new timeout will be applied It's messy I know but it works. Now, some of you may have found the page in the WLS console application that allows you to set a session timeout. Naturally, you'd think this would work if you changed it (that's the other feature of WLS that is useful.) The web.xml setting however, takes precedence over the WLS setting. I have not tried this yet but I have been told that, if you remove the session-config section from the web.xml file and then redeploy BIP, the WLS setting will be respected. It's gotta be a better way to manage sessions. Just remember that until the development team either provide a better way to do it or remove the default time out in the web.xml file you are going to have to maintain your timeout method if you patch or upgrade.

Couple of inquiries this week around the default timeout for BIP 11g installs and being able to change it. Its not currently documented (Leslie is working on that :) and when you see what you need to...

RTF

Tricky Grouping

A week off trying to catch up with all the stuff that builds up was not enough but it was pretty fruitful. You clear the decks the week before you leave, you even answer a few mails in the first few days of vacation, until your manager slaps your wrists. But there is always a pile of work waiting when you get back. It was not too bad this time; I at least deleted the crap mail during the week via my phone.How the heck did we survive without smart phones? I took a trip to north Denver last week to pick up a part for our bear ravaged pop up camper. I think he wanted a cold beer from the fridge just wish he'd used the front door rather than slicing the camper open. That gaping hole is meant to be covered! I guess I could have checked a map before we left but my phone got us through the mess of roads up there. An 80 mile round trip for a $10 louvered cover! Still, we found a great Indian place for lunch, delicious curry! Today's tidbit (titbit for my British brethren) is a bit tough to describe. It comes from Charlotte in New Zealand. On the surface it looks simple and it is when you know how :0) But there's a wrinkle in to smooth out.Here's the data: <DATA_DS> <G_1>  <SERIALNUMBER>HSS0000156</SERIALNUMBER>  <LOCATION>1 The Street Anytown USA</LOCATION>   <ATTRIBDESC>Lettable Date</ATTRIBDESC>  <ATTRIBVALUE>15/JUN/11</ATTRIBVALUE>  </G_1> <G_1>  <SERIALNUMBER>HSS0000156</SERIALNUMBER>  <LOCATION>1 The Street Anytown USA</LOCATION>   <ATTRIBDESC>Status Reason</ATTRIBDESC>  <ATTRIBVALUE>Planned Major Repair/Upgrade</ATTRIBVALUE>  </G_1> <G_1>  <SERIALNUMBER>HSS0000276</SERIALNUMBER>  <LOCATION>11 The Street Anytown USA</LOCATION>   <ATTRIBDESC>Lettable Date</ATTRIBDESC>  <ATTRIBVALUE>18/MAY/11</ATTRIBVALUE>  </G_1> <G_1>  <SERIALNUMBER>HSS0000276</SERIALNUMBER>  <LOCATION>11 The Street Anytown USA</LOCATION>   <ATTRIBDESC>Status Reason</ATTRIBDESC>  <ATTRIBVALUE>Planned Major Repair/Upgrade</ATTRIBVALUE>  </G_1> <G_1>  <SERIALNUMBER>HSS0001046</SERIALNUMBER>  <LOCATION>21 The Street Anytown USA</LOCATION>   <ATTRIBDESC>Lettable Date</ATTRIBDESC>  <ATTRIBVALUE>21/JUN/11</ATTRIBVALUE>  </G_1> <G_1>Heres the desired output: Serialnumber Status Reason Date HSS0000156 Planned Major Repair/Upgrade 15/JUN/11 HSS0000276 Planned Major Repair/Upgrade 18/MAY/11 HSS0001046 Planned Major Repair/Upgrade 21/JUN/11 HSS0001303 Planned Major Repair/Upgrade 19/MAY/11 HSS0001403 Planned Major Repair/Upgrade 14/JUN/11 Hok-Min, Zen Master of the RTF template, jumped on this for me and nailed it first time. On first look you think, ah this is just a simple re-group left, using some xpath to repeat the ATTRIBVALUE element twice in the table based on the ATTRIBDESC. Along the lines of: <?ATTRIBVALUE[../ATTRIBDESC='Planned Major Repair/Upgrade']?> and <?ATTRIBVALUE[../ATTRIBDESC='Lettable Date']?> But you oh so very easily end up with this: Serialnumber Status Reason Date HSS0000156 15/JUN/11 Planned Major Repair/Upgrade HSS0000276 18/MAY/11 Planned Major Repair/Upgrade HSS0001046 21/JUN/11 Planned Major Repair/Upgrade HSS0001303 19/MAY/11 Planned Major Repair/Upgrade HSS0001403 14/JUN/11 Planned Major Repair/Upgrade and start scratching your head? With the 10.1.3.4.1 and 11g template builders there is a nice Group Left feature that removes the need for nasty nested tables but for this slightly corner case, it drops you into a world of misery. You need to take a step back from what the wizard has given you. Yes, you need to group by serial number but you do not need to loop over the remaining data (current-group().) Rather, you just need to drop into that current-group() and bring the values you need up to the level of the serial number group. So you just need to modify your XPATHs thus: <?current-group()/ATTRIBVALUE[../ATTRIBDESC='Status Reason']?> i.e. provide a complete path to the element you want within the serial number re-group level. Neat! Template and sample data available here.

A week off trying to catch up with all the stuff that builds up was not enough but it was pretty fruitful. You clear the decks the week before you leave, you even answer a few mails in the first few...

BI Publisher Enterprise

BIP and Mapviewer Mash Up IV

Dang folks, we're on part four already, things start to get really groovy now. If you're jumping in at part four, here are the previous episodes: BIP and Mapviewer Mash Up I BIP and Mapviewer Mash Up II BIP and Mapviewer Mash Up III The current approach to get the maps into the output using encoded concatenated URL strings works but if you need to make changes to the map request call it gets fiddly and annoying quite quickly and if you need to drop another map into another output you'll need to embed the code into the second RTF template. What if we could simplify all of this and create a 'map request broker' that sat between the template and the map server? Even better if that broker could handle any map request and support as many layout templates that I want. I have just the solution built and ready to roll. Of course all three components might be running on the same physical server inside the same weblogic domain. Its a pretty simple concept and it makes life so much easier when embedding a map into an output. The servlet is called from the RTF template using a URL that is constructed on the fly; we can also pass parameters on the URL to influence the map. For my example those parameters include the mapviewer server and port (to make the template portable) the map title, what needs to be mapped and the map request file to be used. The servlet receives the request grabs the parameters off the URL. It then reads the map request files and substitutes the appropriate variable values and then makes the call to mapviewer requesting a map. Once it receives the map it streams the image back to the template for rendering by BI Publisher. No messy encoded URL's minimal concatenation in the template to create the servlet URL and I have fine control over the map request. Best of all any template can call the same servlet, we just need the base map request on the server. The Breakdown There are three components to consider, what's in the RTF template the servlet code and the map request. Lets look at the map request first. <?xml version="1.0" encoding="UTF-8"?><map_request title="param1"  basemap="world_map"  datasource = "obiee_navteq_sample"  width="640"  height="480"  bgcolor="#a6cae0"  antialiase="false"  format="param2"> <center size="45">  <geoFeature>   <geometricProperty typeName="center">    <Point srsName="SDO:8307">     <coordinates>-96, 34</coordinates>    </Point>   </geometricProperty>  </geoFeature> </center> <legend bgstyle="fill:#ffffff;stroke:#ff0000"          profile="MEDIUM"          position="SOUTH_WEST">         <column>          <entry text="Number of Renal Disease Cases:"/>          <entry style="V.POPULATION_COUNTY" tab="1"/>         </column> </legend> <themes>  <theme name="theme1" min_scale="5.0E7" max_scale="0.0">   <jdbc_query     datasource="obiee_navteq_sample"     jdbc_srid="8307"     spatial_column="geometry"     render_style="OBIEE_NAVTEQ:V.POPULATION_COUNTY">    SELECT geometry, param3     from obiee_state     where iso_country_code='USA'</jdbc_query>  </theme> </themes></map_request> Its just a well formed XML file. This has loads going on and to find out more you're going to have to hit the books - the mapviewer documentation is here - check out the XML API section for some samples. I was playing with it last night and found that the 'size' attribute under the 'center' element controls the zoom level ... go figure. But combining that with the 'coordinates' value under geoFeature and some nifty LOVs in your BIP report and folks can move around a map and zoom in and out. More on that later.  Testing your map requests is simple enough inside the mapviewer web console http://server:port/mapviewer >> Requests. Just copy, paste and submit. The other features of note are my parameters highlighted in blue. Before I pass this request on to the mapviewer server I do a search and replace on the 3 parameters. You'll see that in the servlet code. Remember, this XML is not hard coded into the servlet, its a separate XML file in a 'resource' directory that is read by the servlet. Taking this approach I can re-use the servlet for multiple map requests. Next, the servlet code, ready for my awesome java skills? Read on ... package oracle.bipmapper;//Read-write libsimport java.io.BufferedInputStream;import java.io.File;import java.io.FileInputStream;import java.io.IOException;import java.io.InputStream;import java.io.OutputStream;//Handle the URL callimport java.net.HttpURLConnection;import java.net.URL;import java.net.URLEncoder;//Servlet libsimport javax.servlet.*;import javax.servlet.http.*;public class bipmap extends HttpServlet { private String CONTENT_TYPE = ""; public void init(ServletConfig config) throws ServletException { super.init(config); } public void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException { doPost(request, response); } public void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {((1)) - Fetch the URL params. Check that we have values, if not, assign defaults. String measure1 = (request.getParameter("a1").toString() == "") ? "sqkm" : request.getParameter("a1"); String server = (request.getParameter("serv").toString() == "") ? "75.101.156.237" : request.getParameter("serv"); String port = (request.getParameter("port").toString() == "") ? "9704" : request.getParameter("port"); String title = (request.getParameter("title").toString() == "") ? "Default Title" : request.getParameter("title"); String format = (request.getParameter("format").toString() == "") ? "PNG_STREAM" : request.getParameter("format"); String filename = (request.getParameter("file").toString() == "") ? "mapreq.xml" : request.getParameter("file") + ".xml"; // Needed objects for the mapviewer call HttpURLConnection huc = null; URL mapAddress = null; ((2)) Set the content format based on the format parameter value // The else value is just text, thats for the SVG support if (format == "PNG_STREAM") { CONTENT_TYPE = "image/png"; } else if (format == "JPG_STREAM") { CONTENT_TYPE = "image/jpg"; } else { CONTENT_TYPE = "text/javascript"; } response.setContentType(CONTENT_TYPE); ((3)) //Find the request file ServletContext context = request.getSession().getServletContext(); String separator = System.getProperty("file.separator"); final String TMPDIR = context.getAttribute("javax.servlet.context.tempdir").toString(); // With WLS the tempdir drops you into the _WL_user/APP_NAME/RANDOM_DIR/public // we need the 'war' directory to find the resource directory String reqFile = TMPDIR.substring(0, TMPDIR.lastIndexOf(separator)) + "/war/resource/" + filename;((4))// Construct the start of the URL map request String mapURL = "http://" + server + ":" + port + "/mapviewer/omserver?xml_request=";((5)) // Load the XML request file String xmlReq = getMapReq(reqFile); ((6)) // Look for a replace the parameters in the XML request. xmlReq = xmlReq.replace("param1", title); xmlReq = xmlReq.replace("param2", format); xmlReq = xmlReq.replace("param3", measure1); InputStream is = null; ((7)) // Fetch the output stream from the mapviewer request try { mapAddress = new URL(mapURL + URLEncoder.encode(xmlReq)); huc = (HttpURLConnection)mapAddress.openConnection(); huc.setDoOutput(true); huc.setDoInput(true); huc.setUseCaches(false); huc.setRequestMethod("POST"); //Use HTTP POST method. is = huc.getInputStream(); // Write the image stream back to the caller ie BIP in the template layer OutputStream out = response.getOutputStream(); byte[] buf = new byte[1024]; int len = 0; while ((len = is.read(buf)) >= 0) { out.write(buf, 0, len); } is.close(); out.close(); } catch (Exception e) { e.printStackTrace(System.err); System.exit(1); } } private static String getMapReq(String fname) throws java.io.IOException {((5.5)) // Fetch the XML request file and load it into a string object byte[] buffer = new byte[(int)new File(fname).length()]; BufferedInputStream f = null; try { f = new BufferedInputStream(new FileInputStream(fname)); f.read(buffer); } finally { if (f != null) try { f.close(); } catch (IOException ignored) { } } return new String(buffer); }} I have highlighted the important bits: (1) Standard stuff here, pulling the parameters off the URL and assigning them to string variables. For the un-initiated each entry is an inline if statement. (2) Im checking the requested output so that I can set the appropriate content type. In my example Im supporting PNG, JPG and SVG. The SVG is handled in the else condition. More on that in another post. (3) Here I fetch the request XML file. Its in a 'resource' directory in the deployment. This was a bit of a pain, I expected the 'javax.servlet.context.tempdir' to return he root of the web application but in weblogic you end up in a 'public' directory under the root. My resource directory is actually under the 'war' directory under the root so this piece of code: ServletContext context = request.getSession().getServletContext();String separator = System.getProperty("file.separator");final String TMPDIR = context.getAttribute("javax.servlet.context.tempdir").toString();String reqFile = TMPDIR.substring(0, TMPDIR.lastIndexOf(separator)) + "/war/resource/" + filename; does some substringing and concatentation to build a path to the correct directory. Im sure there is an easier way but it works for now. Note the user of the 'separator' string so that the servlet can be deployed to windblows, unix or linux. I had a devil of a time catching that one. I developed on Linux but it would not run on windblows until I grabbed the appropriate separator ... grrr! (4) Starting to construct the URL request for the XML map request (5) Fetching the map request XML from the file in the resource directory   (5.5) This is the function to read the map request file and load it int a string variable (6) A simple replace for each of the parameters created in the map request. We could have gotten fancier here with an XML parser but its not a huge string and there are three replacements to do. Its not a slow process. (7) Now we construct the full http request for the map. Note the encoding of the xml request string, thats a must. Once we get the result we just have the servlet write it back to the response object for BIP to render. Nothing earth shattering, could it be better, sure, if you fail to pass enough parameters you get an ugly null pointer exception which could be handled better but just make sure you pass enough parameters :0) Finally the RTF template, its a case of declaring parameters for the report parameters and then building the URL request to the servlet. You can see the fields in the graphic on the right. The only ones of note are: The file parameter, allows you to point to any deployed request file The mURL that constructs the URL. Its just a big long concat with the name|value pairs for the servlet parameters. Then just use the external-graphic place holder as we have before to reference the mURL parameter. The curly braces {} get BIP to pre-process the value before processing the graphic. Finally ... Thats it, put the three pieces together et voila, you have a mapping solution that's more manageable and configurable. You can of course increase the number of parameterized values in the xml map request. I have bundled up the JDeveloper (11.1.1.5) project as it is ... its only really got the servlet and XML request in it. You can just copy and paste into your own project - get the project here. I have also zipped the 11g BIP report with the RTF template (Mapviewer4.rtf) get that here. For 10g'ers just unzip the xdoz file to get at the RTF template. So, we can now get maps into our BIP outputs, the quality is pretty good but it could be better. Having spent some time digging around in the mapviewer docs I spotted that they support SVG outputs ... more on that next time.

Dang folks, we're on part four already, things start to get really groovy now. If you're jumping in at part four, here are the previous episodes: BIP and Mapviewer Mash Up I BIP and Mapviewer Mash Up II B...

RTF

BIP and Mapviewer Mash Up III

This is the third installment of the BIP and Mapviewer Mashup, for the previous entries: BIP and Mapviewer Mash Up I BIP and Mapviewer Mash Up II BIP and Mapviewer Mash Up III Its been a hectic couple of weeks which has included all things mapviewer and integration. I have finally got my piece de resistance in mapping integration working but more on that next time. Its very cool in a geeky BIP, kinda way; my wife was completely fascinated when I told her all about it over dinner last night. Either that, or she has become very adept at nodding and saying 'that's nice honeycakes' at appropriate times. I hope the former but suspect the latter :0( The next part of this mashup series, I said we would parameterize the map call. That's actually pretty easy to do, just a bit more effort with the parameters and the concat functions. 1. Set up your parameters in the data model. These do not have to be tied to the query. We support 'template ' only parameters. they just all have to be defined in the datamodel. Note the name you give to the parameter(s) 2. In your RTF template you need to declare 'interest' in the parameters. Its the same as the CURRENT_SERVER_URL format. <?param@begin:name;defaultvalue?> 3. In the fields where you are building the encoded request string you can use the concat function to drop the parameter values into the string. In this case we are changing th map title with a parameter called 'title' <xsl:param xdofo:ctx="begin" name="pMapRq">concat("%3Cmap_request%20title%3D%22",$title,"%20basemap%3D%22world_map%22%20datasource%20%3D%20%22obiee_navteq_sample%22%20width%3D%22640%22%20height%3D%22480%22%20bgcolor%3D%22%23a6cae0%22%20antialiase%3D%22false%22%20format%3D%22JPG_STREAM%22%3E")</xsl:param> Its not tough but boy is it a bit of a nightmare to manage and keep track of everything in that encoded URL. As I mentioned in my last post its a good idea to maintain the parts of the URL as un-encoded text in the template surrounded by an 'if:1=2' if statement to keep it hidden at runtime. Next post, we get to a much more robust, easier to manage and as I mentioned cooler solution ... enter the mapping servlet. It acts as a map request broker between the BIP template and the mapviewer server. The RTF template is not full of encoded URLs but just a simple URL call to the servlet that will call the mapviewer server and stream the image back to BIP for rendering. I have built in a couple of tricks but more on that next time.

This is the third installment of the BIP and Mapviewer Mashup, for the previous entries: BIP and Mapviewer Mash Up I BIP and Mapviewer Mash Up II BIP and Mapviewer Mash Up III Its been a hectic couple of...

RTF

BIP and Mapviewer Mash Up II

Quite some time ago now I wrote the first of what I thought were going to be at least a couple of articles on getting BIP to render maps via Oracle Mapviewer. It was a real HelloWorld example with no 'World' just the 'Hello' bit. I like to think it was like Kevin Costner's 'Waterworld', a nice idea but poorly executed and a flop at the blog box office but it would have been a perfect map for the movie. I have to admit, I think Dennis Hopper was awesome as the bad dude. Well, I recently needed to show something more than an area of blue ocean, we needed some land. As you'll see if you go back to the original post, I have dabbled, I know how to render a map view in OBIEE but I knew I needed to lean on our resident map meister David to take this forward. David lives and breathes maps and probably knows the Oracle GIS solutions better than the folks that wrote them. After a conversation, I was on the right track with the original post, mapviewer has an XML API that is accessible via a URL. Its just a case of building the URL and calling mapviewer and getting the result rendered by BIP. One thing that David noted was to not use the XML data that the template had access to. You can create your set of name/value pairs from the XML and pass it on to the URL. But imagine doing that for even a map showing data across all 50 states of the US or countries across the EU or APAC. It's going to get large very very quickly. The XML API does support passing a query to the mapviewer server for it to execute fetch and format the data into a map. Yep, we're breaking one of Publisher's cardinal rules, going back to the db for more data but the benefits far out weigh the costs. Armed with this information I got going. We have been working with a customer that needs this functionality (Hi Wilson :-) so I had a sample XML request to play with: <?xml version="1.0" standalone="yes"?><map_request title="US Renal Disease Rates" basemap="world_map" datasource = "obiee_navteq_sample" width="640" height="480" bgcolor="#a6cae0" antialiase="false" format="PNG_STREAM"> <center size="45">  <geoFeature>   <geometricProperty typeName="center">    <Point srsName="SDO:8307">    <coordinates>-96, 34</coordinates>    </Point>   </geometricProperty>  </geoFeature> </center> <legend bgstyle="fill:#ffffff;stroke:#ff0000" profile="MEDIUM" position="SOUTH_WEST">  <column>   <entry text="Number of Renal Disease Cases:" />   <entry style="V.POPULATION_COUNTY" tab="1" />  </column> </legend> <themes>  <theme name="theme1" min_scale="5.0E7" max_scale="0.0">  <jdbc_query    datasource="obiee_navteq_sample"   jdbc_srid="8307"    spatial_column="geometry"    render_style="OBIEE_NAVTEQ:V.POPULATION_COUNTY"> SELECT geometry,sqkm from obiee_state where iso_country_code='USA'</jdbc_query>  </theme> </themes></map_request>  That's a hunk of XML to pass right, there is a lot going on in there. It basically sets the base map, size, center point, themes (or layers) to be added. The format is important, when you hop on over to the mapviewer doc you'll see that the format can take multiple values in our case we need 'XXX_STREAM' where XXX is the image format name e.g. PNG, JPG, etc. If you just request XXX then you get a URL string to the image on the server (that will come in handy in an upcoming post but not here) Finally the query that needs to be executed; this is the important bit for us; it needs to marry a map related column to some measure in this case 'sqkm'. Once the data set is returned, mapviewer can then 'map' the data. Our XML gets us this map to the left. Big prizes for the first one to spot whats wrong with the map? and why? Answers can be placed on the down tube of a shiny new 58cm Cervelo S5 and sent to the usual address. For those of you that have gone back to part I of this post, you'll remember that we need to encode all of the XML before we can use it. Otherwise BIP gets all upset and reports a problem. When I started on this example I started to encode it all by hand, not a good plan and much shouting at the monitor ensued. Being lazee or smart, take your pick, I jumped on Google to look for some kind soul that had provided some web page that would do the encoding for me. Deepest thanks go out to the owner of http://meyerweb.com/eric/tools/dencoder/ you saved me from pulling out what hair I have left. For someone that has very little hair; I sure do talk about it on this blog a lot; maybe I need to talk to someone about that or get a toup? Encoding nightmare solved but its going to be a big ugly piece of text to manage if I just encode the lot and assign it to a parameter. I decided to break it up a bit into sections and then use a concat function to bring it all back together. <?param@begin:mReq;concat($mURL,$pXMLStr,$pMapRq,$pCenterOp,$pGeo,$pCentCl,$pLeg,$pTheme,$pMapRqCl)?> The individual pieces make the string more manageable in terms of needing to make changes. I would recommend embedding the actual XML string into the template and noting which section belongs to which parameter and then surrounding it with an IF statement to hide it at runtime. The concat generated a big ol URL that I can test in a browser to ensure its going to return the map I want. You'll see in the template that you can test on the desktop too, as long as you have access to the mapviewer server. Once you have the URL correct its just a case of dropping it into a form field: <fo:external-graphic src="url({$mReq})"/> At runtime the URL is resolved, called and the map returned to BIP for rendering.  For those of you on 11g (I have tested this on 11.1.1.5 BIP) you can download the complete report here. You just need to upload it and probably change the data connection on the data model. It relies on the obiee_navteq db user that is installed with the BIEE sample app. For those of you on 10g, heres the template and some sample data to play with. Next for this series, parameterizing the XML so that users can set various features at runtime. Imagine being able to ask for 'Renal disease cases by state' for one request and then 'Liver disease cases by country' for another using the same report/template.Following that, after my somewhat 'hit it with a big hammer until it submits' (we're good at that in our house :) approach I have plans for something a bit more sophisticated.

Quite some time ago now I wrote the first of what I thought were going to be at least a couple of articles on getting BIP to render maps via Oracle Mapviewer. It was a real HelloWorld example with no...

BI Publisher Enterprise

BI Publisher sessions for Oracle Open World 2011

Are you coming to Oracle Open World 2011? No?  There's still time to register!  The conference starts Sunday, Oct 2nd Yes?  Great!  This year, in addition to the awesome sessions and demo pod, there are several hands on labs where you can get your hands on the product. For a list of all the sessions that will highlight BI Publisher, please check out the Focus On Business Intelligence Publisher handout. Be sure to add these sessions using the Schedule Builder, so you can be assured a spot.  BI Publisher Demo Pods:Oracle Business Intelligence Publisher    Moscone South, Right –SR-287PeopleSoft PeopleTools     Moscone West – W-074Oracle DEMOgrounds Schedule: MONDAY, OCTOBER 3:     9:45 am – 5:30 pmTUESDAY, OCTOBER 4:    9:45 am – 6:00 pmWEDNESDAY, OCTOBER 5:    9:00 am – 4:00 pm Sessions and Hands On Labs: Everyone Sun 2-Oct 1:00 pm – 1:45 pm 28541: Upgrading Your Oracle BIEE Software? Leverage 11g New FeaturesMoscone West 2011Mon 3-Oct 3:30 pm – 4:30 pm    32020: Building Reports and DataModels in Oracle Business Intelligence Publisher 11g (HANDS ON LAB)    Marriott Marquis - Salon 5/6Mon 3-Oct 5:00 pm – 6:00 pm    14982: Create All Your ReportsMore Easily and Quickly with Oracle Business IntelligencePublisher    Moscone West 3024Mon 3-Oct 5:00 pm – 6:00 pm    12426: Oracle BusinessIntelligence Publisher: The Reporting Platform for OracleApplications   Moscone West 2022Wed 5-Oct 1:15 pm – 2:15 pm    32020: Building Reports and DataModels in Oracle Business Intelligence Publisher 11g  (HANDS ON LAB)    Marriott Marquis - Salon 5/6Wed 5-Oct 5:00 pm  - 6:00 pm    16762: Become a ReportingSuperstar with Oracle Business Intelligence Publisher BestPractices   Moscone West 2002/2004Thr 6-Oct 1:30 pm – 2:30 pm    15010: Oracle BusinessIntelligence Publisher: Anatomy of a Template Moscone West -  2000JD Edwards Enterprise Tue 4-Oct 10:15 am  - 11:15 am    8009: OperationalReporting with Oracle Business Intelligence Publisher and JD Edwards: SuccessStory     Moscone West 3008Wed 5-Oct 1:15 pm – 2:15 pm    15308: Simplify Your JD EdwardsReporting with Oracle Business Intelligence Publisher    St. Francis - St. Francis Suite EastThr 6-Oct 10:30 am -11:30 am    21406: Empower JD Edwards Userswith Oracle Business Intelligence Publisher for Ad Hoc Reporting (HANDS ON LAB) Marriott Marquis - Nob Hill ABThr 6-Oct 12:00 pm -1:00 pm    21406: Empower JD Edwards Userswith Oracle Business Intelligence Publisher for Ad Hoc Reporting (HANDS ON LAB)    Marriott Marquis - Nob Hill ABPrimavera Tue 4-Oct 10:15 am  - 11:15 am    9774: PrimaveraApplication Users, Welcome to Oracle Business Intelligence Publisher Moscone West 2016Thr 6-Oct 9:00 am - 10:00 am    29803: Using Oracle BusinessIntelligence Publisher with Primavera Contract Management (HANDS ON LAB)    Marriott Marquis - Nob Hill CDE-Business Suite Sun 2-Oct 11:45 am –  1:00 pm    28214: BusinessIntelligence (BI) Publisher OAUG SIG   Moscone West 3006 Siebel Tue 4-Oct 5:00 pm – 6:00 pm    18234: Building More-InsightfulReports with Siebel Reporting Solutions Moscone West 2010PeopleSoft Thr 6-Oct 10:30 am – 11:30 am    14002:  PeopleToolsDeveloper Series: Mastering PeopleSoft's Reporting Tools   Moscone West 2024

Are you coming to Oracle Open World 2011? No?  There's still time to register!  The conference starts Sunday, Oct 2nd Yes?  Great!  This year, in addition to the awesome sessions and demo pod, there are...

BI Publisher Enterprise

11g September Rollup

The latest rollup patch for BIP 11.1.1.5 is available, 12954375. For your delight and delectation and to bulk out this post, I have reproduced the readme file below. This patch contains fixes of following bugs for Oracle Business Intelligence Publisher 11.1.1.5.0. 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 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 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 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 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 12791282 - WS PROXY WIZARD CANNOT GENERATE PROXY WHEN SERVICE NAME HAS SPECIAL CHARACTERS 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 12972852 - DYNAMIC IMAGES BROKEN FOR FO/PDF 12989467 - NEED TO CONVERT DOCUMENT FORMAT "EXCEL" TO "EXCEL2000"----------------------------- INSTALLATION FOR ENTERPRISE-----------------------------1. Login to BI Domain Weblogic Administration Console.2. Find the location of the "bipublisher".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.ExampleSELECT 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.BasicIt 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 Plus you have the new XHTML formatter included in the patch too, more on that here.

The latest rollup patch for BIP 11.1.1.5 is available, 12954375. For your delight and delectation and to bulk out this post, I have reproduced the readme file below. This patch contains fixes of...

Oracle

Integrated Cloud Applications & Platform Services