Creating Custom Reports for Materials Management and Financials Version

Contents

This topic discusses:

Links to related topics:

Search Online Doc
Current Release Notes

Overview

Customized reports offer flexibility in deciding what data to include in a report and how to display the data. In addition to formatting standard lists and using reports defined by the system, you can define and run your own custom reports. You can also edit the defined report definitions to tailor them to your needs. When you create your own reports, you can...


Before you start, it is important to know this:
Reports that you output in .pdf are OCR enabled. This means that you can use the .pdf search
to locate information in a long report. This feature is extremely handy when you have a very large report,
and wish to locate a single piece of data
.

Security

Custom reports are fully integrated with the system's security settings.

The "Reports" Role Object

A user's ability to view, create, edit, or delete a report definition is controlled by the role object "Reports."

The objective is to allow Materials, AP, and GL managers to have more control over report quality so that only users authorized to create or change report definitions can access those features.

The role object  “Reports” provides user access to the features in My Report Definitions and Standard Report Definitions. (Materials Management, AP, or GL main Contents > Reports)

For the “Reports” role object, settings result in the following restrictions/capabilities for users.

Affect on User Capabilities

None

The options “My Report Definitions” and “Standard Report Definitions” are not visible to the user on the Reports menu. The user cannot view report definitions at all.

View

The options “My Report Definitions” and “Standard Report Definitions” are visible on the Reports menu.
The user cannot create, edit, copy or delete report definitions, but can display them.
- The New button is grayed out.
-  Editing the report definition is disabled, including General, Advanced, and Comment tab fields.
- The Preview, Select Fields, and Selection Builder buttons are disabled.
- The user can run and view completed reports, but cannot change the value of selection fields.

Modify

The options “My Report Definitions” and “Standard Report Definitions” are visible on the Reports menu.
The user cannot create, copy, or delete report definitions.
The user can edit a report definition:
- Editing fields on the General, Advanced or Comment tabs. 
- Editing report field definitions  (Menu > Edit) on the Report Fields panel.
- Adding new fields and removing existing fields.

- Clicking the Selection Builder and changing selection values.

The user can also:
- print a report
- change selection values when printing a report
- view the Completed report.  

Create

The user has all the capabilities allowed in Modify.
In addition, the user can create report definitions. The New button is enabled.
- The user cannot delete a report definition.

All

The user can create, edit, modify, or delete a report definition.

 

Report Objects and Data Profiles

Custom reports are implemented using report objects. A report object is a logical view into the database -- it contains data from various related database tables. A user's ability to create, run, and/or view specific reports based on their report objects depends on whether a Report Profile is assigned to the system User ID; and if so, which Report Profile. A Report Profile limits the report report objects that a user can access, so that not all users at a site may be authorized for all reports. A site's system administrator can permit or restrict viewing and / or report creation among users at a site. If you need access to custom reports, see your site's system administrator.

Many report objects are restricted by data profile. For those report objects, only data for departments and organizations allowed in the user's data profile can be accessed. Report objects restricted by data profile contain "Yes" in the Data Profile column on the Report Objects list.

Training

Several modules on the Training menu will help you work with reports. Figure 1 displays the reports training lessons.

Figure 1 - Training Modules for Reports

To open the custom reports feature:

From the main Contents, select Reports > My Report Definitions. The list of custom reports appears (Figure 2), if any have been created by users at your site.
If there are no reports, No Data appears. (In Figure 2, Quick Filters are hidden.)

Figure 2 - List of Custom Reports

From Menu next to any report definition on the list, you can print a report, schedule a report to run, view reports that have run, edit the report definition, delete the report definition, and more. Clicking New lets you create a new report definition.

See Setting up scheduled jobs for details on how to use the job scheduler to run your reports on specific days/times.

To create a new report definition:

  1. From the Materials Management, Accounts Payable or General Ledger main Contents, select Reports > My Report Definitions.
  2. Click New. The Create New Report Definition panel appears (Figure 3).

