Drill Down to Detail or Another Report

I want to click some values in the report and see the detail report for the particular data. For example, when I see a Sales report and realize some regions sale revenue is way small and now I want to find something more detail to answer why, by opening up another report that has detail data for the particular regions.

Well, this is sometimes called as ‘Drill Down’, sometimes ‘Report Link’, or ‘Reports Navigation’. Whatever the name is, what you want is so called ‘Master-Detail’ reports relationship with which a master report opens a detail report and passes a value to filter the data in the detail report.

You can develop such reports very easily with BI Publisher. All you need to do is to develop two reports, one regular report with a hyper link and one with a parameter which will filter the data.

As I covered yesterday you can receive the parameter value and use it within the RTF template to filter the data. But I’m going to pass the parameter value from the first report to a SQL query for the second report and have the SQL query (Database) to filter the data for the second report.

So here are the steps we need to follow.

Create Detail Report with Parameter

Since we need to know the target report name and parameter value in order to create the hyperlink in the master report, let’s start with the detail report.

First you create a report as normal, nothing special. And you add a parameter. Let’s just call it as ‘p_country’.

And put that parameter into a query for the report.

My example is using a LOV (List of Values) to get the ‘p_country’ parameter value list. And the report looks like the below.

Now you can select a value from the country parameter drop down list and the report data will be updated based on your selection.

Create Report with Hyperlink

Now the master report. Again, nothing special, you just create a report as normal. Only additional thing is to add a hyperlink. In order to do this you need to know the two things.

• Target (Detail) Report Path
• Target (Detail) Report Parameter

For that, we need to go back to the detail report. And select ‘Current Page’ from ‘Link to this report’ menu at the right hand side top.

Copy the link into a notepad or something. Mine is something like below.

http://knishida-lap.us.oracle.com:9704/bip/Data/Trend by Country/Trend by Country.xdo?_xpf=&_xpt=0&_xdo=%2FData%2FTrend%20by%20Country%2FTrend%20by%20Country.xdo&p_country=USA&_xt=Trend%20Chart&_xf=html&_xmode=0

You can find the detail of the each parameter in the above link in BI Publisher’s Designer’s guide. But for now what we need to know at least is the report path and the report parameter. And those are ‘Report Path’ and ‘Report Parameter’. You can find those as below from the above link.

Report Path	http://knishida-lap.us.oracle.com:9704/bip/Data/Trend by Country/Trend by Country.xdo
Report Parameter	p_country

Now you go back to the first report, open the RTF template and add the hyperlink on the place you want your users to click on. 

And use the MS Word’s menu to insert a link.

And you want to copy the URL link that you copied above. But before dump it into the hyper link box, you want to modify the link a little bit.

There is a ‘p_country’ parameter in the link. Instead of hard coding its value you want to dynamically generate the the value by taking an actual value from the data when the report is generated. It’s basically the same thing as you do when you print or use the data in the RTF template, like <?ISO?>. Only the difference is you need to use these brackets ‘{‘, ‘}’ in stead of the BI Publisher’s tag ‘<??>’. With my report I can get the value as ‘ISO’ from the xml data, so it would be something like below.

http://knishida-lap.us.oracle.com:9704/bip/Data/Trend by Country/Trend by Country.xdo?_xpf=&_xpt=0&_xdo=%2FData%2FTrend%20by%20Country%2FTrend%20by%20Country.xdo&p_country={ISO}&_xt=Trend%20Chart&_xf=html&_xmode=0

Now one more thing. You never know what would be the host name when the report will be deployed, right? You might have different server instances like ‘Development’, ‘Testing’, ‘Production’, etc. There is a pre-defined BI Publisher’s variable called ‘CURRENT_SERVER_URL’, which returns an URL that contains hostname, port number, and the root path. So it would be ‘http://knishida-lap.us.oracle.com:9704/bip/’ for the above example.

So now you can replace the URL to be something like:

{$CURRENT_SERVER_URL}Data/Trend by Country/Trend by Country.xdo?_xpf=&_xpt=0&_xdo=%2FData%2FTrend%20by%20Country%2FTrend%20by%20Country.xdo&p_country={ISO}&_xt=Trend%20Chart&_xf=html&_xmode=0

