# Calculation contexts issue with Sum, Where and If

Make a report based on eFashion universe with [Year], [City], and [Sales revenue].

Now we will try to calculate total Sales revenue for Austin in 2004 but in two steps.

## Where and If

Define variables as:

[Sales Austin] =[Sales revenue] Where([City]=”Austin”)

[Sales Austin 2004] =Sum(If [Year]=”2004″ Then [Sales Austin])

The expected value for [Sales Austin 2004] is 561123.

But

1) The value calculated in the Sum line for Sales Austin for some reason is 2805617 (which is 5 x Sales Austin 2004 = 5 x 561123).

2) The value calculated in the table on the right is 13498366 (Which is the total of all values in the column Sales Austin i.e. 5 x (561123 + 1003071 + 1135479) = 13498366).

This was a result for BO3.1 SP7. In BO4.1 SP7, the result is slightly different, we will get #MULTIVALUE in the right table.

We can explicitly add [Year] to calculation context.

[Sales Austin 2004] =Sum((If [Year]=”2004″ Then [Sales Austin])ForEach([Year]))

This will fix the value in the right table, but not in the Sum line.

If we explicitly add [Year] and remove [City], then the result will become correct.

[Sales Austin 2004] =Sum((If [Year]=”2004″ Then [Sales Austin])ForEach([Year])ForAll([City]))

or

[Sales Austin 2004] =Sum((If [Year]=”2004″ Then [Sales Austin])In([Year]))

So the calculation context in the variable [Sales Austin 2004] is derived incorrectly. Since we have [City] in the expression, it should have been included by BO. The variable [Sales Austin] has Where([City]=”Austin”), which removes [City] from context, so [City] should have been excluded. But for some reason, we have to specify this manually.

## If

[Sales Austin] =If [City]=”Austin” Then [Sales revenue]

[Sales Austin 2004] =Sum(If [Year]=”2004″ Then [Sales Austin])

This works fine unless we add sum to [Sales Austin]:

[Sales Austin] =Sum(If [City]=”Austin” Then [Sales revenue])

[Sales Austin 2004] =Sum(If [Year]=”2004″ Then [Sales Austin])