Figure 3 - Create New Report Definition Title

  1. Enter a title for the report in the Report Title field.
  2. Click the Report Object prompt (...) to select a report object.
    A report object is a logical view into the database -- it contains data from various related database tables. For example, the Purchase Order header and Purchase Order lines together are a report object.
    The list of report objects appears. Use Position To (or sort the columns) to find the report object that you need. Figure 4 shows report objects that begin with Ven.
  3. Click Select next to the report object that you wish to use.

Figure 4 - Selecting a Report Object

  1. If you wish to make your report available to other users at your site, select the Shared Report box.

    Hint
    You may wish to leave your report unshared while you are working on it. When you have the report definition finished, you can edit it to select the Shared Report box.

  2. If you wish to make the report available for individual departments to run, select Is a Departmental.
  3. Click Next.
    The Report Definition header appears along with the Select Report Fields list (Figure 5).

Figure 5 - Selecting Report Fields

The Select Report Fields list contains all the fields that you may include in your report.

  1. Click in the box next to a field to select the field. If many fields are available, you may need to scroll down (or press Next Page) to see all the fields..
  2. Click Next.
    The application displays the Report Definition panel with three tabs: General, Advanced, and Comments.
    The Report Fields edit panel is at the bottom (Figure 6).

Figure 6 - Report Definition Fields Edit Panel for Ordering and Configuring Fields

The General tab contains the Report Title and the Shared Report selector.
The Advanced tab (Figure 12) lets you define your report's output type. The Comments tab is a place for you to annotate your report.
The Report Fields edit panel lets you order your fields and configure your report.
- If you need to add more fields to your report, click Select Fields. The system again displays fields for you to select.

  1. Format your fields for the report.
    You can change the labels that actually print or display on your report. In the previous example, we have defined a field called: "Vendor No," but this field can print as "Vendor Number," "Vendor #," etc.

    - To change the field label, click Menu > Edit next to the report field, or click the edit icon.
    A panel appears (Figure 7).

Figure 7 - Formatting a Report Field

You can enter a new column heading label and column width, specify a font and font size, and other options.
- Click Help for details on the values you can enter.
- Click Save to save your work.
- Click Submit when you are finished.
The system closes the window and applies the designated formatting to the field. The changes you made take effect during the next report run.

  1. On the Report Fields edit panel, specify the order of the columns in your report, fields that are filters, sortable fields, the sort order, columns to be totaled and grouped, and for fields that are grouped, whether you want a page break after the group's data.

Figure 8 - Selectable Options for This Field Will...

Use the values in the drop box to identify the classification or grouping fields in your report, and the fields that you want to total. For example, suppose you are creating a report that shows how much each department spends on stock and non-stock items for a given month. The data you have in mind for the report looks like Figure 9:

Note: Another example that uses grouping fields is in Creating Summary Reports: Grouping Data .

Figure 9 - A Potential Layout for a Report

You want data by department, and by item type: stock or non-stock. Your report will have fields for the Month (selectable), Department Number, Department Name, Item Number, Item Description, Item Type, and probably the Total Cost to the department for each item. Your grouping fields are Department Number and Item Type. You want separate data for items of each type and each department. Your totaling field is Total Cost.
Selections shown in Figure 8 are:

begin a new heading or totaling group - Select this value when your field is a grouping or classification field. In Figure 9, you would select this value for Department No and Item Type. A field with this value selected should not have a Sorting Order assigned. (See Creating Summary Reports.)

prints with previous group - Select this value if the field belongs with another field. For example (Figure 9), you would select this value for Dept Name.

summarize into 1 detail line - This value "collapses" several lines on your report into one line. For example, with Item Type (Figure 9), perhaps you only want to see one line in the report that shows the cost for all stock items and a line for all non-stock items. You don't want to see each individual item listed. In that case, select this value for the Item Type field. (Also, see Creating Summary Reports for examples.)

print a total - This value identifies any field that you wish to total. In the Figure 9, you would select this value for the Total Cost field. The application also generates Grand Totals in the report. (See Creating Summary Reports.)

not print on report - If you do not want a field to actually print on the report (but you do want the report to use data from the field), select this value.