And now you can copy this and paste it into the hyper link box.

And here is one thing. In order to use the ‘CURRENT_SERVER_URL’ variable you need to declare it in the RTF template. SO you need to just type the following syntax at the beginning.

<?param@begin:CURRENT_SERVER_URL?>

And that’s all you need to do. Once you upload the RTF template you should be able to see the hyper link in the report. And the link should contain the report path and report parameter value, both of which are dynamically generated.

As you see in the above picture, each link has the ‘CURRENT_SERVER_URL’ and ‘ISO’ values dynamically generated. With my example, when I click the link for Japan it will open another report and show me the detail data about Japan.

Or,

 

So that’s pretty much it, though one last thing I should add. If you have tried this you must have already noticed. When you click the link it will open a new window. But you might want to keep in the same window to display the detail data. You can do this by un-checking ‘Open Links in New Window’ property.

Enjoy Drill Down/linking !

How to Pass User Input Values to Report Layout?

“I want to allow my users to enter their comment from the report UI and print it in the report along with the reporting data.”

“I want to change the calculation or condition logic based on the user’s selection or inputs at the runtime.”

These are the questions I hear once in a while and the short answer is ‘Yes’. For example, you can do something like below, which users type their comment and generate the report with the comment.

BI Publisher supports Parameter functionality, which allows the end users to select or enter values when they open the reports and filter the reporting data based on the input values.

As a report developer you can pass this value to the underlying data source such as SQL query and filter the data result. For example, in order for you to pass this user input values and filter the data you can create a parameter called ‘param1’ and add the parameter value prefixing with ‘:’ into a WHERE clause. (e.g. where department_id = :param1)

Now, how about passing the user input values to the layout template instead of to the SQL query ? Yes, you can pass the values to your template and use the values in your condition, calculation, or just to display it in the report output.

It’s pretty simple, but there are a couple of steps to follow.

Create Parameter at BI Publisher Server UI

This is no brainer. You can just go to the Report edit window and create a report parameter from the UI.

Please see the Reports Designer’s guide for the detail.

Declare Parameter in RTF Template

Before you use the parameter in the RTF template file you need to declare it first. You can do so by having the following syntax.

<?param@begin:%parameter_name%?> 

Note: where %parameter_name% is the parameter name that you have created at the BI Publisher Server UI.

Here is an example:

<?param@begin:param1?>

Use it in RTF Template!

Once it’s declared in the RTF template you can start using it as the same way you use the variable. You need to just add ‘$’ prefix in front of the parameter name. Here is the example:

<?$param1?>

The above example would print the parameter value in the report output. If you want to use this value in your condition or calculation you can simply use it as ‘$param1’. Yes, it’s that simple.

If you are not sure if and how the parameter values are passed to the report when you run the report you can use this command in your RTF template, which will return all the parameter values passed to the RTF template at the runtime.

<?xdoxslt:getXDOProperties($_XDOCTX)?>

Note that the above command has been introduced relatively new so if you’re running on an older version of BI Publisher it might not work.

Anyway, so now you know the steps involved to get the user input values and use it within your RTF template. Have fun!

I Want My Custom Java Function for Siebel!

Have you ever wanted to create a new customized function and call that from your RTF templates so that you don’t have to implement a same condition or calculation logic again and again with many different reports ?

I talked about this yesterday a little bit, but I had this problem when I was developing the Siebel reports and had to implement a date formatting or do some date related calculations. As I discussed yesterday that the Siebel  Integration Object generates the date data in its own format like ‘08/15/2008 09:20:11’ unlike the ‘Canonical’ format that BI Publisher expects, which is something like ‘2008-08-15T09:20:11’. Please check the previous post for the detail.

At the previous post, I have suggested two options to workaround this and talked about the first option with ‘totext()’ function. Today, I’m going to talk about the second option, which is to develop a Java custom function and use it in the RTF template.

Develop Custom Extended Java Function

BI Publisher supports the Java custom function that the users can develop their own functions with Java and call it from the RTF template. This will allow the users to hide tedious or complex calculation/business logic and centralize them in a single place, which not only improves the development productivity but also reduce the maintenance headache.

