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!

%d bloggers like this: