Purpose
This procedure allows you to create User Sales Report Templates which can be used for Sales Analysis. User Sales Reports allow you to nominate what is to appear in the columns of a report when you want to see details differently to the standard sales reports provided in the system. These can be Sales reports that you may require on a regular basis, or just a once-off sales report. This routine allows you to enter and maintain the Sales User Formatted Reports. These User Formatted Sales Reports allow you to represent 25 months worth of product/customer sales history in a report format of your design. There is no limit to the number of User Sales templates you can create using this function.
Once you have set up a User Sales Report Template, use User Sales Reports in the Sales Analysis reporting area to run the report.
Data Input
Step 1 – Report Code
Enter a new, unique report code, that does not already exist on the system in order to create a new template. You may select an existing report code for editing via the code or by using the search functions provided.
For details on the use of “codes”, see The Role of “Codes” in Online Advantage
Return to the Top
Step 2 – Data Period
Select the basic period type for reporting. The system provides for either weekly or monthly templates to be created. Currently the system supports the creation of report templates using one of the following:
- 25 months rolling sales – allowing reports to include the current month, the current month from last year, and the current month from the previous year to last year
- 104 weeks rolling sales – allowing reports to include the current week, the current week from last year, and/or all the weeks across the current and last year
Step 3 – Details
Report Heading
Enter the report heading for this report template. This heading is automatically printed at the start/top of each page of the report for identification.
For more details on descriptions, see The Role of “Names & Descriptions” in Online Advantage
Zero Replacement Character
Enter a replacement character for the number zero. This is an optional function which allows you to specify replacement character(s) for any figure that would normally show on the report as zero i.e. ’0′
Characters such as ‘*’ or ‘-’ may be entered to replace any field with a zero value. A maximum of three characters is allowed for zero replacement so that the word ‘Nil’ can also be used.
Minus Sign Position
Select the position of the ‘minus’ sign i.e. the ‘-’ sign. This entry will determine how values with a negative value will appear on the report. A setting of ‘Left’ will display a minus sign to the left of the value e.g. -999 whilst ‘Right’ will display a minus sign to the right of the value e.g. 999-
Selecting the ‘Bracket’ option will surround the value with brackets e.g. (999). Note that this is a mandatory entry with ‘Bracket’ being the default option for templates.
Scaling Factor
Enter a scaling factor here that allows reports to be produced with all the financial figures rounded up to your specified factor. This entry must be numeric. An example would be ’1000′ to provide a report rounded in thousands. Note that the scaling used here is across all columns on the report containing financial figures, but the number of decimal places can be overridden for each column on the report if desired in the ‘Column’ section to follow.
No Decimal Places
Select the default number of decimal places to be shown on the report – either ‘None’, ‘One’ or ‘Two’. Note that the number of decimal places can be overridden for each column on the report if desired in the ‘Column’ section to follow.
Include All Budget Details
Select this option if you wish to include sales budget details on the report template. This option is only available for a Monthly “Data Period” report as sales budgets are entered and held as monthly figures in the Sales Analysis system.
Return to the Top
Step 4 – Columns
The ‘Column’ section of the template allows you to specify how many columns you want on your report, and the details to be printed in each of the columns.
Note that whilst the maximum number of columns on the report is unlimited, when viewing in fixed page output formats like Acrobat PDF you will only be able to see the columns that fit within a single page width. The same will apply when sending the report to print to a network printer or direct printer, all of which have a ‘fixed’ width. The use of other output formats like Excel, CSV and Text files allows all the columns on your report template to appears since they only have a theoretical maximum width.
Click the ‘Add’ button to add a report column to the list of existing columns on your template. You may edit existing column details by clicking on the columns listed. You can also click the delete icon to remove the report column details from the list.
Type
Select the source of information to appear in this column.
- Figure – specifies that the column is to show a sales figure held in the Sales Analysis database
- Figure Ascend – same information as ‘Figure’ but sorted in ascending value i.e highest to lowest. Note that all rows on the report will be sorted by the figures in this column
- Figure Descend – same information as ‘Figure Ascend’ but sorted in descending value i.e. lowest to highest. Note that all rows on the report will be sorted by the figures in this column
- Hidden – same information as ‘Figure’ but the column is to be hidden from the report view. The reason for providing a ‘Hidden’ column is to allow you to use sales figures in calculation columns without actually showing the figures on the report
- Calculation – indicates that the column will show the results of a calculation you specify based on the details/results in other columns on the report template
- Calculation Ascend – same information as ‘Calculation’ but sorted in ascending value. Note that all rows on the report will be sorted by the figures in this column
- Calculation Descend – same information as ‘Calculation’ but sorted in descending value. Note that all rows on the report will be sorted by the figures in this column
- Blank – used for spacing or grouping columns allowing you to insert a ‘blank’ column
Type: Figure or Figure Ascend or Figure Descend or Hidden
Column Heading
Enter the heading text to be shown on the report for this column. This heading will be printed at the top of each column at the start of each page.
To insert actual months as each Month end rolls there is a special naming convention i.e. MY-n, F-n, or LF-n. Where MY is Month/Year, F is Fiscal period and LF is Last Year Fiscal period.
For example, if the column represents sales figures from the current month, typing MY-1 in the heading will cause the report generator to print the month and year of the current month as the column heading. MY-2 would print as the month name of the previous month and so on. MY-13 would show the current month of last year. You can include up to 25 months.
No. Decimal Places
You are allowed to adjust the number of decimal places specific to this column. Select the number of decimal places to be displayed for the data in this column from either ‘Zero’, ‘One’ or ‘Two’.
Font
Select the size of font to be displayed on the report; Small, Medium or Large. The appropriate choice is really determined to how many columns you are trying to get across a “page” on a printed report or document. The more columns you attempt to fit in, the smaller the font you might require on some/all of the columns.
Figure Type
Select the sales figure ‘type’ you want to have in this column. There are three types of sales figures being:
- Quantity – show the Quantity figure in sales units
- Cost – shows the Cost of Sale Value recorded
- Value – shows the Sale Value recorded
Sales Type
Select the sales type you want that relates to the “Figure Type” for this column. The available types are listed and explained below:
- Normal Sales Only – All Sales transactions (invoices) processed that are not marked as special
- Returns Only – All returns transactions (credit notes) processed
- Special Sales Only – Any sale transaction item (invoice item) marked with the “Special Sales” flag during processing
- Gross Sales – “Normal Sales” + “Special Sales”
- Total Sales – “Gross Sales” – “Returns”
- Budgets – allows you to report on Budgeted Sales & Cost of Sales using the Margin budget. Note that there is no Quantity entered for Budgets
Months
This option is available if you have selected to report on “Monthly” data periods. You can select the months you wish to include in this column of the report. Thus each column can be defined for different periods e.g. a month, a quarter, a full years worth etc.
There are a number of ways to select the months to include in a column. We have provided easy to use check-boxes so a user can select “This” or “Last Year” (YTD), or pick from the “Fiscal Months” or “Historical Months” available. Note: for the “Historical Months” it uses 1 for the current month, 2 the previous and so forth. We’re providing 25 months history so that you can write reports that show this month this year, this month last year, and this month the previous year to last.
Weeks
This option is available if you have selected to report on “Weekly” data periods. Select the weeks you wish to include in this column of the report template e.g. Current week, Last week, Current week last year etc.
As with months, there are a number of ways to select the weeks to include in a column. We have provided an easy to use check-box for “Current Week” and “Last Week”. When used in combination with the “Corresponding week last year” option these become “Current Week Last Year” and “Last week Last Year”. Obviously writing a report template using those options means whenever you run it you will always get the Current and Last weeks as time moves on. Other options provided for week selection are:
Current Minus ‘X’ weeks – where ‘X’ is entered by you on each column as a way of counting back from the current week. Basically giving a “-5” input to get 5 weeks ago as a column.
Specific Weeks – provides a way to enter the weeks you want to appear as numbers. i.e. ‘1’ will use week 1’s figures. ’50’ indicates the template will get week 50’s figures. Multiple weeks can also be listed at this prompt with a space in-between each week and the system will then add the entered weeks figures together during a report run e.g. 1 2 3 4 5 6 7 8 9 10 11 12 (to accumulate the last 12 weeks figures). Note that the entry of the letter ‘C’ stands for ‘Current Week’ and ‘L’ for ‘Last Week’ as an alternative to using the week numbers.
Specific Weeks Last Year – as with the ‘Specific Weeks’ function above, the inputs are the same here but the number ‘1’ indicates week one from last year, whilst ’14’ means week fourteen from last year etc.
Selections
You may tie the template column into specific data at this step. It is an entirely optional step, but is does give the template “author” the ability to establish very specific rules about what “data” is included or excluded for the column. All the “elements” listed as being available to report on can be used here to filter the data used to accumalte the figures for each column.
With the ‘Figure’ and ‘Hidden’ column types, the option exists to add in additional ‘Selections’ to be used when picking the figures from the sales history. This is essentially a way of adding filters on the specific column so that only figures that match the criteria will be shown. For example, if you always want the report to be run for certain sales reps, you can enter those reps here. You could have different reps on each column of a report, or put in selections for two or more reps to combine their figures for this report column. You can even mix different selection types on a columns filter e.g. specify one rep and two branches. Any combination of selections is possible, as long as it is logical to the figures appearing in the given column. The selection types available for use on columns are as follows:
- Buying Group
- Customer Branch
- Customer Category
- Customer Division
- Customer
- Head Office Customer/Account
- Issuing Branch
- Market Segment (Current On Customer)
- Market Segment (On Transaction)
- Sales Rep (Current On Customer)
- Sales Rep (On Transaction)
- Product Division
- Product Category
- Product Group
- Product Class
- Product Material
- Product Section
- Product Type
- Model
- Product
Column to Copy
There is even a handy “copy” tool so you can copy these rules from other columns to save having to set them up each time. Using these column based selections a user can create reports where each column then represents a “Branch”, or “Buying Group”, or “Sales Rep”, or multiples of each. Even combinations of these can be written per column.
For example, if you ware making a report specifically relating to a set of divisions, you can fix the selection of divisions here instead of at the time of running the report.
Type: Calculation or Calculation Ascend or Calculation Descend
Formula
Input the formula to be used for the column to calculate the figure to appear on the report. A formula must be entered for calculation columns. The formula is entered in the following basic sequence: ‘Value’ ‘Operator’ ‘Value’ ‘Operator’ ‘Value’ etc. A ‘Value’ can either be a reference to another column on the report, or a ‘Constant’. You can enter as many values as required to complete your formula. Each ‘Value’ must be separated by an ‘Operator’.
An ‘Operator’ is a mathematical symbol. The following operators are available:
- + : Adds two values together
- – : Subtracts the second value from the first
- * : Multiplies the two values together
- / : Divides the first value by the second
- > : If the first value is greater than the second, the first value will be retained for further calculations. Otherwise, zero will be used for further calculations
- < : If the first value is less than the second, the first value will be retained for further calculations. Otherwise, zero will be used for further calculations
- % : The first value will be expressed as a percentage of the second value
NOTE: You cannot have an operator on the end of a formula.
Each item entered in the formula must be separated by a space if typed in. The report will perform all calculations in the formula from left to right.
Formula Build Tools
There are ‘Formula Build Tools’ that can be used to help you enter or complete a formula. They provide all the basic options normally used in calculation columns. Simply select the ‘column’ you wish to use in your formula and click the ‘Add Column to Formula’ button. This will include the column for calculation into the formula field above.
Alternatively input the ‘constant’ you want to use in the formula (if necessary), and click the ‘Add Constant to Formula’ button. This will include the constant into the formula field above.
You may then click on the operator to be used after entering a ‘Value’ which will automatically include the operator into the formula field. Once an operator has been applied to two values, it is the result of that calculation that is used by the next operator. You can then add ‘Values’ and ‘Operators’ to the formula, in sequence as required.
Formula Examples
Some examples of valid column formula that may be in report templates:
Example 1
1 + 2 + 3
Will add columns 1 and 2 and 3 together to provide a quarterly figure. Note this could also be achieved with a ‘Figure’ column by selecting the 3 months to be used to get the same result.
Example 2
1 * “1.1”
Which will multiply column 1 (e.g. Sales Value) by a constant of 1.1 (i.e. inflating the figures by 10 percent)
Example 3
1 – 2 % 1
This will subtract column 2 (say Branch “X”) from column 1 (say Branch “Y”) and the result will be expressed as a percentage of column 1. In other words it will calculate the difference between branches “X” and “Y” as a percent of branch “Y”‘s figures
Type: Blank
Select this type to include a blank column on the report template.
Step 5 – Consolidation
Consolidation Group
Select the consolidation group you would like to add to the report. The generated report will be sorted in sequence by each data element added as a consolidation level. Sub-totals will also be shown for each “unique” item in the consolidation group within the sequence of the other consolidation groups listed on the report template. Below is the full list of “Consolidation Groups” available for use:
- Buying Group
- Customer Branch
- Customer Category
- Customer Division
- Customer
- Head Office Customer/Account
- Issuing Branch
- Market Segment (Current On Customer)
- Market Segment (On Transaction)
- Sales Rep (Current On Customer)
- Sales Rep (On Transaction)
- Product Division
- Product Category
- Product Group
- Product Class
- Product Material
- Product Section
- Product Type
- Model
- Product
Page Break
Enable/select whether the selected consolidation group is to start a new page for each “unique” data item included on the report.
Click Add to add the consolidation details to the list.
You may edit the details by clicking the details on the list.
Click the delete icon to delete the consolidation details from the list.