Oracle BI Publisher Blog

More on URLs ... this time how to create a dynamic URL in your template to point to another report or another destination. Im going to talk about the Enterprise release primarily although the same general idea holds for Oracle Applications customers.

Once you have your basic template you'll need to add a URL to a form field, easily done, just highlight the field, right click and select Add Hyperlink. You now get a dialog to add the URL.

So the basic URL to drill to another report is going to look something like the URLs we discussed yesterday:
 http://server:port/xmlpserver/ReportDirectory/ReportName.xdo + parameters
at runtime this looks something like

 http://xdopf.us.oracle.com/xmlpserver/Private/SalaryReport/SalaryReport.xdo + parameters

Lets break up the URL again and deal with the parts individually:

• server:port  - are provided via a parameter that you can have your template subscribe to - CURRENT_SERVER_URL, in the URL dialog we are going to enter {$CURRENT_SERVER_URL} The curly braces tell the parser that it needs to evaluate this value, the $ sign denotes that it is a parameter value. So that takes care of the first part.
  
• xmlpserver - this is provided as part of the CURRENT_SERVER_URL parameter.
  
• ReportDirectory - this is where you need some knowledge about where the report is that you want to drill to. XMLP does not currently support a relative path nor a report id which would make life easier if you need to move reports around in the directory structure. So for now you are going to have to keep track of the target report and its directory.
  
• ReportName.xdo - this is the report you want to run from the link
  
• Parameters - this is the most useful bit here we are going to make the URL depend on what the using is currently hovering over. We'll deal with them in a second.

So without the parameters we would have:
 {$CURRENT_SERVER_URL}/Private/SalaryReport/SalaryReport.xdo

for the parameters lets assume we have a parameter called 'dept' and 'emp' and based on the data the user is hovering over we want to pass those values to this report. Lets assume the XML element names for each are DEPT_ID and EMP_ID. We can add the following to the URL to read the two values:
    ?dept={DEPT_ID}&emp={EMP_ID}
Notice the curly braces again forcing the parser to evaluate the values. What really cool is that DEPT_ID and EMP_ID do not have to be in the template layout, so the user can hover over say a department name value and the DEPT_ID value is evaluated and passed.

So the full URL is going to be :
{$CURRENT_SERVER_URL}/Private/SalaryReport/SalaryReport.xdo?dept={DEPT_ID}&emp={EMP_ID}

Of course all the other parameter values we discussed yesterday can be added too ie template and output format.

Now we have the URL we can type it into the dialog, dont worry if your '{}' turn into %7b  and and %7d thats Word encoding the values, annoying but they will be evaluted correctly at runtime.
The only thing we need to add to the template is to declare that we are interested in the CURRENT_SERVER_URL parameter ... the value will be passed by the server all the time but if we do not declare our interest then we're going to get an error. So we create a formfield with the following contents:
<xsl:param name="CURRENT_SERVER_URL"  xdofo:ctx="begin"/>
Of course if you have other report parameters that you want to use in the URL or template itself just declare them in a similar way and they can be used just like the CURRENT_SERVER_URL one i.e. {$PARAMETER}.

If you want to see all of this in action then download and install XMLP with the sample reports, and get to the Home > Executive > Sales Dashboard and check out the 'Sales by Custome' table and float over the customer names, you'll see the URL changing as you hover.

Happy Templating!