ANALYTICS FOR SPIRE USER MANUAL

Download the Spire User Manual PDF - Here

Goals and Objectives of this document

All businesses are different. These examples may or not apply in all businesses. Where possible these examples demonstrate a concept that is generic enough to apply in any business. For example, an example using warehousing may not apply to your company but the mechanism for using this field likely applies to many other fields used in similar ways.

We are going to work primarily in the Sales Analysis to show you how to:

1. Configure and Customize
2. Build, save and load layouts
3. Simple and Complex filtering
4. Explain standard fields
5. Explain special fields
6. Build Custom Fields
7. Sort
8. Group and Summarize
9. Show/Hide Columns
10. Resize
11. Drag/Drop
12. Drill Down
13. Graph
14. Chart
15. Print
16. Export

Application Launch

You can launch Analytics for Spire from the shortcut that gets created when you install the application or from the program shortcut off your Start button. The executable is in C:\Program Files (x86)\Gemini Logic\Analytics for Spire. By default, Analytics stores layouts in C:\ProgramData\AnalyticsForSpire.              

After you launch Analytics the company selection window will appear. The listing will be the same companies that are shown on your Spire selection list. Select the company you want to use and enter your User Credentials.

Analytics will validate your logon credentials against your Spire Settings and will allow access only to those modules that are explicitly granted in Spire. Modules that are not available to you will be greyed out. For example, you may have a SALESREP ID that does not allow access to AR, AP or GL in Spire as such those modules will be greyed out Analytics.   This ensures that the permissions you carefully crafted in Spire are extended to Analytics.

Where do I start?

One of the great things about Analytics is that you can start wherever you want, and you should be able to quickly explore trends, patterns and opportunities in your company data. How do you start? It’s quite simple.

1. Choose the appropriate module – See chart below for a description of each module
2. Decide if you want to see summary or detailed information or filtered data in one of the existing report formats – See chart below for available options.
3. Set an initial date filter range. Start and End Date.
4. Press the “Get Data” button.
5. For detail grids, a variety of default columns are provided. You can add and remove columns by dragging them from the column chooser into the grid.
6. For summary grids, select fields to display in the header, column and data areas. Some defaults are provided to help with your initial data visualization. These are just defaults that can be changed to meet your specific requirements (multiple versions of inquiries can be saved as layouts).
7. Custom tab allows you to create You can add new fields to the grid
8. Inventory, Customer and Vendor Details do not require a date filter range. The Optional “More Criteria” is available.

We are going to describe the Details Grid in Sales Analysis and the Summary Grid in Sales Analysis. The concepts described will apply to the other modules while the field selection will be different.

Data Source/ Spire Modules	Analytics Tab	SummaryGrid	Detail Grid	Reports	Speciality
Sales History, Inventory, Customers, Related UDF’s	Sales Analysis	Yes	Yes	Yes
Purchase History, Inventory, Vendors, Related UDF’s	Purchase Analysis	Yes	Yes	Yes
Sales, Inventory, Customers, Related UDF’s	Sales Orders	Yes	Yes	Yes
Purchase Orders Inventory, Vendors, Related UDF’s	Purchase Orders	Yes	Yes	Yes
Inventory, Related UDF’s	Inventory	Yes	Yes	Yes
Customers, Related UDF’s	Customers		Yes	Yes
Vendors, Related UDF’s	Vendors		Yes	Yes
Inventory Receipts, Inventory, Vendor, Related UDF’s	Inventory Receipts	Yes	Yes	Yes
General Ledger	General Ledger	Yes	Yes
Payroll, Employees	Payroll	Yes	Yes
AR, Customer, Related UDF’s	Accounts Receivable	Yes	Yes
AP, Vendor, Related UDF’s	Accounts Payable	Yes	Yes
Job Costing	Job Costing	Yes	Yes
ALL	Custom	Yes	Yes		Create your own SQL statement from virtually ANY SPIRE TABLE and use the Summary and Details Grid and all of its functionality to Analyse your own Custom Query.

Details Grid using Sales Analysis - START POINT

Establishing an initial filter range –DATE RANGE

1. RESET LAYOUT - -This is a best practice – OPTIONAL STEP. When you hit the reset layout button it removes all the columns from the grid and populates the grid with default layout. It also loads all the user defined fields from the company you are currently signed into. This is recommended if you are jumping between companies and you want to start with a clean slate in your analysis.
2. START DATE and END DATE – <REQUIRED STEP> You also have the option of selecting a preset criterion of ranges: Today, Calendar Current Week, Calendar Current Month, Calendar Current Year, Fiscal Current Year, Calendar Current Quarter, Fiscal Current Quarter. Example below encompasses two years of data.
3. CLEAR ALL FILTERS – This is an OPTIONAL STEP. This clears any filter that are in play.
4. GET DATA - <REQUIRED STEP> This will retrieve all the data based on your From and To Range. A progress indicator is displayed so that you know the application is building the data set for your analysis.

Please note that this initial filter (FROM AND TO) simply prepares the initial data set. You can filter information further using the filter tools in the grid explained later in this document. The filters are available once the data is loaded in the grid. The conditional criterion is much more elaborate than those offered in your accounting system (such as ands/ors/begins with, contains) and can use multiple columns and conditions, and can filter on fields not displayed in the grid.

ANATOMY OF THE DETAILS GRID DESCRIBED

The blue bubble displays the record count of the data set returned based on your start and end date selection.

Distinct Columns are defined by the fields you choose or a loaded layout. These are stated in your Row Title Header.

A Quick Filter Row appears below the Row Title Header (a description of filtering is described later)

Each Row is defined as a Detailed Record

Footer Summaries – Choice of any or all of Total, Min, Max, Average or Count. Summary Totals are based on the prevailing filter applied to your grid.

Prevailing Filter of your Grid – Identifies the filter currently in play based on your filter defined in ‘Show Filter’ in Tools.

Construct is very similar to a standard Spreadsheet

With Detailed Analysis….

Each record builds from a “main” record and pulls in detailed information

Many fields available to display

Columns can be sorted (with multiple sort columns)

Supports multiple level grouping

Automatic totals at header level

Automatic totals at group summary level              

Filtering

On the Fly Filters - Simple Filters – DETAILS GRID

Technique 1 – Use the column filter

