Using APP Report Maintenance and Delivery

The separate TWBS_SBO-Common database will no longer be used by the application. All APP information will be stored in SAP® Business One user-defined tables in the respective company databases. For existing customers upgrading to this new version, the application Server Configuration screen provides a method to migrate this information into the customer database.

• Click the Report Definition menu option under the Administration Setup APP Configuration menu groups to load the APP Server Configuration screen.

• Click the Migrate APP Records button to initiate the process of migrating APP information from the TWBS_SBO-Common database to the SAP® Business One company database.

• When the process is complete the user will receive a message on the SAP® B1 satus bar that the process was successful.

Every SAP® Business One or ISV add-on screen generated using the UI API has a unique ID associated with it. This value can be easily retrieved from within SAP® Business One through the following steps…

• Open the screen in SAP® Business One that you wish to identify the unique ID for.
• Click the SAP menu option titled System Information under the View drop down menu or use the Ctrl+Shift+I keyboard shortcut.

• Hover your mouse over any control on the form or the form itself. The bottom left panel in SAP® Business One will display information regarding the form.
• Note the Form= value from the panel. In the example below the Form= value is TWBS_RptMainEx.

Every field on an SAP® Business One or ISV add-on screen generated using the UI API has a unique ID associated with it. This value can be easily retrieved from within SAP® Business One through the following steps…

• Open the screen in SAP® Business One that you wish to identify the field unique ID for.
• Click the SAP menu option titled System Information under the View drop down menu or use the Ctrl+Shift+I keyboard shortcut.

• Hover your mouse over the field. The bottom left panel in SAP® Business One will display information regarding the field.
• Note the Item= value from the panel. In the example below the Item= value is txtrptName.

• Click the User Roles menu option under the Administration Setup APP Configuration menu groups to load the APP User Roles Definition screen.

• Double click the Define New line. This will bring up the APP Role Properties screen.

• Type the name you want to assign this role in the Role Name field.
• Click the Add button to bring up the list of users.

• Select one or more users (use the Ctrl+Shift keys for multiple user selection).
• Click the Ok button.
• Select the Permissions tab. You will see a list of all available reports.
• Uncheck any reports you do not want to Allow permissions.

• Click the Update button to save the information.

• Click the Report Maintenance menu option under the Administration Setup APP Configuration menu groups to load the Reports Maintenance screen.
• Click the SAP icon menu option for Add .

• Select the Report Group option button.
• Enter the criteria required (Name, Category, etc.)
• After selecting a category in the Link To box, assign reports to the group by checking their boxes in the list that appears.
• Click the Add button.

• Click the Report Maintenance menu option under the Administration Setup APP Configuration menu groups to load the Reports Maintenance screen.
• Click the SAP icon menu option for Add .

• Select the Query option button.
• Enter the criteria required (Name, Category, Database, Query, etc.)
• Click the Add button.

• Click the Report Maintenance menu option under the Administration Setup APP Configuration menu groups to load the Reports Maintenance screen.
• Click the SAP icon menu option for Add .

Select the Query option button. Enter the criteria required (Name, Category, Database, Query, etc.)

For the purpose of this example, the following query will be used

• Click the Add button.

• Click the Query Parameters button. This will launch the Query Parameters Mapping screen.

• Enter the text to be prompted to the user for the value of the parameter.

• Click the Update button on the Query Parameters Mapping screen.
• Click the Update button on the Reports Maintenance screen.
• Execute the Report Query through the APP Reports menu option

• The user will be prompted to enter the value of the Sales Order #

• Enter the parameter value.
• Click the OK button.

The results of the Query based on the entered parameter will be visible to the user.

• Click the Report Definition menu option under the Administration Setup APP Configuration menu groups to load the Reports Maintenance screen.
• Click the SAP icon menu option for Add .

• Select the Query option button.
• Enter the criteria required (Name, Category, Database, Query, etc.)

• Click the Add button.

• Click the Report Definition menu option under the Administration Setup APP Configuration menu groups to load the Report Maintenance screen.
• Click the SAP icon menu option for Add or load an existing Query.

• Select the APP Query option button.
• Enter a Name value for the Query title.
• Select a Category for the Query assignment.
• Enter a Description value for the Query.
• Select a Query to be executed.
• Click the Add or Update button.
• Click the Refresh APP Menu button to repopulate the SAP menu structure.
• Click the SAP menu option for the query that you added/updated.

• The query will be executed and the data results will be loaded into the data tab of the TWBS Chart Viewer screen.

• Avoid using * operator for multi table queries. It is okay to use * for queries for a single table. For example, SELECT * FROM ORDR (NOLOCK) is supported, but SELECT * FROM ORDR (NOLOCK) INNER JOIN RDR1 (NOLOCK) ON ORDR.DocEntry = RDR1.DocEntry is not supported. Always specify individual field names in multi table queries.
• For more complex queries with joins, table and field aliases using GROUP BY or ORDER BY functions, we recommend the following approach…
  • Create a SQL view in the B1 company database to aggregate all the required data.
  • Create a simple query in B1 that just executes a select statement off the sql view. This approach enables the user setting this up to leverage all of the robust query features provided in Microsoft SQL server and at the same time simplified the implementation and support requirements for APP.

