Tim Dexter

Subscribe to Tim Dexter feed
from pixel-perfect to interactive reporting...
Updated: 5 hours 43 min ago

Supporting Font Encoders on 11g and 12c

Wed, 2017-03-15 16:55

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)

Categories: BI & Warehousing

Oracle BI Publisher 12.2.1.1 released !!

Sat, 2016-06-25 07:06

Oracle BI Publisher 12.2.1.1.0 has been released this week. The links to download files, documentation and release notes are available from BI Publisher OTN home page. The download is also available from Oracle Software Delivery Cloud.

The new features in this release are primarily driven by the integrated Cloud Applications and Platform Services. Data Security, Self-Service, Robustness, Easier Integration and Cloud based Data & Delivery has been the main focus here. Check the new features guide available in the BI Publisher OTN home page for a quick glance at these new features.

Upgrading to Oracle Business Intelligence from 12.2.1.0 to 12.2.1.1 is an in-place upgrade performed by Upgrade Assistant.

Migration of Oracle Business Intelligence from 11g to 12.2.1.1 is an out-of-place upgrade similar to 12.2.1.0 release, but now you do not need to separately migrate BI Publisher configuration as a post migration step and you can use Baseline Validation Tool to verify the upgraded BI Publisher reports. 

Stay tuned for more information on the new features, upgrade and migration.

Have a nice day !

Categories: BI & Warehousing

Best Practices for the cloud !!

Mon, 2016-05-30 05:19

Greetings!!

Wish everyone a very happy Memorial Day !! 

Last week we published in OTN a white paper with title "Oracle BI Publisher Best Practices for SaaS Environments". As the title suggests, this white paper is a compilation of all the best practices for BI Publisher that are relevant to a Cloud Platform, especially for Fusion Applications. You can find the link to this white paper here. We will soon have an updated best practices guide for on-premise installations, so stay tuned.

Have a nice day !! 

Categories: BI & Warehousing

Revisiting "Continued..."

Tue, 2016-03-15 06:39

Adding "Continued.." to the bottom of a table if the content spills over more than one page is a very common requirement for Customer Bills. I am sure most of you have already seen Tim's blog on this topic. Just wanted to add a small note here which I got as a quick tidbit from our template expert, Hok Min. This requirement came from a telecom customer:

  1. The invoice had multiple tables giving different bill breakups such as "Current Charges", "Usage Charges", "Discounts", "Itemized bills for Local Calls", "Itemized bills for STD Calls" etc. Among these, any of the table could spill over to next page in any of the pages.
  2. The itemized bills were grouped under a category "Your Itemized Bill"

The requirement was

  1. Whenever a table splits across page, the next page should repeat the table header and should also display "(Continued ..)" in the table header 
  2. If the table is inside the category - "Your Itemized Bill", then the heading "Your Itemized Bill" should repeat in the next page added with "(Continued ..)" text
  3. With multiple tables within the category "Your Itemized Bill", the "(Continued ...)" message should be displayed for all tables if they split across page.

This can be seen here in the images:

Page 1: Here "YOUR ITEMIZED BILL" and "Local Calls" starts in this page. 



Page 2: Here "YOUR ITEMIZED BILL" and "Local Calls" are in continuation from previous page while "STD Calls" table starts in this page.



Page 3: Here "YOUR ITEMIZED BILL" and "STD Calls" are in continuation from previous page. 


We can use the same code logic that was explained in Tim's blog. The main thing to note here is that the init-page-total should be included within each table. If the init statement of a table is kept outside then it will not be able to reset the context to display "Continued ..." correctly. Here the first two rows of the external table and the nested table are marked to "Repeat as header row at the top of each page". The itemized bills are displayed grouped by date, therefore for-each-group is done in the third row of the nested table and the last row has the for-each loop to display each transaction.


The below image shows the code corresponding to the above table design. Notice the use of  display-condition="exceptfirst" so that "(Continued..)" text will show in all table-headers except the first one. 

