OTBI Analysis to BI Publisher Query

Sometimes while writing queries we are not sure about the exact tables or joins to use. In such cases creating a simple OTBI analysis and getting exact BI query out of it is very helpful.

Below steps can be followed to get the BI publisher query from OTBI analysis.

• Create an analysis in OTBI

                                              

                                           

• Go to advanced tab and copy the SQL issued based on subject area

• Click on Administration

• Under "Maintenance and Troubleshooting" click on Issue SQL.

• Paste the SQL issued based on subject area in the box and click on Issue SQL.

• You will get the sample output.
• To get the exact SQL query issued for Oracle BI Publisher click on "view log". The log will get downloaded and you can see the exact SQL query issued in the log.

• The log would look like below. Copy the log into notepad.

• Now remove the unwanted part from the log get the final sql query. This sql wuery will be based on data securities(Roles and data access assigned to user). We will be able to figure out the exact DB tables and joins used in the query. Also we can directly use this query BI Publisher data model.

• The OTBI query would have columns as c1, c2,c3.... We can modify to our need. In the example, they have added column c1 to have distinct 0(this column will have value as 0 always, we can remove this). C2-> invoice date, C3-> Description, C4-> Invoice Number.

• From the extracted query we get the exact DB tables and joins used or we can use the query as it it in BI publisher.

Excel Output For Large Number Of Columns With Formatting

 

Sometimes we get a reporting requirement where we have for example 30 plus excel columns in output ,columns need to be formatted and more that 65536 records in output.

Below are the 3 options with limitations that come to our mind to achieve the above solution:

1). RTF Template - The limit of MS Word page width is 22 inches, so it cannot   

                             accommodate 30 plus columns perfectly.

2). Online Template from BI publisher screen - It has limited formatting options.

3). Excel Template - This would have been the obvious choice if ".xls" would not have limit

                               of 65536 rows.

The Solution: XSL-FO Stylesheet

• To overcome the above limitations we can go with another approach that is Exporting RTF to "XSL-FO Stylesheet" template.
• We do not need to create the XSL template from scratch, rather we create a RTF template and we export it to the XSL.
• When we create template in RTF with 30 plus columns, the column width will be very less in RTF but after exporting to XSL we can increase the page and column width. We need very less knowledge of XSL to edit the exported  XSL from RTF.
• With this we get output in Excel(.xlsx with 1048576 rows) format and desired look and feel. 
• The columns won't be shrinking unlike RFT output to Excel for these many columns.

Below are the screenshots to achieve the same.

The exported code would look like below. Edit the code as per requirement and save it with .xsl extension.

Once tested locally, upload the template to BI publisher with type as "XSL Stylesheet(FO) and select the default output as Excel.

How to Create Conditional Watermark In RTF Template

 Sometimes client requirement will be to print  watermark in BI report based on some condition.

We can achieve this easily in a RTF template.

Follow the below steps to print the conditional watermark in pdf output of RTF template.

1. Open Microsoft word
2. Save the file as .rtf extension.
3. Go to BI Publisher tab(BI Publisher should be already installed and Add-in  should be present)

      

     4. Load sample XML

     5. Add a table, columns and insert fields from XML. Add a for each loop.

     6.  Create a Xsl variable in the RTF

      6. Now go to Design tab in MS word->Watermark->go to custom watermark

    7.  The printed watermark window appears

         Here check "Text watermark"

    8.  Now under "Text watermark" leave everything as is(default) except the "Text".In the "Text" field

          enter value as for eg <?$wMark?>

    9. Save the template.

   10. View the output for Payment flag as Y and N in XML one by one. The output will have different

           watermark based on the Payment flag value we have set in the variable.

Payment flag as Y in xml.

Output:

Payment flag as N in xml.

Output: