TWBS Advanced Productivity Pack (APP) provides tools to create charts from SAP Business One data and present them as either a stand-alone Chart or as an element of a Dashboard.

• Click the SAP menu option titled Dashboard Setup under the Administration Setup APP Configuration menu groups to load the Dashboards setup screen.

• Enter the Dashboard Name for the new Dashboard. This will be the name for the dashboard that is displayed to the user.
• Select users from the User Defaults drop down list. See the How To Set Up User Defaults for an APP Dashboard section for more information about this feature.
• Select the option for the desired orientation of the dashboard…
  • Full Screen Layout - centered and fills the entire screen.
  • Horizontal Strip - displayed at the bottom of the screen with the charts placed side-by-side in a horizontal row.
  • Vertical Strip - displayed on the side of the screen with the charts placed in stacked fashion in a vertical row.
• Click the Create button to move to the dashboard layout.
• The dashboard layout form is will launch based on the orientation option previously selected.

• For each chart window, you must specify the following information…
  • Frame Type - Designates the type of information to be displayed in the chart window
    • Chart
    • Grid
  • Unique ID - Unique ID for the chart window.
  • Chart ID (for Chart option) - Click the drop down menu to select a chart.
  • User Query (for Grid option) - Click the drop down menu to select a query.
  • Frame Mode - Mode for the chart window.
    • Standalone - Chart/grid data independent from other chart windows.
    • Linked - Chart/grid data linked to another chart window.
      • Link To (for Linked option) - Select the chart window to link to.
      • Condition Filter (for Linked option) - Select the chart window condition filter.
      • Default Filter (for Linked option) - Select the chart window default filter.
      • Load Behavior (for Linked option) - Select the chart window data load behavior.
        • Show on Load - The data for the chart window will only display once on load.
        • Show on Linked - The data for the chart window will be refreshed on the change of the linked value from another chart window.
• The Dashboard can be resized by grabbing and dragging the corners or sides of the form.
• The Dashboard windows can be resized by grabbing and dragging the divider between the windows.

• Click the Save button to save the Dashboard layout; click the Cancel button to exit the Dashboard layout form; click the Preview button to see how the Dashboard will appear when in use.

• Click the SAP menu option titled Dashboard Setup under the Administration Setup APP Configuration menu groups to load the Dashboards setup screen.
• Click the Folder icon to display a list of existing Dashboards.
• Select the desired Dashboard and click the Open button. The Dashboard information will be displayed on the screen.

• Click the Update button to make changes to display the Dashboard windows layout.
• The windows layout is automatically displayed with four windows, even if the original layout had fewer. Remove any unwanted windows.
• Modify the Dashboard information as needed.

• Click the Save button.

• Click the SAP menu option titled Dashboard Setup under the Administration Setup APP Configuration menu groups to load the Dashboards setup screen.
• Click the Folder icon to display a list of existing Dashboards.
• Select the desired Dashboard and click the Open button. The Dashboard information will be displayed on the screen.

• Click the Delete icon to delete the existing Dashboard.
• Click the Yes button on the confirmation window.

• Click the SAP menu option titled Dashboard Setup under the Administration Setup APP Configuration menu groups to load the Dashboards setup screen.
• Click the Folder icon to display a list of existing Dashboards.
• Select the desired Dashboard and click the Open button. The Dashboard information will be displayed on the screen.

• Click the User Defaults drop down menu to display the available users.
• Select the users that you want to assign this Dashboard as the default that will display at time of login to SAP® Business One.
• Click the Update button to make changes to display the Dashboard windows layout.
• The windows layout is automatically displayed with four windows, even if the original layout had fewer. Remove any unwanted windows.
• Modify the Dashboard information as needed.

• Click the Save button.

One of the integration features of SAP® Business One custom queries is the ability to include a “Link To” golden arrow in the results set that allows the user to drill back to the referenced marketing document. In order to accomplish this, there are several key pieces of information that are required in the query to accomplish this link…

• Document Entry (DocEntry) value from the document table.
• Object Type (ObjType) value usually from the document table.

Assume that the current date is May 15, 2010 for the following examples.

• Current Date - Select getdate() will return the current system date & time.

• Parsing Dates - Use the SQL datepart function to determine the year, quarter, month, week, and day of a date. The value returned is an integer (number) and can be used in additional calculations.
  • Select datepart(year,getdate()) returns 2010.
  • Select datepart(quarter,getdate()) returns 2.
  • Select datepart(month,getdate()) returns 5.
  • Select datepart(week,getdate()) returns 21.
  • Select datepart(day,getdate()) returns 20.
• Filtering by Date -The SQL datepart function can also be used to compare dates in order to automatically select a reporting period. In these examples we will use the SAP® Business One Order Header (ORDR) date and the current date of May 15, 2010.

Records where the order date is in the current week

Select t0.docnum, t0.docdate from ORDR where datepart(year,t0.docdate) = datepart(year,getdate()) AND datepart(week,t0.docdate) = datepart(week,getdate())

Records where the order date is in the prior week

Select t0.docnum, t0.docdate from ORDR where datepart(year,t0.docdate) = datepart(year,getdate()) AND datepart(week,t0.docdate) = datepart(week,getdate()) - 1

Records where the order date is in the current quarter

Select t0.docnum, t0.docdate from ORDR where datepart(year,t0.docdate) = datepart(year,getdate()) AND datepart(quarter,t0.docdate) = datepart(quarter,getdate())

The user interface for SAP® Business One displays decimal numeric values based on the Display tab setup which can be accessed the SAP menu option for Display under the Administration -System Initialization -General Settings -Display menus.

However, decimal values are stored in the database to six-decimal precision. This means that a decimal value such as US Dollars that is displayed in SAP® Business One as 100.00 will be retrieved and displayed in the APP Charts as 100.000000. The number of decimals can be controlled by the SQL statement in the query. Combinations of cast and round can be used to arrive at a reasonable display value.

Display the database value 100.000000 for the column DocTotal with 2 decimals (100.00)

Select cast(t0.DocTotal as decimal(16,2))

Display the database value 55.900000 for the column DocTotal rounded to a whole number (56.000000)

Select round(t0.DocTotal,0)

Display the database value 55.900000 for the column DocTotal rounded to a whole number (56.000000) and without decimals

Select cast(round(t0.DocTotal,0) as int)

Once an APP Dashboard has been created it is available for viewing via the right-click menu from the SAP® Business One form that it has been linked to.

When only one dashboard is linked to the screen, the title of that dashboard will display in the regular right-click menu below any reports or charts…

When more than one dashboard is linked to the screen, the titles of those dashboards will display in a Dashboards sub-menu off the regular right-click menu below any reports or charts…

A specific chart from a dashboard can be accessed by double-clicking on the chart window. This opens the standard APP Chart window for additional use.