Transaction based reports are accessed by choosing the Report > Transactions… menu item or by clicking on the Transactions item in the Transactions section on the Reports tab of the Navigator:
All these reports (and there are many!) summarise supplier or customer transactions in many different formats. Many different filters have been incorporated, allowing filtering on transactions, names, prescribers, items, categories and departments. The filters that do not apply to different reports are hidden as required. Feel free to mix and match and experiment with the filters and different reports to see the different views of information that you can get.
The basic sequence for creating a transaction report is this:
See below for more in-depth detail on all these steps.
Choose from several formats for displaying transaction information by:
The following table lists the available report names, and gives brief details of the information provided by each report .
Report name | What it does | Sample |
---|---|---|
Each invoice grouped by name | The report will sort each invoice in the date range according to the name (Supplier or Customer) and give totals for each name. | Sample |
Each invoice grouped by date | As above, but totals given for each day. | Sample |
Each invoice line by item | Lists each batch supplied or received, and the transactions for that item. This report can be very long | Sample |
Invoice line with donor | As above, but including the 'donor' field and formatted to be most useful for analysis using Excel filtering and Pivot Tables. This report can be long. | |
Each invoice showing profit | The cost and selling price totals for each invoice are displayed. | Sample |
Each invoice line grouped by item department then item | The invoice lines are grouped by item department assigned to each item. Each item department is then sorted in alphabetical order. Includes batch information. | Sample |
Each invoice line grouped by name | The value of batches received/supplied from/to each name. Includes batch level information. | Sample |
Each invoice line grouped by item and then name | Invoice lines are all displayed, grouped by item and then, within those groups, by name. Includes batch information. | Sample |
Totals for each item category | The total value for each category will be displayed - each transaction will not be shown. | Sample |
Totals for each item | Each item that has been received (or issued) will be shown with the total quantity and value received/issued | Sample |
Totals for each item - Net Inventory adjustment | Each item on which an inventory adjustment has been made is shown; the net adjustment is displayed, with both quantity and value shown. | Sample |
Totals for each name | The invoice lines for the chosen date range are grouped together for each name. This report allows you to see quickly what has been issued to a customer or customers. | Sample |
Totals for each item broken down by name | Lists all items, showing their distribution to recipient customers or delivering suppliers | Sample |
Totals for each item dept broken down by item | Sample | |
Totals for each name broken down by item | As above, but listing customers or suppliers and items received by or delivered by them | Sample |
Totals for each item department | The total value supplied/received for each item department is shown | Sample |
Totals for each item grouped by day | The total quantity and value of each item is shown for each day. | Sample |
Totals for each item grouped by month. | The total quantity and value of each item is shown for each month. | Sample |
Totals for each item grouped by item description. | First the report is sorted by item then the total quantity and price of each item is shown. It also provides a grand total for the item quantity and price columns. | Sample |
2 period comparison by item category | User selects two periods, and the transactions for each period form a column. The rows are determined by the item category. This is a special report that has different input options. See entry below. | |
Totals for each item broken down by packsize | The packsize for each item and the number of packs issued. | Sample |
Prescription and general issues over time | This report shows the quanity of items given to patients and other customers over a period of time, separated by month. It exports to Excel only and has 2 tabs; the By Item tab shows which prescribers prescribed what items and the By Customer tab shows which items were issued to which customers | Sample |
Prescriber report | This report allows you to view the total value, mean item value and item count for prescriptions dispensed for each prescriber | Sample |
Dispensing with patient name and prescriber | This report shows who dispensed what to which patients with which directions | Sample |
Historic prices - Volume weight average price | This report produces a volume weighted average price for all transactions within the specified time period, for items that are On the price list in the store that you are logged in to. Transactions that have have no transactions or a Cost price of 0 are ignored.Calculations are done on the basis of the Catalogue pack size (often be the same as the Preferred pack size). That is, transactions that are not in the Catalogue pack size will have their calculation adjusted accordingly. | |
Cross tab: items in rows, names in columns, quantity in cells | These reports are like Excel pivot tables. Warning: they can be slow to produce if you have a lot of data. Start with small date ranges until you are comfortable your machine can handle the load. Note that reports that produce a lot of columns won't print well - it is better to export them to a spreadsheet and print from there. | Sample |
Cross tab: items in rows, names in columns, value in cells | See above | Sample |
Cross tab: items in rows, days in columns, quantity in cells | See above | Sample |
Cross tab: items in rows, days in columns, value in cells | See above | Sample |
Cross tab: items in rows, months in columns, quantity in cells | See above | Sample |
Cross tab: items in rows, months in columns, value in cells | See above | Sample |
Cross tab: names in rows, months in columns, quantity in cells | See above | Sample |
Cross tab: names in rows, months in columns, value in cells | See above | Sample |
Distribution of quantities issued | First the report is grouped by item name and then by the pack size issued. It displays the total quantity and total count for each item. | Sample |
Totals for each item grouped by transaction category then item | The report is grouped by each item's transaction category first and then by each item.The report shows the total count, total quantity and total value for each item. Lastly it also calculates a grand total of the items count, quantity and value columns. | Sample |
Each invoice sorted by Name >Address1 field then by name | The invoices are firstly sorted by the address1 field, and then by the name they were supplied to. This groups invoices together by whatever you entered in the “Address1” field for each name - e.g. you might enter the region or province. | Sample |
Each invoice sorted byName >Address2 field then by name | As above, but the first sort is by the entry in “Address2” field. | Sample |
Customer ordered short fall report | Where there has been a shortfall, this report shows the shortfall of each item and of each item category by quantity and percentage. It shows what demand from customers has been met and what has not. It applies only for issues to customers. | Sample |
Customer transaction report 1 | This report and the following three are, in fact, templates, and are the starting point for designing reports on specific criteria not covered in any of the existing reports. | Sample |
Customer transaction report 2 | Sample | |
Customer trans line report 1 | Sample | |
Customer trans line report 2 | Sample | |
Received vs. sent | Shows the difference between stock issues from a supplier and stock receipts for the same lines at the customer. If you choose Purchases from suppliers as the Transaction type then the report will run over supplier invoices and show differences between what the current store was sent from the supplying store (on customer invoices) and what the current store received on the corresponding supplier invoices. If you choose Sales to customers as the Transaction type then the report will run over customer invoices and show differences between what was sent from the current store (on customer invoices) and what was received on the corresponding supplier invoices in the receiving store. WARNING: any non stock-transfer transactions included in the report (if you select Normal transactions or Both normal transactions and stock transfers in the Transaction type section) will show a full difference because there is no corresponding customer or supplier invoice. So it is recommended to only choose the Stock transfers option in the Transaction type section for this report. | received_v_sent.pdf |
Months to expiry | Sample | |
Order processing time | If you are filling out the order written date and order received date for incoming customer orders, then this report will show you how long it is taking for orders to reach the store, and how long it is taking to process orders from receipt to order entry and to order dispatch | |
Historic prices - Volume weighted average price | Calculates the Volume weighted average price for all items in the catalogue in those stores (i.e. where the item's On price list checkbox is ticked (see the 4.01.03. Items - Misc(ellaneous) tab page for details)) for all confirmed or finalised supplier invoices within dates provided by the user for all selected stores. Please note: * Transactions where the stock has a cost price = 0 will not be included in the calculation.* Items that have had no transactions within the selected period will not appear in the report. * The On price list checkbox must be set for each store. This will be important if you're running the report over more than one store. |
In the first drop down list, you can choose to report on:
In the second drop down list, you have the following options:
A stock transfer is a transaction made when the supplier or customer is another store in your datafile. A normal transaction is when the customer or supplier is a normal customer or supplier in your datafile, and not a store.
Enter the date range of transactions to report on, having selected Confirmed date or Shipping date. Note that the date used may differ from the original entry date, depending on how your system is configured in the Preferences.
Assuming you have set up transaction categories, your report may be filtered by category. For help setting up Transaction categories click see sectrion 22.07. Transaction categories.
You can enter a particular customer code or category of transactions to be displayed.
Here you can specify the items to include in the report.
Note that sometimes the item choice options will be hidden because the chosen report is reporting on transactions, not on transaction lines:
If you have donor tracking turned on in the preferences, and choose a report that bases it's data on invoice lines, then you will be given the option to filter the report for a particular donor or group of donors.
If you are using mSupply in both store and dispensary mode, you might want to report only on transactions in store mode (items distributed to customers) or in dispensary mode (items distributed to patients). You can do this by checking Store mode or Dispensary mode radio button depending on your requirements.
This checkbox enables you to choose whether you include stock transfers (where stock is transferred from one store to another in mSupply) in the report calculations. If you want stock transfers to be included in your report then uncheck this checkbox. If you want stock transfers to be left out of the report then check this checkbox.
Excluding transfers is only really useful if mSupply is being used in your central and regional warehouses (between which 'transfers' are happening, and none of your customers of interest are using mSupply.
This report allows you quickly to compare sales or purchases for 2 periods of your choice based on item categories.
When you choose this report a different set of options is shown.
Set the date range for each of the columns and then choose a summary level. It should be noted that if no data is present for the first period defined, this report will not run.
The summary level is based on the item category field. If you have set up item categories using dot notation, then you can summarise categories.
To set up item categories, choose Items > Show item categories
Take the following simple example of categories that have been set up as follows:
The rules for dot notation are
If we run the report with a summary level of “none” each category is shown
If we run the same report with level one chosen, only the first level of the hierarchy is shown. Items with categories 1.1 and 1.2 and 1.1.1 have all been summarised under “1 Antibiotics”
And with level two chosen, items with category 1.1.1 and 1.1.2 are summarised under the heading “1.1 Penicillins”
Note that if you have items to which no category has been assigned, they are shown under the heading `none“ at the top of the report.
The categories are printed in the order that you have specified by drag-and-dropping in the window shown by choosing Item > Show item categories….
Transaction filter: Filters the report for given category
Prescriber filter: Reduces the found transactions to those for the given prescriber only, depending upon the selection made (equals, starts with, contains, is not equal to).
Exclude transfers from calculations: The check-box Exclude transfers from calculations when checked will ignore transfers between stores within the organisation.
Exclude Ad Hoc and non stock items: The check-box Exclude Ad hoc and non stock items is displayed accordng to the type of report selected. By default, it is checked.
When checked, the transaction report generated will not include any Ad Hoc items or non stock items (items you don't normally keep in stock).
These four reports should be regarded as templates for you to design your own report .
Having decided whether you wish to report on transactions or transaction lines, you need to design the report according to your specific needs. Coose Reports > Manage Reports :
You are presented with the Report management window. In this example, our report will be on transactions, so you should highlight custom_transaction_report_1 , and click onthe Duplicate report button:
Choose an appropriate name for your report, and any comment you wish, and click OK. You are returned to the Report management window, where you should scroll down until you reach the report you have created; note that the name you gave the report will appear in the Custom name column; highlight the row and click Edit report .
This brings up the Edit report window. Creating custom reports is described in detail earlier in this chapter. See the Custom Reporting Tutorial
The save template button allows you to save a given report as a template once you have input your chosen settings. Templates can be reused as needed.
Pressing the Save Template button brings up the following window:
This dialog allows you to set a name and description for the report template. You can also define user permissions: at the moment, you can define these as ‘Public’ - for anyone, or ‘Private’, in which case the permissions only apply to the user who created the template.
There is an almost infinite range of reports that you might want to generate from transaction data. If you are comfortable using Excel Pivot tables, there is an mSupply transaction report format that you will likely find quite useful. This report has been designed to work in Supervisor Mode but will also work in a single store.
To generate it:
CustomTransactionByItem
report type and send the output to Open in ExcelThe report will open in Excel in a form that can then be easily filtered, sorted and manipulated through a Pivot table. If fact, the records are not sorted at all, so they must be processed to make any sense!
Previous: 13.02. Stock and items reports | | Next: 13.04. Purchasing reports |