Oracle BI Publisher Blog

I have Mike who leads the Product Management team for today's post. If you create a report in the Enterprise release based on a data template you'll know you can get the parameter values entered by the user into the XML data set and can then include them in your output very easily.

But, what if you are not using a data template, maybe a single query or a web service? You can still get at the parameter values - just a little effort is required. The server actually passes all of the parameter values to the template you just need to register your interest in them.

Lets assume we have a parameter in our report called 'param1' - we want to be able to see that value in our report output. All you need to do is declare the parameter in the template:

<?param@begin:param1;'None'?>

<?$param1?>

That dollar sign is important! Note that if you are using an LOV for a parameter and doing something like this for the select statement:

select department_name

,         department_id 

from departments

Where you show the user the name but pass the id to your data extract. We will pass the id to the template. If you are using the 'All' option on your report parameter we actually pass '*' as the All value so you need to handle that.

So, you can render the parameter values in the report output but of course you can now get into the wonderful world of template filtering. Lets assume you have a report that generates sales data for all of your local offices. You want each office manager to only see their own sales numbers. Rather than execute the report for each office you can run the extract for all offices (using the caching we provide) and just have the template limit the data that can be viewed in the output when the office manager sees the report.

Assume you have the OFFICEID as a hidden parameter in a report. You can then limit the data based on the parameter value:
<?param:OFFICEID?>
<?for-each:SALES_DATA[OFFICE_ID = $OFFICEID?>
....
<?end for-each?>

The text in the [ ] is an XPATH expression which is effectively an 'if' statement on the group.
So, there are your parameter values!