On each column header a filter icon appears in the top right corner. Clicking on that icon reveals the distinct values that appear in that column. When you select one or multiple items, Analytics automatically filters your records by the selection. The filter you have constructed appears at the bottom left of the grid. This type of filter is an “equal to” filter. In the example below the distinct values are where EQUIP-Equipment and GAM-Games is the product code details of the invoice line details.

• This simple filtering technique does not change the number of records in your dataset (your first step to Get Data). This type of filter as all filtering described below returns the pertinent records to the grid. This is best demonstrated by adding a record count summary in your footer and comparing that number to the dataset number (the number in the blue bubble). The footer will count the rows based on your filtered criteria whereas the dataset number will display the total number of records returned based on your date range and hitting the get data button. In the example below 1,293 records is your data set number whereas 226 (footer value in product code details) are the number of rows in the grid based on your constituted simple filter (bottom left).

Technique 2 – Use the row filter line

To add a quick filter, enter a value in the quick filter line. This is done from the blank row below the column header.

The filter condition is dependent on the type of field. If it’s a text field <as noted by the abc marking in the quick filer row> the criterion is containing. In the example below the filter is contains WP in the part number field. If the field type is a pick list such as product code details the criterion is equals to. If the field is numeric such as Cmt. Qty. the criterion is equals to, greater than or less than. If the field is a date type such as Invoice Date the criterion is equals to.

• This quick filtering technique does not change the number of records in your dataset (your first step to Get Data). This type of filter as all filtering described below returns the pertinent records to the grid. This is best demonstrated by adding a record count summary in your footer and comparing that number to the dataset number (the number in the blue bubble). The footer will count the rows based on your filtered criteria whereas the dataset number will display the total number of records returned based on your date range and hitting the get data button. In the example above 1,293 records are your data set number whereas 188 (footer value in part number) are the number of rows in the grid based on your constituted simple filter (bottom left).

Filter Editor – Adding multiple line criterion - Complex Filters ALL GRIDS

Complex filtering provides a higher level of control when constructing your filter. The complex filter also provides a wider range of conditional criterion along with some sophisticated capabilities to group. While simple filtering quickly helps you build a filter using equals to, greater than, less than and contains; it is best used when your selection is based on one or two fields and perhaps a handful of distinct values.  

You can get quite elaborate with complex filters and remember they save with layouts, so you don’t have to perform the filter repeatedly.

The explanation with the complex filter applies to both the summary and details grid.

If your field type is text or numeric you could use starts with, ends with, is any of, is less than or greater than, (see screen cap below for the full list)

If your field type is a date. You have some of what is listed above plus an evaluation of the date field looking forward and backwards such as Is next week, Is prior to this year. (see screen cap below for the full list)

To start building your filter. Hit the Show Filter button under Tools. The filter Editor window will open where you can now compose your filter.

If we want to isolate your records to Accessory Type products where we sold more than 10 units at a time between January 1st and April 30th, 2015. That can be easily done by building out a filter with the editor.

1. With the Filter editor open, hit the plus symbol to the right of the word And  
2. Click on the square bracket area where the list of available fields will be listed and select Product Code Details
3. Click on conditional operator to the right of the field name and choose Begins with
4. Click in the value area and enter ACC (the first 3 characters of the Accessory Product Code)
5. Our first line of our filter should be set as below

To add our second condition of where we shipped more than 10 units at a time. We would repeat steps 1 through 4 with the following changes.

Step 2 select Cmt. Qty   ----- Step 3 choose is greater than or equal to ---- Step 4 enter 10 –Result is below

To add our third condition of sales between January 1st and April 30th, 2015. We would repeat steps 1 through 4 with the following changes

Step 2 select Invoice Date ----- step 3 choose Is Between ---- step 4 enter or navigate to 01/01/2015 in the first value text box and enter or navigate to 04/30/2015

Our fully composed filter should look like… Hit Apply to apply the filter and keep the window open and OK to apply and close the window.

While you are composing your filter, you can hit apply at any time to see the result set on the grid.

Our grid now appears as shown below. Our composed filter is at the bottom left and parts of it are described in the quick filter. As stated earlier, the quick filter can only filter based on equals to, greater than, less than, and the contains operator. That’s why we don’t see the construct of the invoice date between 01/01/2015 and 04/30/2015. The Filter Editor allows us to use that operator and whole lot more. It is a very powerful and useful tool and the one we recommend you use to build your filter.               

Filter Editor - Adding more complicated conditions into a filter - Complex Filters ALL GRIDS

Let’s say you need to create a filter that’s a bit more complex. For this example, let’s say “show me every time we sold where the sell price total was greater than $500, and the product code is ACC or WEIGHTS.

It would seem the way to do this is to add a filter like this;

The problem is that the result of this condition will yield no records. Why? Because this condition is saying “show me all records where the sell price total is greater than 500 AND the product code equals ACC and the product code equals WEIGHTS. An inventory item in Spire can only have one product code so all records will always fail to qualify for this filter.

The proper way to set this up is to use GROUPINGS with AND/OR Conditioning.

Step 1 – First apply the common condition across the product groups. Specifically Sell Price Total is greater than $500

Step 2 – Then Add a Group – Click on the word And then click on Add Group          

Step 3: Then add another group to facilitate the OR condition by clicking on the word And in the newly created group;

Step 4: Now change the last groups condition to “Or” by clicking on the word And. Then add your product codes. The fully constructed filter should look like this. Hit Apply and OK. What it’s saying is “show sales details where a sales event was greater than 500 AND the product code was ACC OR WEIGHT.

Details Grid - Sorting and Grouping

You can sort these grids in sophisticated ways. The trick to sorting is to remember two things;

• – Holding down the shift key while clicking on a column header allows you to sort by more than one column at a time.
• – The order you click the columns determines the order of the sort.

In the example below, we clicked “Customer details” then “Sell Price Total (twice)” and “Part No.” while holding the shift key.

This means “sort by customer – within customer by Sell Price Total in descending order then part number”.

Remember also that this works in conjunction with grouping. In other words, you can drag a column (say customer details) to the header and still sort within the columns.  There are also some useful totals that can be viewed on the header and the footer of each distinct group. The next section will describe these totals.               

Details Grid - Different kinds of Totals

