BusinessObjects: DocumentPartiallyRefreshed function

When developing a Business Objects or Web Intelligence report, how many of you can honestly put your hands up and state that you make use of the DocumentPartiallyRefreshed function? If I were a betting man, I’d bet that very few. Certainly on all the client sites I have worked at, I haven’t seen it used.

The DocumentPartiallyRefreshed function, is in fact a very handy function to incorporate into the cover page of any report you produce for end users. Why?

Well, for seasoned report developers, should a document not refresh fully, we would note a tiny yellow flashing icon in the bottom toolbar indicating a partial refresh. But it could very easily escape your attention. And I’m not sure many end users would recognise what it meant.

The danger of partially refreshed document is that action is taken by the business based on information held in that partially refreshed report.

“Yikes…our report is showing we only have 100 widgets left and we need to order more in for the weekend”. In fact, stock is showing over 1000 widgets in stock.

A trivial example, but you get what I mean.

So my tip for today is to add a cell to the cover page of your report (you do create cover pages don’t you? – maybe a future post).

The DocumentPartiallyRefreshed function is a boolean function which will return a 1 or a 0 (true or false) when the report is refreshed. If the report is partially refreshed, your value will be 1.

You can create a variable that wraps the DocumentPartiallyRefreshed function inside an If statement. So, If the document is partially refreshed then show this message else show this message.

A simple implementation of this is as follows:

=If (DocumentPartiallyRefreshed()) Then “***DOCUMENT PARTIALLY REFRESHED***” Else “Document has been refreshed”

We can take this a step further by adding a date of last full refresh:

=If (DocumentPartiallyRefreshed()) Then “***DOCUMENT PARTIALLY REFRESHED***” Else “Document last refreshed:”+FormatDate(LastExecutionDate(DataProvider()) ,”dd/mm/yyyy”)

So a user opens their ‘refreshed’ report, and if there has been a problem, the statement “***DOCUMENT PARTIALLY REFRESHED***” should be prominently displayed.

If you REALLY want to make sure the user notices this message, you could create an Alerter on the cell. If the document is partially refreshed, lets highlight the text in this cell a bright red colour.

To do this, create a formula as follows:

=DocumentPartiallyRefreshed()

Give the formula a variable name such as v_Partially_Refreshed.

Highlight the relevant cell on the cover page and click on the Alerter button.

Click Add to add a variable to compare. In our case, select v_Partially_Refreshed.

Operator 1 should be an equals sign and Value 1 should be 1.

Select the dropdown from the Result box and Format to your heart’s content. Red and Bold for this type of exception looks good.

For those using Web Intelligence, the DocumentPartiallyRefreshed function and the steps to create an Alerter are exactly the same.

There you have it. A practical use of the DocumentPartiallyRefreshed function.

Are there any other functions you would like me to cover in this blog? If so, drop me a comment and I will try to oblige.

SAP Training and Certification shop

SAP have implemented a training and certification shop.

To access, make your way to:

 https://training3.sap.com/gb/en/search?SolutionId=140#certification

You will be presented with a list of certification and training modules. These include the following certifications:

• C_BOE_30 – SAP Certified Application Associate – SAP BusinessObjects Enterprise XI 3.x
• C_BOBIP_40 – SAP Certified Application Associate – SAP BusinessObjects Business Intelligence platform 4.0
• C_BOWI_30 – SAP Certified Application Associate – SAP BusinessObjects Web Intelligence XI 3.x
• C_BOWI_40 – SAP Certified Application Associate – SAP BusinessObjects Web Intelligence 4.0

I didn’t see a certification for Data Services. However, a Data Services course is available from here:

https://training.sap.com/gb/en/course/bods10-sap-businessobjects-data-services-40-classroom-096-gb-en/

You can also select training specific to your country. At the present time, the following countries appear to be supported:

 Good luck with your training and certification.

Business Objects: Count v CountAll functions

Business Objects: Count v CountAll

I’m going to quickly run through the difference between the Count and CountAll function. Note that the CountAll function is only available in Full Client. Web Intelligence provides the Count function with a set of parameters, which I will discuss later.

Let’s take a simple example data set:

Two columns, one called Name and the other called Function.

Lets create eight rows of tab delimited data in a text file as follows:

Note that the seventh row does not have a persons name. Let us assume this is a position that needs to be filled. The eighth row has a name and no function.

If we load this data into BusinessObjects, we get the following result:

