Relational Databases: Creating Reports

Relational Databases: Creating Reports

Determining the Scope of a Report

  • Reports in relational databases provide detailed output of the stored data, customised for specific informational needs.
  • The first step in creating a report is to determine its scope and purpose; the data it should include and what it’s intended to convey.
  • The scope will dictate the tables and fields required, as well as any filter criteria, calculated fields, and the desired sort order.

Selecting Data for Reports

  • Data selection for a report is accomplished through SQL queries, which are commands that extract the required data from one or more tables.
  • The simplest form is a select query, which retrieves data based on specified criteria.
  • The WHERE clause in SQL is used to filter records, extracting only those that meet specific conditions.

Design and Layout of Reports

  • Report design involves deciding how the extracted data will be presented on the printout or screen.
  • The layout of a report can include headers, footers, graphics, and grouping levels that organise records in a visually meaningful way.
  • Most softwares allow for easy dragging and dropping of elements such as text boxes, labels, and graphics to design a report.

Creating Calculated Fields

  • Calculated fields are created when the necessary data isn’t stored directly in the database, but can be computed from existing fields.
  • Examples include calculating sales tax from a subtotal field, or deriving age from a birthdate field.
  • These are generally temporary, existing only for the duration of the report, and are not saved back into the database.

Sort Order in Reports

  • The sort order of a report affects how data is arranged when the report is run.
  • Records can be sorted in ascending or descending order based on the value in one or more fields.
  • A well-selected sort order can make data much easier to understand and spot trends.

Formatting a Report

  • After selecting data and determining the layout, data in a report can be formatted for better readability and visual appeal.
  • Formatting options include adjusting font size, colour, typeface, alignment, and more.
  • You can also format numerical data with specific number of decimal places, currency symbols, date and time formats, etc.

Previewing and Printing Reports

  • Prior to finalising a report, it should be previewed to ensure it displays all information correctly and in a visually appealing manner.
  • Any necessary adjustments to the layout, calculated fields, or sort order can be made before the final version is printed or saved.
  • Most software provides a print preview function that simulates the printed output on screen.