Detail grids are often overlooked for their usefulness in analyzing data. One of the things that makes them useful is that you can add totals in multiple areas of the detail report.

Adding footer totals

You can add totals to the footer of any detail report by right clicking in the footer of a column and picking the type of total you want. “Sum” is often the most useful type of total but you can also specify averages, counts, minimums and maximums. When you right click on a footer column a context menu appears allowing you to select the summary attribute of your choice. The ‘Add New Summary’ selection at the top of this menu are the available summary choices for your grouping.

Add group summary totals

Group summaries are another area where you can add useful information. To do this, right click on any column that is used for grouping in the group header.

Using the group summary editor, you can add additional metric into the row displayed when items are grouped. If you find the grouping too busy, you can also remove these metric (some are provided automatically by default).

In the example below, we choose the Current Cost field and the summary attribute Average. We also choose Unit Price and selected the minimum and maximum unit price amount.

Group Header modified….

Field Chooser (details grid) and Show Field Chooser (summary grid) – Available Fields

A wide range of fields are available to choose from in the both grids. In the details grid you will find the full selection of fields under Tools à “Show field chooser”.

It’s here where you can search and add fields to your grid. Double clicking on any field will append the field to the grid. Available are the user defined fields (udf), standard fields, special fields and custom fields. Udf’s sort to the top and are bracketed prefixed with the word udf.

Below is a brief description of the type of fields available. The full description of fields in some of the modules is available in the appendix of this document.

Concatenated Fields as Details

There are many code fields offered in Analytics. The code and the description of the code are available as independent fields. Code and description also appear together as a single field for example “Customer Details” contains the Customer Number and the Customer Name.

Current Fields

Sometimes it’s important to look at records based on the current code. When fields such as Sales Department, Email, Product Code, Telephone, Territory, Salesperson or any related addressing fields end in (Cur) or “Current”; the value returned is the code on the Inventory or Customer or Vendor Master (i.e. Province Current or Product Code Current).  

When those same field names do not have Current in them the value is the code on the sales order or invoice or purchase order (i.e. Province or Product Code etc.…).

The need for this distinction might be because a sales person is assigned a new customer and existing orders and historical invoices may have different codes. However, it’s important to look at all this information from the perspective of who is currently responsible for that customer relationship. Additionally, inventory items may be reassigned product codes and there might be a desire to look at historic sales in this reclassified state.

Address Fields

Bill to and Ship to Address line information is included in both the sales and purchasing modules.

The current state of the bill to and ship to customer is best identified by fields ending in (Cur) and starting with ‘Bill’ for bill to customer and starting with ‘Ship’ for ship to customers. i.e. Bill Address 1 (Cur) is the current first line of the Billing Customer. i.e. Ship Address 1 (Cur) us the current first line of the Ship to Customer

The address at the time of posting is prefixed by Inv. i.e. Inv Bill Address 1 is the first line of the Billing Customer at the time the invoice was posted (for Sales Analysis). i.e. Inv Ship Address 1 is the first line of the Ship to Customer at the time the invoice was posted (for Sales Analysis).

Date Fields

Order Date, Required Date, Invoice Date, AP Date, AR Date, PO Date, Due Date and Transaction Date are all available in d-Analyzer. In the Summary Grid these date fields are parsed in intervals that allow you to group by Day, Week, Month, Quarter and Year. Fiscal Period, quarter and year are also available.

Currency Fields- Costs, Profit and Sell Price

Spire maintains two costs and Analytics presents these and uses them both for margin calculations.

So, which should you consider using? It isn’t as though one cost is correct and the other is not. You need to understand what you are getting when you use one or the other.

Current cost is the current cost of an item (sometime called replacement cost). Business people understand this number well because it is generally the last cost paid for an item (and most likely price paid for items in the future). Context matter when you are looking at current cost. In the context of sales history and sales order reports current cost is the cost current at the time of the sale. In the context of inventory, current cost is generally the last cost price paid for an inventory item.

If prices usually rise over time, the current cost price will generally be higher than the average cost. If you are performing sales history reports for commission for example – you might choose to use current cost rather than average cost for this reason.

Average cost is the weighted average cost of inventory. This value is maintained by constantly averaging costs, considering the existing inventory quantities and costs. This is the cost used for postings. Average cost is accurate in the sense that it properly represents the value of inventory over time. Where this sometimes confuses business, people are that average cost considers what was paid historically for inventory. Average cost says that inventory value is what you paid for it. Current cost is saying that inventory value is what the item is “worth” (what it would cost to replenish or replace inventory).

Profit calculations are presented using both costs. (i.e. Average Profit Total = Sell Price Total – Average Cost Total or Current Profit Total = Sell Price Total – Current Cost Total).

Total Sell Price factors in all possible discounts that took place at the time of invoicing

Retail Price is the price before a line discount is factored, also known as the “usual price”. Unit Price includes the line discount.

If working in a multi-currency environment Sell Price Total, Average Cost total Frn., Average Profit total Frn., Current Cost total Frn., Current Profit total Frn., coupled with the foreign currency field will show you your sales, costs and profit in the currency of transaction.  

In a multi-currency environment, to see the above-noted values in the converted base currency use Sell Price Total Base, Average Cost Total, Average Profit Total, Current Cost Total and Current Profit Total.

User Defined Fields (udf’s)

Analytics now supports user defined fields (udf’s). You have all the capabilities of the standard and special fields with udf’s. Meaning they can be reported on, they can be used in custom fields, sorted on, grouped on and summarized.               

Custom Fields - ALL GRIDS – Summary and Detail

While a large selection of standard and special fields is available to you (including udf’s) sometimes there is a need to create your own custom fields. Analytics provides you with the utility to create those fields. They are like Excel formula fields in that they operate on existing data columns and perform string functions, math functions and many more. There are a virtually unlimited number of available options when you consider that these formulas not only contain dozens of functions, but can be applied with conditional logic, between more than one field at a time and are calculated at runtime.

These fields then offer the same utility and function as any standard field notably they can be reported on, they can be used in other custom field formula’s, sorted on, grouped on and summarized. Below are some examples of some custom fields.

Building a Custom Field – Start here and follow the Steps

From the left navigation panel. Select Tools Then Add Custom field

Simple Expressions - Substring

