Wednesday, November 18, 2009

Dynamic Date Parameter – Current Date

This is actually really not known trick maybe it is not documented in the user’s guide yet, but BI Publisher supports a ‘dynamic date parameter’.

WHAT???

Ok, so don’t you want to set the default date for the date parameter of the report and make it to be a today’s date or a week before or something ? Or, don’t you want to schedule your report and want it to generate always the last 7 days of the data ?

Yes, here comes the dynamic date parameter!

For example, you can use a current date function (sysdate()) to set a today’s date for your date parameter so that the date will be set always to today’s date depending on when you open the report. So if you open the report today the date would be 11/18/09, if you open tomorrow then it would be 11/19/09.

This would be even more critical when you want to set the date parameter values for your scheduling report. For example, you might want to run a report on every Monday to display the data for the last 7 days. You can’t hard-code any date to the date parameter of the report because then the date range will get too old and invalid. In order to get the last 7 days of the data in SQL world, typically you set a start date to be ‘sysdate – 7’ and an end date to be ‘sysdate’. Yes, you can do the same thing with the BI Publisher’s scheduled report by doing exact the same but setting such parameter values from the scheduler edit window. BI Publisher generate the appropriate dates based on the dynamic date function and pass them to the underlying sql query.

BI Publisher supports not only the current date (sysdate) function but also other types of functions for commonly used dates.

  • SYSDATE()   - Today’s date
  • FIRST_DAY_OF_MONTH() - First day of the current month
  • LAST_DAY_OF_MONTH()  - Last day of the current month
  • FIRST_DAY_OF_YEAR()  - First day of the current year
  • LAST_DAY_OF_YEAR()   - Last day of the current year

And here is some example…

Set Default Date to Current Date

In order to use the above date functions you need to enclose it with curly bracket and dollar sign such as ‘{$SYSDATE()$}. You can simply type this in the ‘Default Value’ text filed in the report edit window.

default_date

You can also have some simple calculation with the date functions. For example, if you want to set the default date to be 7 days before the current date, then you can type ‘{$SYSDATE()-7$}’. Plus (+) and minus (-) are supported for the calculation.

Set Dynamic Date for Scheduled Report

You can also set the date parameter values using the above functions. For example, if you want to schedule a report and want the report to generate the last 7 days of the data, then you can set the From Date to be ‘{$SYSDATE()-7$}’ and To Date to be ‘{$SYSDATE()$}’

default_date_schedule

Or, if you want to run the report to always return the month-to-date data then you can set the From Date to be ‘{FIRST_DAY_OF_MONTH()}’ and To Date to be ‘{$SYSDATE()$}’.

Just a useful trick if you haven’t known!

