Invoke BI Report with Multiple Parameters Using Web Service

In a previous article I wrote about how you can invoke an Oracle BI Report using ExternalReportWSSService Web Service. You may read it here.

In the article I have an example of a BI Report with a single parameter. I had a very interesting question from a reader asking about how we pass parameter values if there are more than one report parameter. So here it is.

Prerequisites

Access to Oracle BI Server

• Create a BI Report Data Model (xdm) and Report (xdo)
• Create a PowerShell script to invoke the ExternalReportWSSService web service

The only difference between various scenarios is the content of the Pay Load File. So I am going to explain the differences in the XML Pay Load file for the following 3 scenarios:

• BI Report with Single Parameter
• BI Report with Multiple Parameters (each parameter accepting single values)
• BI Report with Multiple Parameters (one or more parameters accepting multiple parameter values)

BI Report with Single Parameter

Lets say we have a simple report that lists the GL Periods for a given year. The report has a parameter P_YEAR that accepts one value like 2019. The payload for the same will be as below:

On line 8, we specify the parameter name and on line 10 we specify the parameter value.

BI Report with Multiple Parameters Accepting Single Values

Lets say we modified the report that lists the GL Periods to accept the year and the period set as parameters. The report has two parameters

• P_YEAR that accepts one value, like 2019
• P_SET that accepts one value, like “US Ledger Set”

The payload for the same will be as below:

Let’s focus on Lines 7 through 18. For each parameter you have, the following section will be repeated within <pub:parameterNameValues> and </pub:parameterNameValues> XML tags.

<pub:item>    
    <pub:name>PARAMETER_NAME</pub:name>    
    <pub:values>     
        <pub:item>PARAMETER_VALUE</pub:item>    
    </pub:values>   
</pub:item>

In our example, we have P_YEAR parameter defined on line 8 and the value 2019 against the P_YEAR parameter on line 10. On line 14, we have the parameter P_SET and the corresponding value “US Ledger Set” on line 16.

BI Report with Multiple Parameters Accepting Multiple Values

Lets say we modified the report that lists the GL Periods to accept the year and the period set as parameters. The report has two parameters

• P_YEAR that accepts more than one value (the user can select multiple years and report will show data for the years selected)
• P_SET that accepts one value, like “US Ledger Set”

The payload for the same will be as below:

Let’s focus on Lines 7 through 19. The section looks mostly the same as the previous example. The only difference is line 11. If you are passing multiple values for a parameter, you will have the following section repeated within the <pub:values> and </pub:values> XML tags for the specific parameter.

<pub:values>
    <pub:item>VALUE 1</pub:item>
    <pub:item>VALUE 2</pub:item>          
</pub:values>

In our example, we have P_YEAR parameter defined on line 8 and on lines 10 and 11 we are passing 2019 and 2020 as values for the parameter. On line 15, we have the parameter P_SET and the corresponding value “US Ledger Set” on line 17.

Conclusion

As you can see it is pretty easy to modify the XML Payload to suit your needs.

Hopefully this helps some of you out there. Let me know your comments and feedback. Feel free to download the PowerShell script from my github account.