In this example, we are going to assume that the first letter of the Part Number represents a classification of your parts. As such we will create a string custom field called Part Classification that will extract the first character of the part number.

Step 1 – The Custom Fields window opens. A list of existing custom fields is listed. To create a field, Click New Custom field

Step 2 - Complete the Properties window and hit Expression editor

Enter a field Name – this is how Analytics stores the field

Enter the Caption – this is how the field is presented to you. It will be available from the field list

Select the data type – String is text, Boolean is a checkbox (True or False), DateTime is date and time, Decimal is numeric with decimal places, integer is a whole number, currency is currency type

Step 3 – Build your expression with the Expression editor. The three text boxes at the bottom of the window are described.

1. The left panel window allows you to choose a predefined function, operator, field or Constant.
2. The middle window is the related choices based on your selection.
3. The right window is some instructional information on how to build your expression. In the example we have chosen the Substring Function to return the first character of the part no. field.
4. We did that by double clicking on the function. The substring function appears on the top panel window.

Step 4 – Build your custom formula – use the instructions in the right window to assist you with this operation.

1. String = field name enclosed with square brackets. Delete the single quotes as the quotes represent a fixed value. We want to pass the value of the part number. As such position your mouse in place of the quote then select [Part No.] field by double clicking it. It will automatically appear where your cursor was positioned.
2. Complete the rest of the parameters. Startposition = 0 <in the computer world the start position is noted as point 0> Length = 1 <as we only want to return the first character, but you can return any number of characters>. Then press OK. Analytics validates your formula. If there is a problem the error message will identify the starting point of the error and a brief explanation of the error.

The completed formula is            

Substring([Part No.], 0, 1)

Step 5 – Hit Apply to save your work and Save to Save and close the Window. The new custom field Part Classification is now available. Analytics will automatically add it to the grid

Simple expressions - Formulas

Here is a simple expression showing a proposed price at a 50% markup over current cost. This was done in the inventory module. In our Boolean example (explained later in this document), we use this custom field to derive another custom field.

The formula uses the Round function out two decimal places.

           ROUND([Cur. Cost]*1.50,2)

…and a new column is created.

Complex expressions with conditions

You can get really elaborate with expressions. We have seen how these can be basic math expressions, but they can also be embedded into decisions (If statements). Here is an example of a commission type calculation that groups territories into groups based on the commission they charge. This is a formula that works in Sales Analysis Details.

Iif([Territory] == 'FS1' || [Territory] == 'DC1' || [Territory] == 'DC2' ||

[Territory] == 'RR1' || [Territory] == 'WA1', Round([Sell Price Total] * .05, 2),

Iif([Territory] == 'AB2' || [Territory] == 'AB1', Round([Sell Price Total] * .06, 2),

Iif([Territory] == 'CD1', Round([Sell Price Total] * .07, 2),

Iif([Territory] == 'HA1', Round([Sell Price Total] * .08, 2),

Iif([Territory] == 'OC1', Round([Sell Price Total] * .09, 2),

Iif([Territory] == 'CK1', Round([Sell Price Total] * .10, 2),

Iif([Territory] == 'TC1', Round([Sell Price Total] * .11, 2), 0)))))))

A second expression field displays the effective commission for each line to make the report a bit clearer.

Iif([Territory] == 'FS1' || [Territory] == 'DC1' || [Territory] == 'DC2' ||

[Territory] == 'RR1' || [Territory] == 'WA1', '5%',

Iif([Territory] == 'AB2' || [Territory] == 'AB1', '6%',

Iif([Territory] == 'CD1', '7%',

Iif([Territory] == 'HA1', '8%',

Iif([Territory] == 'OC1', '9%',

Iif([Territory] == 'CK1', '10%',

Iif([Territory] == 'TC1', '11%', 'None')))))))

You simply add a decimal field and text field and paste in the expressions. The results look like this;          

Complex expressions – Boolean and filtering

Often you want to use an expression to target in on records of concern. These might be sale records where the margin is to low, PO records where the cost is too high or for a myriad of other things.

In this example we use our custom field called proposed price and then compare it to the existing price. Next, we are creating a Boolean field and then filtering on this. This example works in the Inventory Module.

Now we apply a filter to show only those prices that are less than the proposed price.             

Summary Grid using Sales Analysis - START POINT

Establishing an initial filter range – Start Date and End Date - DATE RANGE

1. RESET LAYOUT - -This is a best practice – OPTIONAL STEP. When you hit the reset layout button it removes all the row, column and data field from the grid and populates the grid with the default layout. It also loads all the user defined fields from the company you are currently signed into. This is recommended if you are jumping between companies and you want to start with a clean slate in your analysis.
2. FROM AND TO Date Range – <REQUIRED STEP> You also have the option of selecting a preset criterion of ranges (circled in red): Today, Calendar Current Week, Calendar Current Month, Calendar Current Year, Fiscal Current Year, Calendar Current Quarter, Fiscal Current Quarter. Example above encompasses two years of data.
3. GET DATA - <REQUIRED STEP> This will retrieve all the data based on your From and To Range. A progress indicator is displayed so that you know the application is building the data set for your analysis.

Establishing an initial filter range – Start Date and End Date- with a SECONDARY DATE RANGE

A Secondary Date Range is useful when comparing two like periods. It is most commonly used when comparing your sales year to date to the previous year to date. In the example below, we will use the secondary date range function to return invoice details between January 1st through January 22nd, 2016 and January 1st through January 22nd, 2015 ONLY.

You may ask yourself why not just return all data between January 1st, 2015 and January 22nd, 2016 and then filter on the fly to the month of January. The problem with that approach is that we will have all records for the month of January 2015 and only the 22 days in January 2016. As such, we are not doing an apple to apples comparison with regards to length of time. You could try and create a complex filter or create a custom field to capture the like periods, however; that is a lengthy and elaborate process. It’s in this regard the secondary date range is useful and very easy to use. Do the following in place of the above for defining a date range (Step 2 from above):

               

1. The Secondary date will be offset automatically by a year. As such, in the top Start date and End date text boxes set the more current range. In the example below, we start with the date range in 2016 (January 1st, 2016 – January 22nd, 2016).
2. Hit the Secondary date range button. This will automatically offset your primary range by a year. In the example below, Analytics has automatically populated the secondary date range as January 1st, 2015 – January 22nd, 2015.