39 comments:

  1. Hi,
    I have a question regarding the date parameter. If I select parameter type for the date field as Hidden and the date has a default value, then I am not able to pass/view the date field value. Is that a known issue? Can I only use the Date field when the parameter type is specified as Date?

    Please let us know.
    Thanks!

    ReplyDelete
  2. I might be that's how it works. But why would you want to use the date with hidden parameter type when you can get sysdate in Data Template or RTF Template ?

    ReplyDelete
  3. hello,

    I wonder how about the others type parameter. Such as, I go the LOV of user_id and I want to set default value fot that LOV is current user ( the user login to BI PUblisher ).
    Please help me,
    Thank you,

    ReplyDelete
  4. Very useful help....
    Thank You.

    ReplyDelete
  5. Hi,
    I have a question regarding the date parameter.
    CAN I READ A DEFAULT VALUE WITH A QUERY, FOR EXAMPLE FROM A VALUE IN A TABLE?
    TAHNKS?

    ReplyDelete
  6. I'm not clear on your question. Can you elaborate ?

    ReplyDelete
  7. Hi,
    For Example to read default value in a dataset.
    Thanks.

    ReplyDelete
  8. Hello all,

    i have a question.
    Can we group our data based on month in BI publisher.
    For example i have a field called created now i want to group data per month as per the cretaed date.

    Thanks in advance

    ReplyDelete
  9. Yes, you can. You can use for-each-group and subsring to get the month name.

    ReplyDelete
  10. Hello Kanichiro,
    I tired

    but it gives me error in string'xdofx:substr(PrimaryRevenueCloseDate,0,3)'

    i even tried using psfn function to first change date to Canonical but still the error..

    Thabks in advance

    ReplyDelete
  11. You don't need to add the 'xdofx' prefix, you can just put substr(elementname, starting point, end point)

    ReplyDelete
  12. Hello,

    i used


    it gives error as substr function not found

    thanks in advance

    ReplyDelete
  13. what is the syntax are you using ?

    ReplyDelete
  14. Hello Kanichiro,
    Syntax m using is

    regards

    ReplyDelete
  15. sorry, but I think this is due to this blog service's restriction or something, but your syntax is not showing up. Can you put the syntax without ?

    ReplyDelete
  16. Ok, I can't even type those letter. I meant to say, < ? and ? >

    ReplyDelete
  17. Hello..
    This is the syntax
    for-each-group:Opportunity;./substr(PrimaryRevenueCloseDate,0,3)

    regards

    ReplyDelete
  18. Looks ok to me, but maybe something to do with the XML structure and your XPath. Can you send me your XML data file to kanichiro.nishida@oracle.com ?

    ReplyDelete
  19. Hi Kanishiro,
    I have a new question.
    My user want decide when change a default value of a parameter like yyyy-mm.
    For example a user writes in a table a value like 2010-03 and all publisher reports, with this parameter, change the dafault value in 2010-03. Can I configure the default value of a parameter in this way?

    ReplyDelete
  20. Do you want to change the default format for the parameter value which appeared to be in the parameter box ? Then you can use the Date Format String box in the edit UI to specify the format. However, this value can not be dynamic. Once you set this at the report level all users need to share the formatting.

    ReplyDelete
  21. thanks. I understand ... the default value of a parameter can not be dynamic. I can use function like sysdate but I can not read a value in a table or use other type of source. Correct?

    ReplyDelete
  22. You can actually set the date format dynamic by creating a parameter. Let's say you create a parameter of Text or LOV then the users select their desired date format. Now you can take the parameter value inside your SQL query like 'to_char(EMPLOYEES.HIRE_DATE, :date_format) as HIRE_DATE' where ':date_format' is the parameter identifier. In this way you can make the date format dynamic based on user's selection.

    Alternatively, you can do this within the RTF template instead of within the SQL queries. Check this post as to how to pass the parameter value to RTF template if you're sure.
    http://bipconsulting.blogspot.com/2010/02/how-to-pass-user-input-values-to-report.html

    Also, keep in your mind that the parameter value can be either hard-coded, user input, or SQL query. So if the user preferred date format is stored in the database then you can use SQL query to get the value for the parameter then use it either in SQL query or RTF template.

    Is this something you want ?

    ReplyDelete
  23. I see that in default value of a parameter i can use function like:
    SYSDATE()
    FIRST_DAY_OF_MONTH()
    LAST_DAY_OF_MONTH()
    FIRST_DAY_OF_YEAR()
    LAST_DAY_OF_YEAR()
    there are other function that i can use? where can I find a list of function that can i use?

    ReplyDelete
  24. Just confirmed with the produce development that those are the only functions provided.

    ReplyDelete
  25. I want to generate DAILY /WEEKLY /MONTHLY Calendar Reports with Siebel8.1.3 (like siebel77 actuate reports). I have created a reports based on "Action", I can filter that report on Activity Type.
    How can I pass some Date value as parameter and filter reports based on that parameter?

    Do you have any examples for :
    a. How to pass date parameters
    b. How to filter data using DATE parameters
    c. How to filter Calendar reports for DAILY /WEEKLY/MONTHLY scenarios ?

    Thanks

    ReplyDelete
  26. Hi Kanichiro!

    Can I used dynamic date in e-mail delivery parameters, for example in Subject of the letter or in the attachement name?
    I tried to use {$SYSDATE()$} in the subject, but the letter came with just "{$SYSDATE()$}" characters in the subject :)

    Alex.

    ReplyDelete
  27. I am using {$FIRST_DAY_OF_MONTH()$} and {$LAST_DAY_OF_MONTH()+1$} with the following SQL

    and d.day >= to_date(:Begdate,'YYYY-MM-DD')+8/24
    and d.day <= to_date(:Enddate,'YYYY-MM-DD')+8/24

    but it keeps erroring with: ORA-01858: a non-numeric character was found where a numeric was expected

    why would this not work? I figure BI would pass a date value as yyyy-MM-dd that should fit right in the sql parameter...

    ReplyDelete
  28. Well, I found that the {$FIRST_DAY_OF_MONTH()$} displays the date as:
    2011-07-01T00:00:00.000-06:00, instead of 'YYYY-MM-DD' as I had expected. With that, I have tried several versions of the following sql, but still cannot get it to work correctly. Thoughts?

    and d.day >= to_date(to_date(:Begdate,'YYYY-MM-DD HH24:MI:SS:MMM (+|-)HH:MM', 'NLS_TIMESTAMP_TZ_FORMAT'), 'YYYY-MM-DD')+8/24

    ReplyDelete
  29. Once you set the parameter type to be 'Date', then you don't need to use the 'to_date' function. it can be just like

    and d.day >= :Begdate

    ReplyDelete
  30. Kanichiro Nishida
    Hi
    after passing the parameter d.day >= :Begdate
    the query is not fetching the data and If format the date field with to_date() then it is throwing the error message as ORA-01858: a non-numeric character was found where a numeric was expected
    Can you Please the solution for this ?
    Regards
    Naga

    ReplyDelete
  31. Hi Kan,

    In my parameter i would like to default to current month, could you advise me how to get the month the month from SYSDATE()? Thank you.

    ReplyDelete
  32. Can I use {$SYSDATE()-1$} as Active Start date for weekly report scheduling? I am trying but getting following error..

    racle.apps.xdo.servlet.scheduler.ProcessingException: Error occurred while scheduling the job.

    at oracle.apps.xdo.servlet.ui.scheduler.SchedulerServlet.getDateObject(SchedulerServlet.java:2263)

    at oracle.apps.xdo.servlet.ui.scheduler.SchedulerServlet.scheduleJob(SchedulerServlet.java:1485)

    at oracle.apps.xdo.servlet.ui.scheduler.SchedulerServlet.doPost(SchedulerServlet.java:293)


    Can any suggest?

    ReplyDelete
  33. ye ye i tried and this work for me. Thank you ! :)

    ReplyDelete
  34. hi,
    is there any way to define date range in parameter using date function (eg sysdate())

    i want to restrict user from selecting future date from calender, i want to use sysdate function in 'Date to; field of date setting section.

    Regards,
    Dharmi

    ReplyDelete
  35. Hi Dharmi, You can use those date functions for the default values but not for the date range. I'll put that one on our list of future improvements.

    ReplyDelete
  36. Hi Kanichiro,

    I need ur urgrnt help.

    I tried using SQL query in BIP for LOV creation.

    I am trying to extract the year from sysdate to match with the database year.I am getting syntax error each time.i want to actually prompt the report based on value selection and prompt should default to current year.
    please suggest how should i go ahead with this..

    i am using the below query
    select "Time"."Year" as "Year"
    from "WMT-Service Request Detail"."Time" "Time" where "Time"."Year" <= select YEAR(SYSDATE()) from dual

    ReplyDelete
  37. Hi Kanichiro,

    I want to query the latest date from the table and show it in the parameter.
    Is that possible?

    ReplyDelete