Bring It To Everybody - SQL Reports

User Manual

Fair Warning : BITE is for database and software developers. Using BITE requires a knowledge of SQL syntax, and building reports requires an intimate knowledge of your database structure. As such, this manual assumes that you have this knowledge, and covers only the features of BITE, not how to use SQL.

BITE SQL Reports is a report rendering library for SQL databases. It has no support for data entry or validation. It allows you to build reports for your database, and store them in the database to run easily. BITE can easily handle many databases this way, and each database can have customized reports. Your reports can be grouped together with "Trigger" commands, and you can use the BITE library to render all the reports when a trigger is called.

There are 3 main functions of BITE - Report Building, Data Selection, and Report Rendering. All functions are included in the software, though you could use report rendering engine as a library in your client software and not include the report building functionality.

  1. Getting Started

    1. Requirements

      MySQL Client Library and wxWindows 2.4.1. We used wxWindows for the cross platform functionality - in hopes of being able to make reports render the same across any clients.

    2. Database Setup

      First step is to create database connection. To do this, click open BOLT, click 'Setup' and then click 'New'. This doesn't create a database on your server, it just tells the client where it is. Put in the important information, click Save. Then click Ok to leave setup mode. Double click on your database to get to the main screen.

    3. Table Initialization

      The first time you connect to a database, you should initialize the BITE tables. These are the tables that will store the reports that you are building. The table structure is included in a file called 'Init_Reports.bck' included in this distribution. Click on the 'Restore' button, choose that file, and click 'Restore'. The software will ask where the MySQL.exe program is locate (available in the tools folder), and you are ready to go.

  2. Data Selection

    Since people vary rarely want to type in ID number of a record, BITE includes an easy way for the user to choose a record from the database. These are called QuickPicks.

    1. QuickPicks

      QuickPicks are SQL queries that are grouped under a keyword. The default databse include 4 QuickPicks under the class 'Reports'. When you click on the 'Reports' button, those 4 queries are displayed. When you expand a query, the results are shown, and you can choose a record to modify. This functionality is available to you for your reports. You can decide on a keyword, and make a number of queries that the user can navigate the records and make a selection.

    2. QuickPick Editor

      QuickPicks must return an unsigned integer, and the integer is the first field in your query. All other fields in the query are concatenated to form the label for the selection. The title is the text that will be displayed in the quickPick list, and the class is the keyword you will use to identify a group of quickPicks. Use the test button to see what the query will look like, and to test for syntax errors. Make sure to Save the quickPick when you are satisfied.. Done closes the windows.

  3. Report Building

    1. Report Basics

      A report is a collection of SQL queries laid out on a page. The reports can be grouped together using triggers. Queries can be be displayed as text, images, or subreports. Your report can have a background image, which will be stored in the reportPictures table. Lastly, your report can have parameters which the user inputs before the report is ran.

    2. Triggers

      Using triggers you can group a series of reports together to call them all at once. You can trigger these reports using the 'Stored Reports' section see below, or use the triggers in your application that has the Report Rendering library. Using the trigger you can define the number of copies that will be output, and where to output them. You can output to the screen, printer, disk, or another computer via FTP. The details about these work just like clicking the similar buttons in the report editor, only the specific target is used when the trigger is sent. This can enable you to display one report, print another 3 times, and save another on the hard drive; all with one command. See the section on report rendering for more details.

    3. Queries

      The main reason for BITE is to display SQL queries. You define queries, set the size and location, number of columns, and how many results per page.

      Queries can include parameter values (see next section) that will be replaced at render time with the values of the parameters.

      There are 4 types for the output of the query.

      • Text is used for formatted text, and you can define the font and size of text.
      • String is useful for text only reports (like csv). You set a character to seperate the fields and the rows.
      • Image is used for outputing images stored in the database (assumes jpg format). The query should return the actual data for the image (the BLOB field).
      • Subreports are very useful, for each row of data that is returned by the query, the subquery report is ran using the fields to fill in the parameter values.
      The results are scaled to fit inside the space available. You can set the records per page field to limit how many results will go in the box. If more results than will fit are returned, a second (or more) page will be produced.

    4. Parameters

      Parameters are variables for you report. The parameter value is determined before the report is ran, and every time a query is processed, any term inbetween percentile marks (%%) will be replaced with the value of the parameter, as set by the user or parent report.

      Parameters can come in a number of types

      • Date - a calendar is displayed for the user to choose a date.
      • Number - an integer value
      • Money - a number value with decimal information
      • QuickPick - this is where your quickpicks come in useful (see above). A drop down will let you choose which QuickPick Class that the user will be presented with. QuickPicks can only return unsigned integer values, making it most useful for the auto_incrementing primary key of a record.

  4. Report Rendering

    1. Viewing

      Either using triggers, or the 'View' button in the report Editor to render the report onto the screen. A window with scroll bars and a close button is displayed. If there are more than one page, the next page will be displayed once this one is closed.

    2. Printing

      Either using triggers, or the 'Print' button in the report Editor to render the report onto a printer. The printer settings dialog box is displayed (unless set by the trigger), and the report is rendered and printed.

    3. Saving

      Reports can be saved as JPG images or text documents. When saving as a text document, use the 'String' and 'SubReport' query types. Queries are executed in the order in the list box in the report editor, and records and row seperators can be defined. When saving as a JPG image, use 'Text', 'Image', and 'SubReport' query types. Font and image sizes can be defined, and a background image can be used.

    4. FTP

      You can FTP reports as text documents or JPG images. Supply the username, password, server, path, and filename. If the filename ends in .jpg, the file will be rendered as an image, otherwise it will be text.

    5. Stored Reports

      The main screen of BITE has a list of 'Stored Reports'. These are triggers that will be run (with optional parameter See the Queries, Setup, and Cleanup queries. This can be useful for running database maintenance tasks and a set of reports in one step. Type in a descriptive label (which will be displayed on the front screen), choose the trigger to run, and fill in any queries (not needed). Click 'Done', click on the report in the list, and click 'Run'.

      The parameter Query is useful for setting parameter values of the query. If the report takes a data parameter, and you want to have a report that runs for today, simple type 'select now();' in the parameter query field.

      The pre-run and post-run queries can be useful for maintenance tasks. Update, Insert and Delete queries are acceptible, and the use of the LIMIT command is possible.

  5. Data Backups and Restore

    Data backups and restore are controlled from the main screen. Use the 'Backup Reports' button to backup only the tables required for BITE, and the reports you have built. The 'Backup' button will backup the entire database. Use the restore button to apply the backup. Use 'Restore' to apply the initial database structure to your database.