You can find the sample RTF template and XML data here

Stay tuned for more updates... 

Enjoy :) !! 

Categories: BI & Warehousing

Up in the JCS Clouds !!

Wed, 2016-01-27 04:05
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!! 

BI Publisher on JCS

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

  1. Set up RCU on DBaaS
    • Copy RCU
    • Run RCU
  2. Install BI Publisher in JCS instance
    • Copy BI Installer in JCS instance
    • Run Installer
    • Use Software Only Install
  3. 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:

  1. 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
  2. Oracle Database Cloud Service: 1 OCPU, 7.5 GB Memory, 90 GB Storage
    • To install RCU
    • To use DBaaS as a data source
  3. 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 !!

Categories: BI & Warehousing

Oracle BI Publisher 12c released !!

Mon, 2015-10-26 04:43

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: 

  1. 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.  
  2. 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.
  3. 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:

  1. 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
  2. Install BI
    • Launch installation by invoking executable ./bi_platform-12.2.1.0.0_linux64.bin
  3. Configure BI
    • Run Configuration Assistant
  4. 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 !!

Categories: BI & Warehousing

PDF417 for E-Business Suite

Mon, 2015-10-19 16:49

A while back I wrote up a how to on 2D barcode formats. I kept things generic and covered the basics of getting the barcodes working.  Tony over in Bahrain (for we are truly international :) has had a tough time getting it working under EBS. Mostly to do with the usual bug bear of the JAVA_TOP, XX_TOP and getting class paths set up. Its finally working and Tony wanted to share a document on the 'how' to get PDF417s working under EBS.

Document available here.

Thanks for sharing Tony!

Categories: BI & Warehousing

Orphan Table Rows ... ugh!

Fri, 2015-10-09 10:57

This week, orphaned table rows and how to avoid them.

Its a bit more subtle than rows breaking across a page border and the solution is a doozy!

Im using another video to demonstrate because

  1. I don't have to type and grab screen shots, even thou I have one above
  2. Its faster and more easily understood, even in my umming and erring English.
  3. I'm hip and happening and video help is the future kids!
  4. You get to hear my Southern (England) drawl; a great sleep aid for insomniacs!

Here it is. You might want to 'fullscreen' it. Enjoy!


Categories: BI & Warehousing

Page Borders and Title Underlines

Wed, 2015-08-26 16:32

I have taken to recording screen grabs to help some folks out on 'how do I' scenarios. Sometimes a 3 minute video saves a couple of thousand words and several screen shots.

So, per chance you need to know:

1. How to add a page border to your output and/or

2. How to add an under line that runs across the page

Watch this!   https://www.youtube.com/watch?v=3UcXHeSF0BM

If you need the template, sample data and output, get them here.

I'm taking requests if you have them.

Categories: BI & Warehousing

WebCenter Content - A new Delivery Channel in 11.1.1.9.0 !!

Wed, 2015-06-03 03:13

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

Categories: BI & Warehousing

Oracle BI Publisher 11.1.1.9.0 is available !!!

Mon, 2015-05-25 14:12

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

Categories: BI & Warehousing

BIP scheduleReport with Parameters

Wed, 2015-05-20 15:37

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.

Categories: BI & Warehousing

How do I ...

Thu, 2015-02-12 15:23

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#BIPAD294
Then create your data model using that Essbase connection
http://docs.oracle.com/cd/E28280_01/bi.1111/e22258/create_data_sets.htm#BIPDM404
Use 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#BIPDM431
Add parameters (optional)
http://docs.oracle.com/cd/E28280_01/bi.1111/e22258/add_params_lovs.htm#BIPDM306
Then build layouts for your Essbase query
http://docs.oracle.com/cd/E28280_01/bi.1111/e22254/toc.htm
annnnd 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!

Categories: BI & Warehousing

