NameData Analysis & Reporting Database
Data Analysis & Reporting Database Built in Microsoft Access
Delfont Mackintosh Theatres is a theatre group owned by British theatrical producer Sir Cameron Mackintosh. The company was founded in 1991 by Mackintosh and Bernard Delfont. The group owns eight London theatres. The group operates eight of the most prestigious theatres in London's West End.
The business requires the means to cross-analyse sales data from the ticket seller (Ticketmaster) and compare and report the figures against Ticketmaster’s and box office invoices as well as Ticketmaster’s own end-of-day reconciliation files. Currently, this is being managed on a spreadsheet, but it is very large and growing. Also, there are a number of checks that need to be carried out, and a spreadsheet does not provide the kind of functionality needed to manage the data and report on it accurately
Data from the ticket seller can be changed retrospectively, and the business requires the need to cut-and-slice the data in many different ways, allowing changes to be made to certain fields and an audit trail kept of those changes.
Xlteq Access Database Consultants designed and built a MS Access Back-End & Front-End with the new screens and functionality required by the business to import and maintain the data in the Back-End
After receipt of the customer’s own spreadsheets and discussion on use, the Back-End was designed to hold the specific data from the three sources above.
Once the Back-End was built, the Front-End was constructed to contain the functionality to automate the file imports, and input screens to input invoice data.
The main screens in the Front-End allow the user to cut-and-slice the data as required, perform the necessary checks and change the data as needed as well as providing the reporting.
Switchboard Menu System
On opening the Database Front-End the user is presented with a Switchboard Menu. This is concise, guides the user through logical groupings of the database’s functionality, and removes the risks attached to creating multiple menu screens. The menu (as the other screens) are colour-coded and branded using DMT’s corporate colours and logo.
The Switchboard Menu is fully customisable and is extremely flexible. New functionality and navigation can be added easily in the future
Figure 1: The Switchboard Menu
Importing Monthly Data
The customer needs to import sales data from Ticketmaster and also the EOD reconciliation files from TIcketmaster. The menu for Data Import is simple and contains BROWSE and IMPORT buttons.
There is functionality built in to prevent duplication, and the user is notified in this case. As files can be large, there is a progress counter on the import form to show how the import is running.
The EOD reconciliation files are fixed-width text files with no field identifiers. To import the data, the automation validates the file name and key content, reads the whole file in, parses the data and finds the relevant values for storing from key identifiers in the file body.
There is also functionality for the user to selectively delete imports of both the Sales File and the EOD reconciliation files.
Figure 2: Importing Data
There is an import audit report to allow the user to view which files have been imported, and some checksums to allow for a visual confirmation that the amount of data in the imported files is in the correct range.
There is also a form to view and update the EOD Reconciliation Data.
Figure 3: Import File History & Data
As part of the reconciliation, the user receives invoices from the Box Office and from Ticketmaster. These need to be input and viewed in one place and are used subsequently in reconciliation reporting.
Invoices. Due to the hard-copy nature of the invoices and the method of receipt, this data does not have an automated import.
Figure 4: Inputting Invoice Values
Sales Data Screen
The Sales Data Screen allows the user to see a great deal of data in one place. Here, the user has the following functionality to cut-and-slice the data:
- Performances are listed on the left, with all sales for that performance on the right.
- Filters at the top of the screen allow the user to filter the data for Method of Payment, Transaction Date, Price Level & Type.
- All the checks the customer requires are listed underneath the list of performances. Adjacent to these are radio buttons allowing the user to see only the rows that have non-zero check values. These radio buttons can be used in conjunction with the filters above.
- As the user may need to know the exact file and row in that file for a transaction, these are determined on import and available on this screen.
- The user can change any of the data values in the left-hand pane. On attempting to change a value, the user is prompted for a reference. The data is changed and the change is recorded in an audit table for later reference.
Figure 5: Main Sales Screen
Finally, there is a button at the bottom to allow the user to change bulk change a performance reference to another performance reference according to a date range, and a button to view reconciliation reports for that performance.
The Database Front-End has three types of reports:
- Summary Files – This produces formatted spreadsheet outputs containing reporting totals for a list of selected performances.
- EOD Reconciliation – Reports a number of checks for each performance and a total.
- Settlement Week – An interactive, weekly report summarising values across multiple performances. This report allows the user to focus in on certain rows with certain payment types.
Selection of the output for Summary and EOD Reconciliation reporting is made through selection forms (the Summary form allowing multiple selection).
Weekly Settlement Report
Accumulative Difference Reporting
Finally, the user can view a simplified summary of all the differences, accumulated, that have been reported in the other reporting. These are only required for standard ticket sales.
The DMT solution was delivered with complete documentation on design and use, including sections on the Front-End, the Back-End, and detailed descriptions of importing data and using the data screens.