Create Pixel Perfect Report using BI Publisher that includes charts, tables and dynamic SQL query

Hello

This time I want to share with you how to create a pixel perfect BI Publisher report out of an existing dashboard that includes different type of reports.

When it comes to reporting, the BI Dashboard is highly interactive, it allows navigation, ad-hoc query and other dynamic features. That's the value of OBIEE. However, sometimes when you want to send the results with pdf or other formats, it is not so pixel perfect. In some areas people don't mind, but in a lot of inter-company communications, the version has to be pixel perfect. I mean, have you ever seen an invoice letter from your bank that's components are out of proportion? This is why BI Publisher has been integrated into the OBIEE platform.

Take a look at the following dashboard:

It has multiple charts, tables and texts arranged in a articulated way. There are even reports created using direct SQL query:

So reports like the following are regular reports displayed in charts or tables:

Some reports like the following are created using direct SQL with presentation variables passed from the dashboard prompt:

So lets see how we can create the same version using BI Publisher which is pixel perfect when you print it out.

We will start by creating new data model. In the data model windows, go to diagram and select Oracle BI Analysis for the reports that are created using regular approach. I then locate all of the saved BI analysis that I want to create, just select them one by one.

For the one that are created using SQL query with presentation variable, you need to change the data set to using SQL Query and paste the SQL query into the windows:

As shown in the screenshot, the syntax of the presentation variable will need to be changed from @{PV} to :PV:

After these are done, close the box and it will automatically ask you to create parameters. Just create the parameters with the same Presentation variable name and give default value:

After some times, all of the data sets (each one corresponds a BI Dashboard analysis) are create as shown:

Now save the data model:

Now create new BI Report using the data model that just created as the data source. While creating and going through each windows, it will ask you for layout templates or add tables, just click next and go through them without doing anything:

Then save it:

Now open the saved report. It is going to run and most likely it will not return anything. But it will allow you to edit report, so do just that:

It will open up a blank template, this is where you can feel free to design your own report arrangements:

One the left side, all of the data set that you created earlier for that data model will be available to be put on the blank sheet.

The insert type on the top of the sheet allows you the insert all kinds of objects, from gauages to tables to charts. I used layout grids to break this blank sheet into different smaller subdivisions and then insert each of the reports into those subdivisions:

The properties on the left side of the sheet allows you to adjust it's size, color and arrangement to make it look good:

After it's done (these are not technical work, it is more tedious art work), save the report. 

Now we can add this report to the original BI Dashboard:

The BI came be launched here:

Now after launching the BIP report, it has to pass the value of the BI dashboard:

The result is working:

Thanks

Until next time