In this blog I am going to share my experience with a very interesting Dashboard application (yes dashboards used as application in modern BI world) that I developed very recently. Users scan & pick up items from the warehouse and need to update the item records as Scanned=”Y” in the database. The database table in concern has a “Scanned” column which is NULL by default. The same field and another field called “Scan Date & Time” needs to be updated with value 1 and current system date respectively.
I used SAP Dashboards 4.1 SP3 for the interface and InfoBurst (http://bit.ly/XdjweD ) for updating back to database. InfoBurst allows inserting a new record and, updating and deleting an existing record. This feature is available as a custom connector that is part of the InfoBurst suite.
The interface was rather simple compared to many typical dashboards filled with graphs. I used a scorecard component to display the warehouse, item and quantity details along with a barcode display for the Item ID. In the last column of the table I added combo boxes with two values Yes and No and they default to No when loaded. End users scan the items and change the value to Yes. After selecting the items from the page that they need to scan, they click a Save button. Once the save button is clicked the records with Yes selected gets updated with Scanned=1 in the database.
The InfoBurst allows defining named SQL script for the INSERT/UPDATE/DELETE statement according to the database type (For example, Oracle, MS SQL Server, Teradata etc.) inside InfoBurst server within an object called “DB Connection”. Then I supplied the server information, the name of the SQL script within the Dashboard custom connector. Then bind the destination of the data to excel and the parameter to be passed to the SQL to excel. The parameters are typically the values to be updated with.
For example, if the SQL statement is : UPDATE Table 1 SET Scanned = “Y” WHERE ITEM_ID = ‘@ItemID”
Then there will be a cell in excel with hardcoded ItemID and adjacent to it will be the value to be passed.
The two cells will be bound to the parameter property in the custom connector.
If there are more than one ItemID to be passed then, it can be setup as A1=ItemID, A2=1000, A3=2000, A4=3000 and so on where 1000, 2000 and 3000 are ItemID of the records to be updated with Scanned column = Y in the database table. The parameter property in this case will be bound to A1 through A4 or more.
I used a hidden combo box to filter all the records with Combo box set to Yes and then used those filtered item ids as parameter to the writeback SQL.
The usage tab can be configured to automatically trigger the update. But I used the Connection Refresh Button to trigger it. The connector also has return message from the UPDATE which returns whether the rows were successfully updated and how many records were updated. I used the message and the values to trigger loading of the next set of data.
With this sleek feature of writeback (http://bit.ly/1s5jrIw) , I developed this cool dashboard which is being displayed in a large screen for the user.
Please share your feedback and share the article with others so they can benefit.
Thank you. Happy dashboarding!