Skip to Content
0

Change crystal reports SAP BW datasource via Java RAS SDK

Jul 11, 2017 at 10:37 AM

113

avatar image

Hi guys,

I have some Crystal reports which are getting data from a SAP BW via a BW query. I want to change the BW to which the reports are connecting via the java sdk.

I'm able to retrieve the connection info from a report, this is the result for a report connectet to the system DEV:

// I'm passing the instance of ReportClientDocument as parameter rcd into this method

ConnectionInfos ci = rcd.getDatabaseController().getConnectionInfos(null);
for(IConnectionInfo oldConnectionInfo : ci) {
PropertyBag p = oldConnectionInfo.getAttributes(); 
for(Object obj : p.keySet()) {
System.out.println(obj + " ------- " + p.get(obj) + "-------" + PropertyBag.class.isInstance(p.get(obj)));
}

Key Value

QE_DatabaseName CLIENT=100 LANG=DE MSHOST=sapdev GROUP=DEV R3NAME=DEV
QE_SQLDB false
SSO Enabled false
QE_DatabaseType SAP BW MDX Query
Database DLL crdb_bwmdx.dll
QE_LogonProperties {GROUP=DEV, Server=sapdev, MSHOST=sapdev, Database=CLIENT=100 LANG=DE MSHOST=sapdev GROUP=DEV R3NAME=DEV, R3NAME=DEV, SHOWEMPTYDATA=false, LANG=DE, sap_selected_item = 1320 DEV Development, CLIENT=100}
QE_ServerDescription sapdev

(I'm aware that QE_LogONProperties again is a property bag itself)

When I try to change the connection (e.g. to system PROD), the programm works, but the report somehow is broken afterwards. When viewing the report I just get a repeating logon prompt. Also in CMC when viewing the database information of the report I can see that some information seems to be missing comparing to before.

IConnectionInfo newConnectionInfo = new ConnectionInfo();
PropertyBag propBag = new PropertyBag();
PropertyBag prop_logonProperties = new PropertyBag();
propBag.put(PropertyBagHelper.CONNINFO_CRQE_DATABASETYPE, "SAP BW MDX Query"); 
propBag.put(PropertyBagHelper.CONNINFO_DATABASE_DLL, "crdb_bwmdx.dll");
prop_logonProperties.put("GROUP", "PROD");
prop_logonProperties.put("Server", "sapprod");
prop_logonProperties.put("MSHOST", "sapprod");
prop_logonProperties.put("Database", "Client=100");
prop_logonProperties.put("LANG", "DE");
prop_logonProperties.put("MSHOST", "sapprod");
prop_logonProperties.put("GROUP", "PROD"); 
prop_logonProperties.put("R3NAME", "PROD");
prop_logonProperties.put("R3NAME", "PROD");
prop_logonProperties.put("SHOWEMPTYDATA", false);
prop_logonProperties.put("LANG", "DE"); 
prop_logonProperties.put("sap_selected_system", "1330  PROD Production");
prop_logonProperties.put("CLIENT", "100"); 

propBag.put(PropertyBagHelper.CONNINFO_CRQE_LOGONPROPERTIES, prop_logonProperties);
propBag.put(PropertyBagHelper.CONNINFO_CRQE_DATABASENAME, "CLIENT=100 LANG=DE MSHOST=sapprod GROUP=PROD R3NAME=PROD"); 
propBag.put(PropertyBagHelper.CONNINFO_CRQE_SQLDB, false);
propBag.put(PropertyBagHelper.CONNINFO_SSO_ENABLED, false); 
propBag.put(PropertyBagHelper.CONNINFO_CRQE_SERVERDESCRIPTION, "sapprod"); 


newConnectionInfo.setAttributes(propBag);
newConnectionInfo.setKind(ConnectionInfoKind.CRQE); 
int replaceParams = DBOptions._ignoreCurrentTableQualifiers + DBOptions._doNotVerifyDB;
Fields fields = null;
rcd.getDatabaseController().replaceConnection(oldConnectionInfo, newConnectionInfo, fields,  replaceParams); 
rcd.saveAs(io.getTitle(), io.getParentID(), ReportSaveAsOptions._overwriteExisting);

I also tried getting the connection info from a report which is connecting to prod and then replacing the connection info from another report with this - this will work fine, but in this case I would always need a "reference report" and can't add the properties from scratch.

Does anyone give me a hint on how to to this? Thanks in advance!

Regards,

Marius

10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

2 Answers

Best Answer
Daniel Paulsen
Jul 11, 2017 at 11:24 PM
0

Hi Marius,

It has been a long time since I've tried this (XIR2/XIr3) and I was never able to create a BW connection and add it to a report successfully, but since you say you can copy/clone the connectioninfo from a prod reference report and have it work, there must be something missing in the property bags you are adding. Have you tried looping through and printing out the property bags for the reference report to see what could be missing? What information is missing when you view the connectioninfo in the CMC compared to a working report?

one thing to try is a RemoveAll() from the oldConnectionInfo attributes. There may be some conflict there that is not getting updated.
Also, if you change the datasource in the CR Designer, is there anything in this workflow that might be missing when changing the datasource in code? I usually end up reviewing the Designer workflow to see that my code is doing the same. Also try not using the "donotverifyDB" option to see if that makes a difference for BW. (just thinking that there could be some internal ID generated when connecting to BW and you might be getting this ID when cloning and existing prod connection)

Dan

Share
10 |10000 characters needed characters left characters exceeded
Marius Margraf Jul 12, 2017 at 09:28 AM
0

Hi Dan,

thanks for you answer! It was very helpful and pointed me directly to the issue(s). I found two mistakes I did:

1) there was an error in the property bas I have been adding. I wrote a new piece of code to print out the property bags of the reference report and figured out that there were slightly different than what I added (in detail, the property "database" in the "sub-property-bag QE_LogonProperties" is a long string and not seperate values...). Here's the piece of code to print out the properties and the result for the reference report.

private static void printConnectionProperties(PropertyBag p) { 
	for(Object obj : p.keySet()) { 
		if(p.get(obj) instanceof PropertyBag) 
			{ System.out.println("Start of Sub-Property-Bag: " + obj); 
			printConnectionProperties((PropertyBag) p.get(obj)); 
			System.out.println("End of Sub-Property-Bag: " + obj); } else 
			{ System.out.println(obj + ": " + p.get(obj)); } 
					} 
}

Here's the result:

-------- Connection CRQE --------
QE_DatabaseName: CLIENT=100 LANG=DE MSHOST=sapprod GROUP=PROD R3NAME=PROD
QE_SQLDB: false
SSO Enabled: false
QE_DatabaseType: SAP BW MDX Query
Database DLL: crdb_bwmdx.dll 
Start of Sub-Property-Bag: QE_LogonProperties 
GROUP: PROD 
Server: sapprod 
MSHOST: sapprod 
Database: CLIENT=100 LANG=DE MSHOST=sapprod GROUP=PROD R3NAME=PROD 
R3NAME: PROD 
SHOWEMPTYDATA: false 
LANG: DE 
sap_selected_system: 1330  Prod Produktion 
CLIENT: 100 
End of Sub-Property-Bag: QE_LogonProperties
QE_ServerDescription: sapprod
-------- Connection CRQE --------
QE_DatabaseName: CLIENT=100 LANG=DE MSHOST=sapprod GROUP=PROD R3NAME=PROD
QE_SQLDB: false
SSO Enabled: false
QE_DatabaseType: SAP-Tabelle, -Cluster, oder -Funktion
Database DLL: crdb_opensql.dll 
Start of Sub-Property-Bag: QE_LogonProperties 
GROUP: PROD 
Server: sapprod 
MSHOST: sapprod 
Database: CLIENT=100 LANG=DE MSHOST=sapprod GROUP=PROD R3NAME=PROD 
R3NAME: PROD 
LANG: DE 
sap_selected_system: 1330  PROD Produktion 
CLIENT: 100 
End of Sub-Property-Bag: QE_LogonProperties
QE_ServerDescription: sapprod 

As you can see, the report has two different connections to the same BW system. This is because we are using not only BW queries but also BW function modules.

2) In the CMC there was the property "table prefix" missing after I edited the report with my programm. I took a second look on the replacement parameters and got rid of the option "_igonoreCurrentTableQualifiers". After this, the table prefix is correct.