What result do you expect if you perform a count on the name column? Remember, you have loaded 8 rows.

If you guessed 6, you are correct.

To prove this, select the n icon from the toolbar or right click on the column and select Calculations/Count, an additional row is added to the table with the value 6. If you take a look at the formula behind the value 6, you will see it is =Count(<Name>).

Now if you perform a CountAll on the name column, what value do you expect to see?

If you guessed 7, you are wrong. Why? Because CountAll counts all rows, including duplicate values and empty rows.

To prove this, right click again on the column and select Calculations/CountAll. An additional row is added to the table with the value 8.

We can repeat this exercise on the Function column. Remember, we loaded 8 rows. What will be returned if you run a Count on the Function column?

If you guessed 7, you are wrong. Count will return a count of DISTINCT rows. So while you have loaded 8 rows, two of those contain Sales. Hence the Count will return one count of Sales. Also note it will not count empty rows.

To prove this, select the n icon from the toolbar or right click on the column and select Calculations/Count, an additional row is added to the table with the value 6. If you take a look at the formula behind the value 6, you will see it is =Count(<Function>).

So what value will be returned if we perform a CountAll?

If you guessed 8, you are correct. CountAll will return a sum of all rows, including duplciates an empty rows.

Web Intelligence: Count

If you are using Web Intelligence, you will not have a CountAll function. Instead you have a Count function two parameters:

Count([object]; [INCLUDEMEPTY]; [DISTINCT|ALL])

Using our example data set, what will Count([Name]) return?

The answer is 6. Count([Name]) will return a distinct list of Names.

What do you think will be returned by Count([Name];[INCLUDEEMPTY])?

If you guessed 8, you are wrong. If you guess none, well done.

Why? Okay, I slightly misled you. The syntax of the formula is incorrect. If you are including a parameter, do not use the square brackets for the parameter. The correct formula is: Count([Name];INCLUDEEMPTY).

This will bring back 7 rows. Six distinct rows plus an empty row.

So how many rows are counted using Count([Name];INCLUDEEMPTY;DISTINCT)?

The answer is 7. You bring back all the distinct rows (there are six – John is duplicated), and one empty row.

So what will Count([Name];INCLUDEEMPTY;ALL) bring back?

The answer is 8. All rows, including duplicates and empty rows.

There are valid scenarios where you may want to use the CountAll or Count([Name];ALL) function. It is important to be clear on where these functions should be used, as used incorrectly, they could bring back incorrect results.

Did you find this type of article useful? Are there any other functions that you would like me to walk through? If so, please let me know via a comment. Thanks for reading!

SAP offer accelerated training packages for BI 4.0

I have recently found out that SAP offer accelerated learning packages for their product suite.

Of interest to most readers will be the SAP BI 4.0 package which can be found at:

https://training.sap.com/bundles/product/sap-bi

Purchase of the package requires your SAP login or a new registration.

For those that have experience in previous versions of the Business Objects product suite and need a helping hand to understand the new features of BI 4.0, this may be the way to go.

Web Intelligence – Extracting a date from a string

This article discusses a solution to a problem encountered by one of our clients.

In this particular case, our client has a string field which is populated with some free form text; a comment. When this data is pulled into the data warehouse, the date the comment was entered is appended to the comment.

The client wanted to create a Web Intelligence report that would bring back comments made over the past N number of days.

A number of issues meant this was not a simple request.

First - The comment creation date is not held seperately within the data warehouse and we have no quick means of changing the data warehouse to do so.
Second – The date has been appended to the comment as one long string, so the date format has been lost.
Third – Finally, the user wanted some flexibility to the report to select the last N number of days.

The solution:

The solution to the problem required several steps.

Step 1
The first step was to create an object to return the current date less a number of days specified by the user. We created a formula which prompted the user for a value (the number of days), and we subtracted that value from the current date. As we wanted to keep the user input, we used a prompt:

