Blog Post by Murat Yaşar


Posted on Thursday, April 11, 2019 1:10:12 PM and it has been read 3602 times since then.


Creating a Count Widget that uses Hana DB as a data source and displaying it in SAP Business One Cockpit

Hello,

 

I am here to talk about another SAP Business One related subject, Count Widget.

 

Recently, I was asked for creating a widget that will be shown in SAP Business One Cockpit. The widget's purpose was displaying number of activities which are set for current week that we are in. Users of the system also wanted to be able to see the count for themselves and when they click on the number on the widget, they wanted to see the details screen.

 

I created the below script in order to use it as a data source of my widget.

 

SELECT 
T."ClgCode" AS "Activity Number", T."Name" AS "Activity Name", T."CardName" AS "Customer", 
T."Recontact" AS "Start Date", U."USER_CODE" AS "User Code"
FROM
(	SELECT T0."ClgCode", T0."Recontact", T3."Name", T2."CardName",
	CASE WHEN E1."userId" IS NULL THEN T0."UserSign" ELSE E1."userId" END AS "userId"
	FROM OCLG T0	
	INNER JOIN OCRD T2 ON T0."CardCode" = T2."CardCode"
	INNER JOIN OCLT T3 ON T0."CntctType" = T3."Code"	
	LEFT JOIN (SELECT "empID", "userId", "salesPrson" FROM OHEM WHERE "userId" IS NOT NULL AND "salesPrson" IS NOT NULL ) E1 
	ON T0."SlpCode" = E1."salesPrson" 
	WHERE TO_INTEGER(WEEK(T0."Recontact")) = TO_INTEGER(WEEK(CURRENT_DATE))
	ORDER BY T0."Recontact" ASC, T0."ClgCode" ASC
) T
INNER JOIN OUSR U ON T."userId" = U."USERID" AND U."USERID" = $[USER]
ORDER BY T."Recontact" ASC, T."ClgCode" ASC

 

If you try to execute above script on Hana Studio, it will give you an error similar to below screen.

 

/BlogEntryImages/150/Capture01.PNG

 

This is because the $[USER] parameter is recognized only by SAP Business One to provide user information that is currently logged on. The script will not work in hana studio because hana studio does not recognize this parameter.

 

Another important part is T."ClgCode" in script. If you want to open an activity window for each row which shown in details screen, you need to set table's primary key field as the first column in result set. By doing this, we let details screen to put yellow link button at the beginning of each row. This yellow link button will open related activity window in Sap Business One if it is clicked.

 

I need to save this script into SAP Business One in order to use it later for my count widget. To do this, I used query generator in Sap Business One. I saved the query for later use then I opened Count Widget Setup screen by going to Tools – Cockpit – Count Widget – setup.

 

After setting this window with necessary information including selecting previously saved query as data source, the steps I have to take is completed.

 

Now, we just need to add this widget from widget gallery to cockpit screen in Sap Business One.

 

If you have data belongs to currently logged in user for current week, the widget will show you the total count.

 

/BlogEntryImages/150/Capture02.PNG

 

If you click on the number on the widget then detail window will pop up and will show you which activities you have to take care for current week. You can use the yellow link button at the beginning of each row and click on that link in order to see specific activity details in you SAP Business One.

 

/BlogEntryImages/150/Capture03.PNG

 

That is all.

 

Have a great day.

 


(In order to use this feature, you have to register.)

Tag Related Blog Entries

SQL Server Database table and ready script with color name information in English and Turkish

Wednesday, June 26, 2019 0   4680   1

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

Saturday, January 19, 2019 1   4020  

Countries Android Mobile Application

Saturday, April 23, 2016 0   3998  

Compare Tables Row By Row in PostgreSQL

Sunday, June 21, 2015 2   14538  

Some Helpful Links For Software Developers

Saturday, April 28, 2012 0   8395  

Populate Nested TreeView In Asp.Net Using Common Table Expression In SQL Server

Sunday, May 01, 2011 0   8817   5

LINQ

Friday, October 08, 2010 0   3550  

PostgreSQL 8.4.4 Data Types

Monday, September 27, 2010 0   2901  

Tuning, Optimizing, Increasing and Improving Performance of Asp.Net Application - Part III

Saturday, January 23, 2010 0   4516