Inventory Analysis Report

Purpose of this document

The purpose of this document is to examine the setup and capabilities of Inventory Analysis Reports in Ceres.

Ceres Object release 6.00.00 is required for the functionality described in this document.

TABLE OF CONTENTS

• Design
• Analysis Types
• Analysis View Card Inventory
  • General FastTab
• Inventory Analysis Line Templates
  • Functions from the Inventory Analysis Lines Page
    • Deleting a line
    • Insert an Item or Several Items:
    • Inserting Item Groups
    • Renumber Lines
• Inventory Analysis Column Templates
  • To insert a new column
  • To delete an existing column
• Inventory Analysis Reports
  • General Fast Tab
  • Filters Fast Tab
  • Matrix Options Fast Tab
  • Display Report Information
  • Set Up Lines
  • Set Up Columns
  • Export To Excel
  • To Print The Report

Design

The Analysis report can be used to display inventory, sales and purchase statistics. Analysis reports can be used, for example, for reports on key figures. You use the Column Template Name table to define what columns should be included in the report. For example, you can design a column template to compare distributions for the same period this year and last year.

Note: that a printed version of an analysis report can display a maximum of 5 columns. If there are more than five columns in an analysis report, only the first five will be printed. The preview will show you exactly how the lines and columns will appear in the printed version. The process of creating an analysis report involves setting up a line template, a column template and an analysis report combining a line and column template together. There is one other component to the report analysis called analysis types which define the metrics to be measured. Ceres comes with default metrics; however, the reporting system is flexible to define new metrics. Defining new metrics requires a detailed understanding of the underlying database and is beyond the scope of this document.

Analysis Types

Analysis Types can be accessed by selecting “Search” from the Dynamics 365 Business Central Banner and entering “Analysis Types” in “Tell me what you want to do” then selecting “Analysis Types” from the results in the Go to Pages and Tasks section OR by selecting the “Analysis Types” menu action (if present) from your Role Center home page.

This will display a form containing the defined metrics for the system. These Analysis Types are normally setup when your system is implemented. If the Page is blank or the entries are deleted, it is possible to clicking Reset Default Analysis Types. If additional types have been created, they will not be removed when recreating defaults. However, if you have modified an existing default type, it will be reset back to its default settings.

Note: Ext. Gross Weight was added to the list of available Value Types to be measured allowing food banks to run analysis reports based on pounds. To use this feature, new analysis types need to be added to the default analysis types. Notice here we have added 3 new codes for use in our reports. These analysis types are not created by default and must be manually added to the list if you wish to analyze pounds in your reports.

If you need to add these Analysis Types to your database, select New to create a blank line and enter the fields as shown above.

Once you have added the first one, repeat the process by clicking new to add the subsequent lines.

Note: If you wish to add any additional types of metrics other than the 3-weight metrics shown in this document you will need to contact your support organization for help.

Analysis View Card Inventory

Inventory Analysis Views can be accessed by selecting “Search” from the Dynamics 365 Business Central Banner and entering “Inventory Analysis Views” in “Tell me what you want to do” then selecting “Inventory Analysis Views” from the results in the Go to Reports and Analysis section OR by selecting the “Inventory Analysis Views” menu action (if present) from your Role Center home page.

This will open the Analysis View List as shown here. You may define as many views as you wish. On any view you can specify up to 3 dimensions to be analyzed within the view and whether or not to include budgets for comparison purposes.

You may edit any existing view by clicking Edit. To create a new view, click New. Edit any existing view or creating a new view will launch the Invt. Analysis View Card shown here.

There are several fields on the view card that control the information available in the view. The following section describes these fields and how they are used:

General FastTab

Code: This field contains a code for the analysis view. The code can reflect the purpose of the analysis view or who will use it.

Name: Use this field to describe the analysis view.

Item Filter: This field contains a filter to specify the items that will be included in an analysis view. By setting filters, you also specify that only entries posted to the filter items will be included when an analysis view is updated. You must use the standard filter expressions.

Location Filter: This field contains a location filter to specify that only entries posted to a particular location are to be included in an analysis view. You must use the standard filter expressions.

