How to calculate the number of days in a month in WebIntelligence

A quick method of determining the number of days in a month in WebIntelligence is through the use of the LastDayOfMonth function.

For a specified date, the LastDayOfMonth will give you the last day in that month.

=LastDayOfMonth(CurrentDate())

To extract the number of days, we want to use the first two digits of this result. The following formula will acheive that:

=Left(FormatDate(LastDayOfMonth(CurrentDate());”dd/MM/yyyy”);2)

We’ve added two functions here. The first is FormatDate. LastDayOfMonth returns a value held as a date field. We ultimately want the first two digits of this field. However, using the Left() function means we have to have a string input, not a date input. We have to convert the input into a string format first.

This requires two steps.

The first is to convert the LastDayOfMonth value into a string. We do this using the FormatDate function:

=FormatDate(LastDayOfMonth(CurrentDate());”dd/MM/yyyy”)

We now have a string.

We then extract the first two digits to give us the number of days in a month:

=Left(FormatDate(LastDayOfMonth(CurrentDate());”dd/MM/yyyy”);2)

If we want to subsequently use this in a calculation, we need to further manipulate this value and convert it to a number. This is achieved using the ToNumber function:

number ToNumber(string number_string)

This takes a string value and converts it to a number.

In our case, we create the following formula:

=ToNumber(Left(FormatDate(LastDayOfMonth(CurrentDate());”dd/MM/yyyy”);2))

We now have the number of days for a particular month that we can use in our calculations.

Missing FirstDayOfMonth() function…

Sadly, there isn’t a function to determine the first day of the month. However, we have a method of extracting the last day of the month using LastDayOfMonth() and using the formulas described above, we can work out the first day:

=RelativeDate(LastDayOfMonth(CurrentDate());-ToNumber(Left(FormatDate(LastDayOfMonth(CurrentDate()) ;”dd/MM/yyyy”);2))+1)

Despite looking complex, we are subtracting the number of days in the month (plus one) from the last day of the month. If we subtracted the total number of days in the month, we would end up with the last day of the previous month! We don’t want this. So we add a 1 to give us the first day.

Also note that in order to subtract days, we have prefixed the ToNumber function with a minus symbol.

Update:

Hat tip to JB for pointing out that we can avoid the string conversion by using the DayNumberOfMonth function. 

Hence, to calculate the last day of the month:

=DayNumberOfMonth(LastDayOfMonth(CurrentDate()))

And the first day of the month:

=DayNumberOfMonth(RelativeDate(CurrentDate(); 1-DayNumberOfMonth(CurrentDate())))

How to convert a string prompt into a date in WebIntelligence

 There are occasions where we need to prompt the user for a date. This can be done using the prompt functionality in the query panel.

We can display the value entered by the user at the prompt using the UserResponse function.

The UserResponse function is made up of two parts:

string UserResponse(string data_provider;string prompt)

The first part is the Data Provider that contains the prompt that you want to use. This is an optional field and is only required if your report contains multiple data providers. If you are only using one data provider, ignore this first part.

The second part is the EXACT text of your prompt string.

This needs to be a perfect match to your prompt. A way to ensure this is to copy and past the prompt string before you refresh the report.

When refreshed, the function will return the prompt value.

Note that the value returned from the UserResponse function is a string. Despite the value being shown as a date, the value is stored as a string. This means we are not able to perform date calculations on this field as it stands. The error message that will be shown is:

 

To overcome this, we expand the formula slightly and nest the UserResponse function inside a ToDate function.

The ToDate function is made up of two parts:

date ToDate(string input_string;string date_format)

The first part is the input string (in our case the UserResponse formula), and the second part is the format of the input. Note that the format has to match the input exactly.

We end up with the following formula:

 =ToDate(UserResponse(“Enter Date:”);”dd/mm/yyyy”)

At this stage, the user prompted date is converted to a date format and can be used in date calculations.

Bonus:

If you subsequently want to display the user prompted date in a specific date format, we would nest the formula further and include a FormatDate function:

string FormatDate(date date_to_format;string date_format)

This function takes two values. The first is the date you want to modify and the second part is the format string you want the date to appear in.

For example, if the user has entered the value 14/01/2012 at the prompt and we want to display this as Saturday 14 January 2012, we would create the following formula:

=FormatDate(ToDate(“Enter Date:”);”dd/mm/yyyy”);”Dddd dd Mmmm yyyy”)

In the above example, note that the output of the FormatDate function is a string, not a date. This is important to remember. If you need to perform calculations on a date, make sure it is in a date format (using ToDate).

Once you have finished your date calculations, you can then format the output in any way you like using FormatDate.

For a list of available date formats, take a look at this post. https://bobjblog.wordpress.com/2012/03/05/web-intelligence-date-formats

Note: If you receive #ERROR messages in your calculations, ensure that you are not using a date and time string. The examples above are based on dates in the format mm/dd/yyyy and not mm/dd/yyyy hh:mm:ss

Web Intelligence Date Formats

