SAP Business One, version for SAP HANA: Crystal Reports with an

SAP Business One, version for SAP HANA: Crystal Reports with an image ly n o e id sl e tl ti Use this

Objectives At the end of this unit, you will be able to: Describe how to use Crystal Reports 2015 SAP SE or an SAP affiliate company. All rights reserved. Public 2

Agenda Prerequisites Connection drivers Best practices 2015 SAP SE or an SAP affiliate company. All rights reserved. Public 3

Prerequisites

Prerequisites for Crystal Reports development SAP Business One Client is installed on the machine.* SAP HANA Client is installed on the machine. Crystal Reports Designer version for SAP Business One * This is valid if only user would like to userB1CRHPROXY32 as connection driver. 2015 SAP SE or an SAP affiliate company. All rights reserved. Public 5

Connection Drivers

Connection Drivers for Crystal Reports There are 2 connection drivers for Crystal Reports: B1CRHPROXY32 HDBODBC32 2015 SAP SE or an SAP affiliate company. All rights reserved. Public 7

Connection Drivers for Crystal Reports Step 1 Step 2 2015 SAP SE or an SAP affiliate company. All rights reserved. Public 8

B1CRHPROXY32 Sample ODBC connection string: DRIVER {B1CRHPROXY32};UID db user ;PWD db pwd ;SERVERNODE hana ho st/ip : hana port ;[DATABASE company schema ;] 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; company schema -- Company schema name on HANA instance* *DATABASE company schema -- This is optional parameter. When it is set, only table and view in specified company schema is available; otherwise, all schemas in HANA instance are accessible. 2015 SAP SE or an SAP affiliate company. All rights reserved. Public 9

HDBODBC32 Sample ODBC connection string: 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; 2015 SAP SE or an SAP affiliate company. All rights reserved. Public 10

Best practices

Create Data Source – Case I B1CRHPROXY32 With DATABASE company schema Same experience as in SQL Server. For accessing data in different company schema with Command, user can add parameter Schema@ as prefix before table name, like SELECT “CardCode” FROM “{? Schema@}”.”OCRD”, add parameter Package@ as prefix before HANA model name, like SELECT “CardCode” FROM SYS BIC.”sap.{?Package@}”/GET BP LIST(). Without DATABASE company schema User can only create a Command with single query as data source. And it is also required to apply parameters of Schema@ and Package@ for placeholder of schema name and package name. 2015 SAP SE or an SAP affiliate company. All rights reserved. Public 12

Create Data Source – Case I – example B1CRHPROXY32 - With DATABASE company schema 2015 SAP SE or an SAP affiliate company. All rights reserved. Public 13

Create Data Source – Case I – example B1CRHPROXY32 – Using “Add Command” 2015 SAP SE or an SAP affiliate company. All rights reserved. Public 14

Create Data Source - Case II HDBODBC32 User can only create a Command with single query as data source. It is also required to apply parameters of Schema@ and Package@ for placeholder of schema name and package name. 2015 SAP SE or an SAP affiliate company. All rights reserved. Public 15

Best Practices for creating Crystal Reports Design the SAP HANA Model first, using the models with the Crystal report instead of direct access to tables. Create a procedure to call a SAP HANA model with CE function or SQL Script. Only return required data. Leave all calculations and formulas in the model rather than by using Crystal Reports formulas. As a result: All calculation logic remains in SAP HANA All layout design in the Crystal Reports level Connected with a static and simple procedure wrapper. 2015 SAP SE or an SAP affiliate company. All rights reserved. Public 16

Tips for Querying Analytic View Query of SELECT clause over Analytic View must be applied with aggregation function like SUM(), MAX(), MIN(), AVERAGE(), and GROUP BY clause. E.g. SELECT SUM(“Value”) FROM SYS BIC.sap.{?Package@}/AN SALES CUBE GROUP BY “CardCode”. 2015 SAP SE or an SAP affiliate company. All rights reserved. Public 17

Exercise 11 – Crystal Report Step 1: Create a new connection driver to connect to your package Select the procedure for Open Inovices Run the report with a selected sales employee Save the report in SAP Busines One menu under A/R Step 2: Create a Total Sales report, based on the scripted or graphical calculation view 2015 SAP SE or an SAP affiliate company. All rights reserved. Public 18

Summary Key points: For Crystal Reports design, there are two connection drivers available: B1CHRPROXY and HDBODBC32. The former allows access to tables and view in the SAP HANA DB directly from Crystal Reports. 2015 SAP SE or an SAP affiliate company. All rights reserved. Public 19

