Thursday, March 18, 2010

Running Siebel Reports with Parameters

clip_image008

“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 8.1.1.1, 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.

clip_image002

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.

 <?param@begin:Qrt;'Q1';'string'?>

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.

 


 <?for-each:ListOf_ssRevenue[Quarter=$Qrt]?>

clip_image004

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.

clip_image006

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.

clip_image008

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.

28 comments:

  1. Hi Kanichiro...I understand that list of values and parameters are part of .xdo file and these files are only used if the report is generated in connected mode...Since in the case of disconnected users .xdo file is not used for report generation, how does parametrized reports execute in disconnected mode?

    ReplyDelete
  2. The parameters are not saved in .xdo file, it's in RTF template file. So it should work the same way even with Disconnected mode.

    ReplyDelete
  3. Sorry, I've made a wrong comment. Actually the parameterized reports don't work in the Disconnected mode with the current release. The requirement has been fed into the product development and planned for a future release, don't know when.

    ReplyDelete
  4. Hi Kanichiro, Is there any way we can default the parameters. I want to provide all the ReportOutputTypes and default it to PDF.

    ReplyDelete
  5. Hi Kanichiro,

    I'm working on a report using BI Publisher. The report will be used within Siebel.

    I'd like to use a DATE as a parameter and not a string field. So how can I do ? thx in advance

    ReplyDelete
  6. You can create a free text then have your users type the date. There is no date picker supported though...

    ReplyDelete
  7. Hi Kanichiro,
    in your post you mentioned the following:
    ...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. ...
    ...These are all handled by Integration Objects, the reporting data extraction layer....

    I would like to use this feature. I got the requirement to build a report which only contains the current record (e.g. in a List Applet). Can you tell me how to implement this? (without scripting if possible!?)
    Thx in advance!

    ReplyDelete
  8. Hey,

    For running siebel reports with parameters, i'd like to know what version of BI Publisher Server, we should install ??

    ReplyDelete
  9. Hi,

    I followed the same exact steps specified by you. Still i am not getting popup applet with parameters. I still get the old popup applet with only one dropdown to select output format. Is there something else I can look into? When I changed my template to include paramater, I also added some new static text in the RTF file and that is visible in output report. So I am assuming that my updates to template are properly uploaded.
    we are using 8.1.1.2

    Any help to debug is highly appreciated.

    Pradeep

    ReplyDelete
  10. Hi ,
    I have two parameters start from : and To: What is the correct syntax to filter parameter data between two parameter. Below is my error syntax



    Thank you

    ReplyDelete
  11. Hi there,

    does this also work if SQL is used as Data Source in the BIP report (xdo)?

    I experienced great problems with the IOs and switched to SQL to access data from Siebel.

    If yes how does I have to include the parameter in the SQL.

    Many thanks
    Stefan

    ReplyDelete
  12. Hi Kanichiro,
    I have 4 parameters in RTF:Quarter_Year,Month_Year,Week_Year,Day_Year.
    So can I name them parameters with spaces like "Quarter of Year"...
    The second question is:I want the parameters should look in sequence in Siebel as it is in the RTF.Is it possible and how to achieve this?

    ReplyDelete
  13. Hi,

    I would like to ask how to pass date range parameter in rtf?
    My syntax is:





    My target date to filter is :02/03/2011 07:18:09

    but the data still can't filter

    Do you have some example how to declare from date-to date parameter in siebel BIP report?

    Thank so much before

    ReplyDelete
  14. sory, the syntax not appear
    My syntax is:

    ?format-date:START_DATE;'DD-MM-YYYY'?
    ?param@begin:START_DATE?

    ?for-each:Campaign[StartDate=$START_DATE]?

    My target date to filter is :02/03/2011 07:18:09

    but the data still can't filter

    Do you have some example how to declare from date-to date parameter in siebel BIP report?

    Thank so much before

    ReplyDelete
  15. Many Thanks for this blog.
    If you want to allow user to select all the options or multiple values, how will you do that using parameters?

    ReplyDelete
  16. Thanks for this! It helped me quite a bit to filter out specific records in my report.

    @jochen: To filter to a "selected record" in Siebel, simply select the "Selected Record" tick on the report template registration view in Siebel. (Note, we're running 8.1.1.3, so I can't guarantee this to be 100% accurate for other versions)

    @pradeep: I noticed when using these parameters that you also need to update the XLIFF file. Not sure if you've done this, but it's worth a shot.

    ReplyDelete
  17. Thanks for the write up. I have question for you (or the group). I would like to create a BI Report available at the Oppty>Quote view. I would like to build the report such that it displays the opportunity and related information on any active quotes to build a proposal to present to the client. However, sometimes only a few of the active quotes will end up being presented so my question is can you build a BI report (for a proposal) that would allow the end user to dynamically select which active quotes should be in the final report output?

    ReplyDelete
  18. Not aware of. You might want to ask the question to the Oracle Discussion forum.
    http://forums.oracle.com/forums/forum.jspa?forumID=245

    ReplyDelete
  19. All,

    Need a it of help here. I need to put FROM and TO parameter on the SR Created date do the data in the view can be filtered, but unfortunately am not able to do so. I tried using the folloing syntax to put in the parameters but the data is not getting filtered.


    :param@begin: Closed Date;'mm/dd/yyyy';'String'?]param@begin: Closed Date;'mm/dd/yyyy';'String'?>

    May be i am using thee wrogn syntax here or am no applying the syntax at the right place. Really appreciate if someone can help me pout here.

    Much Appreciated
    Sumit

    ReplyDelete
  20. Hi,we are using Siebel 8.1.1.1 .... just wanted to know if parameterized reports work fine with this version or not..

    ReplyDelete
  21. 8.1.1.1 should work fine, it's documented.

    ReplyDelete
  22. I am using siebel 8.1 my client required templates to prepared in pdf I prepared templates in pdf but when i load into BIP error has no file and when i try to make in siebel i am getting error 00203.please help me out.

    Thanks & regards

    ReplyDelete
  23. Hi,
    How do incorporate logical operators(e.g OR. AND) with . i.e if I want to add more condition to this.

    ZK

    ReplyDelete
  24. Hi,we are using Siebel 8.1.1.5 .... just wanted to know if parameterized reports work fine with this version or not..

    ReplyDelete
  25. Nivastce, Yes, should work fine.

    ReplyDelete
  26. Hi,
    I have a requirement in BIP to pass 'FromDate' and 'ToDate' as Parameters,
    in to BIpublisher report , how to do this can any body help me on this.

    Thanks,
    Ravi

    ReplyDelete
  27. We are using BIP reports. I have few questions.
    1. Is parameterized BIP reports can use scheduling future in BIP?
    2. Is SQL integration BIP reports, can support parameterized Reports? if yes? can you give the details
    3. We have develolped the reports under Account View and for using the assets data. These reports are always failing accross organization view.
    Can you help me solve above issues?

    ReplyDelete
  28. Sometimes it seems the siebel didn't pass parameter to BIP and the output file is blank. It looks like the parameter delivery feature was disabled.however, I checked the xmlp server is running well. can you give me some idea? Thanks in advance.

    ReplyDelete