Date Compression: This field contains the period that the program will combine entries for in order to create a single entry for that time period. By using date compression, you specify the level of detail for an analysis view. For example, if you want to analyze item information for an analysis view on a monthly basis, you can use date compression to sum all entries in a given month and create one single entry for the entire month. This parameter should be set to the smallest size you wish to analyze on a normal basis; otherwise, you will use database space unnecessarily. Remember, if you only need to only look at the analysis on a weekly basis, this should be set to week, because you can always change it to “Day” if needed and update manually to isolate a one-time potential problem and set the view back after determining the cause of the problem.

Starting Date: This field contains the date from which item ledger entries will be included in an analysis view. All entries posted on or after this date will be compressed to the level you select in the Date Compression field and included in the analysis view. The program will compress all item ledger entries posted prior to the date in this field into one entry and give them the date immediately before the starting date. This function can be used for database management to keep views from growing very large with very old data.

Last Date Updated: This field is maintained by the system and indicates the day the analysis view was last updated.

Last Entry No.: This field contains the number of the last item ledger entry you posted prior to updating the analysis view. If you have posted item ledger entries since you last updated the analysis view, the analysis view will not include these entries.

Last Budget Entry No.: This field contains the number of the last item budget entry you entered prior to updating the analysis view. If you have entered additional item budget entries since you last updated the analysis view, the analysis view will not be up to date.

Update On Posting: This field contains a toggle to indicate that the program automatically updates the analysis view every time you post an item ledger entry, for example from an agency invoice. The program will only update the analysis view with item ledger entries. To update with item budget entries, you must click Update in the Analysis View Card window or use the Update Analysis View batch job. If you have “Update On Posting” turned on for many views, you will experience a performance when posting daily transactions. To determine the best method of updating analysis views you need to determine how often and when the views are used. In general, if you only work with analysis views a few times a month and generally at month end, it would be best to update the views manually with the batch job. If users are using the views daily, then it would be best to have the update on posting option turned on. You may determine this on each individual view. Be sure to check the last updated date to determine if manual updating is required before accessing the view.

Blocked: This field contains a toggle to indicate that the analysis view is blocked so that it cannot be updated. Neither the Update on Posting function nor the Update Analysis View batch job can be used to update an analysis view while it is blocked.

Dimensions FastTab

Dimension 1, 2 & 3 Code: This field contains one of the three dimensions that you can include in an analysis view. The dimension allows you to filter entries in the Analysis by Dimensions window, which will allow you to investigate and monitor relationships between entries and the dimension information attached to them. The order in which the three analysis view dimensions are entered does not affect their characteristics or the way they are used.

Inventory Analysis Line Templates

Inventory Analysis Line Templates can be accessed by selecting “Search” from the Dynamics 365 Business Central Banner and entering “Inventory Analysis Line Templates in “Tell me what you want to do” then selecting “Inventory Analysis Line Templates” from the results in the Go to Pages and Tasks section OR by selecting the “Inventory. Analysis Line Templates” menu action (if present) from your Role Center home page.

The Line Template form will open and display any defined line templates. You may add as many additional line templates as necessary keeping in mind that line templates and column templates are re-useable. This means that you can create many different analysis reports by making different combinations of line and column templates.

Click on New to create a new line template. Below is a description of the fields:

Name: This field contains the name of the analysis line template. The name must be unique – you cannot have the same name twice on one table.

Description: This field contains a description of the analysis line template. The description makes it easier to identify the purpose of a specific analysis line template when you have several of them.

Item Analysis View Code: This field contains the name of the analysis view that the analysis report is based on. If this field is blank, the analysis report will be based on item ledger entries and item value entries.

Click on Lines to open the Inventory Analysis Lines Page where you can define the items to be analyzed in the report. Below is an example of the Inventory Analysis Lines Page and a description of the fields contained on the page.

Row Ref. No.: This field contains a row reference number for the analysis line. The row reference numbers are optional and are only used to create formulas. They have no relation to other number series in the program. If you use the same row reference number on more than one line, the rows will be treated as a group. For example, if you include the row reference number in a formula in the Totaling field, it will represent the sum of all the lines that have that row reference number. If you use Insert Items, this field is automatically copied from the No. field in the item list.

Description: Enter a description of the line. If you use Insert Items, this field is automatically copied from the Description field in the item list. If you are grouping several items together or creating a total, you can enter a description of the group or total.

Type: This field contains the type that you want the program to total. You can select one of the following options: item, item group, or formula. The program uses the contents of this field combined with the Range field to calculate the total for the analysis line.

Range: This field contains the number or formula of the type that you want the program to use to calculate the total for on this line. If you use Insert Items, this field is automatically copied from the No. field in the item list.

