This document aims to guide you through how to migrate Crystal Reports of SAP Business One from SQL Server to SAP HANA, which is part of Cookbook of migrating SAP Business One from SQL Server to SAP HANA as step 9.
In general, the layout design of custom Crystal Reports and Crystal Dashboards can be reused. However, you need to replace the data source of SQL Server with SAP HANA. We recommend you implement the analytics with SAP HANA models rather than simply migrate the data source of Crystal Reports and Crystal Dashboard from SQL Server to SAP HANA, which is easy understanding with business context for business user and reuable for multiple analytics tools, gaining the best performance from SAP HANA etc. Refer to the step 8 for modeling part of the cookbook above in details.
How can Crystal Reports in SAP Business One connect to SAP HANA?
The Crystal Reports in SAP Business One can connect SAP HANA data source (Schema and Models(preferable)) with ODBC connection.
ODBC Connection Strings of SAP HANA for Crystal Report in SAP Business One
Option 1 - B1CRHPROXY: merely available since SAP Business One 9.0, version for SAP HANA, as part of the B1 client application installation. You are recommended to use B1CRHPROXY driver rather than HANA HDBODBC32 driver below.
DRIVER={B1CRHPROXY};UID=<db_user>;PWD=<db_pwd>;SERVERNODE=<hana_host/ip>:<hana_port>;[DATABASE=<company_schema>;]
Option 2 - HDBODBC32: SAP HANA genric ODBC driver
DRIVER={HDBODBC32};UID=<db_user>;PWD=<db_pwd>;SERVERNODE=<hana_host/ip>:<hana_port>;
<db_user> -- SAP HANA database user;
<db_pwd> -- SAP HANA database user’s password;
<hana_host/ip> -- SAP HANA server hostname or IP address;
<hana_port> -- SAP HANA instance port number like 3xx15.
e.g., port number for instance 01 is 30115;
Samples of connection string:
DRIVER={B1CRHPROXY};UID=SYSTEM;PWD=manager;SERVERNODE=hanab1:30015;
DRIVER={HDBODBC32};UID=SYSTEM;PWD=manager;SERVERNODE=hanab1:30015;
It is best to set up crystal reports to be dynamically deployed to the target SAP Business One system with different company schema names. To do this, use Add Command function of Crystal Report to retrieve the data from SAP Business One companies as data source instead of accessing to direct tables or procedure of a fixed company schema.
Two default parameters of Crystal Report have been predefined in SAP Business One for this purpose as below:
Schema@ – When retrieving data from tables in SAP Business One company schema with the Add Command query, the schema name of current company loging on in SAP Business One is passed to Crystal Reports during run-time when the report is viewed from the SAP Business One Client.
E.g. A crystal report is designed to show the invoice total amount by customer with an Add Command query accessing table “OINV” directly as below.
SELECT “CardName” AS “Customer Name”, SUM(“DocTotal”) AS “Invoice Amount”
FROM “{?Schema@}”.”OINV”
GROUP BY “CardName”
When the report is viewed from the SAP Business One application with log on of company “SBODEMOUS”, the query of Add Command in Crystal Report will be translated into:
SELECT “CardName” AS “Customer Name”, SUM(“DocTotal”) AS “Invoice Amount”
FROM “SBODEMOUS”.”OINV”
GROUP BY “CardName”
Package@ – When retrieving data from SAP HANA contents (Models, Procedures, etc.) of a SAP Business One company package with Add Command query, whic is a more preferable approach, the lower current company schema name of SAP Business One is passed to Crystal Reports during run-time when the report is viewed from SAP Business One Client.
E.g. A crystal report is designed to show the invoice total amount by customer with an Add Command query retrieving data from a custom SAP HANA analytic view “XXX_AN_AR_INV”.
SELECT "BPName" AS "Customer Name", sum("InvoiceAmount")
FROM "_SYS_BIC"."sap.{?Package@}/XXX_AN_AR_INV"
GROUP BY "BPName"
When the crystal report is viewed from an SAP Business One application logon with company “SBODEMOUS”, the query of Add Command in Crystal Report will be translated into SELECT "BPName" AS "Customer Name", sum("InvoiceAmount")
FROM "_SYS_BIC"."sap.sbodemous/XXX_AN_AR_INV"
GROUP BY "BPName"
Note:
- More details about SAP HANA SQL and SQL Script available here.
http://help.sap.com/hana_platform#section7
http://help.sap.com/hana/SAP_HANA_SQL_and_System_Views_Reference_en.pdf
http://help.sap.com/hana/SAP_HANA_SQL_Script_Reference_en.pdf - The sample custom model XXX_AN_AR_INV above must be already deployed to the company package for schema “SBODEMOUS” with SAP HANA Model Management in SAP Business One.
Best Practices for creating Crystal Reports for SAP HANA
- Design reusable and good SAP HANA Models first according to the question domain first, and then use the models with the CR report instead of directly accessing tables inside a schema.
- Create a procedure to call a HANA model with a CE function or SQL Script as a simple and static interface for accessing HANA Models. Only return the required data instead of all columns.
- Call the procedure above in CR with the Add Command Query as the data source.
- Maintain all calculations and formulas in the SAP HANA Model with calculated attributes and measures rather than formulas in Crystal Report. As a result, all calculation logic remains inside HANA, and all the layout design at the Crystal Reports level connected with a simple and static procedure wrapper.
- SAP Managed Tags:
