• Name
      Data Analysis & Reporting Database
      Name
  • 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.

    The Solution

    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

    The Process

    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
  • 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

     

  • Import Data Access Database
  • 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

     

  • Importing File History and Data
  • Entering Invoices

    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

     

  • Inputting Invoices 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

     

  • 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.

     

    Reporting

    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).

     

    Reconciliation Reports

     

  • Reconciliation Reports
  • Weekly Settlement Report
  • 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.

     

    Documentation

    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.

     

    Documentation

     

  • Documentation