In this case, the right table will show wrong value (13498366 in BO3 and #MULTIVALUE in BO4). We need to add [Year] to calculation context in [Sales Austin 2004].

## Where

[Sales Austin] =[Sales revenue] Where ([City]=”Austin”)

[Sales Austin 2004] =[Sales Austin] Where ([Year]=”2004″)

The result will be correct if we use variable value [Sales Austin 2004] in Sum line. If we add Sum, the result in Sum line will become wrong 2805617.

You might also find it interesting that the following two formulas return different result

=Sum([Sales revenue] Where ([Year]=”2004″ And [City]=”Austin”))

=Sum([Sales revenue] Where ([Year]=”2004″)Where([City]=”Austin”))

The first is correct but the second will return 1683370. (I am not sure what this number is.)

BO BI 4 Tomcat generates some logs in the root folder such as SBOPWebapp_BIlaunchpad, SBOPWebapp_CMC, SBOPWebapp_Mobi_Server:

To move the logs to another folder, add Tomcat Java option

`-Duser.home=C:\Program Files (x86)\SAP BusinessObjects\BO Logs`

# How to disable version check in Upgrade Management Tool

To avoid error

“Version check failed. The source system or source BIAR file must be of an older version. The destination system system must be of the current version. (UMT 20012)”

you can use (at own risk) the option

`-internal_use_only_noversioncheck`

# Web Intelligence RESTful Web Services SDK with Java

In this post you will find an example of how to use Web Intelligence RESTful Web Services SDK with Java. The code displays names of the variables for each web intelligence document.

To run the code, you will need json library that can be found for instance on http://mvnrepository.com/artifact/org.json/json/20160212

## Example (Program.java)

```import org.json.JSONArray;
import org.json.JSONObject;

public class Program {
public static void main(String[] args) throws Exception {
// Establish connection
Bo4Connection connection = new Bo4Connection("http://ANALYTIX:6405/biprws");
try {
// Get list of documents
String docs = connection.query("GET", "/documents", "application/json");
JSONArray documents = new JSONObject(docs).getJSONObject("documents").getJSONArray("document");
for (int i = 0; i < documents.length(); i++) {
JSONObject document = documents.getJSONObject(i);
int id = document.getInt("id");
// Get information about the document
String doc = connection.query("GET", "/documents/" + id, "application/json");
JSONObject info = new JSONObject(doc).getJSONObject("document");
String name = info.getString("name");
String path = info.getString("path");
System.out.println(path + "/" + name);
// Get variables of the document
String var = connection.query("GET", "/documents/" + id + "/variables", "application/json");
JSONArray variables = new JSONObject(var).getJSONObject("variables").getJSONArray("variable");
for (int j = 0; j < variables.length(); j++) {
// Print variable information
JSONObject variable = variables.getJSONObject(j);
String variableName = variable.getString("name");
System.out.println(variableName);
}
}
} finally {
connection.disconnect();
}
}
}
```

## API (Bo4Connection.java)

The helper API is very simple and contains a constructor and 3 functions: connect(username, password, auth), disconnect(), and query(method, link, format).

The constructor’s parameter is the link to web services.

Bo4Connection connection = new Bo4Connection();

The call to web services is performed with function query. For instance, to get list of documents we send a GET request to /documents, and we want to get result in json format.

connection.query(“GET”, “/documents”, “application/json”);

To get the list of all possible request, please refer to RESTful Web Services SDK Developer Guides on http://scn.sap.com/docs/DOC-27465

```import java.io.*;
import java.net.*;
import java.nio.charset.StandardCharsets;
import org.json.JSONObject;

public class Bo4Connection {

private String biprws;
private String token;

public Bo4Connection(String biprws) {
this.biprws = biprws;
}

public String query(String method, String link, String format) throws Exception {
return query(method, biprws + "/raylight/v1" + link, format, token, null);
}

String link = biprws + "/logon/long/";
String method = "POST";
String format = "application/json";
String body = "<attrs xmlns=\"http://www.sap.com/rws/bip\">"
+ "<attr name=\"auth\" type=\"string\">" + auth + "</attr>"
+ "</attrs>";
JSONObject json = new JSONObject(query(method, link, format, null, body));
token = json.getString("logonToken");
}

public void disconnect() throws Exception {
String link = biprws + "/logoff/";
String method = "POST";
String format = "application/json";
}

public static String query(String method, String link, String format,
String token, String content) throws Exception {
HttpURLConnection conn = null;
try {
conn = (HttpURLConnection) url.openConnection();
conn.setRequestMethod(method);
conn.setRequestProperty("Accept", format);
if (token != null) {
String logonToken = "\"" + token + "\"";
conn.setRequestProperty("X-SAP-LogonToken", logonToken);
}
conn.setDoOutput(true);
conn.setDoInput(true);
if (content != null) {
conn.setRequestProperty("Content-Type",
"application/xml; charset=utf-8");
conn.setRequestProperty("Content-Length",
Integer.toString(content.length()));
OutputStreamWriter out = new OutputStreamWriter(conn.getOutputStream());
out.write(content, 0, content.length());
out.flush();
}
conn.connect();
if (conn.getResponseCode() != 200) {
throw new Exception("HTTP Error Code: " + conn.getResponseCode()
+ " " + conn.getResponseMessage());
}
StringBuilder result = new StringBuilder();
String output;
while ((output = br.readLine()) != null) {
result.append(output);
result.append('\n');
}
br.close();
return result.toString();
} finally {
if (conn != null) {
conn.disconnect();
}
}
}
}

```

# Re-deploying BusinessObjects XI 3.1 web applications

These steps describe the procedure for re-deploying web applications for BO XI 3.1 with Tomcat 7. Re-deploying is required, for instance, if you uninstall a language pack from Business Objects.

• Stop Tomcat
• Backup Tomcat7 folder located in Business Objects folder
• Remove all applications except ROOT, manager, host-manager, docs, examples (these are Tomcat applications) from Tomcat7\webapps
• Remove all subfolders from Tomcat7\work\Catalina\localhost
• Change directory to deployment located in Business Objects folder
• Run wdeploy tomcat7 deployall
• If the message is “BUILD SUCCESSFUL”, the application are successfully deployed.
• Start Tomcat and check web applications.

# How to edit merged dimensions in BO BI 4.x

In BO XI 3.1, to edit a merged dimension, we could right click on a merged dimension, and select “Edit merged dimension” from pop up menu, this would open a dialog for editing merged dimensions. This has changed in BI 4.x, and it might be not obvious how to adjust merged dimensions.

Add dimensions to a merged dimension:

Select the merged dimension and objects you want to add, click right button, select “Add to Merge”.

Remove dimensions from a merged dimension:

Select dimensions that you want to remove from merged dimensions, click right button, and select “Remove from Merge”

# Referencing to a document’s block in OpenDocument link

When we use a OpenDocument link to open a Webi document, the report is displayed with a number of controls.

http://localhost:8080/OpenDocument/opendoc/openDocument.jsp?sType=wid&sIDType=CUID&iDocID=Aan15wubifNFikJjmlT.LVU

Sometimes we want to get rid of the controls and display only specific block (for instance, when it needs to be embedded into another application). This can be done with undocumented parameter sReportPart

http://localhost:8080/OpenDocument/opendoc/openDocument.jsp?sType=wid&sIDType=CUID&iDocID=Aan15wubifNFikJjmlT.LVU&sReportPart=UIREF:RID=469:BID=473&mode=part

The tricky part is to find the ids: RID – Report id, and BID – Block id.

To find the IDs,

• open the document in Web Intelligence Rich Client and save it as WID file
• rename WID file to ZIP
• unpack ZIP file
• open file Data\RE\DOCSPEC
• find necessary report element with RID attribute
• find necessary block element with BID attribute

# SAP BO SL SDK 4.1 Interface requested not found : csLIB

I have struggled quite a lot with the error “Interface requested not found : csLIB” when trying to open a semantic layer using SAP BO SL SDK 4.1. It was quite a complex issue so I will summarize it here in case someone has a similar problem.

Here I am using a local universe Test with SQL Server connection.

The code is simple.

```import com.sap.sl.sdk.authoring.businesslayer.RelationalBusinessLayer;
import com.sap.sl.sdk.authoring.local.LocalResourceService;
import com.sap.sl.sdk.framework.SlContext;

public class Test {
public static void main(String[] args) {
SlContext context = SlContext.create();
LocalResourceService service = context.getService(LocalResourceService.class);
context.close();
System.out.println("OK");
}
}
```

Batch for compilation and execution

```@echo off
set JAVA_HOME=C:/Program Files (x86)/Java/jdk1.6.0_45
set JAVA="%JAVA_HOME%/bin/java"
set JAVAC="%JAVA_HOME%/bin/javac"

set CS=%BO%/dataAccess/connectionServer
set CP=%BO%/SL SDK/java/sl_sdk.jar;%BO%/java/lib/*

set PATH=%BO%/win32_x86

%JAVAC% -classpath "%CP%" Test.java
pause
```

Everything was done according to SL SDK documentation but I still got the error:

Caused by: java.lang.UnsatisfiedLinkError: C:\Program Files (x86)\SAP BusinessObjects\SAP BusinessObjects Enterprise XI 4.0\win32_x86\cs_jni.dll: The specified procedure could not be found

I started investigating DLL loading process using Process Monitor and found that the library cs_jni.dll depends on the library icuin30.dll from win32_x86. However I had an older version in the folder C:\Windows\SysWOW64\. And since the system directories are checked before going through directories in the PATH variables, a wrong version was picked.

I have overwritten the icu??30.dll libraries in SysWOW64 with the libraries from win32_x86 and the code started to work.

(The “icu” stands for “International Components for Unicode”.)

I do not know the impact of the change to other application, use it at your own risk. If you are going to try it, make backup of the existing files.

# XI 3.1 Issue with Excel Data Provider

Problem

A Webi document has a query based on Excel (Excel data source). The document can be refreshed in Web Intelligence Rich Client, but it fails in Infoview with WIS 30270 error. The following errors appear in the log:

```ExtensionFactoryImpl.cpp:201:void __thiscall WICDZExt::ExtensionFactoryImpl::createRemoteExtension(const
class WICDZExt::ExtensionDescriptor &,struct WICDZExt::IExtension **): TraceLog message 24650
2014/10/17 09:02:31.474|>>|E| |13916|15036| |||||||||||||||ExtensionFactoryImpl::createRemoteExtension
has failed : ExtensionFactoryImpl.createExtension has failed : java.lang.Exception: extension creation failed : null object
ExtensionFactoryImpl.cpp:202:void __thiscall WICDZExt::ExtensionFactoryImpl::createRemoteExtension(const
class WICDZExt::ExtensionDescriptor &,struct WICDZExt::IExtension **): TraceLog message 24651
2014/10/17 09:02:31.474|>>|E| |13916|15036| |||||||||||||||java.lang.Exception: extension creation failed : null object
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
at java.lang.reflect.Method.invoke(Method.java:592)
at com.crystaldecisions.enterprise.ocaframework.idl.OCA.OCAcdz.WICDZExtensions.ExtensionFactoryPOA\$\$EnhancerByCGLIB\$\$9792817d.createExtension(<generated>)
at com.crystaldecisions.enterprise.ocaframework.idl.OCA.OCAcdz.WICDZExtensions.ExtensionFactoryPOA._OB_op_createExtension(Unknown Source)
at com.crystaldecisions.enterprise.ocaframework.idl.OCA.OCAcdz.WICDZExtensions.ExtensionFactoryPOA._invoke(Unknown Source)
at com.crystaldecisions.thirdparty.com.ooc.OBPortableServer.ServantDispatcher.dispatch(ServantDispatcher.java:234)
at com.crystaldecisions.thirdparty.com.ooc.OBPortableServer.POA_impl._OB_dispatch(POA_impl.java:1917)
at com.crystaldecisions.thirdparty.com.ooc.OB.DispatchRequest_impl.invoke(DispatchRequest_impl.java:75)