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

Advertisements

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: