Configuring Grid Layouts and Automated Reports

In Deacom, the reports or lists that are generated via pre-filters are referred to as grids. Grid Layouts refer to the interface that controls the characteristics of the fields contained in the grid as well as which fields will be displayed and in what order. Grid Layouts are one of the key components for allowing users to tailor the type of information that is displayed in Deacom.

Configuration

Deacom system Grid Layouts are pre-populated with defaults and do not need configurations. Users can create and/or modify user versions of the system grids as detailed below. When a user version of a system grid is created, the word "User" will be displayed in parentheses on the Grid Layout button located on the Deacom Toolbar for that grid. Grid Layouts, accessed via System > Maintenance > Grid Layouts, should only be accessed and/or modified by system administrators or power users for the purpose of removing a field/column from a grid that is causing an error, such as when trying to launch a report; or when a new user version of a system grid is needed, such as when creating additional user reports.

The security setting "Toolbar -- edit grid layouts for all users" control access to this area. Access to the "Copy Grid Layout" button, used to create additional user reports, is controlled by the "General -- copy grid layout" setting.

Process

Managing Grid Layouts

Controlling visibility to reports/Grid Layouts

Beginning in version 16.02, companies can control which Users or User Groups have access to reports/Grid Layouts in the system via the user of Grid Layout Security. See the Managing Grid Layout Security page for additional information.

Adding a new Grid Layout

  1. Obtain the name and title of the Grid Layout that will be copied. To find the name and title, first generate the grid or report in the system, then navigate to More > Grid Layout on the Deacom toolbar. The name and the title of the Grid Layout will appear in the top left-hand section of the form.
  2. Navigate to System > Maintenance > Grid Layouts to launch the Grid Layouts form, which is used to create user versions of existing system grids. System grids may never be added or modified by end users.
  3. Grid a new Grid Layout by adding a new one, or copying an existing one, as indicated in the steps below.
    1. Select the Grid Layout you wish to copy and click the "Add New" button to display the Edit Grid Layout form. See the Edit Grid Layout section on the Basic Objects and User Interface form for details on the fields and options available.
    2. Select the Grid Layout you wish to copy and click the "Copy Grid Layout" button to display the Edit Grid Layout form. See the Edit Grid Layout section on the Basic Objects and User Interface form for details on the fields and options available. Note that beginning in version 16.02.130, users can copy, and modify, grid layouts via System > Maintenance > Grid Layouts for all grid layouts, both system and user-defined.
  4. Click the "Add" or "Modify" button to access the Edit Grid Column form. This form is used to add new columns to the grid or to modify existing columns. See the Edit Grid Column section on the Basic Objects and User Interface form for details on the fields and options available.
  5. Once all the necessary additions or changes have been made, click the "Save" and "Exit" buttons to commit changes and return to the Edit Grid Layout form.
  6. If additional columns need to be added or modified, repeat steps 3-4.
  7. Once all changes have been made, click the "Save" and "Exit" buttons to complete the process.

Adding a new user report

