“I want to select or pick a value before running the reports and filter the reporting data for Siebel reporting.”
This is a very common requirement in reporting in general, which is to provide a parameter (or prompt) to the users, have them specify a value, and filter the returned data in the reports. With BI Publisher Server standalone UI, you can quickly create a parameter and run the reports. But how can we do with Siebel reporting ?
In Siebel you can select a particular row in the view window and run a report which will contain only the data related to the row. For example, in Account View, when you select a particular account and run the ‘Account List’ report it will show you only the selected account related information in the generated report. Also, Siebel reporting filters the data based on the user’s position, so depending on who run the report they will get a different set of the data.
These are all handled by Integration Objects, the reporting data extraction layer. But how about filtering the data even further ? With the above example, when I get a data related to a specific account I still want to filter the data so that I can see only the accounts who have purchased iPhone in the last quarter by passing a product name as a parameter value.
Siebel Reporting with BI Publisher had added this capability with 18.104.22.168, which is called ‘Parameterized Reporting’. Now you can provide a drop down box or free text form where the users can select or type a value and filter the reporting data result based on the parameter value.
There is only one limitation. You can’t make this parameter values dynamic. It’s common that you want to run a query to the database and retrieve the parameter values, like the way you do with BI Publisher Enterprise Server UI. But with Siebel this option is not available today. So basically you need to define the parameter values beforehand within the RTF template.
Anyway, today, one of our consultants, Shankar Duvvuri, who is our new BI Publisher expert especially in Siebel integration, has written an example scenario about how to setup and use the parameterized reports in Siebel and wants to share with you. So, Enjoy!
How to Create Parameterized Report
In this example, we want to create a report that shows revenue data and provide a drop down box parameter to the users so that they can select which quarter they want to see the revenue data for. So we are going to need a drop down box with values of ‘Q1’, ‘Q2’, ‘Q3’, and ‘Q4’ and have the users select one of the values before they run the report to filter the revenue data. So if a user selects a ‘Q1’ then only the revenue in Q1 will be displayed in the generated report.
Create Parameter in RTF Template
First, we need to create a parameter, which displays a pick-list of quarters (Q1,Q2,Q3,Q4) to the user, within the RTF template. Here is an example.
<?param@begin:Qrt; 'Q1', 'string', 'Q1,Q2,Q3,Q4'?>
You can either type this in the RTF template as text or use the text-form and type the above syntax inside the text-form.
In the above example, we are creating a parameter called Qrt with pick list values of: Q1,Q2,Q3 and Q4. The default value of the pick list is set to Q1. The parameters can be added anywhere in the template as either form field or free text. For more details about syntax and other types of parameters (like free text) refer to the Siebel Reports Guide.
If you want to make the parameter to be a free text form where the users can type the value instead of selecting from a list, then you can just ignore the parameter value section. So it would be something like below.
For free text, we are declaring Qrt as the parameter name with Q1 as the default. At run time this would display the parameter with name Qrt and default value Q1, which then can be changed to any value of users choice.
Use the Parameter to Filter Data in RTF Template
Once you have created the parameter as above, now you can use it to filter the data, to condition, or just to print the value. To reference the parameter in the report just prefix a $ before the name of the parameter.
In order to filter the data you can use the parameter inside the ‘for-each’ clause.
Having a default value in the template helps us simulate the testing with BI Publisher MS-Word Add-in. You can manually change the default value to test a different scenario for your condition or calculation.
Test the Report
Once the RTF template is ready, you can upload it to the Siebel Server using the Web Client.
If the report already exists then we need to delete the report first and then click on the Upload Files button. (Due to a defect we can’t override the existing template.)
Once the upload is done, run the report in the associated view. You will get a pop-up window (parameters applet) like below, which shows the newly added parameter.
Choose the one of the value and click ‘Submit’ button, you will get another window where you can select the report output type. Select the output type and submit, then you will get a report that only contains only the values that are related to the selected parameter value.