Figure 10 - Report Fields Defined for Vendor Report in Figure 2

Notice the following:

- State Country is a selection field, and also defines a group.
- Within the State Country group, the data will be sorted in ascending order -- first, by Vendor Name, and then by Vendor ID.

  1. Click Selection Builder to specify values for the Selection field(s).
    If your report has selectable fields, the Selection Builder (Figure 11) shows each selectable field that you can specify for the report. Entering a value for a selectable field helps you refine your report to capture more specific data.

Figure 11 - Selection Builder

You can also enter operators (in the list below). Hold the mouse over the box to the immediate left of the selection value (Figure 11, red arrow). A question mark ? appears. Click the question mark repeatedly to locate and set the operator as you wish. The operators are:

GT - greater than
LT - less than
EQ - equal to
NE - not equal to
GE - greater than or equal to
LE - less than or equal to
Like - match a set of characters. For example, if your site has Clinic A, Clinic B, Clinic C, etc., specifying "clinic" with like will select all the clinics.
You can enter the characters that you want to match directly in the box, or click the arrow to select a value (if there is one) that you want to match. Like is not case sensitive, so it will match a character to both upper- and lower-case occurrences of the same character.
Range - a range of values
List - lists possible values.

Figure 12A and Figure 12B display sample results for a date field when different operators, and NE are used.

In the figures, assume that the current date is July 28, 2015...the date when the user is running the report.

Figure 12A - Sample Results of Using the Various Operators with Date Fields

Figure 12B - Sample Results of Using the "NE" Operator with Date Fields

- If you wish to compare the field to another field, choose an operator, and click Show on the Fields for Comparison box. A list of selectable comparison fields appears.

- Once you set up one or more selection values, you can lock them so that the report will always run for the values you specified. Click on the second box to the left of the selection value to lock or unlock a value (Figure 11, green arrow.)

- To let users specify a value for the selection field when they run the report, leave the selection fields blank.
- Click Help on the Selection Builder for detailed information about its fields, how to use operators, and create comparisons.
- Click Submit when you are finished entering selection field values.
The Report Definition panel reappears.

  1. Click the Advanced tab (Figure 13) to specify the output form of your report.

Figure 13 - Report Definition Advanced Panel

  1. When you are finished setting up your report definition, click Submit. The list of reports (My Report Definitions) appears.
  2. Click Refresh to see your report definition on the list.

To run and view a report from a report definition:

  1. From the main Contents, select Reports > My Report Definitions.
    (If you are running an system-defined report, select Reports> Standard Report Definitions.)

  2. Locate the report that you wish to run.

  3. Next to the report of interest, click Menu > Print Report.
    The Selection Parameters panel appears.

  4. Enter values for the report data on the Selection Parameters panel, or accept the default values.
    For example, if a Year or Period selection field appears, enter the year or period that you wish to run the report for.

  5. Click Submit. A message informs you that the report has been submitted.
    Some reports may take a few seconds or minutes to run. The application updates you on the progress of your report.
    The report is saved in My Archived Reports, but you can also view it immediately from My Report Definitions.

  6. On the My Report Definitions list, next to the report you have just run, select Completed Reports.
    The application displays a list containing each run of the report. If you have just run the report for the first time, the list will have one entry.

    If your report has not finished running, you see messages that update the status of your report.
    - Click Refresh to view the changing status messages as the report runs.

    Multiple runs of the report are listed chronologically.
    - You can delete any run of the report by clicking Menu > Delete.
    - Spreadsheet reports contain a zero in the Page Count column. Zero is normal for spreadsheets.
    - If a zero occurs in the Page Count column for a PDF or other non-spreadsheet report, then no pages were output, and a problem with the report exists.

    When a report contains errors or problems, and cannot run, a problem message also appears.
    - Click the message to read the details of the problem.
    If you are not getting the expected results for a report definition created at your site, you may need to make changes to the report definition.

  7. Select View Report. An online version of your report appears. By default, unless you have specified another file type, the system displays the report as a .pdf file in Adobe Acrobat Reader.

  8. Select the printer icon, or File > Print to print the report.