Beginning in version 16.02, users with permission can create unlimited user versions of select system reports via the use of Grid Layouts. This option uses the "Copy Grid Layout" button which requires users to enter a name and title/caption for the new user report. Version 16.02 also introduced Grid Layout Security which expands the security used to control which Users or User Groups have access to Grid Layouts/reports in the system.

  1. Obtain the name and title of the Grid Layout that will be copied. To find the name and title, first generate the report in the system, then navigate to More > Grid Layout on the Deacom toolbar. The name and title of the Grid Layout will appear in the top left-hand section of the form.
  2. Navigate to System > Maintenance > Grid Layouts to launch the Grid Layouts form, which is used to create user versions of existing system grids. System grids may never be added or modified by end users.
  3. Select the Grid Layout you wish to copy and click the "Copy Grid Layout" button to display the Edit Grid Layout form. See the Edit Grid Layout section on the Basic Objects and User Interface form for details on the fields and options available.
    • Note: If copying a system Grid Layout for the first time the "Grid For" field will be set to "All users" and grayed out. This is due to the fact that an All users version of the system Grid Layout or report must exist before creating a user report for specific Users or User Groups.
  4. Enter a value in the "Name" field and select an option in the "Title" field. The "Title" field represents the name of the report as it will appear in the "Report Type" drop down list in the system so this field should contain a title that clearly identifies the report for users. The "Title" field uses Captions created via System > Maintenance > Captions. New captions can be created as needed.
  5. Click the "Add" or "Modify" button to access the Edit Grid Column form. This form is used to add new columns to the grid or to modify existing columns. See the Edit Grid Column section on the Basic Objects and User Interface form for details on the fields and options available.
  6. Once all the necessary additions or changes have been made, click the "Save" and "Exit" buttons to commit changes and return to the Edit Grid Layout form.
  7. If additional columns need to be added or modified, repeat steps 4-5.
  8. Set the appropriate security access for the Grid Layout. If all users will be allowed to view the Grid Layout, ensure the "Require Grid Security" flag is unchecked. If security access will be required ensure this flag is checked, then complete step 9. after saving the Grid Layout.
  9. Once all changes have been made, click the "Save" and "Exit" buttons to save the Grid Layout.
  10. If the "Require Grid Security" flag was checked, users will now need to grant access to the appropriate users. Grid Layout access is controlled at both the User Group and User levels. Users inherit the permissions of the User Group to which they are assigned. Exceptions to the group can be managed at the User level. For example, if one member of the Accounting department should not have access to a Grid Layout, permission to access the Grid Layout would be set to "Yes" for the Accounting User Group and "No" for the individual user. The steps to grant access to Grid Layouts, for both Users and User Groups, is covered on the Managing Grid Layout Security page.
  11. The newly created report/Grid Layout will now be displayed in the "Report Type" drop down field on the pre-filter in the appropriate reporting section of Deacom.
    • Note: If the pre-filter is already open, the user will need to first exit, then relaunch the pre-filter to see the new report.

Creating a user-defined column

  1. From the Edit Grid Layout form, click on the "Modify Layout" button in the toolbar.
  2. On the Edit Grid Layout form, click on the "Add" button.
  3. Select "User-defined" from the Field pick list.
  4. Enter a SQL expression in the "User Expression" field. For help with expressions, refer to the Deacom Basics > Functions and Operators Library page.
  5. Complete the rest of the form as necessary then click "Save" and "Exit".

Modifying a Grid

The report grid displays each record that matches the selection criteria.

  • Note: The title of the grid changes according to the report type selected on the pre-filter.
  1. To see a list of the fields available to add to the layout, click on the "Show Fields" button in the toolbar.
  2. To add a field to the grid, click on the "Modify Layout" button on the toolbar.
  3. On the Edit Grid Layout form, click the "Add" button.
  4. Complete the Edit Grid Column form as necessary, e.g. Field = to_ordtype, Title = Order Type, Width = 30.
  5. Click "Save" and "Exit".
  6. To change the sort order in which the results are displayed each time the report is run, click on the "Sort" button on the Edit Grid Layout form.

To modify a column, select it in the Edit Grid Layout form and click the "Modify" button, which will open the Edit Grid Column form. Make changes to the Title, Width, etc. as necessary. Click "Save" and "Exit".

  1. To delete a column from a report, select it in the Edit Grid Layout grid and click on the "Delete" button.
  2. To specify who sees a particular grid layout, click on the "Grid for" pick list on the Edit Grid Layout form and choose All users, Specific User, or User Group. If Specific User or User Group is selected, the field below is activated, allowing the user to enter a username or user group.
    • Note: Creating a grid layout for a specific user or user group changes the title of the grid that displays the report results, but the report name does not change in the "Report Type" field on the pre-filter. For example, a grid layout saved under the name "Period Report for Accountants" still appears in the "Report Type" field on the pre-filter as "Period".
  3. To rearrange the columns and relevant data, the user can click and drag the column headers.

Using Show Fields for Report Variables

When configuring grid layouts, it is helpful to have visibility to the available fields to the grid you are editing. Exporting the report variables to excel creates a quick reference to the available fields and the data contained within.

  1. Process begins from within any grid report ex: Sales > Order Reporting > View
  2. Click the “More” button
  3. Click the “Show Fields” button
  4. Click the Excel icon on the toolbar to export the grid variables