Summary Grid - Explained

The Anatomy of a Summary Grid

Filter or Holding Area – This is where your commonly used fields are located. If you place them here, you can more easily drag and drop these fields into the grid.

Row Area – Are the selected fields where totals are returned. Think of them as sub-totals  

Column Area – Column fields where cross totals of for your row fields are shown.

Data Fields – The Blue upright capital Greek letter Sigma indicates that the field values here will be summed. Numeric fields that you want to subtotal and total. Field types are usually quantity or currency.

In the example below, the summary grid divides and subtotals sell price and commit quantity into rows and columns. First by the row fields of Province and then Customer Number and then divides each Province and Customer row into columns first by Invoice Year and then Invoice Month.

Construct is like a Pivot Table

If you click on the “Show Field List” button the Summary Grid Field List window appears (on the right). In the Summary Grid List, you can drag fields from the full field list to the Filter Area, Row Area, Column Area or the Data Area. The region where these fields appear on the Summary Grid are shown to the left and can be traced by following the black solid line. So as not to inadvertently drop a field into an unexpected spot it is recommended you use the Summary Grid Field List when you first start using d-Analyzer. The Summary Grid Field List is like the Pivot Table Field List in Excel.

If you don’t use the Summary Grid List you can drag and drop fields from the Filter/Holding Area to the column, row and data areas on the grid. The example below shows the drop zones and what fields have been dropped where.

The sample below is the result of the summary grid as defined above.  

Subtotals for sell price and commit quantity divide into Province and Customer Number rows and are further divided into columns first by Invoice Year and then Invoice Month. [The 2015 Year and Named months of the Year].

More ways to Filter

Quick Filter on Row and Column Values

A filter icon appears on the row and column fields that are present in your grid. Below you can see the distinct values that are available under Product Code.

To isolate and filter records on a handful of Product Code; simply check the box next to the value you want to see on the grid. In the example below EQUP, SUPP and WEIGHT are the only product code sales that we will see on the grid.

The filter is automatically composed based on your above selection (EQUIP, SUPP and WEIGHT) and appears at the bottom left of the grid

We can also filter on your column fields as well. The Invoice Date (Year) field is in the grid. To view only your 2015 sales; simply uncheck 2016.

The summary grid now appears based on your composed filter of the column and row selections.

If you want to change your simple filter after committing it to the grid, you should either use the edit prefilter to modify it OR delete it entirely and re-compose it based on your new criteria. Returning to the simple filter to check and uncheck values has some unintended consequences to your grid results, as such, we recommend deleting the filter or using the edit prefilter to modify. To delete click the X to the left of the checkmark on the Filter display:

The next section describes how to Edit the filter to facilitate a change.

Filter Editor - Complex Filter on Row and Column Values

Filter Editor - Modify an Existing Filter

The Edit Prefilter reveals the filter editor. If a filter is already in play you will see its composition

The filter editor is also available under Tools à Show Filter

The composed filter appears below. To add additional Product Codes hit the plus symbol and from the drop down select the additional code you want to see.

To add the GAM code select it

Then click Apply and OK to close the window.

Your result set on your grid now has the new code’s sales

Filter Editor - Add an Existing Filter

Pages 12 through 18 of this document describes in detail how to create a filter using the filter editor. Please refer to those pages for instructions and examples of usage as they apply to both the summary and detail grid.

Filter Editor - Add an Existing Filter – Compare one field value to another field value

A very powerful feature in the Filter Editor is the capability to compare one field value to another field value.

In an earlier section we described what it means to have the field current in its name. Salesperson current refers to the current salesperson assignment to the customer whereas salesperson is the salesperson code on the invoice record.

You could use the Filter Editor to compare invoices where the Salesperson current does not equal the salesperson. To do that follow these instructions

Launch the Filter Editor from Tools à Show filter

Hit the plus symbol and select the field Salesperson Current

Change the operator to Does not equal.

Then hit the pencil symbol <you will notice the tool tip in the screen cap below>

After hitting that pencil icon, the drop-down textbox appears where you can now select a field.

Now select the field name Salesperson

The fully constructed filter appears below. Press Apply and OK to save and close the filter editor window.

Our Summary grid now displays those customers where the current salesperson assignment does not equal the invoiced salesperson code. In our example, customer DUPO192 – Dupont Photographers has Salesperson JOHN currently assigned but there are invoices where there is no salesperson code present and where BARRY is the code on the invoice.

You could drill down into those values and see the related invoices. Then in Spire you could change the salesperson code on those invoices.

Filter on Summary Total – Show based on a Value Range

You may want to isolate your grid to values based on a range of values you see on the grid. To do that select the filter icon on your data field (Sell Price Total). The window below appears. When we enter the show values between 25,000 and 50,000 our grid will only reveal that range.

Grid results based on the summary range shown below.

Sorting in the summary grid

For the most part sorting within Analytics is what you see in other applications (you click on the column header to sort information). Click once sorts ascending, and clicking again reverses the sort. You can see this with the glyph indicator in the column heading.

You can even sort the data columns by clicking on the value in the column header.

Sometimes though you want to sort based on values (either in conjunction with or overriding sorts for rows and columns).  

Let’s say in the above example that you want to sort based on the 2015 totals (highest to lowest categories). Clicking on the column headers does not perform a sort (instead it selects the column values).

To sort you right click on the column header.

You also have the option to remove any sorts that are in affect.

You can also tell that a summary sort in effect based on the glyph displayed in the column header.  

How to use +/- fields

+/- fields are special fields that are used in conjunction with existing fields to show change (generally over time). These fields are available only in the summary grid.

Let’s say you want to show sales by product code over time. You could construct a grid like this;

This kind of grid is useful. There are two possibly problems here. Firstly, it’s not always easy to see if the progress over time is positive or negative (period to period). This is especially true of the numbers are close in value. Secondly, you don’t always want to reveal the actual values of sales by product code (even to your own staff).

You can insert a “+/-“ field (or even replace the existing sell price total field). You end up with a grid that looks like this;

Notice now that you are seeing difference values (rather than the values themselves). In other words you are seeing change over time. Positive numbers indicate the sales amount went up (relative to the preceding period). Negative numbers mean the number went down (relative to preceding periods).  