Dimension 1, 2 & 3 Totaling: This field shows the dimension value amounts that are totaled on this line. If the type on the line is Formula, this field must be blank. Also, if you do not want the amounts on the line to be filtered by dimensions, this field must be blank. If the Item Analysis View Name field in the Analysis Line Template window is blank, this field can contain dimension values for Global Dimension 1. When you use a dimension value code interval, all amounts for the dimension values within the range you define (for example, 1900..2100 inclusive) will be totaled.

New Page: A check in this field indicates that you wish a page break after this line prints.

Show: Enter “Yes” in this field to indicate that you wish this field to print on the report. Entering “No” will suppress this line from the report but it can still be used in calculations and formulas.

Bold: A check in this field indicates that this line should be shown in Bold print on the report.

Italic: A check in this field indicates that this line should be shown in Italic print on the report.

Underline: A check in this field indicates that this line should be shown Underlined on the report.

Show Opposite Sign: This field contains a check mark if you want sales or negative adjustments to be shown in reports as positive amounts and purchases or positive adjustments to be shown as negative amounts (that is, with a minus sign).

Functions from the Inventory Analysis Lines Page

There are various functions you can use to modify the inventory analysis lines to insert items, insert item groups, renumber lines and delete line. These can all be accessed from Actions/Functions.

Deleting a line

To delete a line in the template, select the line in the Inventory Analysis Lines form that you wish to delete by clicking anywhere on the line and selecting Manage à Delete. You can also select the three vertical dots on the line and select delete.

Ceres will prompt you to confirm the deletion action, choose “Yes” to delete the line or No to cancel the deletion action.

Insert an Item or Several Items:

Choose Actions à Functions à Insert Items.

Next the item list will open, and you choose one or more items to add to the list. Use standard windows conventions to choose from the list. Left click on any one item you wish. To choose a range of items, left-click on the 1^st item from the list and use shift-left-click to choose the last item in the list. To choose multiple items that are not listed together, you can use CTRL-left-click to choose individual items from the list. Once you have made your selections, click OK in the bottom right of the item list to bring the items onto the item analysis lines.

Notice that the selected items now appear in the Inventory Analysis Lines.

Note: Item groups are only available if you have setup a dimension for item groups and entered that dimension on the Dimension FastTab of the Inventory Setup Page. Each Item Card should be assigned to the proper Item Group Dimension value that it pertains to. If this is not setup you will receive an error message when trying to insert item groups.

Inserting Item Groups

Item groups can be useful for inventory analysis purposes to analyze groups of items instead of individual items. There are many reasons to analyze by group including items that are sporadic. In this way you can group them together into a meaningful group to analyze trends over time. As an example, you may receive many different types of cereals over time and utilizing item groups will allow for the analysis of all the different cereals on one line so that performance of cereals can be viewed without the need to total up many items. To insert an item group, select Actions à Functions à Insert Item Groups… from the Inventory Analysis Lines Page.

Next the dimension value list will open, and you choose one or more groups to add to the list. Use standard windows conventions to choose from the list. Left click on any one group you wish. To choose a range of groups, left-click on the 1^st group from the list and use shift-left-click to choose the last group in the list. To choose multiple groups that are not listed together, you can use CTRL-left-click to choose individual groups from the list. Once you have made your selections, click OK in the bottom right of the dimension list to bring the groups onto the item analysis lines.

Notice that the group selected now appears in the Inventory Analysis Lines.

Renumber Lines

First you must highlight the lines you wish to renumber. You can do this by using standard window conventions to choose multiple lines from a list. Next, click Actions à Functions à Renumber Lines.

Next a renumbering box will appear, and you must fill in the starting row number and then press OK to continue with the renumbering. Here we have entered 100 as the new starting row reference for the lines selected.

After pressing OK, if the renumbering is successful, you will see the newly renumbered Inventory Analysis Lines.

Inventory Analysis Column Templates

Inventory Analysis Column Templates can be accessed by selecting “Search” from the Dynamics 365 Business Central Banner and entering “Inventory Analysis Column Templates” in “Tell me what you want to do” then selecting “Inventory Analysis Column Templates” from the results in the Go to Pages and Tasks section OR by selecting the “Inventory Analysis Column Templates” menu action (if present) from your Role Center home page.

The Column Template Page will open and display any defined column templates. You may add as many additional column templates as necessary keeping in mind that line templates and column templates are re-useable. This means that you can create many different analysis reports by making different combinations of line and column templates.

Select New to create a new Column Template.

Name: This field contains the name of the analysis column template. The name must be unique – you cannot have the same name twice on one table.

Description: This field contains a description of the analysis column template. The description makes it easier to identify the purpose of a specific analysis column template when you have several of them.

Then select Columns to open the Inventory Analysis Columns Page where you can define the columns to appear on the report.

Below is an example of the Inventory Analysis Columns Page and a description of the fields.

Column No.: This field contains a number for the analysis column. The column numbers are optional and are only used to create formulas. They have no relation to other number series in the program. You can use the same column number on more than one line. The columns will then be treated as a group. For example, if you include the column number in a formula, it will represent the sum of all the lines that share the column number.

Column Header: This field contains a header for the column as you want it to appear on printed reports. The program will print the header on reports that include this column.

Invoiced: This field is toggled on, if you want the analysis report to be based on invoiced amounts. If you leave this field off, the analysis report will be based on expected amounts.

Column Type: This field contains the column type for the analysis column. The type determines how the amounts in the column are calculated.

Ledger Entry Type: This field contains the type of ledger entries that will be included in the amounts in the analysis column. The options are:

Item Entries -The program calculates the total from item ledger entries or item value entries.

Item Budget Entries - The program calculates the total from item budget entries.

Formula: This field contains a formula. The result of the formula will appear in the column when the analysis report is printed. The formula in this field can include other columns (referenced by their column number) so that the program can perform calculations on other columns.

You can use the following symbols:

+ (addition)

- (subtraction)

* (multiplication)

/ (division)

^ (exponentiation)

() (parentheses)

Show Opposite Sign: This field is a toggled on if you want purchases or positive adjustments to be shown in reports as negative amounts (that is, with a minus sign) and sales or negative adjustments to be shown as positive amounts.

Comparison Date Formula: This field contains a date formula that specifies which dates should be used to calculate the amount in this column. The program uses this formula to calculate the amount from the comparison period in relation to the period represented by the date filter on the report request.

For example:

-1Y

 means same period 1 year earlier.

Analysis Type Code: This field specifies the analysis type that you want the program to apply to the column. When you select an analysis type for a column, the program automatically fills in the Item Ledger Entry Type Filter, Value Entry Type Filter and the Value Type fields.

Value Type: This field contains the value type that the program uses to calculate the column from. The options are:

• Blank
• Quantity
• Sales Amount
• Cost Amount
• Unit Cost
• Ext. Gross Weight
• Non-Inventoriable Amount
• Unit Price
• Standard Cost
• Indirect Cost

Note: If you have selected one of the program’s default analysis types in the Analysis Type field, the program will fill in this field automatically.

Show: This field indicates when you want the amounts in the column to be shown in reports and in the Analysis Report window. A maximum of five columns in an analysis column template can be shown on printed reports. The options are:

Always - All amounts in the column will always be shown.

Never - The column will never appear on reports. The column is used only for calculation purposes.

When Positive - Only positive amounts will be shown in the column. (That is, amounts that are positive before Show Opposite Sign is applied.).

When Negative - Only negative amounts will be shown in the column. (That is, amounts that are negative before Show Opposite Sign is applied.).

Rounding Factor: This field contains a rounding factor for the amounts in the column.

The amount on reports will be rounded. For example, if you select 1000, then all amounts will be shown in thousands. Amounts in the Analysis Report window will not be rounded unless you select a rounding factor on the Options FastTab in the Analysis Report window.

Value Entry Type Filter: This field contains the filter that the program applies to the item value entry type that you want this column to be calculated from.

You can, for example, enter the filter ‘<>Transfers’ to specify, that Item Transfers should not be included in the calculation in this column. If you enter the filter ‘=Sales,’ only Item Value Entries that are derived from a sale will be included in your analysis report.

If you have selected one of the program’s default analysis types in the Analysis Type field, the program will fill in this field automatically.

To insert a new column

Select the three-dot vertical line and select New Line from the options.

To delete an existing column

Select the three-dot vertical line and select Delete Line from the options.

The program will prompt you to confirm the deletion. Choose Yes to confirm and delete the record or choose No to cancel the deletion.

Inventory Analysis Reports

Inventory analysis reports are the result of combining an inventory analysis line template with an inventory analysis column template to create the desired report. In addition to the columns and lines you may supply filters on date ranges, dimensions, etc. The reports may be viewed on the screen on a matrix form, printed, or exported to Excel.

Inventory Analysis Reports can be accessed by selecting “Search” from the Dynamics 365 Business Central Banner and entering “Inventory Analysis Reports” in “Tell me what you want to do” then selecting “Inventory Analysis Reports” from the results in the Go to Reports and Analysis section OR by selecting the “Inventory Analysis Reports” menu action (if present) from your Role Center home page.

Ceres will open a list of all defined reports in the system. Here is a sample listing and a description of the fields on the page.

Name: This field contains the name of the analysis report. The name must be unique – you cannot have the same name twice on one table.

Description: This field contains a description of the analysis report. The description makes it easier to identify the purpose of a specific analysis report when you have several of them.

Analysis Line Template Name: This field contains the analysis line template name for this analysis report. The program uses the lines defined in this template when you view the analysis report in the Analysis Report window.

Analysis Column Template Code: This field contains the column template name for this analysis report. The program uses the columns defined in this template when you view the analysis report in the Analysis Report window.

Select Edit Analysis Report to view the report information.

The Inventory Analysis Report Page will be displayed. Below is a sample and a description of each of the fields on the Page.

General Fast Tab

Analysis Report Name: Select the name of the analysis report to be shown in the window. To see the existing analysis report names, click the AssistButton to the right of the field. When you have selected an analysis report name, the program will fill in the Analysis Line Template field and the Analysis Column Template fields.

Note: After you have chosen a report name, you can change line and column templates independently and then later select the report name again to restore the original combination.

Analysis Line Template: If you have filled in the Analysis Report Name field, this field will be filled in automatically, but you can change it. Select the analysis line template to be shown in the window. To see the existing analysis line templates, click the AssistButton to the right of the field.

Analysis Column Template: If you have filled in the Analysis Report Name field, this field will be filled in automatically, but you can change it. Select the analysis column template to be shown in the window. To see the existing analysis column templates, click the AssistButton to the right of the field.

Filters Fast Tab

Source Type Filter: If you want to filter your Analysis Report by Item no., customer no. or vendor no., you must choose the source type in this field. Available source types are Item, Customer and Vendor. When you have chosen the source type, you must enter the filter in the Source No. Filter field.

Source No. Filter: If you want to filter your Analysis Report by Item no., customer no. or vendor no., you must enter the filter in this field. Depending on the source type filter you have chosen in the Source Type Filter field, you can enter a filter for either Items, Customers or Vendors in this field.

Note: Notice that the filters you specify in the Source Type Filter and Source No. Filter fields are only applied to the values in the matrix form rather than the lines of the report. For example, if you have customers as analysis lines, then you may want to select item as source type and a specific item number or item range as a filter to only see the analysis figures for the customers filtered on the selected item (item range). The source type and source no. filters will not work properly if they are in conflict with the analysis lines you have chosen for your report. For example, if you have set up an analysis report with customers on the lines and then enter a filter for a specific customer, the program will still show all customers on the lines, but the values will be calculated for the customer you specified in the filter.

Matrix Options Fast Tab

View By: This field determines the period of which to view the data by. The available choices are:

• Day
• Week
• Month
• Quarter
• Year
• Accounting Period

Column Set: This field controls which columns are shown in the matrix. It is not editable.

Display Report Information

To view the report information after you have made your filter selections and option choices, select Show Matrix.

Below is an example of the generated report.

You may expand the filter table and change the filter criteria if desired.

To return to the Inventory Analysis Report Edit Page click on the back button in the top left corner of the Page.

On the Inventory Analysis Report Page there are several options. Select Related à Actions and choose one of the options.

• Set Up Lines
• Set Up Columns
• Export to Excel

Set Up Lines

Opens the current Inventory Analysis Lies Page where you can make changes to the line template setup.

Set Up Columns

Open the current column template page where you can make changes to the column template setup.

Export To Excel

When exporting from Excel, you can choose to Create a New Document or Update Existing Document. This option opens a dialog box where you can specify the Excel Spreadsheet to create.

If you select Create New Document, an Excel that can be opened will be downloaded. You can save the document and then add to it by selecting Update Existing Document.

To Print The Report

Select Related à Reports à Print.

The report dialog will display where you can choose to alter the filtering and options for the report. You can choose a different column template if necessary, change date filtering, or apply dimension filtering.

Below is an example of the printed report.