Have your say ...

Mon, 2015-02-09 15:25

Another messaging exchange last week with Leslie ...

OK, so we practised it a bit after our first convo and things got a little cheesy but hopefully you get the message.

Hit this link and you too can give some constructive feedback on the Oracle doc for BI (not just BIP.) I took the survey; its only eight questions or more if you want to share more of your input. Please take a couple of minutes to help us shape the documentation of future. 

Categories: BI & Warehousing

OAUG BIP SIG ... we're getting the band back together

Thu, 2015-01-15 14:28

 Today's post comes to you from Brent at STR Software. If you could help out, it would be greatly appreciated, read on ...

First off, if you are not familiar with the term SIG, it stands for Special Interest Group. OAUG facilitates a number of SIGs to bring users together that share common or industries concerning certain Oracle products.

Unfortunately, the BI Publisher SIG has been offline for a number of years and has not been given the attention it needs to be a useful resource for members of OAUG. Well... I'm getting the band back together and I need your help!

The SIG itself was formed to specifically focus on BI Publisher embedded in Oracle EBS, Peoplesoft and JD Edwards. I have put together a survey that is being emailed out to previous members of the SIG to get thoughts on how the SIG can be of service. That list is pretty old and YOU may not be on it, so if you are interested in participating in the SIG (or even if you are not), have a look at the link below and let me know your thoughts. Our first official meeting will be at Collaborate 15 in Las Vegas, hope to see you there!

Take the survey -> here!

Categories: BI & Warehousing

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

Fri, 2014-12-12 18:03

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!

Categories: BI & Warehousing

Bordering Text

Tue, 2014-11-18 16:08

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.

Categories: BI & Warehousing

Multi Sheet Excel Output

Thu, 2014-10-02 18:28

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 multiple sheets? I know we can split 1 report in multiple sheets 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 have completely separate data models for each sheet. That would require generating multiple Excel outputs and then merging them, somehow.

However, if you can live with a single data model with multiple 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 set but they will all be presented together in a single data set that BIP can then render. 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 Excel output is pretty simple. It depends on how much native Excel functionality you want.

Using an RTF template you just create the layouts for each data set on a page(s) separated by a page break (Ctrl-Enter.) At runtime, BIP will place each output onto a separate sheet in the workbook. If you want to name each sheet you can use the <?spreadsheet-sheet-name: xpath-expression?> command. More info here. That’s as sophisticated 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 with the layout.



This time thou, you create the layout for each data model on separate sheets. In my example, sheet 1 holds the department data, sheet 2, the employee data and so on. Some conditional formatting has snuck in there.

I have zipped up the sample files here.

FIN!

Normal 0 false false false EN-US X-NONE X-NONE /* Style Definitions */ table.MsoNormalTable {mso-style-name:"Table Normal"; mso-tstyle-rowband-size:0; mso-tstyle-colband-size:0; mso-style-noshow:yes; mso-style-priority:99; mso-style-parent:""; mso-padding-alt:0in 5.4pt 0in 5.4pt; mso-para-margin-top:0in; mso-para-margin-right:0in; mso-para-margin-bottom:10.0pt; mso-para-margin-left:0in; line-height:115%; mso-pagination:widow-orphan; font-size:11.0pt; font-family:"Calibri","sans-serif"; mso-ascii-font-family:Calibri; mso-ascii-theme-font:minor-latin; mso-hansi-font-family:Calibri; mso-hansi-theme-font:minor-latin; mso-bidi-font-family:Calibri; mso-bidi-theme-font:minor-latin;}
Categories: BI & Warehousing

Database Links

Thu, 2014-09-25 14:39

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.manager
from emps e1
, emps@db2 e2
where 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.manager
from emps e1
, managers e2
where 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 :)




Categories: BI & Warehousing

Internal Links

Wed, 2014-03-05 20:06

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.

Categories: BI & Warehousing

Pages