• Post category:BI Publisher
  • Post comments:5 Comments
  • Post last modified:June 9, 2023
  • Reading time:4 mins read
You are currently viewing How to default date parameters in BI Report dynamically?
Dynamic Date Parameters in BI Report

For most of the BI Reports, we have the input parameter as “AsOfDate” alias sysdate or current date… Sometimes we also have a date range “From Date” and “To Date” to fetch transactions between those dates. And these dates need to have a default value when we open the report/schedule the report. 

If the scheduled report includes date parameters, when you enter values for the schedule dates, you can’t change the date values. Every time a scheduled instance of the report is run, the same date parameters are used. If changing the date parameters is required for each run, you can enter an expression in the date parameter field of the scheduler to calculate the date each time the report job runs.

In this article we will try and see what are all the options possible for defaulting date parameters for BI Report.

image 31 - How to default date parameters in BI Report dynamically?

  • {$SYSDATE()$} – current date, the system date of the server on which BI Publisher is running.
  • {$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
  • {$SYSDATE()-7$} – Returns the current date – 7 day
  • {$FIRST_DAY_OF_MONTH()-1$} – Returns the last day of the previous month
  • {$LAST_DAY_OF_MONTH()+1$} – Returns the first day of the next month

We can use these values in the BI Data Model as the default values and when the report is scheduled, the dates will be picked up dynamically based on the system date.

image 32 - How to default date parameters in BI Report dynamically?

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()$}’.

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()$}’

Summary:
Using the above expressions you can populate the default date values when you run the BI Publisher report.

  • Hi Gopi,

    There isn’t a way to get the First day and Last day of the previous month dynamically. It has to be handled in the SQL Query only if there is such need.

    Thanks,
    Sricharan

  • Hi Sir,

    Is is possible to attach a query on Date Parameter’s default value to achieve the retrieval of 1st and last day of the previous month?
    Or not? I’m seeing that BIP Date Parameter – Default Value is only limited to the set of this functions / expression you have given above.

    Thanks,
    Colin

  • Hi Colin,
    It isn’t possible to default the 1st day of previous month for the date parameters. We can get the last day of previous month by using {$FIRST_DAT_OF_MONTH()-1$}. You can write this logic in the SQL query in the data model itself.. We have limited scope to define the defaults for the date parameters.We can’t go beyond that..
    Thanks,
    Sricharan

  • In visible box by plugintheme