• Avoid using aliases in saved B1 queries. Follow above suggestion to create view and then reference the view in the B1 saved query.
• The table registered in the Form Registration Window must be at least one of the tables in the query. APP will filter the query based on the primary key field of this table provided in the Form Registration Window.
• The minimum requirements for successful execution of a B1 saved query via APP are as follows:
  • Record Key DB field specified on Form registration window.
  • Business Partner DB field (if specified)
  • Contact Person DB field (if specified)

TWBS Advanced Productivity Pack (APP) incorporates a Visual Query Builder screen that allows the user to have a dynamic method to create and edit queries which can be used for reporting, query execution, or dashboards.

• Click the SAP menu option titled Query Generator under the Tools Queries drop down menus to load the Query Generator screen.

• Click the Execute button to load the Query Preview screen.

• Click the TWBS SQL icon to launch the Visual Query Builder screen.

• The query data will be empty on the Visual Query Builder screen.
• Edit the query as needed.
• Click the Save & Close button to save the query and return it to the Query Preview screen the Visual Query Builder was launched from.

• Click the SAP menu option titled Query Manager under the Tools Queries drop down menus to load the Query Manager screen.

• Select an existing query by highlighting the query name.

• Click the Ok button to load the query into the Query Preview screen.

• Click the TWBS SQL icon to launch the Visual Query Builder screen.

• The query data will be preloaded into the Visual Query Builder screen.
• Edit the query as needed.
• Click the Save & Close button to save the query and return it to the Query Preview screen the Visual Query Builder was launched from.

The tables to be joined should be loaded into the SQL Graphical Display Pane of the Visual Query Builder screen.

• Click on the field in the table for the left side of the JOIN and hold down the left mouse key (OCRD.CardCode above).
• Drag the field to the table for the right side of the JOIN and position the mouse over the field to be joined to (CRD1.CardCode above).
• Release the left mouse key.
• A black line identifying the join between the table fields will appear.
• The default JOIN will be an INNER JOIN.
• To alter the JOIN type, right clicking on the black line connecting the fields will provide the user with the following options…

• Selection of the Select all rows from OCRD menu option only will change INNER JOIN the to a LEFT JOIN.
• Selection of the Select all rows from CRD1 menu option only will change INNER JOIN the to a RIGHT JOIN.
• Selection of the Properties menu option only will load the Link Properties screen.

• The Link Properties screen can be used to change the link through either the Select All From Left check box (change INNER JOIN the to a LEFT JOIN) or the Select All From Right check box (change INNER JOIN the to a RIGHT JOIN).

• The tables for the query should be loaded into the SQL Graphical Display Pane of the Visual Query Builder screen.
• Select check box next to the field to be used in the aggregate function. This will display the field in the grid below the SQL Graphical Display Pane.

• The Aggregate column of the grid will allow the user to apply a variety of functions to the fields in the query (SUM as shown above).

• The tables for the query should be loaded into the SQL Graphical Display Pane of the Visual Query Builder screen.
• Select check box next to the field to be used in the GROUP BY function. This will display the field in the grid below the SQL Graphical Display Pane.

• The Grouping column of the grid will allow the user to select a field in the query to be used for grouping.

• The tables for the query should be loaded into the SQL Graphical Display Pane of the Visual Query Builder screen.
• Select check box next to the field to be assigned an alias. This will display the field in the grid below the SQL Graphical Display Pane.

• The Alias column of the grid will allow the user to designate an alias ID for a field in the query.

• The entire query (including individual selection statements and UNION keywords) should be loaded into the SQL Query Text Pane of the Visual Query Builder screen.
• Click the Save button to refresh the other display panes of the Visual Query Builder screen.

• The SQL Summary Pane will display the individual selection statements in different groups.
• The SQL Graphical Display Pane will display one selection statement at a time. To switch between statements click on the query box icons in the upper right corner of the pane.

• Click the SAP menu option titled Query Manager under the Tools Queries drop down menus to load the Query Manager screen.

• Select an existing query with SAP Variables by highlighting the query name.

• Click the Ok button to load the query into the Query Preview screen.

• Replace the existing SAP variables with hard coded values that will allow the SQL Query to be executed.

• Click the TWBS SQL icon to launch the Visual Query Builder screen.
• The query data will be loaded into the Visual Query Builder screen.
• Edit the query as needed.

• Click the Save & Close button to save the query and return it to the Query Preview screen the Visual Query Builder was launched from.

• Replace the hard coded values with the previous SAP variables.
• Click the Execute button.

• Set up the query in SAP through either the SAP screens or through the Visual Query Builder screen.

• Click the Report Definition menu option under the Administration Setup APP Configuration menu groups to load the Reports Maintenance screen.
• Click the SAP icon menu option for Add .

• Select the Query option button.
• Enter the query definition details…
  • Name
  • Category
  • Select the Enabled check box
  • Select the APP Query option to load the query in the TWBS screen

• Click the Add button.
• Click the Refresh APP Menu button to reload the SAP® Business One menus.
• Click the SAP menu option for LinkTo Invoice Example query (the Name value you specified in the definition) that you want to view the results for under the APP Reports Queries for Charts (the Category value you specified in the definition) menus to load the selected query into the TWBS Chart Viewer screen.