In addition to sell price total +/- fields are also available for current and average cost profit totals.          

How to use % fields

“%” type fields are another specialized kind of field. When used this field provides a “percentage of total” value for a column it relates to. For similar reasons to the above this can be very useful.

Specifically this field can help you rationalize numbers that might be otherwise difficult to understand. Take this example;

Here we have sorted product codes by percentage of sales (grand total). In summary the ranking is;

• – EQUIP
• – WEIGHT
• – ACC
• -- WARR

Notice though that the rankings change from year to year (In 2016 WARR has 0 sales). Knowing this information can be important. Is this a result of directed changes in the business? Is this a result of the effects of competition? Buying pattern changes in you clientele?  

Fitting lots of data on a page

Sometime you need to do analysis where the number of columns if large. When doing this analysis on screen you have the option of scrolling but often you need to persist this data (print it or store it for further later analysis).

There are two great options for this. The first is to send the data to Excel. You can do this in one click in Analytics by clicking the export button.

Another option is to print the document. The problem is that this can often (even in landscape) exceed the width of the screen.

You can account for this by changing the “zoom” factor.

SALES ANALYSIS DATA DICTIONARY

FieldName	Table	Analytics Field Name	Description
inventory_alt_part_no	inventory	Alternate part no.	Alternate Part No. of the Invoiced Line Item found on the Inventory Record
	ANALYTICS	Area Code	REVEALS NOTHING
sales_history_items_average_cost	sales_history_items	Avg. Cost	Base Average Cost of line item when Invoiced
	ANALYTICS	Avg. Cost Total	Base Average Cost Total (Avg. Cost * Cmt. Qty.) of line item when Invoiced
	ANALYTICS	Avg. Cost Total (+/-)	Base Average Cost Total difference between 2 columns to the left on the Summary Grid
	ANALYTICS	Avg. Gross Profit %	Gross Margin of Line Item at Average Cost Sold when Invoiced
	ANALYTICS	Avg. Cost Total (%)	Row total % shown on the Grid
	ANALYTICS	Avg. Cost Total Frn.	Currency of Transaction Average Cost Total of line item when Invoiced
	ANALYTICS	Avg. Profit Total	Base Profit Total using Average Cost of Line Item when Invoiced
	ANALYTICS	Avg. Profit Total (%)	Row total % shown on the Grid
	ANALYTICS	Avg. Profit Total (+/-)	Base Average Profit Total difference between 2 columns to the left on the Summary Grid
	ANALYTICS	Avg. Profit total %	Gross Margin of Line Item at Average Cost Sold when Invoiced
	ANALYTICS	Avg. Profit total frn.	Currency of Transaction Average Profit Total of line item when Invoiced
	ANALYTICS	Base Unit Price	Base Unit Price of line item when Invoiced
b_address1	b_address1	Bill Address 1 (Cur)	The current value of address line 1 found on the Addresses Tab (Billing Address) on the Customer Record against the Invoiced Customer
b_address2	b_address2	Bill Address 2 (Cur)	The current value of address line 2 found on the Addresses Tab (Billing Address) on the Customer Record against the Invoiced Customer
b_address3	b_address3	Bill Address 3 (Cur)	The current value of address line 3 found on the Addresses Tab (Billing Address) on the Customer Record against the Invoiced Customer
b_address4	b_address4	Bill Address 4 (Cur)	The current value of address line 4 found on the Addresses Tab (Billing Address) on the Customer Record against the Invoiced Customer
b_contact_email1	b_contact_email1	Bill Address Contact 1 Email (Cur)	The current value of Contact #1's Email found on the Addresses Tab (Billing Address) on the Customer Record against the Invoiced Customer
b_contact_name1	b_contact_name1	Bill Address Contact 1 Name (Cur)	The current value of Contact #1's Name found on the Addresses Tab (Billing Address) on the Customer Record against the Invoiced Customer
b_contact_phone1	b_contact_phone1	Bill Address Contact 1 Phone (Cur)	The current value of Contact #1's Phone found on the Addresses Tab (Billing Address) on the Customer Record against the Invoiced Customer
b_contact_email2	b_contact_email2	Bill Address Contact 2 Email (Cur)	The current value of Contact #2's Email found on the Addresses Tab (Billing Address) on the Customer Record against the Invoiced Customer
b_contact_name2	b_contact_name2	Bill Address Contact 2 Name (Cur)	The current value of Contact #2's Name found on the Addresses Tab (Billing Address) on the Customer Record against the Invoiced Customer
b_contact_phone2	b_contact_phone2	Bill Address Contact 2 Phone (Cur)	The current value of Contact #2's Phone found on the Addresses Tab (Billing Address) on the Customer Record against the Invoiced Customer
b_contact_email3	b_contact_email3	Bill Address Contact 3 Email (Cur)	The current value of Contact #3's Email found on the Addresses Tab (Billing Address) on the Customer Record against the Invoiced Customer
b_contact_name3	b_contact_name3	Bill Address Contact 3 Name (Cur)	The current value of Contact #3's Name found on the Addresses Tab (Billing Address) on the Customer Record against the Invoiced Customer
b_contact_phone3	b_contact_phone3	Bill Address Contact 3 Phone (Cur)	The current value of Contact #3's Phone found on the Addresses Tab (Billing Address) on the Customer Record against the Invoiced Customer
	ANALYTICS	BO Sell Price Total	Base Sell Price Total of Back Order Quantity of the line item when Invoiced
sales_history_items_backorder_qty	sales_history_items	BO. Qty	Back Order Quantity of line item when Invoiced
history_bill_address_city	history_bill_address	City	Bill to City on the Invoice Header
city	cust_bill_to_address	City Current	The current value of city found on the Addresses Tab (Billing Address) on the Customer Record against the Invoiced Customer
sales_history_items_committed_qty	sales_history_items	Cmt. Qty	Committed Quantity of line item when Invoiced
	ANALYTICS	Cmt. Qty (%)	Row total % shown on the Grid
	ANALYTICS	Cmt. Qty (+/-)	Committed Quantity difference between the 2 columns to the left on the Summary Grid