A simple post to highlight the different date formats that are available in Web Intelligence.

How to upgrade your Business Objects XI3 install to BI4

SAP have created a useful wiki which provides details on how to upgrade your BusinessObjects XI3 environment to BI4.

The wiki helps you to:

  • Identify what to upgrade
  • Taking backups prior to the upgrade
  • Use of the Upgrade Management Tool
  • Conversion of the Business Objects universes from .unv format to unx. format

A couple of things to be aware of:

  • BI4 uses a different audit schema
  • A 64bit Operating System is required for BI4

Certainly worth a read prior to undertaking the upgrade.

The link to the wiki is open for all and can be found here:

http://wiki.sdn.sap.com/wiki/display/BOBJ/How+to+Upgrade+to+BI4.0

Building relative date objects in Universe Designer

Here is a useful link to the Business Objects Forum – BOB, that provides details of how to create RelativeDate objects on a SQL Server or Oracle RDBMS system.

Useful article requiring no further comment.

http://www.forumtopics.com/busobj/viewtopic.php?t=152613

How to display the previous working day in Web Intelligence

Through the use of the DayName function, we can display the day name of the week for a particular date.

Today is Monday 13th February 2012.

What would be the result of creating the following formula in WebIntelligence?

=DayName(CurrentDate())

The answer is Monday. Today is Monday 13th February 2012.

Now, if I wanted to display the previous working day, I couldn’t use the RelativeDate function on it’s own. Remember we are looking for previous working day and not previous day.

In order to do this, we need to check the current day and determine if the previous day is a weekend. If it is, we need to adjust the output accordingly.

We can develop this logic within WebIntelligence using a nested IF statement.

Let’s work through an example:

Create a new WebIntelligence report. If you have a CurrentDate object in your universe, we can use that. If you don’t, then bring back any object (preferably one that doesn’t bring back many rows – we don’t actually want to use that value, but we do want to end up with a table).

If you do not have a CurrentDate object, create the following formula in WebIntelligence:

=CurrentDate()

Note that the format of the date value returned depends on how the universe object has been set up or how your WebIntelligence setup is configured. In some cases it may be formatted as dd-Mmm-yyy or dd/mm/yyyy. If you want to use a specific format and you are using the =CurrentDate() formula in WebIntelligence, you can format the date using:

=FormatDate(CurrentDate();”dd-Mmm-yyyy”)

For the purposes of this article however, we will be using the default format.

Add a second column to your table. We will call this column ‘Current Day Name’ and we will use the DayName function to return the day of the week:

=DayName(CurrentDate())

Now to get the previous working date.

We need to check the value (Day Name) of the current date. If it is a Tuesday, Wednesday, Thursday, Friday or Saturday, we will be fine using the RelativeDate function, as the previous day to the above listed days is a working day. (I’m ignoring Bank Holidays, Festive Days, etc. That’s for a future topic!)

However, if we find that the current Day Name is a Sunday or a Monday, then using RelativeDate is going to retrieve a weekend day. We need to handle these two scenarios using a nested IF statement.

The statement is built up as follows:

If the current day name is a Monday, then subtract three days from the current date
If the current day name is a Sunday, then subtract two days from the current date

The formula is made up as follows:

=If(DayName(CurrentDate())=”Monday”;RelativeDate(CurrentDate();-3);If(DayName(CurrentDate())=”Sunday”;RelativeDate(CurrentDate();-2);RelativeDate(CurrentDate();-1)))

Lets break down this formula.

An IF expression has three components:

If(boolean_expr;value if true;value if false)

The boolean_expr part is the statement that we test.

In our case, we want to check if the CurrentDate() is a Monday.

If it is true that it is a Monday, we want to use the RelativeDate function to subtract 3 days from the CurrentDate().

If it is false, i.e. the current date is not a Monday, we need to perform a further check. This requires a second IF statement to perform this second check.

The second check asks if the CurrentDate() is a Sunday.

If it is true that it is a Sunday, we want to use the RelativeDate function to subtract 2 days from the CurrentDate().

If it is false, i.e. the current date is not a Sunday, and we know its not a Monday as we checked for that previously, then we know that the day is a Tuesday, Wednesday, Thursday, Friday or Saturday and hence we can safely use RelativeDate with a value of -1 to give us the previous working day.

The formula above displays the result in a date format. If we wanted to display the result as a Day Name only, we can tweak the formula a little to incorporate the DayName() function:

=If(DayName(CurrentDate())=”Monday”;DayName(RelativeDate(CurrentDate();-3));If(DayName(CurrentDate())=”Sunday”;DayName(RelativeDate(CurrentDate();-2));DayName(RelativeDate(CurrentDate();-1))))

So using this formula we should end up with the following result:

Current Day Previous Working Day

Monday         Friday
Tuesday        Monday
Wednesday  Tuesday
Thursday     Wednesday
Friday           Thursday
Saturday      Friday
Sunday         Friday

How to add or subtract a number of days from a date field in Web Intelligence

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.

%d bloggers like this: