A report developer may be required to display a date field in a report which is made up of an existing date field plus/minus x number of days. A good example of this may be a report developed for the credit control function where you have issued an invoice on 30 day terms. The credit control team may want to identify those invoices which have exceeded those 30 days terms.
WebIntelligence provides a function to allow us to do this:
RelativeDate()
This function requires two values.
The first is the date field you wish to add/subtract from. The second value is the number of days you want to add or subtract. These two values are separated by a semi colon in WebIntelligence.
Note that the second value should be a numeric value and should not be in single or double quotes.
If you want to subtract a number of days, ensure that the value you enter is a negative number (proceeded by a minus sign).
Lets work through a couple of examples:
Adding seven days to a date field
Create your report and pull in a date field.
In this case I am using an object name called ‘Date Field’. Your date object may have a different name.
Add a new column to the right of the Date Field column, and build the following formula:
=RelativeDate([Date Field];)
*Replace the <number of days to add> with your value
=RelativeDate([Date Field];7)
This adds seven days to the value held in the Date Field

Subtracting seven days from a date field
Create your report and pull in a date field.
Add a new column to the right of the Date Field column, and build the following formula:
=RelativeDate([Date Field];-)
*Replace the <number of days to subtract> with your value. Remember that this should be a negative value.
=RelativeDate([Date Field];-7)

This subtracts seven days to the value held in the Date Field.
If you have access to Universe Designer, or can request a new object to be added to your reporting universe, we can take this to another level by making that value a dynamic field. This means that the user will be prompted for the number of days to add or subtract from the Date Field.
What if I want to prompt the user for a value to use in this formula?
This is possible. However, in order to do this, you need to add a new object to your Business Objects universe.
Create a new dummy object in the Business Objects universe which holds a User Response prompt.
Give it a meaningful name such as User_Prompted_Days.
@Prompt(‘Enter number of days to add/subtract:’)
Save and export the universe.
Rebuild your report and pull your new object User_Prompted_Days into your query alongside your date object.
Refresh the report.
You should now be presented with the following prompt:
“Enter number of days to add/subtract:”
Enter a value. This value will get stored in the object called User_Prompted_Days.
We can now use this object to build up our new date column.
Add a new column, and populate this column with the following formula:
=RelativeDate([Date Field];ToNumber([User_Prompted_Days]))
We use ToNumber in this formula as the prompt value will be stored as a character string.
You should end up with a new date based on the value held in the Date Field column plus/minus the days entered by the user at the prompt.
This approach has a benefit in that the value entered by the user can be applied to any number of date fields you bring back in your report.
If you find that you only need to apply the user response to a particular date field, consider creating an object with the prompt built in:
The object code to subtract x days from the current date in SQL Server is:
convert(datetime,convert( varchar,dateadd(dd,-@Prompt(‘Number of days to subtract from current date:’,’N’,,MONO,FREE),getdate()),102))
The object code to add x days to the current date in SQL Server is:
convert(datetime,convert( varchar,dateadd(dd,@Prompt(‘Number of days to subtract from current date:’,’N’,,MONO,FREE),getdate()),102))
In case you missed it, the key difference in the two formulas is the minus sign before the @Prompt.
Hopefully this will have helped you understand how to add or remove days from a date field in Web Intelligence.
You must be logged in to post a comment.