With Crystal Reports 2011 FP3 it is possible to connect to HANA information models and make use of variables.
We will create a new connection to a HANA system then write an SQL command which will retrieve data from the information model with the parameters we pass to it.
Creating the connection to HANA
Create a new report and in the database expert create a new connection to your HANA system. In our example we connect via the HANA ODBC driver.
When connected to HANA just add a new Command
Defining the correct SQL command
In the command window we have to enter the correct SQL to query the HANA model by using the variables and parameters defined in it.
As we said above, the HANA engine expects an SQL statement in this format:
SELECT […]
FROM <viewname>
('PLACEHOLDER' = ('$$<parameter name>$$',<parameter
value>)
WHERE Education=’<an education level>’
We will make use of the Crystal Reports parameters functionality to fill in the correct values. We will use the following statement in the command SQL:
SELECT *
FROM "_SYS_BIC"."<MyPackage>/SALES" ('PLACEHOLDER'=('$$VARCURRENCY$$','XXX')) WHERE Education='YYY'
Then we define two parameters: MyCurrency, of type string and with default value USD
MyEducation, of type string and with default value “Graduate Degree”. Those will substitute the XXX and YYY strings in the above command.
The final SQL will look as follows:
Build the report
If the SQL generated was correct you are now in the report design page and you can see the list of available fields and the two parameters we have added.
Modifying the parameters
Using the Parameters tab you can now change the values for the currency parameter and the Education attribute filter.
Choose a new value then apply the changes and the report will show the newly retrieved data.
For the time being you have to manually enter the parameter values, this is a complex task. We will add now the list of values which can help filling in the information.
Adding the list of values for the parameters
We will add the list of values as new SQL commands to the report.
To do so, go back to the database expert and add a new command which we will use for the Curreny list of values.
In the command SQL window select all available currency. We will select only the distinct values with the syntax
Click OK and add another command for the education level from the CUSTOMERS attribute view with a syntax like:
SELECT distinct education
FROM "_SYS_BIC"."<myPackage>/CUSTOMERS"
Now, back in the report, edit the two parameters and associate to the MyCurrency parameter the Command value field FCURR and to the MyEducation parameter the command value field Education.
You can define static or dynamic parameters.
Static parameters will open quickly but the LOV will not be refreshed at each utilisation.
Dynamic parameters will contain the latest list of values as found in the database but will require a refresh each time they are opened, with an impact on performance.
10. Variables and input parameters in Dashboards
Using Dashboards SP4 and later versions it is possible to query the universe based on the HANA view and fill in the prompt information. Previous versions of Dashboards could connect to universes based on HANA views but could not answer prompts.
After creating a new empty dashboard, it is necessary to select the Query Browser pane and add a new query
After connecting to the BI platform server, a list of universes will be shown and you can select the universe based on the HANA Analytical View
11. Variables and parameters in Analysis for Office edition
Analysis Office can directly connect to the HANA view and detect the existing variables and input parameters.
Analysis Office can use a local connection or retrieve a HANA connection on the BI platform server. When retrieving from the server, you will have to select the needed connection from the list of HANA published connections (OLAP or relational)
The system now will request filling in the prompt values
The data can now be retrieved.
After the data has been retrieved it is stil possible to change the values of the variables and parameters by selecting the Prompts button on the ribbon. A new query will be run on the system.
12. Variables and parameters in Analysis for OLAP edition
Analysis for OLAP can connect to HANA using OLAP connection shared in the BI platform via the CMC interface.
After connecting to the server and launching the Analysis OLAP application it is possible to connect to a HANA server selecting the appropriate connection.
After choosing the connection it is necessary to select the appropriate view, if this has not been already hard coded in the connection definition
To modify the values of the variables and input parameters afterwards you can just click on the ‘Prompts’ button.
13. Variables and parameters in Design Studio
Design Studio connects directly to a HANA view via the ODBC middleware.
After setting up the HANA ODBC data source you can launch Design Studio and find the HANA database in the Design Studio Tools/Preferences/Backend Connections page
If the connection doesn’t appear it might be necessary to push the Reload All Connections button.
When the source appears, you should go back to the main page and add a data souce connecting to your HANA database and selecting the appropriate view
We will now build a very simple dashboard showing how to set the value for currency. There are probably other ways to achieve the same result (and a better one), the goal here is just to present a sample workflow. Right clicking on the new data source (DS_1) we can ‘edit the intial view’ and at the same time build a simple crosstab containing the information
In the initial view page you can select some dimensions in the rows and some measures in the columns of the crosstab. When finished click the “OK + Create Crosstab” button
A crosstab will appear in the dashboard blank page as follows
Notice that the crosstab is empty and it shows an error message saying that it could not find any data to display. This is correct: the analytical view has the currency compulsory parameter to receive before it can send any data.
We will now add a component used to select the currency and to send the information to the dashboard. We can drag a combo box component on the canvas, then, after clicking the “Items” button we can add to it a few sample currencies. Make sure that the combo box “Value” column contains the correct currency codes.
After clicking OK we have validated the combo box.
Now we can tell the system to pass the value selected in the combo box to the currency variable in a script added to the OnSelect combo box event
14. Related Content
Semantic Layer forum on SDN: http://forums.sdn.sap.com/forum.jspa?forumID=308
Universe best practices on SAP HANA: http://scn.sap.com/docs/DOC-20569
Connecting BI4.0 client tools to SAP HANA: http://scn.sap.com/docs/DOC-32902
Information design tool tutorials: http://scn.sap.com/docs/DOC-8461
BI on HANA basic tutorial: http://www.sdn.sap.com/irj/scn/bi-suite-tutorials?rid=/library/uuid/703298ef-02f9- 2e10-2691-cfef154fd920
Copyright
© Copyright 2012 SAP AG. 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 compyounts of other software vendors. Microsoft, Windows, Excel, Outlook, and PowerPoint 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, System z9, z10, z9, iSeries, pSeries, xSeries, zSeries, eServer, z/VM, z/OS, i5/OS, S/390, OS/390, OS/400, AS/400, S/390 Parallel Enterprise Server, PowerVM, Power Architecture, POWER6+, POWER6, POWER5+, POWER5, POWER, OpenPower, PowerPC, BatchPipes, BladeCenter, System Storage, GPFS, HACMP, RETAIN, DB2 Connect, RACF, Redbooks, OS/2, Parallel Sysplex, MVS/ESA, AIX, Intelligent Miner, WebSphere, Netfinity, Tivoli and Informix are trademarks or registered trademarks of IBM Corporation.
Linux is the registered trademark of Linus Torvalds in the U.S. and other countries.
Adobe, the Adobe logo, Acrobat, PostScript, and Reader are either trademarks or registered trademarks of Adobe Systems Incorporated in the United States and/or other countries.
Oracle is a registered trademark of Oracle Corporation.
UNIX, X/Open, OSF/1, and Motif are registered trademarks of the Open Group.
Citrix, ICA, Program Neighborhood, MetaFrame, WinFrame, VideoFrame, and MultiWin are trademarks or registered trademarks of Citrix Systems, Inc.
HTML, XML, XHTML and W3C are trademarks or registered trademarks of W3C®, World Wide Web Consortium, Massachusetts Institute of Technology.
Java is a registered trademark of Oracle Corporation.
JavaScript is a registered trademark of Oracle Corporation, used under license for technology invented and implemented by Netscape. SAP, R/3, SAP NetWeaver, Duet, PartnerEdge, ByDesign, SAP Business ByDesign, and other SAP products and services mentiyoud 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 mentiyoud herein as well as their respective logos are trademarks or registered
trademarks of Business Objects S.A. in the United States and in other countries. Business Objects is an SAP company.
All other product and service names mentiyoud are the trademarks of their respective companies. Data contained in this document serves informational purposes only. National product specifications may vary.
These materials are subject to change without notice. These materials are provided by SAP AG and its affiliated companies ("SAP Group") for informational purposes only, without representation or warranty of any kind, and SAP Group shall not be liable for errors or omissions with respect to the materials. The only warranties for SAP Group products and services are those that are set forth in the express warranty statements accompanying such products and services, if any. Nothing herein should be construed as constituting an additional warranty.