Wednesday, February 17, 2010

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.

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.

parameter

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!

9 comments:

  1. nice !!
    but am having a problem when i have two parameters order number adn order name....
    if order number is selected correspondig order name has to be populated , can u help.

    ReplyDelete
  2. This was achievable in obiee 10g but we want to know how to Declare Parameter in RTF Template in obiee 11g publisher.

    The said process is not working in 11g.

    Your help will be highly appreciated.

    Thanks
    om chaudhary
    omvirsinghchaudhary@gmail.com

    ReplyDelete
  3. Hi Kan,
    Once a param is declared with the param begin syntax, is there a way to update the value later in the template?

    For example:
    After declaring param@begin:sorttype;'"text"'
    I'd like to update this sorttype param to number on a certain condition. If there is syntax to do that, would you please advise?

    I've tried xdoxlst set/get variables as well. These work to define the value of the variable, but I run into a different issue later.

    Thanks much for any suggestions!
    Fritz

    ReplyDelete
  4. Can we store the comment in the database too?

    ReplyDelete
  5. You can use Event Trigger to do so, you can pass the parameter value as a variable and you can create a PLSQL function with an insert statement using the variable then you can call the function with event trigger.

    ReplyDelete
  6. I have a report that requires five parameters. I set them up in the data model and also in a package as global variables. I get errors. I was able to get one parameter to work. Do you have an example of multiple parameters being passed to a function within a package?

    ReplyDelete
  7. Hi
    Wonder WHY.... when i use the date as optional parameter it does not return data
    for eg..

    and create_date between
    NVL(:begin_date,create_date) and NVL(:end_date,create_date)

    This don't return rows without valid date parameters entered.

    plesae advice..

    ReplyDelete
  8. Hi Kan,
    How can we pass multi value for a parameter defined in RTF. For eg. when we write , we can pass just "Marketing" only, but if we want to pass "Marketing;shipping" or more, its giving error.
    Please suggest.

    Thanks
    Harish

    ReplyDelete
  9. Hi Kan,

    We have setup Siebel application such that it tries to access Reports page in BIP application using symbolic url. We then schedule the report to run at specific time. However we want to use the logon details of the user logged into the siebel application to run a SQL Based paramterised report. I was wondering if we have a feature in Oracle BIP 10g,the external application here siebel would pass a certain parameter like userid which then will be used in a sql query of the report which is being scheduled.

    ReplyDelete