To run a custom report that has been made available for your department:

  1. Identify the department.
  1. On the Quick Click panel, select Financial Reports.
    A field for entering a Department Number appears (Figure 14).

Figure 14 - Entering a Department Number for Financial Reports on the Quick Click Panel

  1. Enter the Department Number in the field.

  2. Click Go.
    The list of available custom reports for the department appears. Figure 15 is an example.

Figure 15 - A Department's Financial Reports

  1. Next to the report of interest, click Menu > Print Report.
    The Selection Parameters panel appears.

  2. Enter values for the report data on the Selection Parameters panel, or accept the default values.
    For example, if a Year or Period selection field appears, enter the year or period that you wish to run the report for.

  3. Click Submit.
    The report is run and appears in My Archived Reports for viewing or printing.

To view reports that have run:

  1. From the main Contents, select Reports > My Archived Reports.
  2. Locate the report that you wish to view.
  3. Next to the report, select View Report.

To edit a report definition:

  1. From the main Contents, select Reports > My Report Definitions or Standard Report Definitions (if you are editing a system report).
  2. Locate the report that you wish to edit.
  1. Click Save periodically while you are editing to save your changes.
  2. When you are finished editing your report, click Submit. The system displays the My Report Definitions list.
    Note: If you have edited a system-defined report, the system adds the copy of the report to the My Report Definitions list, and not to the Report Definitions list.

    From the My Report Definitions list, you can run your report.

To change the format of report fields:

  1. Make sure that you are viewing the Report Definition panels for the report that you wish to format.
    The Report Fields panel is at the bottom.
  2. Locate the field that you wish to format.
  3. Click the pencil icon or select Menu > Edit next to the field.
    The Report Field Definition panel appears (Figure 7).
  4. Change the values in the Report Field Definition panel to specify the format that you wish.
  5. When you are finished making changes, click Submit.
    The changes you made take effect during the next report run.

To output reports in different file types:

  1. Make sure that you are viewing the Report Definition panels for the report..
  2. Click the Advanced tab on the header panel.
    The Report Definition Advanced panel appears (Figure 13).
  3. Click the down arrow in the Output Format Type field. Several output file types appear.
  4. Click on a file type to select it:
  1. Click the Data Only box if you want only data on your report, and not column names
  1. Click Save. The changes you made take effect during the next report run.
  2. Click Submit to return to My Report Definitions.

To add comments to a report:

  1. Make sure that you are viewing the Report Definition panels for the report that you wish to format.
  2. Click the Comments tab on the header panel. The Comment field appears.
  3. Enter comments in the Comments field. When you are finished, click Submit.
    The application saves the comments and returns you to My Report Definitions.

To remove the default Current Settings for the report:

  1. Make sure that you are viewing the Report Definition panels for the report that you wish to format.
  2. Click the Advanced tab on the header panel.
  1. Click on the Use Current Settings Organization check box to clear the box. Future reports will include all the information that you are able to access as specified in your data profile.

Creating Summary Reports

A summary report collapses detail lines into a single line that summarizes the data in the columns. The examples below illustrate how a summary report differs from a detail report, and how to set up a summary report.

Important: Before you read the paragraphs below about summary reports, make sure that you understand the preceding sections on how to create custom reports in general.

Figure 16 shows a report definition for a regular, detail report called "PO by Org." Notice that the columns are ordered by Org, then by Vendor Name and Vendor No. One field, PO Type, is a selection field that does not actually print on the report.

Figure 16 - Report Definition Panel for Detail Report "PO by Org"

A page from the printed output for the report is in Figure 17. Notice that this output continues for 200 pages, with information about every PO for every vendor by organization. In its current form, this report is not very useful.

Figure 17 - Output for Report "PO by Org"

What we really need is a report that provides a summary, for each organization, of PO dollar totals and the number of POs by vendor.

For a summary report, instructions on handling the PO header information and the PO line information must be entered under "This field will..." in the report definition for each column. The instructions (illustrated in Figure 18) are:

Entries for "This field will..." on a PO Summary Report Definition
For the PO header information: For the PO lines:
begin a new heading/totaling group summarize into 1 detail line
prints with previous group print a total

Figure 18 - Instructions Specified Under "This field will..." on Summary Report Definition

With "This field will..." instructions entered for the summary columns, the Report Definition panel looks like Figure 19.

Figure 19 - Report Definition Panel with Entries in "This field will.." for a Summary Report

For each different value of the first four fields (Vendor No, Vendor Name, Org and Org Desc), a line is printed on the report that displays the PO Total Cost. In other words, the instructions ("summarize into 1 detail line") for the top four fields and for PO Total Cost ("print a total") collapse multiple purchase orders for each vendor and organization into a single purchase order total.

The PO Type field is a selection field used to create the report, but is not printed.

Notice that the field PO No is being deleted from the report definition. The PO No field is a text field that cannot be summarized or collapsed, since each purchase order has a unique PO Number. This field cannot contribute anything to the summary report (nor could the report run correctly if it is included), so it is deleted.

For the columns with the instruction "begin a new heading/totaling group," sort order cannot be specified. Columns with "summarize into 1 detail line" can have a sort order specified.

Important: You must enter an instruction for every column in the report under "This field will..." Blank values will produce incorrect results for summary reports.

Running the report definition from Figure 19 produces the output displayed in Figure 20. For each vendor and organization, the total cost of purchase orders is summarized.

Figure 20 - Summary Report of PO Costs by Vendor and Organization

This nine-page summary report is a big improvement over the 200-page detailed report from Figure 16. However, we can refine the report definition even further to summarize the data even better.

Grouping Data

Figure 21 shows changes in the report definition that result in a report where summary data is grouped by vendor.

 

Figure 21 - Summary Report Definition PO Costs with Totaling Group

Under "This field will...", Vendor Name has the instruction "begin a new heading/totaling field." This instruction groups the data (that is, the PO cost totals) by vendor name. Vendor No prints with Vendor Name, so that Vendor No is no longer a separate column, but instead, is part of the group heading. Also, the column order has changed so that Vendor Name prints before Vendor No.

The PO Total Cost field is defined as a sort field and sorts first. (Previously, Org was the field that sorted first.) Within each group, PO Total Cost is ordered by organization, starting with the organization that has the highest PO Total Cost dollars, and descending to the lowest. (Figure 22).



Figure 22 - Summary Report of Highest to Lowest PO Costs Grouped by Vendor

Using Total Lines and Record Count

The Total Lines field is supplied with the "PO Header" report object. Total Lines provides the total number of lines in a purchase order.

The Record Count field is available for every report object. This field records the number of summarized records; that is, the number of row data records that would occur on the report if the rows were not collapsed and summarized.

To use these fields, edit the report definition and click Select Fields (Figure 23)

.

Figure 23 - Summary Report Definition Select Fields

The list of fields appears (Figure 24).

Figure 24 - Summary Report Definition - Selecting Record Count Field

Scroll the list to locate and select Record Count and Total Lines. Click Save then Submit to return to the report definition. When you refresh the report definition (by clicking Refresh), the Record Count and Total Lines fields appear.

Figure 25 is the revised report definition that includes the Record Count and Total Lines fields. Record Count is re-labeled so that it will appear on the report as "PO Count" since the number of records for any vendor will, in fact, be the number of purchase orders. Note that the previous instructions under "This field will..." have been changed from groupings to regular summary lines. The sort order by PO Total Cost remains so that the report sorts with the highest dollar amount at the top, in descending order.

Figure 25 - Summary Report Definition with Total Lines Field and Renamed Record Count Field

The printed output for the report definition is in Figure 26.

Figure 26 - Summary Report Output with Total Lines Field and Renamed Record Count Field

The PO Total Cost column contains the total purchase order amount for each vendor for each organization. The PO Count column (which is the Record Count column renamed) is the total number of purchase orders for the vendor/organization. Total Lines is the total number of purchase order lines.

Output a Report as an Excel Spreadsheet

