Blog Post by Murat Yaşar

1. Blog
2. Creating a Crystal Report that uses Hana as a data source and importing this report into SAP Business One
3. Technical

---

Posted on Saturday, January 19, 2019 8:09:20 PM and it has been read 3560 times since then.

---

Creating a Crystal Report that uses Hana as a data source and importing this report into SAP Business One

Hello,

In this article, I will try to explain how to create a Crystal Reports report and transfer it to SAP Business One based on a data source in the SAP Hana database. To do this, first I create a stored procedure in database as a data source to our report. In this report I will display currency rates based on provided filters. You can find more information about SAP Business One tables from the below link, that is what I did in order to get some information about tables.

http://www.saptables.co.uk/

By filtering out in this web site you can reach the information you need. Below is the screen capture for my need which is a table information in this case.

 

 

The data I use in this report comes from TCMB (Central Bank Of The Republic Of Turkey).

I created stored procedure which takes three parameters. I will send values from Crystal Report to this stored procedure and based on these values, I will return query result to the report. Following script has been used for creating the stored procedure.

 

CREATE PROCEDURE CurrencyRate_Test_MuratYasar
(IN CurrencyCode nvarchar(3), IN RateDate date, IN DisplayAllDate char(1))
LANGUAGE SQLSCRIPT 
SQL SECURITY INVOKER 
READS SQL DATA
AS

BEGIN	
	
	IF (DisplayAllDate = 'Y' OR DisplayAllDate = 'y') THEN
	
		SELECT "RateDate","Currency","Rate" FROM "ORTT"
		WHERE 
		"Currency" = CASE WHEN :CurrencyCode = '-' OR :CurrencyCode = '' 
                OR :CurrencyCode is null THEN "Currency" ELSE :CurrencyCode END;
	
	ELSE
	
                SELECT "RateDate","Currency","Rate" FROM "ORTT"
		WHERE 
		"Currency" = CASE WHEN :CurrencyCode = '-' OR :CurrencyCode = '' 
                OR :CurrencyCode is null THEN "Currency" ELSE :CurrencyCode END
		AND "RateDate" = TO_DATE(:RateDate);
	
	END IF;	

END;

 

I tested this procedure by writing below scripts and saw the result is coming and they are correct.

 

call CurrencyRate_Test_MuratYasar('-','2019-01-19','Y');
call CurrencyRate_Test_MuratYasar('-','2019-01-19','N');
call CurrencyRate_Test_MuratYasar('EUR','2019-01-19','Y');
call CurrencyRate_Test_MuratYasar('EUR','2019-01-19','N');
call CurrencyRate_Test_MuratYasar('-','2019-01-19','Y');
call CurrencyRate_Test_MuratYasar('-','2019-01-19','N');
call CurrencyRate_Test_MuratYasar('EUR','2019-01-19','Y');
call CurrencyRate_Test_MuratYasar('EUR','2019-01-19','N');

 

Then I opened Crystal Reports application on my computer. Below is the main screen I faced when I run the application. You can see slightly different interface depends on your version of Crystal Reports. Mine is 14.1.2.1121 .

 

 

I created blank report from the file menu. The below screen came up. If you haven't defined any connection yet, you can start creating a new connection in this window.

 

 

I will create new connection for my report and since my target database is hana, I will choose the red marked connection node (ODBC (RDO)) from the above screen and another window pops up for me to create a new connection.

 

 

I chose "Enter Connection String" and write a connection string similar to following line.

 

DRIVER={HDBODBC32};SERVERNODE=192.168.1.1:30015;DATABASE=TEST

 

After clicking "Next" button, the below screen comes and asks for credentials to access to database that I specified in connection string.

 

 

If the credentials that you specifed is correct then the window will be closed up once you click "Finish" button.

 

 

I selected the stored procedure that I created earlier from left pane of the screen and clicked the ">" button in order to use it in my report. After clicking ">" button the stored procedure will be listed on the right pane of the screen. It means, I will be able to use this as data source in my report.

 

 

When I press the "OK" button, these windows will be closed and we will be returned to the Crystal Report screen. Below is the screen after choosing data source.

 

 

By default, as you can see in the picture above, some report sections will appear. To get help you can press "F1" and "Crystal Reports Online Help" window shows up.

 

 

I have bought some udemy tutorials to get ideas for creating a Crsytal Report for SAP Business One. As you have always been told, you must get your hands dirty to learn new things. First report can be simple but I assure you can make wiser and visually nicer reports if you continue.

I am not going to explain every part of the report. I am not an expert, nor do I know all the details. Instead, I will give you some links that you can have a look. You can also google or bing the "sections of a Crystal Report" for getting in depth information. I took what I need to from possible sources including youtube. Youtube is also a good place for getting free video lessons.

https://kb.blackbaud.com/articles/Article/37847

https://www.universalclass.com/articles/computers/how-to-format-crystal-reports.htm

From "Special Fields", I chose "Data Date" and "Data Time" options, dragged them and dropped on "Report Header" section. After that, I pressed "F5" being able to see the current result of the report.

 

 

Below is the report preview after pressing "F5".

 

 

You can format the text as you wish.

 

 

 

After formatting date, it will become like the following.

 

 

Crystal Reports menu design and actions work in the same way like Word or Excel. If you are familiar with Microsoft Office applications, Crystal Reports will become really easy to work with. Formatting or alignment operations almost like Word or Excel. Crystal Reports gives you some extra flexibility with function options. You can specify conditions for report fields in order to create different output based on your scenarios.

You have very rich options for aligning objects in Crystal Report. As you can see in the following screen capture, I choose three object. "Currency Rate Report", "Data Date", "Data Time" respectively. Then on the "Currency Rate Report" text object, I clicked right mouse and choose "Align", "Tops" respectively from the popped up menu. I aligned these three object's top margin according to first object ("Currency Rate Report"). I click on the object I wanted to base on aligning others at the end. The last clicked object is the one for others to be the same align, size or other properties to take into account for.

 

 

The data source has three columns' that I can show in the report.
I think grouping by date field would be more suitable. I would want to see the exchange rate by date, would want to see the exchange rate by currency code or would want to see all the information I have in my data source. But me, I would like to see data grouped by date. Your scenarios would be different, for example like grouping by currency code..

To group by date I take the following steps.

I chose "Group Expert" menu item.

 

 

I chose "RateDate" and clicked ">" button in order to make grouping.

 

 

I clicked "Options" and then I set some options as you can see in the following screen capture.

 

 

After taking these steps, screen will be as following.

 

 

Now I will drag "Currency" and "Rate" fields onto "Details" section of the report.

 

 

I formatted a little bit. I used line for making rectangle around fields. I arranged format and style of line by right clicking on them. You can do pretty much everything by using menu items and right mouse click menu.

I decreased empty space in sections by using mouse.

 

 

In this step I get a preview of the report as shown in the screenshot below.

 

 

Now, it is the time for setting our report parameters up.

In order to provide drop down list as parameter for my report, I will use another table called "OCRN".

 

 

 

To be able to use this table in my report, I use "Database Expert".

 

 

I chose "OCRN" table and clicked ">" button in order to add this table into my report.

 

 

I clicked "OK" button for all the messages shown to me after selecting "OCRN" table.

 

 

Report's "Field Explorer" will be as shown as below.

 

 

Now, I will set "CURRENCYCODE" parameter.

 

 

I chose "Static" option from "List of Values" drop down list. "Value Field" will be taken from "OCRN" table's "CurrCode" field.

 

 

"Description Field" will be taken from "OCRN" table's "CurrName" field.

 

 

Chose "Append all database values" from "Actions" drop down menu.

 

 

This step asked me for database credentials. I entered the necessary information into form fields.

 

 

I wrote "Select Currency Code:" as value for "Prompt Text" field in "Value Options" section.

 

 

I also manually added a line for drop down list as shown below. After adding the line into "Value - Description" section, I moved up the line to the beginning of the list by clicking up-arrow sign.

 

 

Now, I am going to set "RATEDATE" parameter.

 

 

I chose "Static" option from "List of Values" drop down list. I wrote "Select Date:" as value for "Prompt Text" field in "Value Options" section.

 

 

Now, I will set "DISPLAYALLDATE" parameter.

 

 

The purpose of this "DISPLAYALLDATE" parameter is whether or not to display the Exchange Rate information for all dates in the data source. I arranged this in my stored procedure. Well, I would have sent null value in Crystal Report preview tab and it would work but I can't send null value for type of date parameters (actually any data type) from SAP Business One. It is obligatory send a value from Business One to Crystal Report. I googled and found out this information and also found the following link which helped me about solving this issue.

https://apps.support.sap.com/sap/support/knowledge/public/en/1206240

I manually added two lines for this parameter. If the user selects "Yes", the report comes in to cover all dates, even if the value is assigned to the "RATEDATE" parameter. If the user selects "No", the report will be filtered by "RATEDATE" parameter value.

 

 

Now it is time for completing parameter settings. In order to do that, I click "Report" from menu bar and then select "Select Expert", "Record" respectively.

 

 

I have two parameters to provide for my report. First I will set "CURRENCYCODE" parameter then then "RATEDATE" parameter.

 

 

I wrote the following script for CURRENCYCODE parameter. If you recall, I added manually a line for "CURRENCYCODE" parameter's select list. I need to write this formula, in order to use this manually added "ALL" option in my select list for this parameter.

 

if {?CURRENCYCODE}="-" then {CURRENCYRATE_TEST_MURATYASAR.Currency} like "*"
else
{CURRENCYRATE_TEST_MURATYASAR.Currency} = {?CURRENCYCODE}

You can see how I accomplished this by looking at the following screen captures.

 

 

I set "CURRENCYCODE" parameter and then clicked "New" tab in order to set "RATEDATE" parameter.

 

 

Inside newly opened tab I set the options as shown below, wrote the below script for "RATEDATE" parameter. I closed this window by clicking "OK" button.

 

If {?RateDate} = Date(1900,1,1) OR IsNull({?RateDate}) or CStr({?RateDate})="" Then
{CURRENCYRATE_TEST_MURATYASAR.RateDate} = CurrentDate
else
{CURRENCYRATE_TEST_MURATYASAR.RateDate} = {?RateDate}

 

 

Parameter settings has been done. You can see the final settings from the below screen capture. I took the following screenshot by clicking "Report" in the top menu bar and then clicking "Selection Formulas", "Record". If formula script is other than what you see, update it accordingly.

 

 

In crystal report, if you press F5 it will prompt you the following screen capture.

 

 

I suggest you to choose "Prompt for new parameter values" option. It will ask you value of each parameters and will bring you refreshed data according to your choice.

Now I am going to test if the report is working, using some scenarios. I will share screen capture for each scenario. The exchange rates may be wrong, but my purpose is to see if the report works. I use test database and test data. For example "JPY" exchange rate should not have been what you see.

Have a look at them.

 

---

Scenario 1:

 

 

Scenario 1 Result:

 

---

Scenario 2:

 

 

Scenario 2 Result:

 

---

Scenario 3:

 

 

Scenario 3 Result:

 

---

Scenario 4:

 

 

Scenario 4 Result:

 

---

 

I tested and saw that the report is working. Now, I will import this report into Business One. In order to do that, I open Business One Client. I assume you already have enough privilege for importing report to Business One.

I opened "Report and Layout Manager".

 

 

I pressed "Import" button.

 

 

I pressed "Next" button.

 

 

I clicked "Browse" button.

 

 

I chose the report file that I created which sits on the desktop.

 

 

I choose "Report" as Content Type. Clicked on "..." button to choose where to put the report file on Business One.

 

 

I specified the place for the report.

 

 

I clicked "Finish" button on this step. This takes couple of seconds to complete.

 

 

After completing importing the report, I got following screen. Pressing "Close" button finishes report importing.

 

 

After clicking on "CurrencyReport" from menu, "Selection Criteria" window will pop up.

 

 

After clicking "OK" button I get the result as shown below. I changed selection criteria and saw the result I want.

 

 

If I change the selection criteria from "Yes" to "No" then the result will be as following screen.

 

 

I tried to make this article as helpful as it can be. I have already said that I am not an expert on Crystal Report. I'd be happy if it helps someone, like me, who is at the beginning of learning Crystal Report.

That is all.

Thanks for reading. Any comments which helps others is welcome.

 

Have a great day.

 

---

---