Producing reports using stock-tracker
here do I access the reports section within Stock-Tracker?
The report section of Stock-Tracker can be accessed via the Accounts tab by clicking Reports->Report Viewer.
What type of reports are available in Stock-Tracker?
Stock-Tracker includes a series of powerful reporting tools that can extract and present information about any of the data you may store on the system. However, for ease, when you first access the reports section of the system you are presented with a number of different ‘report categories’ and these are based on the most frequent types of reports that customers produce using the system.
These categories are:
This category contains all of the fields / information relating to products and prices that are contained within the catalogues you have set up. This is ideal for viewing and comparing pricing etc.
This category contains all of the fields / information relating to addresses which are contained within Stock-Tracker (including customer, supplier and other addresses such as warehouse addresses). This is ideal for creating an export for use in mailshots or for viewing the quality of your data.
This category contains all of the fields / information relating to your customers.
This category contains all fields / information relating to invoices (one invoice per line) and is ideal for creating invoice/credit and turnover reports.
This category contains all of the fields / information relating to your products and is ideal for reviewing the quality of your product data.
This category contains all of the fields / information relating to the elements that make up invoices and credit notes (for example sale of stock). This report is ideal for comparing products invoiced by customer date or other parameters.
Sales Order Lines
This category contains all of the fields / information relating to the elements that make up sales and includes sales that are not yet invoiced including quotes, orders on hold etc. This report is based on the date a sale was made rather than when it was invoiced.
This category contains all fields / information relating to sales (one sale per line) and is ideal for creating sales based reports. This report includes sales that are not yet invoiced including quotes, orders on hold etc and is based on the date a sale was made rather than when it was invoiced.
This category contains fields / information relating to all stock that has EVER been processed via Stock-Tracker and is ideal for producing standard stock reports as well as analysing previous stock movements.
How do I create a new report?
To create a new report simply click the file tab from within the reports section and then click ‘New Report’.
You will then be asked to select the type of data you require within the report.
When you have selected the report category you will be presented with a ‘split screen’.
The top part of this screen includes a number of quick use buttons (including Refresh / Save / Export etc) along with a number of editable fields into which the report name and description can be entered. This section also includes a dropdown ‘Data’ field and it is from this menu that the data used in the report can be limited by date range or pre-set parameters which are significant for the report category you have selected.
The lower half of this screen includes a preview of the report itself and this preview will only include the past 50 records on the system incorporating this data. It will, however, show all of the data fields (columns) for your report.
Where do I see the information that can be included in my report?
When you first create a report you will be presented with a preview of the information that can be included within the report category you have chosen. By scrolling across the bottom of the report you will be able to view all of the available data fields (columns). These can also be viewed by clicking the ‘Select Columns’ tab on the right hand side of the screen.
How do I add / remove columns (data fields from my report)?
To remove a column (data field) from your report, expand the column listing on the far right then simply click on the header of the column and drag it to the empty space above the report:
Alternatively click on the ‘Select columns’ tab at the side of the page and tick the relevant box to remove this field from the report. You can also select / de-select all data fields using this section by right clicking over the box.
What do the different prefixes mean?
The columns / data fields within the report builder contain differing pre-fixes to help organise the data. Always followed by a field name (i.e. the type of data within the relevant column), these pre-fixes can be summarised as follows:
CP = Catalogue Product
CUS = Customer
ADD = Address
PRD = Product
SOL = Sales Order Line
SO = Sales Order
PK = Pick Note
SI = Stock Item
What does a report preview show?
When you first create a report you will be presented with a preview of the report and the data it can contain. This preview will limit the data it is reporting to the first 50 entries. By limiting the data presented the ‘Preview’ screen is an excellent place to quickly build your report and check the data fields / columns are to your liking before applying the settings to a more complete set of data.
What happens to my report when it is saved?
A summary of all saved reports can be found on the reports summary screen under the relevant report category heading. Reports will be saved with the names and descriptions allocated to them during the report set up process.
How do I filter the information within the report builder?
The report builder allows you to easily extract the information you require from a report by allowing the data in each data field / column to be filtered in a variety of different ways.
To filter the information go to the column header which contains the information you need to filter and press the filter button.
When you have clicked this button a drop down menu will appear. Within this menu are a number of options which when clicked will filter the data in the data field / column:
(All) – This option will display all of the data within the selected data field / column
(Custom) – This option will allow you to filter the data within the data field / column in a wide variety of editable way.
The pre-set conditions within the custom drop down menu allow you, for example, to filter the data according to the data values, its contents and also by comparison with other data fields / columns.
As a simple example the filter criteria below are set to filter the ‘default stock location’ to show locations that contain the letters e ‘and’ h. By ticking ‘or conditions’ this would change the filter to show those locations that contain e ‘or’ h.
A more complex example of a custom filter would be one which compares the values of two data sets using the value based filters such as Equals, Does not equal, Less than etc.
(Blanks) – Filters the data to show all blank fields within a data field / column
(NonBlanks) – Filters the data to show all fields which include data within a data field / column
The filter facility within the report builder is also ‘multi-level’ this means that you can filter multiple data fields / columns within one report allowing you to drill down within the data to the level you require. For example, you can filter the report to show a certain customer, then a certain product type and then the range of prices they have paid for the product.
Different filter parameters can also be set within the same column.
How do I sort the information within the report builder?
To sort the data within a data field / column simply click on the header of the relevant column. This will sort the data alphabetically or numerically. By holding down shift and clicking on further data fields / columns you are able to sort more multiple columns.
What does the grouping function of the report builder do?
Alongside the filter and sort functions within the report builder data can also be organised by grouping it according to the information within a data field / column.
To group data in this way simply click on the column header and drag the header into the space above the header.
When you have dragged the column header into this space you will see that your report instantly reorganises itself according to the data within the column you have dragged upwards. In the example below the information within the data fields has been grouped according to the catalogue name.
When the information has been grouped you are then able to expand each set of grouped information to show the other data fields / columns within the report:
As with the other functionality within the report builder, data can be grouped on multiple levels by simply dragging additional column headers into the required space. In the example below we have grouped the data according to supplier name and nit cost (in the example we have opened up the dataset based on a unit cost of 0.624).
To remove the grouping of data from a report simply drag the column header back alongside the other column headers.
What is the summary feature on report builder?
As a standard feature within report builder you can also create summary information relating to any numeric data within the data fields / columns. To summarise information simply click on the sigma symbol contained within the column header.
You will then be presented with a number of different summary options:
Average – This is an average of the data contained within the data field / column
Count – This is the number of rows within the selected data field / column
Maximum – This is the highest numeric value within the selected data field / column
Minimum – This is the lowest numeric value within the selected data field / column
Sum – This is the sum (total) of all of the numeric values within the selected data field / column
The results of the summary are displayed at the bottom of each column.
To remove a summary from a column simply untick the relevant box within the summary drop down menu.
Am I able to create charts showing the data in my reports?
Yes, the report builder allows you to create graphical representations of differing sets of data contained within your reports. To create a chart you first need to identify the sets of data you wish to include. To do this you should first group the main set of data by dragging the data field / column header into the space above the header.
You should then select your second data set and create the required summary of this data field / column.
In the example below we have firstly grouped together the default supplier name data field / column and then we have created an average unit cost per supplier name using the average tool within the summary function.
Before charting this information we need to create an overall summary of these two data sets and we do this by clicking the ‘Generate Summary’ quick use button at the top of the page. By clicking this button we create a table which summarises the information we want to chart.
Finally before creating a chart we need to sort the data in the table according to how we would like this to appear in the chart. In the example shown above we would like the chart to show the average unit cost per supplier with the average order value rising on the chart. We have therefore clicked the data field / column header to sort the relevant information in this way.
Once we have sorted the data we are ready to create our chart and do so by clicking on the ‘Generate chart’ quick use button at the top of the page.
After clicking the generate chart button we are presented with a pop up that asks us to select:
- The data we require on the ‘Y’ axis of the chart
- The number of the highest values we want to show on the chart
- The number of the lowest values we want to show on the chart
To show all of the values in our summary table then simply leave the values as ‘0’ within these fields.
By clicking create chart on this pop up we are then taken to a chart which has been generated using the data fields we have grouped together and summarised.
We are also able to vary the type of chart by choosing from the various options within the chart type dropdown menus.
How do I export my report?
Once you have created a report you can export this as a CSV file, Excel spreadsheet or PDF document. Simply select your desired export type from the quick use buttons at the top of the report builder screen.
Please note that a report will be exported in exactly the same format as you have built the report on screen.