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.
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.
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 |
Murat Yasar | Reply
Friday, April 12, 2019 7:22:34 AMhttps://medium.com/@yasarmurat.com/creating-a-count-widget-that-uses-hana-db-as-a-data-source-and-displaying-it-in-sap-business-one-5ec482afee42