If you wish to perform detailed analysis with a report, a useful strategy is to output the report as an Excel spreadsheet, and then use Excel's tools, such as filters (to filter and rank column data) and formulas (to manipulate mathematical data).

To output a report as an Excel spreadsheet:

  1. On the My Report Definitions list, click the edit icon or Menu > Edit next to the report.
    The Report Definition edit panel appears.
  2. Select the Advanced tab.
  3. Click the down arrow in the Output Format Type field and select Excel Spreadsheet (Figure 27).

Figure 27 - Setting the Output Format Type for a Summary Report to Excel

  1. Save and Submit the report.
    The list of report definitions appears
  2. Next to the report definition on the list, select Menu > Print Report to output the printed report to Excel.

Figure 28 is the Excel spreadsheet output for the report defined in Figure 25. Notice that you can select the Excel AutoFilter capability from the Data menu. AutoFilter inserts a filter selection button (indicated by the red arrow) at the top of each data column. Click the button to view and select a filter for the column. When you select the filter, Excel displays the data filtered as you specified.

To view the spreadsheet as it originally appeared, without being filtered, select Tools > Filter > AutoFilter again to turn off the AutoFilter..

Figure 28 - Excel Output for a Report with Excel AutoFilter Turned On

Hints and Tips for Fields and Excel Reports

Date fields

Several date fields are stored internally as "date plus timestamp." For this reason, entering the date alone will not return the information that you may want associated with the date. The most common date fields that contain timestamps are:

- CreateDate
- LastUsedDate
- DeletedDate
- Transaction TS
- AuthorizedDateTS (in the "PO Header Line View 1" report).

To get information for a single date, enter date values as a Range, with the same date in both fields of the range; for example (Figure 29):

Figure 29 - Using the Range Specifier to Get Data for a Single Date

This date specification is the same as asking for data from the beginning of the day at 12 AM to the end of the day at 12 PM, and avoids any timestamp that might occur in the data.

Quantity or Cost/Price = 0 (zero)

If you leave a quantity or currency field blank, ERP assumes that you want to see all quantities/prices/costs. When you actually want to see data where a quantity or price/cost is zero, for integer values, use: Qty < 1. This works because positive integer fields cannot have values between 0 and 1. For currency fields use three or four decimal places, as appropriate. For example, searching for a Unit Cost of 0 requires using < .0001 since unit costs are four decimal places.  Extended Cost is only 2 decimal places and requires < .01

Searching for Non-Null Contents or Missing Contents in Item Description, Alias, And Other Text Fields

ERP uses an ASCII sorting sequence for characters in which some characters (e.g., all letters) have a "higher value" than other characters, such as *.

- To search for a text value that is non-null, use GE *. This specifies that you want all text values that are "greater than or equal to" *, meaning all text characters. get all text values

- Similarly, to search for missing text values, use LT*. Leaving the field blank instead of entering LT* will not produce the correct result because My Reports ignores any selection value that is blank.
(You can also use Range with the top value empty, and the bottom value set to * . See Figure 30.)

The figure also contains the list of ASCII text characters, so you can get a better idea of what we mean by the sorting sequence with some characters being "greater" than others.

Figure 30 - Using * with a Range to Get Missing Text Values

You can specify item description and item alias values in an Item Desc field using operators such as EQ, NE, etc.; Range and List. Figure 31 summarizes what the various operators mean when used with item description and item alias values. operators with text

Figure 31 - Using Operators with Fields for Item Description/Item Alias Text Values

Avoiding the Excel Lines Limitation Problem

Currently, reports in Excel output are limited to 64K lines. You can work around this limitation by setting your output to .csv. (See Outputting reports in different file types.) You can then open your report with Excel.

This method, however, is not painless. Once your report is open in Excel (if you are using Excel 2019), you will need to convert some of your column types to text. The reason is, among other things, that Excel truncates leading zeros. So, for example, if your report contains organization codes, and your codes begin with zero -- 0012, 0013 -- you will see "12, 13" in the Excel Organization column.

Excel provides a text conversion wizard (Figure 32). Also, in the figure is the location of a You Tube video that goes through the process.

Figure 32 - Excel Text Conversion