• The DocEntry and DocNum columns will each display SAP® Business One Link To arrow that will allow the user to drill back to the specific Invoice. The CardCode column will display an SAP® Business One Link To arrow that will allow the user to drill back to the Business Partner Master Data screen for the BP.
• Click the Link To arrow for the Invoice to be viewed (Invoice #5 for this example).

• The A/R Invoice screen will be displayed and the data for the Invoice document selected will be loaded.

• To change the visibility of the columns to remove the DocEntry and ObjTypes from the view of the user, reopen the Report Definitions under the Administration Setup APP Configuration menus to load the Reports Maintenance screen.
• Expand the category of the query to be modified.

• Select the query to be modified. This will load the query definition data in the area below.

• Click the Query Preferences button . This will launch the Query Preferences screen.
• Uncheck the visible column for the DocEntry and ObjType fields. This action will automatically resort the fields on the screen to move the non-visible columns to the top.

• Click the Update button on the Query Preferences screen.
• Click the OK button on the Query Preferences screen.
• Click the Update button on the Reports Maintenance screen.
• Click the SAP menu option for LinkTo Invoice Example query (the Name value you specified in the definition) that you want to view the results for under the APP Reports Queries for Charts (the Category value you specified in the definition) menus to load the selected query into the TWBS Chart Viewer screen.

• The modifications to the query preferences will be visible on the TWBS Chart Viewer screen.

• Set up the query in SAP through either the SAP screens or through the Visual Query Builder screen.

• Click the Report Definition menu option under the Administration Setup APP Configuration menu groups to load the Reports Maintenance screen.
• Click the SAP icon menu option for Add .

• Select the Query option button.
• Enter the query definition details…
  • Name
  • Category
  • Select the Enabled check box
  • Select the APP Query option to load the query in the TWBS screen

• Click the Link To arrow next to the Link To object selection. This will launch the Form Registration screen.

• Verify that the Form Object Type value is set to Standard SAP Objects.
• Verify that the Object Code is set to 202.
• Click the Update button on the Form Registration screen.
• Click the OK button on the Form Registration screen.
• Change the value of Link To back to None on the Reports Maintenance screen.
• Click the Add button on the Reports Maintenance screen.
• Click the Refresh APP Menu button to reload the SAP® Business One menus.
• Click the SAP menu option for LinkTo Production Order Example query (the Name value you specified in the definition) that you want to view the results for under the APP Reports Queries for Charts (the Category value you specified in the definition) menus to load the selected query into the TWBS Chart Viewer screen.

• The DocEntry and DocNum columns will each display SAP® Business One Link To arrow that will allow the user to drill back to the specific Production Order.
• Click the Link To arrow for the Production Order to be viewed (#6 for this example).

• The Production Order screen will be displayed and the data for the order document selected will be loaded.

• To change the visibility of the columns to remove the DocEntry and ObjTypes from the view of the user, reopen the Report Definitions under the Administration Setup APP Configuration menus to load the Reports Maintenance screen.
• Expand the category of the query to be modified.

• Select the query to be modified. This will load the query definition data in the area below.

• Click the Query Preferences button . This will launch the Query Preferences screen.
• Uncheck the visible column for the DocEntry and ObjType fields. This action will automatically resort the fields on the screen to move the non-visible columns to the top.

• Click the Update button on the Query Preferences screen.
• Click the OK button on the Query Preferences screen.
• Click the Update button on the Reports Maintenance screen.
• Click the SAP menu option for LinkTo Production Order Example query (the Name value you specified in the definition) that you want to view the results for under the APP Reports Queries for Charts (the Category value you specified in the definition) menus to load the selected query into the TWBS Chart Viewer screen.

• The modifications to the query preferences will be visible on the TWBS Chart Viewer screen.

• Launch the APP Server Configuration screen by selecting the Server Configuration menu option under the Administration Setup APP Configuration menu groups.

• Define the SMTP Server name or IP Address for the server in the SMTP Server field. If you need to specify the Port # or Enable SSL for your SMTP Server, you can use the fields provided.

• Log into SAP® Business One.
• Click the Report Definition menu option under the Administration Setup APP Configuration menu groups.
• Click on the Report or Query that you want to set up the SMTP mail for to load that definition into the screen.
• Click on the Delivery Preferences tab.

• Click the Email Provider drop down menu and select the SMTP option. Click the Update button to save your changes for the Report/Query definition.

• Click the SAP menu option titled Business Partner Master Data under the Business Partner menu to load the Business Partner Master Data screen.
• Load the existing Business Partner that you want to set up delivery preferences from the Ship To address for. Click on the Addresses tab.

• Three fields on the business partner's Ship To address need to be set with appropriate values
  • Email Address
  • Fax Number
  • Delivery Preferences
    • Email - Use Email
    • Fax - Use Fax
    • Both - Use both Email and Fax
    • Print - Print Only
    • EmailPrint - Print and Email
    • FaxPrint - Print and Fax
    • All - Print, Email and Fax
    • None - No Action

• Click the Update button on the Business Partner Master Data screen to save the data.
• Click the Report Definition menu option under the Administration Setup APP Configuration menu groups.
• Select the report from the matrix to display the report definition data.
• Click on the Delivery Preferences tab.

• Click the Add Email Recipients button to launch the Additional Email Address Setup screen.
• Click the Use Contact Persons Information of Business Partners option.
• Click the Save button on the Additional Email Address Setup screen.
• Click the Update button on the Reports Maintenance screen.

• Launch the marketing document window, which is required in order to deliver emails. Please note that the Report definition that you just updated must be the same.
• Select a valid Ship To address for the document.

• Deliver the report by clicking the Email button from the SAP® Business One tool bar.

VERIFY STILL EXISTS

• Click the SAP menu option titled Report Definitions under the Administration -Setup →TWBS APP menus to load the Reports Maintenance screen.
• Load the report definition data into the screen by selecting the appropriate report row from the matrix. The report must belong to a Sales Order marketing document.
• Click on the Printing Preferences tab.
• Select the Auto Print On Approve [Update Mode] checkbox

Click the Update button.

To use the functionality…

• Click the SAP menu option titled Sales Order under the Sales - A/R menu to load the Sales Order screen.
• Load the Sales Order that you wish to automatically print on approval update.

• Select the Approved checkbox option on the Logistics tab of the Sales Order.
• Click the Update button.

• Click the Report Definition menu option under the Administration Setup APP Configuration menu groups to load the Reports Maintenance screen.
• Click on the report to load that definition into the screen.
• Click on the Delivery Preferences tab.
• Click the Message ID drop down menu and select the Define New option to launch the Message Editor screen.

• Design your message content as needed.

• Click the Save button on the Message Editor screen.
• Click the Update button on the Reports Maintenance screen.

• Launch the marketing document window and deliver the report by clicking the deliver button from the SAP® Business One tool bar.
• APP will replace all the tags with the suitable values and compose a new email to the recipients.

• Set up the query as outlined in the How To Set Up Report Queries section of the User Guide. Setting up the query will serve two purposes…
  • Define the delivery criteria for APP - APP will send out the email/fax for each record returned by the query.
  • Message customization - All the fields from the query can be incorporated into the message for customization and personalization.
• Once the query is set up, the next step is to create a SQL view in the SAP® Business One company database.

• Once the SQL View is created (if needed), create a saved query in SAP® Business One to select data from the view.

• Set up the Report definition per normal procedures.

• Associate the SAP® Business One query to APP through the Message Editor screen.
• Define the email recipients through the Additional Email Address Setup screen.

Users now have additional flexibility in using their own forms. To take advantage of this flexibility the user should fill in every entry while registering a form. APP can handle these forms the same as it handles a standard SAP marketing document screen.

Clicking on the Define New option at the bottom of the Link To drop down list on the Report Maintenance screen will launch the Form Registration screen. APP allows you to attach custom crystal reports to any SAP® Business One or ISV add-on screen (i.e. you can launch a report from any SAP® Business One window by clicking the Print or Preview buttons). The first step in this process is to register the window in APP using the Form Registration screen.

The Form Registration screen is also accessible through the Form Registration menu option under the Administration Setup APP Configuration menus.

• Complete the necessary fields…

• Table Name - Enter the database table name that the primary table is using to correspond to the form (i.e. ORDR for Sales Order).
• Record Key - Item UID - Enter the Unique ID of the item which contains the record key value in the form.
• Record Key - DB Field - Enter the field name of the item that keeps the record key value in the above specified table.

• Business Partner - Item UID - Enter the Unique ID of the item which contains the business partner in the form. Keep this field blank if not needed.
• Business Partner - DB Field - Enter the field name of the item which keeps the business partner in the above specified table. Keep this field blank if not needed.

• Contact Person DB Field - Enter the field name of the item which keeps the contact person code in the above specified table. Keep this field blank if not needed.

• Use Emails from the DB Field - Enter the field name of the item that keeps the email recipients (email addresses) in the above specified table. APP will use the value entered in this field while delivering the report. The recipients found here will be added to the To list. Keep this field blank if not needed.

• Click the Add button.

• Launch the APP Report that you want to view .
• On the toolbar of the Report Viewer window, click the refresh button.

• Click the SAP menu option titled Sales Order under the Sales - A/R menu to load the SAP Sales Order screen.
• Load the information for the Sales Order that you want to run the default report for.
• Click the Print Preview button from the SAP® Business One tool bar. This will load the default report for the Sales Order in the APP Report Viewer screen.

• Click the SAP menu option titled Sales Order under the Sales - A/R menu to load the SAP Sales Order screen.
• Load the information for the Sales Order that you want to run the default report for.
• Select the desired report to preview from the Reports menu group option from the right-click menu on the Sales Order screen.

• Clicking on the report will launch the APP - Report Options screen.

• Select the Preview option from the Report Action section.
• Click the Execute button.

• Click the SAP menu option titled Sales Order under the Sales - A/R menu to load the SAP Sales Order screen.
• Load the information for the Sales Order that you want to run the default report for.
• Click the Print button from the SAP® Business One tool bar. This will print the default report for the Sales Order.

• Click the SAP menu option titled Sales Order under the Sales - A/R menu to load the SAP Sales Order screen.
• Load the information for the Sales Order that you want to run the default report for.
• Select the desired report to preview from the Reports menu group option from the right-click menu on the Sales Order screen.

• Clicking on the report will launch the Report Options screen.

• Select the Print option from the Report Action section.
• Click the Execute button.

• Click the SAP menu option titled Sales Order under the Sales - A/R menu to load the SAP Sales Order screen.
• Load the information for the Sales Order that you want to run the default report for.
• Click the E-Mail button from the SAP® Business One tool bar. This will deliver the report via email.

• Click the SAP menu option titled Sales Order under the Sales - A/R menu to load the SAP Sales Order screen.
• Load the information for the Sales Order that you want to run the default report for.
• Select the desired report to preview from the Reports menu group option from the right-click menu on the Sales Order screen.

• Clicking on the report will launch the APP - Report Options screen.

• Select the Deliver option from the Report Action section.
• Click the Execute button.

• Click the SAP menu option titled Sales Order under the Sales - A/R menu to load the SAP Sales Order screen.
• Load the information for the Sales Order that you want to run the default report for.
• Click the Fax button from the SAP® Business One tool bar. This will deliver the report via fax.

• Click the SAP menu option titled Sales Order under the Sales - A/R menu to load the SAP Sales Order screen.
• Load the information for the Sales Order that you want to run the default report for.
• Select the desired report to preview from the Reports menu group option from the right-click menu on the Sales Order screen.

• Clicking on the report will launch the APP - Report Options screen.

• Select the Deliver Fax option from the Report Action section.
• Click the Execute button.

• Click the SAP menu option titled Document Printing under the appropriate menu to load the Document Printing - Selection Criteria screen.

• Complete the desired criteria for document printing.
• Click the Ok button to launch the APP Document Action screen.

• Select the desired document printing action…
  • Contact Preferences - This is a new feature of APP. While using this method, APP will automatically choose the action set by the corresponding contact person.
  • Print - APP will print the default reports selected in this criteria.
  • Deliver - APP will email or fax the default reports selected in this criteria.
  • Fax - APP will fax the default reports selected here. This action will be performed when a fax provider is set for the report.

In this version of APP the user has the ability to set the desired action corresponding to each and every customer contact person. To initiate this, the user should select the delivery preference in the Business Partner Master Data window.

• Click the SAP menu option titled Business Partner Master Data under the Business Partner menu to load the Business Partner Master Data screen.
• Load the existing Business Partner that you want to set up Document Printing for.
• Click on the Contact Persons tab.
• Click the APP Contact Type drop down menu and select the preferred type for each Contact Person…

• MBT – Master Bill To
• MST – Master Ship To
• DBT – Document Bill To
• DST – Document Ship To
• None – None

• Click the APP Report Delivery drop down menu and select the preferred action for each Contact Person…

• Email – Use Email
• Fax – Use Fax
• Both – Use both Email and Fax
• Print – Preferred action is Print
• EmailPrint – Print and Email
• FaxPrint – Print and Fax
• All – Print, Email and Fax
• None – No action.

• Click the Update button.

• Click the SAP menu option titled APP - Deliver BP Statements under the under the Business Partner menu to load the Deliver BP Statements screen .

• Complete the desired criteria for BP statement delivery.
• Click the button for the desired delivery action…
  • Print
  • Deliver
  • Print & Deliver

• Click the Report Definition menu option under the Administration Setup APP Configuration menu groups to load the Reports Maintenance screen.

• Click on the Check Printing report to load that definition into the screen.
• Click on the Print Preferences tab.
• Specify the Printer Name and the # of copies to be printed.
• Click the Update button to save the report definition modifications.

• Click the SAP menu option for Checks for Payment under the Banking Outgoing Payments menus to load the Checks for Payment screen.
• Load the check that you need to print.
• Click the SAP icon menu option for Print .
• The user will be prompted to select a printer. Since APP already has a printer associated with the Check report, no other printer will be used for this purpose.
• Click the Print button.

• Click the SAP menu option titled Document Printing under the Banking menus to load the Document Printing - Selection Criteria screen.
• Click the Documents Type drop down menu and select the Checks for Payment option.

• Complete the desired criteria for check printing.
• Click the Ok button to launch the APP Document Action screen.
• APP will prompt the user for the default action.

• The Print Checks for Payment screen will be launched.
• Select the checks to be printed.
• Click the Print button.
• The user will be asked to specify a printer. Since APP already has a printer associated with the Check report, no other printer will be used for this purpose.
• Click the Print button.

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.

All APP charts are based on an SAP® Business One Queries. Queries can be developed using Query Generator, Query Wizard, or direct entry in the query editing window.

• The use of screen field variables ($[$n.0.0] format) is supported. This allows a value such as a BP Code or Item Number to be passed from an SAP® Business One form (screen) as a parameter in the query.
• The use of user parameter variables ([%0] format) is not currently supported.
• If your query includes calculated or summary values be sure to use the AS operator to assign an alias to the value. Otherwise the value will not be available when defining the chart.
• The query must exist (be saved in Query Manager) prior to opening the APP Report Definition function. Any changes made while the APP Report Definition function is open are not available until the function is closed and reopened.

Save the query in the standard fashion. It may be appropriate to create a special category in Query Manager as a common place to hold APP-related queries; however this is not a requirement.

• Click the Report Definition menu option under the Administration Setup APP Configuration menu groups to load the Reports Maintenance screen.
• Be sure that the form is in Add mode.

• Select the Query option.
• Enter a Name for the report. This name is displayed to the user when selecting the query or chart.
• Select an APP Category to assign the query to. These categories are used for management of the queries and reports.
• Select the Database where this query is stored. This selection is required in order for the query to be retrieved correctly.
• Enter a Description value (optional).
• Select the appropriate Query from the drop down list.
• Link the report definition to the desired SAP® Business One screen.
• Click the Enabled checkbox. This can be done at any time but is required for the query/chart to be available to users.
• Click Add button to save the Report Definition.

• Click the Report Definition menu option under the Administration Setup APP Configuration menu groups to load the Reports Maintenance screen.
• Click on the desired Query-type report definition in the upper grid area of the form. The report definition information is displayed in the lower area of the form.

• Click the Manage Charts button. The Chart Definition form is displayed.

• Click on the desired chart definition in the upper left grid area of the form. The chart definition information is displayed in the right grid area of the form.

• Name - This name is displayed to the user when selecting a chart. This value is required.
• Chart Title - This title is displayed on the chart itself. This value is optional but strongly recommended.
• Legend Text - This value is used as the header for the legend area of the chart. Optional but recommended, especially when the chart does not display the X and Y axes such as with a Pie chart.
• Chart Type - This is the default chart type that is displayed when the chart is requested. The user can then select other chart types as desired. This value is required.

• X-Value - These are the values that will be displayed along the horizontal axis of the chart, or the segments of a Pie chart. The values in the drop down list are the values selected in the underlying query.
• Y-Value - These are the values that will be displayed along the vertical axis of the chart, or set the size of the segments in a Pie chart. The values in the drop down list are the values selected in the underlying query.

• Legend - These are the values that will be displayed in the Legend area of the chart. The values in the drop down list are the values selected in the underlying query. Legend values are recommended when the chart does not display the X and Y axes such as with a Pie chart.
• Label - These are the values that will be displayed for the data points in the chart itself. The values in the drop down list are the values selected in the underlying query.
• Tooltip - A tool tip is an additional value that can be displayed when the user hovers the cursor over a data element in the chart. The values in the drop down list are the values selected in the underlying query.
• Dimension - The options are 2D or 3D. This is the default display format that is used when the chart is requested. The user can then change the format as desired. This value is required.
• X-Axis Text - This is the text that is displayed along the X (horizontal) axis of the chart when appropriate. Optional but strongly recommended for chart types that show the X and Y axis.
• Y-Axis Text - This is the text that is displayed along the Y (vertical) axis of the chart when appropriate. Optional but strongly recommended for chart types that show the X and Y axis.
• Legend Series - This value is used to label the individual elements in a chart. Optional but strongly recommended for chart types that do not show the X and Y axis.
• Refresh Time (numeric only) - This value is used to automatically refresh the contents of the chart on a periodic basis while the chart is being displayed. This option should be used judiciously since it executes the underlying query for each refresh. Consider how often the information actually changes when considering the refresh period. A value of 0 means that the chart is not refreshed automatically.

• Click Update button to save the chart definition.
• Click Preview button to view the chart output.

Assume that the current date is May 15, 2020 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 2020.
  • 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.

the Administration System Initialization General Settings menu groups to load the General Settings screen.

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 Chart has been created it is available for viewing via the right-click menu from the SAP® Business One form to which it is assigned.

Selecting a menu option in the Charts sub-menu will load the chart data into the TWBS Chart Viewer screen. The screen has a number of menu options available to the user.

• Hover over a data element in the chart to display the Legend, Label, and Tool tip information.
• Click the 2D or 3D buttons to change the rendering of the chart.
• Click the Refresh button if needed.
• Click the other chart type buttons to render the chart in a different way.
  • Additional chart types can be selected using the drop down arrow found at the right-hand end of the chart type buttons.

• A chart may be printed using the Print button. The output is controlled by the setup on the Printing Preferences tab for the query in APP Report Definition screen.

• A chart may be sent via e-mail using the Deliver button. The output is processed using the setup on the Delivery Preferences tab for the query in APP Report Definition screen.

• The data used for the chart can be displayed by clicking the Data tab of the TWBS Chart Viewer screen, or by double-clicking on any data element in the chart. The underlying data is displayed in a grid format.

• The data in the grid can be filtered by clicking the funnel icon in the header of any column. This will bring up the options for filtering according to the column on which the funnel icon was selected.

• You can then choose specific values by using the check boxes, or you can do more advanced filtering using the Show rows with value that … options.
• Click on the drop downs for more filtering options.
• Once a filter has been applied you can return to the chart. The chart contents are refreshed using only the data found in the filtering process.
• The data view can also be used to group data for review.

• Click and drag a column header into the Grouped by area at the top of the grid.
• Multiple data elements can be used for grouping by clicking and dragging them into the Grouped by area in the sequence by which they should be grouped.
• If multiple data elements are used the grouping sequence can be changed by dragging the desired data element to a different position in the Grouped by area sequence.

• Click the SAP menu option for the query that you want to create the Chart for under the Tools Queries User Queries menus to load the selected query into the SAP Query screen.

• The button to the immediate right of the Close button will allow the user to view existing charts associated with the query or to create a new chart.

• Click the Total Sales by Customer button option to create a new chart. This will load the Chart Viewer form for the selected chart.

• Clicking on the Data tab will give visibility to the data set that is used for the chart.

• To group any data on the chart data tab, click and drag the column title of the data that you wish to group by into the area above the column titles.

• The data view will be refreshed and the data will be grouped according to the field selected.

• Clicking on the down or up arrows will expand or contract the data group.

• Click the SAP menu option for the query that you want to create the Chart for under the Tools Queries User Queries menus to load the selected query into the SAP Query screen.

• The button to the immediate right of the Close button will allow the user to view existing charts associated with the query or to create a new chart.

• Click the Total Sales by Customer button option to create a new chart.
• This will load the Chart Viewer form for the selected chart.

• Clicking on the Data tab will give visibility to the data set that is used for the chart.

• To filter any data on the chart data tab, click the icon to the right of the column title of the data that you wish to filter.

• A form will be displayed which will list all values of that field in the data set. This will allow the user to select only those values that will be displayed either by selecting the individual values or a range of values. The user will also be able to clear any existing filters through the Clear Filter button.

• Select the data that you want to filter for the column.

• Any columns for which the data has been filtered will display the icon colored to indicate filtering has been applied.
• Clicking on the Chart tab will display the updated chart.

TWBS Advanced Productivity Pack (APP) includes functionality called Advanced Delivery. This functionality allows a user to select a group of records (e.g. a specific group of Business Partners) and deliver (e-mail or fax) and/or print associated documents for this group of records. This section describes the steps used to define and execute the APP Advanced Delivery functions.

There are three steps required to set up the APP Advanced Delivery functionality…

• How To Create an Advanced Delivery Query
• How To Create an APP Report Definition
• How To Set Up Advanced Delivery Configurations

Create an SAP® Business One query to retrieve the data elements that you may want to use as filters in the document delivery process. The query must be saved before opening the APP Report Definition function. Otherwise the query will not be available for use.

Save the query in the standard fashion. It may be appropriate to create a special category in Query Manager as a common place to hold APP-related queries; however this is not a requirement.

SAMPLE QUERIES

• Customer List - including customer code, customer name, contact person name, customer group name, salesperson name, and customer balance.

• SELECT T0.[CardCode], T0.[CardName], t0.groupcode as [CustGroupCode], T1. [GroupName] as [CustGroupName], T0.[SlpCode], T2.[SlpName], T0.[Balance], t0.groupnum as [PayGroupCode], t3.pymntgroup as [PayGroupName] FROM OCRD T0 INNER JOIN OCRG T1 ON T0.GroupCode = T1.GroupCode INNER JOIN OSLP T2 ON T0.SlpCode = T2.SlpCode inner join octg t3 on t0.groupnum = t3.groupnum WHERE T0. [CardType] = 'C'

QUERY LIMITATIONS

• There are certain limitations while creating a query.
  • The ORDER BY clause is invalid in views, inline functions, derived tables, sub-queries, and common table expressions, unless TOP or FOR XML is also specified. So while creating a query with the ORDER BY clause, you must specify the TOP keyword. For example the query Select * from ORDR (NOLOCK) ORDER BY CardCode is not a valid query. So specify the TOP keyword like this - Select TOP 100 * from ORDR (NOLOCK) ORDER BY CardCode.
  • Repetition of columns is not valid. For example Select CardCode, * from ORDR (NOLOCK) is not a valid query. Because the field CardCode will be repeated twice. While specifying the * keyword, do not add field names separately. If you need to add the field name separately, specify an alias for the column. For example Select CardCode AS [Alias1], * from ORDR (NOLOCK), but make sure that there is no field with the same name as Alias1.

General Tab

Create a basic Report Definition. The Report Definition for a sample Advanced Delivery setup is shown below.

Delivery Preferences Tab

Delivery Preferences is information about how a report/document is delivered. Click on the Delivery Preferences tab. Create a new E-mail Message ID. The message will be used to format the contents of the outgoing e-mail message and to assign the Query to be used to filter the Advanced Delivery selection list.

Create a new Message ID

• Select Define New from the drop down list. The Message Editor form is displayed.
• Enter a Message Description. This is displayed to the user so it should be descriptive.
• Enter the Email Subject. This is the subject line of the e-mail.
• Enter the Attachment Name . This is used as the name of the attachment sent with the e-mail.
• Select the Query option for customization type. A list of queries in SAP® Business One is displayed. The first column shows the Query Category and the second column shows the Query Name.
• Once a query has been assigned to the message the columns in the query are available for use in the e-mail body (called “tags”) from the Select Tag drop down list.
• Standard data tags can also be selected by selecting the Standard customization type option.
• Enter the text and tags needed to create the body of the e-mail.
• When selecting a tag…
  • The Insert button will insert the tag text at the current cursor position.
  • The Copy button will copy the tag text to the Windows clipboard. You can the position the cursor as needed and right-click/paste (or Ctrl-V ) to insert the tag text.

• Save the Message information - If you use both Standard and Query tags in your e-mail layout, be sure to re-select the Query option button before saving the message definition. Otherwise the tags will not be handled correctly when the e-mail body is created.
• Update the Report Definition.

• Click the Report Definition menu option under the Administration Setup APP Configuration menu groups to load the Reports Maintenance screen.
• Click on the Report that you want to add an Advanced Delivery Definition to in order to load the report data to the Maintenance screen.

• Click on the Advanced Delivery Definitions tab.
• Click the New button.

• The Use Default Report and Use Specific Report options are selected automatically based on the report definitions already set up.
• Enter a Name for the delivery option. This name is displayed to the user for selection.
• Assign the desired Additional Query Fields values as needed…
  • Select the desired Field to use as a filter from the drop down list. The fields shown here are columns in the associated query.
  • Select the desired filter Condition from the drop down list.
  • Enter the value to be used for comparison in Value 1.
  • Enter a second value to be used for comparison in Value 2. This can only be used when the Between filter condition is selected.
  • Select a Relationship option from the drop down list if appropriate. A relationship operator is required for every row except the last in any instance where there are more than one filter condition row.
• Click the Update button to save the filter information.
• Click the Ok button to close the Advanced Delivery Definition screen.
• Click the Update button to save the report definition revisions.

• Click the SAP menu option for Advanced Delivery under the APP Reports menu to load the Advanced Delivery screen.

• Select the SAP® Business One form that is the primary source for the delivery rules from the Select Form drop down list. Only those forms that are associated with queries are displayed.
• Select the APP report definition that uses the desired query from the Use Specific Report drop down list. Only those report definitions associated with the SAP® Business One form selected are displayed.
• Assign the desired Additional Query Filters (see above).
• Click the Ok button to see the results of the filtering process and to execute the delivery process if desired.
• Click the Save As Config button to save this configuration for future use.
  • Enter a Name for this new delivery option.
  • Click the Update button to save the new configuration.
• Click the Refresh APP Menu button to refresh the APP menus so that the new configuration is displayed.

• Click the Report Definition menu option under the Administration Setup APP Configuration menu groups to load the Reports Maintenance screen.
• Click on the Report that you want to edit the Advanced Delivery Definition for in order to load the report data to the Maintenance screen.

• Highlight the definition row that you want to edit.
• Click on the Advanced Delivery Definitions tab.
• Click the Edit button.

• The data for the Delivery Definition will be loaded to the screen for editing. Make any necessary modifications.
• Click the Update button to save the filter information.
• Click the Ok button to close the Advanced Delivery Definition screen.
• Click the Update button to save the report definition revisions.

• Click the Report Definition menu option under the Administration Setup APP Configuration menu groups to load the Reports Maintenance screen.
• Click on the Report that you want to edit the Advanced Delivery Definition for in order to load the report data to the Maintenance screen.

• Highlight the definition row that you want to edit.
• Click on the Advanced Delivery Definitions tab.
• Click the Clone button.

• The data for the Delivery Definition being cloned will be loaded to the screen.
• Enter a new name for the cloned definition and alter any data needed.
• Click the Update button to save the filter information.
• Click the Ok button to close the Advanced Delivery Definition screen.
• Click the Update button to save the report definition revisions.

• Click the Report Definition menu option under the Administration Setup APP Configuration menu groups to load the Reports Maintenance screen.
• Click on the Report that you want to remove the Advanced Delivery Definition for in order to load the report data to the Maintenance screen.

• Highlight the definition row that you want to edit.
• Click on the Advanced Delivery Definitions tab.
• Click the Delete button.
• The user will be prompted for confirmation to remove the Delivery Definition.
• Click the Yes button to confirm definition removal.
• Click the Update button.

• Click the SAP menu option for the Advanced Delivery Definition that you want to execute under the APP Reports Advanced Delivery Definitions menus.
• Select the desired delivery set.
• Click Ok to execute the filtering process. A list of the records that will be processed is displayed.
• Click the check boxes of the records to be processed further. All records are selected by default.
• Click the header of the checkbox column to select or clear all records.

• Click Print button, Deliver button, or Both button to execute the delivery process.
• You can continue with other tasks while the Advanced Delivery process is working. A progress bar is displayed to show you how many records have been processed.

A special or one time Advanced Delivery Definition can be created and executed from the Advanced Delivery function of the APP Reports menu. This definition can also be saved for reuse.

• Click the SAP menu option for Advanced Delivery under the APP Reports menu to load the Advanced Delivery screen.

• Select the SAP® Business One form that is the primary source for the delivery rules from the Select Form drop down list. Only those forms that are associated with queries are displayed.
• Select the APP report definition that uses the desired query from the Use Specific Report drop down list. Only those report definitions associated with the SAP® Business One form selected are displayed.
• Assign the desired Additional Query Filters (see above).
• Click Ok to execute the filtering process. A list of the records that will be processed is displayed.
• Click the check boxes of the records to be processed further. All records are selected by default.
• Click the header of the checkbox column to select or clear all records.

• You can continue with other tasks while the Advanced Delivery process is working. A progress bar is displayed to show you how many records have been processed.

A detailed video highlighting the process of setting up Pivot Grids, manipulating Pivot Grids in real-time for extensive data analysis, and setting up key performance indicators (KPIs) is available on our TWBS YouTube Channel. A direct link to the video is provided below.

TWBS APP How To Set Up Pivot Grids and Utilize KPIs