Result: This is the code to build a new propertyBag which works for me now. "SystemName" and "ConnectionType" are enums which I defined myself to determine which kind of connection I am editing and which is the target system I want to connect to. There is an outer loop which loops through the connections auf the report and then executes the following code for each connection:

IConnectionInfo newConnectionInfo = new ConnectionInfo(); 
PropertyBag propBag = new PropertyBag();  
PropertyBag prop_logonProperties = new PropertyBag();
  if(s == SystemName.PROD) { 
	if(ct == ConnectionType.BW_QUERY) { 
		propBag.put(PropertyBagHelper.CONNINFO_CRQE_DATABASETYPE, "SAP BW MDX Query");  
		propBag.put(PropertyBagHelper.CONNINFO_DATABASE_DLL, "crdb_bwmdx.dll"); 
		prop_logonProperties.put("SHOWEMPTYDATA", false);  
	} else if(ct == ConnectionType.BW_FUNCTION) { 
		propBag.put(PropertyBagHelper.CONNINFO_CRQE_DATABASETYPE, "SAP-Tabelle, -Cluster, oder -Funktion");  
		propBag.put(PropertyBagHelper.CONNINFO_DATABASE_DLL, "crdb_opensql.dll"); 
	} 
	prop_logonProperties.put("GROUP", "PROD"); 
	prop_logonProperties.put("Server", "sapprod"); 
	prop_logonProperties.put("MSHOST", "saprpod"); 
	prop_logonProperties.put("Database", "CLIENT=100 LANG=DE MSHOST=sapprod GROUP=PROD R3NAME=PROD"); 
	prop_logonProperties.put("R3NAME", "RPOD"); 
	prop_logonProperties.put("LANG", "DE");  
	prop_logonProperties.put("sap_selected_system", "1330  PROD Production"); 
	prop_logonProperties.put("CLIENT", "100"); 
	propBag.put(PropertyBagHelper.CONNINFO_CRQE_LOGONPROPERTIES, prop_logonProperties); 
	propBag.put(PropertyBagHelper.CONNINFO_CRQE_DATABASENAME, "CLIENT=100 LANG=DE MSHOST=sapprod GROUP=PROD R3NAME=PROD");  
	propBag.put(PropertyBagHelper.CONNINFO_CRQE_SQLDB, false); 
	propBag.put(PropertyBagHelper.CONNINFO_SSO_ENABLED, false);  
	propBag.put(PropertyBagHelper.CONNINFO_CRQE_SERVERDESCRIPTION, "sapprod"); 
}  

newConnectionInfo.setAttributes(propBag); 
newConnectionInfo.setKind(ConnectionInfoKind.CRQE);
// set the parameters to replace. 
// The 4 options are: 
// _doNotVerifyDB  
// _ignoreCurrentTableQualifiers  
// _mapFieldByRowsetPosition  
// _useDefault   
int replaceParams = DBOptions._doNotVerifyDB;  
// If this connection needed parameters, we would use this field 
Fields fields = null;  
rcd.getDatabaseController().replaceConnection(oldConnectionInfo, newConnectionInfo, fields,  replaceParams);

Another interesting and maybe helpful finding is that I noticed that I didn't have to loop throught each subreport and change it's connections. It's enought to retrieve the main reports connection info, the connections of the subreports will be changed to. (In the previous version, I had another outer loop which loops through all subreports).

Regards,

Marius

Edit: In addition, I need to change the parameter of the crystal report, too. The reports are using dynamic cascading promts which are accessible via the business view manager. Do you know how to switch them in the report? (The dynamic cascading promts are connecting to the BW, too. The prompts for each BW system are already existing, I just need to switch which one is used in the report)

Share
10 |10000 characters needed characters left characters exceeded