contact_email	cust_bill_to_address	Contact Email Current	SHOULD BE REMOVED ALREADY CONTACT 1 and EXPRESSED UNDER CURRENT EMAIL
history_bill_address_country_code	history_bill_address	Country	Bill to Country on the Invoice Header
country_code	cust_bill_to_address	Country Current	The current value of country found on the Billing Address of the Invoiced Customer
sales_history_currency_rate	sales_history	Cur. Rate	Currency Exchange Rate at time of Invoicing (not applicable if not using multi-currency)
sales_history_currency_rate_method	sales_history	Cur. Rate Mthd.	Operator for Currency Conversion (not applicable if not using multi-currency)
sales_history_items_current_cost	sales_history_items	Cur.Cost	Base Current Cost of line item when Invoiced
	ANALYTICS	Cur. Cost Total	Base Current Cost Total (Cur. Cost * Cmt. Qty) of line item when Invoiced
	ANALYTICS	Current Cost Total	Base Current Cost Total (Cur. Cost * Cmt. Qty) of line item when Invoiced
	ANALYTICS	Current Cost Total (%)	Row total % shown on the Grid
	ANALYTICS	Current Cost Total (+/-)	Base Current Cost Total difference between 2 columns to the left on the Summary Grid
	ANALYTICS	Cur. Gross Profit %	Gross Margin of Line Item at Current Cost Sold when invoiced
	ANALYTICS	Cur. Profit total %	Gross Margin of Line Item at Current Cost Sold when invoiced
	ANALYTICS	Cur. Cost Total Frn.	Currency of Transaction Current Cost Total when Invoiced
	ANALYTICS	Cur. Profit Total	Base Profit Total using Current Cost of Line Item when Invoiced
	ANALYTICS	Cur. Profit total frn.	Currency of Transaction Current Profit Total of line item when Invoiced
	ANALYTICS	Cur. Profit Total (%)	Row total % shown on the Grid
	ANALYTICS	Cur. Profit Total (+/-)	Base Current Profit Total difference between 2 columns to the left on the Summary Grid
sales_history_currency	sales_history	Currency	Currency code of transaction
sales_history_cust_no	sales_history	Cust No.	Customer No on the Invoice Header
sales_history_cust_po_no	sales_history	Cust PO. No.	Customer PO No on the Invoice Header
customers_credit_line	customers	Cust. Credit	Current Credit Limit found on the Billing Tab on the Customer Record against the Invoiced Customer
customers_hold	customers	Cust. Hold	Current Hold Status found on the Addresses Tab on the Customer Record against the Invoiced Customer
customers_notes	customers	Cust. Misc	Current Customer Miscellaneous field found on the General Tab on the Customer Record against the Invoiced Customer
customers_name	customers	Cust. Name	Current Customer Name on the Customer Record against the Invoiced Customer
sales_history_cust_name	sales_history	Cust. Name Inv.	Customer Name on the Invoice Header
customers_reference	customers	Cust. Ref	Current Reference field found on the General Tab on the Customer Record against the Invoiced Customer
customers_spec_handling	customers	Cust. Spec. Handling	Current Special Code field found on the General Tab on the Customer Record against the Invoiced Customer (returns a one-character value)
customers_status	customers	Cust. Status	Current Status Code field on the Customer Record against the Invoiced Customer
customers_website	customers	Cust. website	The current value of the website found on the Addresses Tab on the Customer Record against the Invoiced Customer
customers_misc1	customers	Cust.UserType	Current Miscellaneous field found on the General Tab on the Customer Record against the Invoiced Customer
	ANALYTICS	Customer Details	A concatenation of the Current Customer number and Customer Name on the Customer Record against the Invoiced Customer
dflt_whse	cust_bill_to_address	Def. Whse	Current Default Warehouse found on the Order Defaults Tab on the Customer Record against the Invoiced Customer
sales_history_items_discountable	sales_history_items	Discountable	Discountable flag (True or False) of the line item when Invoiced
ecommerce	cust_bill_to_address	Ecommerce	IN ADDRESS TABLE BUT NOT EVIDENT ON CUSTOMER RECORD
email	cust_bill_to_address	Email Current	The current value of the email found on the Addresses Tab on the Customer Record against the Invoiced Customer
sales_history_items_employee	sales_history_items	Employee	Employee referenced against the line item when Invoiced
	ANALYTICS	Fiscal Period	Fiscal Period of Invoice Date (sorted 01 through 13)
	ANALYTICS	Fiscal Quarter	Fiscal Quarter of Invoice Date (sorted Q1 through Q4)
	ANALYTICS	Fiscal Year	Fiscal Year of Invoice Date
