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.