Blog Post by Murat Yaşar

1. Blog
2. Creating a Count Widget that uses Hana DB as a data source and displaying it in SAP Business One Cockpit
3. Technical

---

Posted on Thursday, April 11, 2019 1:10:12 PM and it has been read 3163 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.

 

 

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.

 

 

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.

 

 

That is all.

 

Have a great day.

 

---

---