Introduced in TravReports v0.06, The expenditure report returns the following data by deriving it from the following tables :
Customer Table (CUST0R)
Clients Account : The account code which the client belongs to.
Clients name : The clients Title, Initials and Surname.
Consultation Table (CONS0R)
Custom Code : Any of the user defined fields.
Number of Pax : Adults + Children + Infants.
Transaction Table (HIST0R)
Booking Fee : (Optional) The total value of a transaction that is deemed to be a fee.
Cost : The total cost of the transaction minus taxes if applicable.
Destination : The major destination on the transaction.
Travel Date : The date of departure on the transaction.
Transaction Date : The date on which the transaction was created.
Transaction Type : The transaction type.
Transaction Detail Table(s ) (Det*)
AirLine : The airline, can be derived here or through the ticket, the ticket is more reliable and so if there is one that will be used and this will be ignored.
ClassOfTravel : The class of travel, can be returned as a code (quicker) or as a description depending on the option used.
Duration : The length of the journey or the duration of the item in DAYS. Derived either by an explicit value or by calculating the duration between sectors.
Region : The region in which the major destination in which the major transaction falls.
Routing : On a multi sector booking this will string together the places visited.
Tickets Table (TTRN0R + BTRN0R)
AirLine : The airline, can be derived here or through the transaction detail, the ticket is more reliable and so if there is one this will be used.
Tax : If the booking has a ticket then this will be populated with the tax value and the total value will be reduced to show the breakdown of tax and cost. If there is no ticket then this item will appear as "-" or "nil"
Fees Table (Feecosts)
BookingFee : (Optional) The total value of a fee, if the fee does not match what is expected then use the "get fee values from transaction" option which will take more time but may resolve the difference. For automatically applied fees that are seldom changed this should be sufficient.
Payment History (PHST0R)
Invoices : This is the last payment document done for this transaction. In Business house this may also be the only invoice.
The main Expenditure Report screen is shown below, to obtain an items description simply click on the screen shot.
Transaction / Departure Dates.
This section enables the user to define a set of transaction and / or departure dates to report between. Entering 00/00/00 or nothing will mean no limit, or forever.
Account Selection.
Select an account, an account group or a selection of accounts. Note that if you would like to include ALL bookings even those customers without accounts then you must un tick "business house only"
Transaction Types.
This area allows you to define particular transaction types, note that if you select a transaction type that could be a linked fee (a fee downloaded via an MFID for example) but you do not select the type of transaction that it may be linked to then it will only appear if the hide linked fees option is un ticked. This is because the master transaction will never appear as it does not match your criteria. If you would like every item to appear do not choose to hide linked fees.
Return Class Descriptions.
Using this option will slow the report down slightly because each time a class code is found then the description of that code will be obtained. However, for customer facing reports it may be desirable to send a full description rather than a code to avoid confusion.
Get Fee Values from transaction.
Under normal circumstances this would never need to be used, however when a fee is manipulated after it has been downloaded / paid then it may be helpful to have the report derive the value of the fee from the actual transaction rather than the fee table, using this option will incur a performance hit upon the preliminary stages of the report.
Hide Linked Fees
When ticked the fees that are linked to other transactions will not be shown on lines in their own right, they will instead be show as a part of the master transaction. If this option is un ticked then they will appear as separate lines (normally service charges or fees). The fee item on the main transaction will also be nil because the values will be elsewhere on the report and showing them twice would result in duplication.
Custom Column.
The expenditure report gives the user the option to return any of the user defined fields. Simply select an item and generate the report.
Sort Report By :
Although this can be defined prior to the reports generation please bear in mind that after the report has been created that you can exit the print preview and re-sort on a new option. This is to enable users to print the same report sorted differently without having to regenerate it all over again.
Show Report when done.
If ticked then when the generation of the report has completed it will immediately appear on screen. If left un ticked it will await your "show report" button press. This can be useful if running the report in the background whilst other work is carried out.
Show Search SQL.
Prior to the reports final stages the SQLS that where used to make the report will be sent to the report status box, this is extremely useful if there is a problem and support / the client need to understand why the problem occurred. Normal circumstances would not require this option.
Report status.
Along with the progress bar this box outlines what the program is doing, occasionally some stages may take a little time to complete (see fees from transactions) but please do be patient! If there is a problem then cut and pasting this box into an e-mail with the "show search sql" button checked will be very helpful for support staff.
Table Name.
This gray box will change each time you open the report and each time you start a new report. It is the name of a temporary table which holds your data, again if there is a problem at all during the reports creation, note this number down in the support call and it will enable support staff to recall the exact report you were just running. The full location of this table is shown when the application boots up and when the report is begun.
Exit.
When the report is running it will abort at the next suitable stage (normally after the current booking has been processed), give it a moment as occasionally it can take 20-30 seconds. When the report is not running it will terminate the session and return you to the main screen.
Generate report.
Begins the generation of the report, when the report has been generated this button changes to "new report" (See below).
The red highlighted section shows the buttons that we will be using next,
New Report
Discards the current report, changing the report number and starts again.
Save Report
Save report will present you with a save dialog and export the report to a CSV file. See notes on exporting to CSV.
Show Report
Using the "sort report by" option will show the report in the order in which it was asked for.
On screen
The red section highlights your options from this point. You may print, save or close the report.
When this report is saved to CSV you may notice that in Excel there are more data fields than on the actual printout. These extra items are intended to give the end user more control over the data when it ultimately ends up in Excel or Open Office Calc, use the data as you see fit using the filters and formulae availible in these packages.
The extra fields are shown below :
Branch : The branch in which is transaction lies.
ClientNo : The client number this transaction belongs to.
Consult : The consultation number this transaction belongs to.
Tran : The transaction number for the transaction.
TypeOfTran : The Type of Transaction. (Air, Hotel, Ferry)
HideOnPrintOut : True if the final report is to hide this line, lines are hidden when the "hide linked fees option" is used.
NoOfTickets : The number of tickets found for this transaction.