convert(datetime,convert( varchar,dateadd(dd,-@Prompt(‘Go back N days’,'N’,,MONO,FREE),getdate()),102))

When the user selects this object in the query panel, we get two values. The first is the result of the prompt ‘Go back N days’. This will be used in the report later. The second is a date value which is the current date less the number of days specified by the user. This will also be used in the report.

Step 2
The second step was to isolate the date field from the comment. Luckily the date is held in a constant format (Mmm dd yyyy) and is always at the end of the comment. The date part of the string is always eleven characters long.

Using the ‘Right’ function, we created a variable as follows:

=Right([Retail_User_Comment];11)

This tells Web Intelligence to read eleven positions back from the end of the string ‘Retail_User_Comment’.

This was stored in a variable called v_Comment_Date. We will use this later.

Step 3
The next step was a bit more tricky. The date field is held in a character string format. We wanted this in a date format. To do this the component parts were broken down into day, month and year.

Day:
Left(Right([v_Comment_Date];7);2)

Month:
If(Left([v_Comment_Date];3)=”Jan”;1;
   If(Left([v_Comment_Date];3)=”Feb”;2;
      If(Left([v_Comment_Date];3)=”Mar”;3;
         If(Left([v_Comment_Date];3)=”Apr”;4;
            If(Left([v_Comment_Date];3)=”May”;5;
               If(Left([v_Comment_Date];3)=”Jun”;6;
                  If(Left([v_Comment_Date];3)=”Jul”;7;
                     If(Left([v_Comment_Date];3)=”Aug”;8;
                        If(Left([v_Comment_Date];3)=”Sep”;9;
                          If(Left([v_Comment_Date];3)=”Oct”;10;
                            If(Left([v_Comment_Date];3)=”Nov”;11;
                              If(Left([v_Comment_Date];3)=”Dec”;12
                              )
                            )
                          )
                        )
                      )
                    )
                  )
                )
             )
          )
        )

Year:
Right([v_Comment_Date];4)

The items were then concatenated together, seperated by a forward slash using the Concatenation function.

Finally, the whole string was converted into a date field in the format we required dd/MM/yyyy. The resulting formula is as follows:

=ToDate(Concatenation(Concatenation(Concatenation(Concatenation(FormatDate(ToDate(Left(Right([v_Comment_Date];7);2);”dd”);”dd”);”/”);FormatDate(ToDate(If(Left([v_Comment_Date];3)=”Jan”;1;If(Left([v_Comment_Date];3)=”Feb”;2;If(Left([v_Comment_Date];3)=”Mar”;3;If(Left([v_Comment_Date];3)=”Apr”;4;If(Left([v_Comment_Date];3)=”May”;5;If(Left([v_Comment_Date];3)=”Jun”;6;If(Left([v_Comment_Date];3)=”Jul”;7;If(Left([v_Comment_Date];3)=”Aug”;8;If(Left([v_Comment_Date];3)=”Sep”;9;If(Left([v_Comment_Date];3)=”Oct”;10;If(Left([v_Comment_Date];3)=”Nov”;11;If(Left([v_Comment_Date];3)=”Dec”;12))))))))))));”MM”);”MM”));”/”);FormatDate(ToDate(Right([v_Comment_Date];4);”yyyy”);”yyyy”));”dd/MM/yyyy”)

This variable was named v_Comment_Date2.

Step 4:

We now have the date field isolated from the character string. Now we want to see rows which have been updated in the last N days. If you remember, we prompted the user for a number of days. This is held in the ‘Go back N days’ prompt.

Using the ‘DaysBetween’ function, we can create the following formula:

=If(DaysBetween([v_Comment_Date2];[Go back N days])

If the days between the comment date and the ‘Go back N days’ date is less than the value entered by the user, we return a 1 otherwise we return a 0.

We save this formula as a measure called v_Comment_Filter. It is important that this is saved as a measure otherwise we would not be able to filter on this value.

Step 5:

The final step is to filter the v_Comment_Filter object to show records with a 1 value, i.e. those records with a comment date less than the numberof days entered by the user at the prompt.

When the user now refreshed the report, he or she is presented with a prompt for number of days to go back, and is presented with a report with comments updated N days ago.

I hope you found this useful.

SAP to acquire Sybase

SAP today announced that it was acquiring database vendor Sybase for $5.8bn in cash.

Sybase, founded in 1984, is famous for developing the original SQL Server source code and signed a deal with Microsoft to share the source code. The two companies went different ways some years later, and started developing their own versions of the source code.

More recently Sybase gambled on a move into the enterprise mobile space, and this may be the key reason for SAP’s interest in the company.

With a heavy financial services Sybase user base, SAP is now able to integrate its enterprise class applications to an increasingly mobile workforce. The avalanche of mobile devices like the iPad, iPhone, Blackberry and Windows Mobile devices, is a clear sign that Enterprise is moving into this area.

Configuring ODBC for XI 3.1 on a 64bit Operating System

So you’ve got a nice shiny new box running 64bit Windows Server 2008 and SQL Server 2008.

It’s going to run XI 3.1, so you go in to SQL Server and create your Business Objects repository.

You then go into the ODBC data source administrator and set up your ODBC connection. You test the connection, it works fine. Great.

You then kick off the XI 3.1 installation, and you get to the screen to select your database. No problem. I’m using SQL Server, I’ve created the database and I’ve created the ODBC connection.

So where is my ODBC connection?

You may have spotted a new check box on the SQL Server Logon screen ‘Consume DSN created under WOW64′.

“Never seen that before”, you say. “I’ll uncheck it”. And voila, your ODBC connection appears.

STOP RIGHT THERE!

That connection have been created using the 64bit version of the ODBC data source administrator.

You need to go back and recreate your ODBC connection using the 32bit ODBC data source administrator. Eh? What’s that all about?

To save yourself from a sore head, take a look at the posting provided by Dallas Marks. All will be revealed.

Many thanks Dallas.

SAP User ID

As I’ve mentioned in previous postings, a SAP User ID is now required if you want to take a SAP Business Objects exam.

I regularly receive emails asking how you go about getting one of these ID’s, especially if you are not a partner/customer.

Note that this blog is not connected with SAP so I can’t tell you for sure. However, two options seem to work:

1. Register at the following link. This will take you to the SAP site where you can register your details.
or
2. Call the SAP education department in your country

It takes a couple of weeks to get the User ID through, so be patient.

New Certification Exams – Data Integrator now added

Further information regarding the new Business Objects exams. Data Integrator is going to be added to the list of available exams.

More info can be found here.

Note that existing BOCP holders do not need to recertify:

“…individuals holding a current valid BusinessObjects certification will not need to recertify.”

Also nice to see that existing holders of the BOCP certifcation can use the new titles:

‘…those holding the valid BusinessObjects certification in the latest release are entitled to use the SAP Associate certification logo and title but no new certificates will be issued.”

One thing to point out though, if you are BOCP-BOE certified to XIR2 and haven’t yet taken the XI3 upgrade SABE501, you may want to do it before the exam is removed:

“If a test taker is certified on BusinessObjects Certified Professional – Business Objects Enterprise XI R2 and requires only the XI 3.0 upgrade exam (SABE 501) to qualify for the XI 3.0 certificate what should he/she do?

To avoid taking the full certification exam after the retirement of the XI 3.0 upgrade exam the final exam should be taken prior to its retirement at the end of April.”

Get your skates on! There may be limited places in your chosen test centre.

New Certification Exams – Part 2

Following on from my recent post about the new SAP Business Objects exams, I spotted a link to a FAQ.

Some interesting items to bring to your attention:

1. It would appear that you no longer need to take 3 papers/tests to earn the SAP Certified Application Associate – SAP BusinessObjects Enterprise XI 3.x certification. Those that have the BOCP-BOE certification will be aware that we had to sit 3 exams, or 4 if you have brought your skills up to XI3 level.

2. Those with an existing BOCP certification do not need to re-qualify. Phew!

3. If you have BOCP-BOE at XIR2 level and haven’t sat SABE501 to take you up to XI3 level, you need to hurry. To quote
“To avoid taking the full certification exam after the retirement of the XI 3-1 upgrade exam the final exam should be taken prior to its retirement at the end of April.”

4. Those holding BOCP can use the title SAP Associate. Again, to quote:
“…those holding the existing certification BusinessObjects certification are entitled to use the SAP Associate certification logo and title.” (Personally I prefer BOCP-BOE – Certified Professional. Sounds better than Associate don’t you think?)

5. You still need a SAP S-User ID to take the exams.

6. For those that are looking to find out where their certificate is, and I know that there are many of you..
“On successfully passing the exam, the certificate will be sent to the test taker within 4-6 weeks. For queries please contact kps.businessobjects.kt@sap.com.”

7. It looks like the exams will become more expensive.

So basically, if you are working through your BOCP exams, get them done by April 2010.

If you haven’t upgraded your BOCP to XI3, get the SABE501 exam out of the way by April 2010.

If you are considering taking the exams and haven’t started BOCP yet, you might want to consider the SAP Associate exam instead of the four exams needed for BOCP.

More details can be found in this FAQ.