Thank you This concludes the topic Crystal Reports 2015 SAP SE or an SAP affiliate company. All rights reserved. Public 20

2015 SAP SE or an SAP affiliate company. All rights reserved. No part of this publication may be reproduced or transmitted in any form or for any purpose without the express permission of SAP AG. The information contained herein may be changed without prior notice. Some software products marketed by SAP AG and its distributors contain proprietary software components of other software vendors. Microsoft, Windows, Excel, Outlook, PowerPoint, Silverlight, and Visual Studio are registered trademarks of Microsoft Corporation. IBM, DB2, DB2 Universal Database, System i, System i5, System p, System p5, System x, System z, System z10, z10, z/VM, z/OS, OS/390, zEnterprise, PowerVM, Power Architecture, Power Systems, POWER7, POWER6 , POWER6, POWER, PowerHA, pureScale, PowerPC, BladeCenter, System Storage, Storwize, XIV, GPFS, HACMP, RETAIN, DB2 Connect, RACF, Redbooks, OS/2, AIX, Intelligent Miner, WebSphere, Tivoli, Informix, and Smarter Planet are trademarks or registered trademarks of IBM Corporation. Linux is the registered trademark of Linus Torvalds in the United States and other countries. Adobe, the Adobe logo, Acrobat, PostScript, and Reader are trademarks or registered trademarks of Adobe Systems Incorporated in the United States and other countries. Oracle and Java are registered trademarks of Oracle and its affiliates. UNIX, X/Open, OSF/1, and Motif are registered trademarks of the Open Group. Google App Engine, Google Apps, Google Checkout, Google Data API, Google Maps, Google Mobile Ads, Google Mobile Updater, Google Mobile, Google Store, Google Sync, Google Updater, Google Voice, Google Mail, Gmail, YouTube, Dalvik and Android are trademarks or registered trademarks of Google Inc. INTERMEC is a registered trademark of Intermec Technologies Corporation. Wi-Fi is a registered trademark of Wi-Fi Alliance. Bluetooth is a registered trademark of Bluetooth SIG Inc. Motorola is a registered trademark of Motorola Trademark Holdings LLC. Computop is a registered trademark of Computop Wirtschaftsinformatik GmbH. SAP, R/3, SAP NetWeaver, Duet, PartnerEdge, ByDesign, SAP BusinessObjects Explorer, StreamWork, SAP HANA, and other SAP products and services mentioned herein as well as their respective logos are trademarks or registered trademarks of SAP AG in Germany and other countries. Business Objects and the Business Objects logo, BusinessObjects, Crystal Reports, Crystal Decisions, Web Intelligence, Xcelsius, and other Business Objects products and services mentioned herein as well as their respective logos are trademarks or registered trademarks of Business Objects Software Ltd. Business Objects is an SAP company. Citrix, ICA, Program Neighborhood, MetaFrame, WinFrame, VideoFrame, and MultiWin are trademarks or registered trademarks of Citrix Systems Inc. Sybase and Adaptive Server, iAnywhere, Sybase 365, SQL Anywhere, and other Sybase products and services mentioned herein as well as their respective logos are trademarks or registered trademarks of Sybase Inc. Sybase is an SAP company. HTML, XML, XHTML, and W3C are trademarks or registered trademarks of W3C , World Wide Web Consortium, Massachusetts Institute of Technology. Crossgate, m@gic EDDY, B2B 360 , and B2B 360 Services are registered trademarks of Crossgate AG in Germany and other countries. Crossgate is an SAP company. Apple, App Store, iBooks, iPad, iPhone, iPhoto, iPod, iTunes, Multi-Touch, Objective-C, Retina, Safari, Siri, and Xcode are trademarks or registered trademarks of Apple Inc. All other product and service names mentioned are the trademarks of their respective companies. Data contained in this document serves informational purposes only. National product specifications may vary. IOS is a registered trademark of Cisco Systems Inc. RIM, BlackBerry, BBM, BlackBerry Curve, BlackBerry Bold, BlackBerry Pearl, BlackBerry Torch, BlackBerry Storm, BlackBerry Storm2, BlackBerry PlayBook, and BlackBerry App World are trademarks or registered trademarks of Research in Motion Limited. 2015 SAP SE or an SAP affiliate company. All rights reserved. The information in this document is proprietary to SAP. No part of this document may be reproduced, copied, or transmitted in any form or for any purpose without the express prior written permission of SAP AG. Public 21