So, how to develop the Java custom function ? Luckily, Mr. BIP, Tim Dexter, has already talked about how to develop the custom Java function at his blog. Please check his post for the detail. At this post I’m going to talk about how to create one to convert the Siebel’s date data to BI Publisher’s ‘Canonical’ date format.

Here is the example of the function does the conversion.

package oracle.bip.extensions; public class BIPExtensions {     public BIPExtensions() {     }        public static final String convertDate(String cdate){         String conv_date = "";         if(cdate.length() == 11){             conv_date = cdate.substring(6,10)+"-"+cdate.substring(0,2)+"-"+cdate.substring(3,5);         }else if(cdate.length() == 19){             conv_date = cdate.substring(6,10)+"-"+cdate.substring(0,2)+"-"+cdate.substring(3,5)+"T"+cdate.substring(11,19);         }else{             conv_date = cdate;         }         return(conv_date);     } }

The example above is a pretty simple one for a demo. It gets the Siebel given date, convert it to the ‘Canonical’ date formatted string, and returns. If the given date contains only the ‘MM/DD/YYYY’ portion then it returns only the date in ‘YYYY-MM-DD’ format. If it contains the time data also then it returns ‘YYY-MM-DDTHH:MI:SS’.

This is just an example. So of course you can develop anything you want and develop as many functions as you want in the same class. Once you completed the development you can compile it and deploy it to a JAR file.

At this point, the next thing you want to do is to test the function with your RTF template. There are three steps to follow.

Setup Your MS-Word Add-in Template Builder Environment

I have talked about how to setup the MS-Word Template Builder environment so that we can use the Siebel’s extended functions at this post, ‘Siebel’s Extended Function for RTF Template’.

Basically we need to do the same thing in order to use the above custom function. So what we need to do is to add one more JAR file location in the ‘_JAVA_OPTIONS’ variable in the MS-Word launch batch script file. Here is the example assuming that the JAR file is called ‘BIP_Extension.jar’ and located under ‘C:\JDeveloper\mywork\Local\Project1\deploy\’.

echo %1 set _JAVA_OPTIONS=-Xbootclasspath/a:D:\811DQSSIA\client\classes\SiebelXMLP.jar;D:\811DQSSIA\client\classes\XMLP.jar;D:\811DQSSIA\client\classes\siebel.jar;D:\811DQSSIA\client\classes\XSLFunctions.jar;D:\811DQSSIA\client\classes\SiebelCustomXMLP.jar;D:\811DQSSIA\client\classes\SiebelCustomXMLP_SIA.jar;C:\JDeveloper\mywork\Local\Project1\deploy\BIP_Extension.jar "C:\Program Files\microsoft office\Office12\Winword.exe" %1

Now you can double click this .bat file to open your RTF template.

Declare a Name Space for the Extended Function

As mentioned in the ‘Siebel’s Extended Function for RTF Template’ post, you need to first specifying the custom class file by declaring its name space with the class path.

<?namespace:bipext=http://www.oracle.com/XSL/Transform/java/oracle.bip.extensions.BIPExtensions?>

Use it!

Once the name space is declared then you can use it as the same way you use the ‘xdoxslt’ or ‘xdofx’ functions. Type the name space first then type the function. Here is an example.

<?bipext:convertDate(ssCreatedDate)?>

My convertDate function takes an input parameter value of date string so I set a XML element name that holds the date data.

And after you ensure that the functions works appropriately then you can deploy it to the server side following the steps at ‘Siebel’s Extended Function for RTF Template’.

Conclusion

So now you got this whole freedom of developing and adding your functions to the BI Publisher’s reports development realm. However, note that as I suggested before you should review the existing BI Publisher’s native functions and the Siebel’s extended functions first to see if they can be used to meet your requirements. Developing your own new functions means you will be responsible for the maintenance. And it’s not uncommon that one developer develops something and leave after a project is completed, then the other members left alone have no idea how to fix it when a problem occurred with the custom functions. As long as you use the functions provided by Oracle then Oracle will be responsible for the fix. That’s the difference you want to consider for the long term.

Keep that in your mind have fun with the custom Java function !