sales_history_fob	sales_history	FOB	FOB field on the Invoice Header
sales_history_trans_no	sales_history	GL Transaction	GL Transaction number against the Invoice
inventory_average_cost	inventory	Inv Avg Cost current	Current Base Average Cost of the Invoiced Line Item found on the Details Tab of the Inventory Record
h_b_address1	h_b_address1	Inv Bill Address 1	Bill to - Address Line 1 on the Invoice Header
h_b_address2	h_b_address2	Inv Bill Address 2	Bill to - Address Line 2 on the Invoice Header
h_b_address3	h_b_address3	Inv Bill Address 3	Bill to - Address Line 3 on the Invoice Header
h_b_address4	h_b_address4	Inv Bill Address 4	Bill to - Address Line 4 on the Invoice Header
sales_history_contact_name	sales_history	Inv Contact	Bill to - Contact #1's Name on the Invoice Header
sales_history_contact_email	sales_history	Inv Contact email	Bill to - Contact #1's Email on the Invoice Header
sales_history_contact_fax	sales_history	Inv Contact Fax	Bill to - Contact #1's Fax on the Invoice Header
sales_history_contact_phone	sales_history	Inv Contact Phone	Bill to - Contact #1's Phone on the Invoice Header
inventory_current_cost	inventory	Inv Current cost current	Current Base Current Cost of the Invoiced Line Item found on the Details Tab of the Inventory Record
inventory_description	inventory	Inv Desc. Current	Current Inventory Description of the Invoiced Line Item found on the Inventory Record
inventory_duty_perc	inventory	Inv Duty Pct.	Current Duty Percentage that will be used on a Purchase Order of the Invoiced Line Item found on the Details Tab of the Inventory Record
sales_history_location	sales_history	Inv Locn.	Location/Warehouse of the Invoiced Line Item
inventory_lot_numbered	inventory	Inv Lot Numbered	Current Inventory Lot Tracking Status of the Invoiced Line Item found on the Details Tab of the Inventory Record (True if under Lot control)
history_bill_address_postal_zip	history_bill_address	Inv Postal	Bill to Postal Code on the Invoice Header
sales_history_profit_centre	sales_history	Inv Profit center	Profit Centre on the Main Tab of the Invoice Header
history_bill_address_slspsn_name	history_bill_address	Inv Salesperson name	Salesperson's Name on the Main Tab of the Invoice Header
history_ship_address_ship_code	history_ship_address	Inv Ship Addr. Code	USING SHIP VIA CODE and NOT SHIP TO NAME
h_s_address1	h_s_address1	Inv Ship Address 1	Ship to - Address Line 1 on the Invoice Header
h_s_address2	h_s_address2	Inv Ship Address 2	Ship to - Address Line 2 on the Invoice Header
h_s_address3	h_s_address3	Inv Ship Address 3	Ship to - Address Line 3 on the Invoice Header
h_s_address4	h_s_address4	Inv Ship Address 4	Ship to - Address Line 4 on the Invoice Header
sales_history_ship_carrier	sales_history	Inv Ship Carrier	Carrier Info Name on the Info Tab of the Invoice Header
sales_history_ship_date	sales_history	Inv Ship date	Ship Date on the Info Tab of the Invoice Header
history_bill_address_ship_code	history_bill_address	Inv Ship Mthd	Ship Via Code on the Main Tab of the Invoice Header
history_ship_address_sales_person	history_ship_address	Inv Ship Salesperson	Salesperson Code on the Main Tab of the Invoice Header as chosen from with the Ship to Customer
history_ship_address_slspsn_name	history_ship_address	Inv Ship Salesperson Name	Salesperson Name on the Main Tab of the Invoice Header as chosen with the Ship to Customer
inventory_std_cost	inventory	Inv Std Cost	Current Standard Cost of the Invoiced Line Item found on the Details Tab of the Inventory Record
history_bill_address_sales_terr	history_bill_address	Inv Terr.	Territory Code on the Main Tab of the Invoice Header
inventory_type	inventory	Inv Type	Current Inventory Type of the Invoiced Line Item found on the Details Tab of the Inventory Record
sales_history_phase_id	sales_history	Inv. Phase ID.	Last Phase ID on the Phase Tab of the Invoice Header
inventory_preferred_vendor	inventory	Inv. Preferred Vendor	Current Primary Vendor of the Invoiced Line Item found on the Details Tab of the Inventory Record
history_bill_address_sales_person	history_bill_address	Inv. Salesperson	Salesperson Code on the Main Tab of the Invoice Header as chosen from the Bill to Customer
history_ship_address_prov_state	history_ship_address	Inv. Shipto Prov	Ship to - Province Code on the Invoice Header
history_ship_address_sales_terr	history_ship_address	Inv. Shipto Terr	Territory Code on the Main Tab of the Invoice Header as chosen from with the Ship to Customer
history_ship_address_sales_terr_desc	history_ship_address	Inv. Shipto Terr Desc.	Territory Name on the Main Tab of the Invoice Header as chosen with the Ship to Customer
history_bill_address_sales_terr_desc	history_bill_address	Inv. Terr Desc.	Territory Name on the Main Tab of the Invoice Header as chosen with the Bill to Customer
sales_history_job_acct_no	sales_history	Invc. Job Acct.	Job Cost Account No. on the Jobs Tab of the Invoice Header
sales_history_job_no	sales_history	Invc. Job num.	Job Cost Job No. on the Jobs Tab of the Invoice Header
inventory_rental_description	inventory	Invent. Rental Desc.	Current Inventory Rental Description of the Invoiced Item on the Inventory Record
inventory_rental_part_no	inventory	Invent. Rental Part	Current Inventory Rental Part of the Invoiced Item on the Inventory Record
inventory_rental_whse	inventory	Invent. Rental Whse	Current Inventory Rental Warehouse of the Invoiced Item on the Inventory Record
sales_history_invoice_date	sales_history	Invoice date	Invoice Date and Time on the Invoice Header
sales_history_invoice_no	sales_history	Invoice. No.	Invoice Number on the Invoice Header
sales_history_invoiced_date	sales_history	Invoiced date	Just the Invoice Date on the Invoice Header
	ANALYTICS	Invoice Date Text	Unformatted Invoice Date on the Invoice Header
	ANALYTICS	Invoice Date (Day)	Invoice Calendar Day of the Week (Monday through Friday) based on the Invoice Date on the Invoice Header
	ANALYTICS	Invoice Date (Week)	Invoice Calendar Week of the Year (1 through 52) based on the Invoice Date on the Invoice Header
	ANALYTICS	Invoice Date (Month)	Invoice Calendar Month of the Year (January through December) based on the Invoice Date on the Invoice Header
	ANALYTICS	Invoice Date (Quarter)	Invoice Calendar Quarter of the Year (1 through 4) based on the Invoice Date on the Invoice Header
	ANALYTICS	Invoice Date (Day)	Invoice Calendar Year based on the Invoice Date on the Invoice Header
sales_history_items_comments	sales_history_items	Item comment	THIS DOES NOT COME ACROSS and PROBABLY SHOULD NOT
sales_history_items_description	sales_history_items	Item Desc.	Description of the Invoiced Line Item on the Details Grid
sales_history_items_job_acct_no	sales_history_items	Item Job Acct.	Job Cost Account No. of the Invoiced Line Item on the Details Grid
sales_history_items_job_no	sales_history_items	Item Job num.	Job Cost Job No. of the Invoiced Line Item on the Details Grid
sales_history_items_whse_location	sales_history_items	Item Locn	Warehouse or Location of the Invoiced Line Item on the Details Grid
sales_history_items_notes	sales_history_items	Item Note	Memo of the Invoiced Line Item on the Details Grid
sales_history_items_po_number	sales_history_items	Item PO Num.	PO Number from Requisition of the Invoiced Line Item on the Details Grid
sales_history_items_ref_no	sales_history_items	Item Ref.	Reference of the Invoiced Line Item on the Details Grid
sales_history_items_required_date	sales_history_items	Item Req Date	Required Date of the Invoiced Line Item on the Details Grid