Deleting Existing Grid Layouts

  1. System > Maintenance > Grid Layouts.
  2. Locate the grid you wish to delete.
  3. Report can be filtered by “Grid For” to search by user or user group.
  4. Highlight the grid layout you want to delete.
  5. Click the “Delete” button on the form.
  6. System grids cannot be deleted, if you attempt to delete a system grid Deacom will prompt you that this cannot be done.
  7. Click “Yes” on the prompt to confirm the delete.

Duplicating a User Grid to other Users/User Groups

  1. System > Maintenance > Grid Layouts
  2. Locate the existing grid that you wish to duplicate.
  3. Report can be filtered by “Grid For” to search by user or user group.
  4. Highlight the grid you want to duplicate.
  5. Click the “Add New” button.
  6. Update the “Grid For” and “User” or “User Group” field to the group or user receiving the new grid.
  7. Click the “Save” button on the Edit Grid Layout form and close the form.

Managing Automated Reports

Scheduling Published Data for automated PDF generation

Only users with the new permission “System – Edit Automatic Exports" will be able to use this functionality. Information on modifying permissions can be found on Managing Users and User Security.

Deacom can schedule automatic exports of published data, saving users time and providing an easy way to get data consistently. There are two pre-requisites for automated reports: published data, such as a favorite, and a report layout for that grid.

  1. Click on the modify button of the favorite you wish to set up an automated report for.
  2. On the ‘Edit Favorite’ tab on the ‘Automatic’ tile, click the Add button.
  3. This will open the Auto Export form. You can now select your:
    • Report Layout
    • Interval Type – Day, Month or Week
    • Day of Month – If Interval Type is Month
    • Day of Week – If Interval Type is Week
    • Time
    • Export To - select "File"
    • Export Destination – this is the file path that the PDFs will automatically be exported to

If you are setting up many large automated reports, it can be beneficial to space the exports with a few minutes between them as a buffer (example: 9:00, 9:05, 9:10, etc.).

Scheduling Published Data for automated email generation

Only users with the new permission “System – Edit Automatic Exports" will be able to use this functionality. Information on modifying permissions can be found on Managing Users and User Security.

Deacom can schedule the automatic emailing of published data. There are three prerequisites for automated emails: published data, such as a favorite, a report layout for that grid, and ensuring the trigger email information is set up correctly on the System > Options > Users tab.

  1. Click on the modify button of the favorite you wish to set up an automated report for.
  2. On the ‘Edit Favorite’ tab on the ‘Automatic’ tile, click the Add button.
  3. This will open the Auto Export form. You can now select your:
    • Report Layout
    • Interval Type - Day, Month or Week
    • Day of Month - If Interval Type is Month
    • Day of Week - If Interval Type is Week
    • Time
    • Export To - select "Email"
    • Export Destination - enter the email addresses of the recipients. When sending to multiple email addresses, the email addresses need to be separated by semicolons.
    • Email Subject - option to enter a subject for the email that will be generated
    • Email Body - option to enter an email body

Sending attachments and reports using Triggers

Triggers can be used to attach and email Grid Layouts, as MS Excel files, or Report Layouts, as PDF files. Note: Reference the Configuring Triggers page for information on setting up and using Triggers prior to following the steps below.

  1. Navigate to System > Maintenance > Triggers.
  2. Click the "New" button to create a new Trigger, or click the "Modify" button to update an existing one.
  3. Fill in the required fields, marked in blue, and any other fields as necessary. See the link in the first sentence for information on the fields available to Triggers.
    1. In the "Destination" field enter a valid email address. When sending to multiple email addresses, the email addresses need to be separated by semicolons.
    2. In the "Output" field, select either "Html Email" or "Plain Text Email."
  4. Click the "Attachments" tab and select the appropriate Grid Layouts or Print Outs (Report Layouts) that will be sent via email. Ensure the "Export To Excel" box is checked.
  5. Click the "Save" button to save the Trigger.
  6. The next time a record is updated or inserted in the table specified on the Trigger, and the criteria defined in the "Conditions" field is met, the system will email the attachments to the email addresses specified on the Trigger.

Note: The "Attachments" tab on the Trigger may also be used to place Grid Layouts or Report Layouts in the path specified in the "Destination" field when the option in the "Output" field is set to "EDI."

Exporting Reports to Text Files

Deacom allows users to export reports to text files on the Edit Grid Layout form. The option is useful in situations where customers need to send information to banks, for example, which generally only accept text files.

  1. Navigate to a printable grid/report.
  2. Click on the "More" ellipsis button and select the Grid Layout option.
  3. Click the "Add" button in the Export Format section to display the Export Format form.
  4. Enter a value in the "Name" field and select the Text option from the "Type" field.
  5. Enter the appropriate text in the "Text" field. Refer to the Grid Layouts page for additional information on the type of text that can be used in this field.
  6. Once complete save the Export Format form.
  7. Save and exit the Edit Grid Layout form.
  8. The system should still display the printable grid/report form step 1.
  9. Click the "Print" button on the Grid Layout toolbar.
  10. In the "Report" field select the report name from step 4. and click the "Print/Export" button to have system download the report as a text file.

FAQ & Diagnostic Tips

I'm receiving an error message about "unknown fields" in grid definitions while I'm logged into Deacom. Sometimes Deacom freezes and I have to log out and log back in. What's happening and how do I fix it?

This issue is sometimes caused by user-defined fields on reports that have been made into Favorites with Previews. The error message will list the field or fields that are causing the problem.

Please note: there are unknown fields in the grid definition for 'ordsum'.

To fix this, use the "Grid Layout" button on the toolbar to update the following columns: u_product_type. This error usually occurs because the expression used in the field was modified or a field is no longer available to a report (whereas it used to be in a previous version of Deacom).

To resolve the error, follow these steps:

  1. Navigate to Tools > Management Reporting > Report Type - Favorites > click View. Highlight the Favorite that is generating the error. Click "Modify". Uncheck the "Preview" box, click "Save" and "Exit". You may have to turn off all of the user's previews until you can narrow down the one that is generating the error. Alternatively, you can turn off the Preview from the Main Menu by right-clicking on the Favorite and unchecking the "Preview" box.
  2. Modify the grid layout of the report via System > Maintenance > Grid Layouts OR Modify the grid layout via the "Grid Layout" button on the toolbar on the appropriate form, e.g. Sales > Order Reporting > Sales Order Summary. Delete the column and re-add it or modify the expression used in the field.
  3. Highlight the Favorite on the Main Menu and hit Delete on the keyboard. Re-create the Favorite with Preview via the steps outlined above.

Tip: The "Pinned Columns" button , available on the Edit Grid Layout form, allows users to pin or freeze columns on the grid. See the pinned column field description on the Grid Layouts help page for additional information.

Examples of Calculations within User Defined Columns:

Date Calcs:

1) Calculating the difference between a system date and current date.

Ex: Days remaining until shipment for un-shipped orders, Sales Order Summary grid.

IIF(EMPTY(to_shipped), to_dueship - DATE(), 0)

2) Calculating the difference between two system dates.

Ex: Determining the days between a Vendor promised delivery date to actual receipt date for received orders, Purchase Order Summary grid.

IIF(!EMPTY(tp_recevd), tp_promised - tp_recevd, 0)

Math Calcs:

1) Performing math on multiple system variables.

Ex. Total weight of a sales order line item by ordered quantity for open orders and shipped quantity for shipped orders, Sales Order Detail grid.

IIF(!EMPTY(to_shipped), or_qship * or_unitwgt, or_quant * or_unitwgt)

Logic Evaluations:

1) Display text when a condition evaluates to be true.

Ex. Display “Early”, “On-time”, or “Late” in a column depending on when a PO was received compared to the vendor promised date, Purchase Order Summary grid.

IIF(EMPTY(tp_recevd), ‘N/A’, IIF(tp_recevd < tp_promised, ‘Early’, IIF(tp_recevd > tp_promised, ‘Late’, ‘On-time’))

Is there any way to easily modify grid layouts properties?

As of version 16.02.108, editing grid layouts in-depth is available via Scripting. An example of functionality can be found on Editing the Grid Layout.