The Reports & Forms Tab gives access to the system reports, forms, communication and marketing manager functions. By modifying the existing reports or creating their own queries users have an unlimited access to all of the data contained in the database. Queries permit users to retrieve information based on the view of the data they require. Users can assemble and re-assemble the data and data mine the information contained in their database to identify trends, or report on specific elements.
The flexibility offered by the report writer is only limited by the users creativity on how to access and format the information they want to view. The advanced editor provides all of the functionality to create simple or complex reports and forms and can be further expanded by user customized coding that can address all of your reporting needs.
Create Reports and Forms by querying the Filopto SQL database.
Definitions and Concepts:
Relational Database: Filopto uses the Firebird SQL Relational database. A relational database is a collection of data items organized as a set of formally-described tables from which data can be accessed or reassembled in many different ways without having to reorganize the database tables. A high-performance SQL database like the one use by Filopto, is access by doing SQL queries (lookups) to the database which is significantly more efficient and faster than loading the full database just to get one piece of information. Queries allows you to assemble or re-assemble the data in a view that answers your question even when the data was not initially assembled in the form you require.
The Filopto SQL database (Database file) contains Tables (think of tables as separate files within the database file, something like a ZIP file) that contains and segment the data. Filopto has over 540 tables and views (specialized tables), 511 generators (indexes), 621 triggers and 190 procedures.
Tables contains individual records (think of tables as a spreadsheet with rows and columns of data). Tables are linked using Primary and Secondary Key index (generator) fields. These fields are typically numbers that link each record with other information (records in other tables). For example, the Invoice primary index key (invoice_no) is linked to corresponding line items in the invoice which are stored in the line Item detail table. The invoice table is linked to over 17 tables just to describe the Invoice information, this does not include the links to the Inventory tables, Patient tables, EMR tables, System Settings tables, lookup fields tables, etc.
One of the many advantages of a SQL database is that it is not affected by the volume of data it contains. That the database has 1 record or billions of records its speed to retrieve the information is not affected. The speed of retrieval is affected however if you retrieve 1 record verses a very large number of records. That is where Queries come in. The more accurate you can create the query the faster it will be to retrieve the information because it does not need to retrieve a large amount of data. Accurate in this context refers to how specific you can make the query. If you can specify, for example, the invoice number, the query only needs to retrieve the information related to it, versus retrieving thousand of invoice that have nothing to do with the what the user wants to see.
SQL (Structured Query Language) is a domain-specific Query language used in programming, reporting and designed for managing data held in a relational database management system.
Queries: are a small program written in the SQL language that retrieves, updates, deletes etc.. data in a Relational database. In the report editor, queries are for retrieving information.
The most common operation that a SQL Query makes use of is the declarative SELECT statement. SELECT retrieves data from one or more tables, or expressions. Standard SELECT statements have no persistent effects on the database( you cannot update or change the content of the database). A query includes a list of columns (field names) to include in the final result, normally immediately following the SELECT keyword. An asterisk ("*") can be used to specify that the query should return all columns (fields) of the queried tables. (not recommended if the query will return a lot of useless data not pertinent to the report or form). SELECT is the most complex statement in SQL, with optional keywords and clauses that include:
▪The FROM clause, which indicates the table(s) to retrieve data from. The FROM clause can include optional JOIN subclauses to specify the rules for joining tables together.
▪The WHERE clause includes a comparison predicate, which restricts the rows returned by the query. The WHERE clause eliminates all rows from the result set where the comparison predicate does not evaluate to True.
▪The GROUP BY clause projects rows having common values into a smaller set of rows. GROUP BY is often used in conjunction with SQL aggregation functions or to eliminate duplicate rows from a result set. The WHERE clause is applied before the GROUP BY clause.
▪The HAVING clause includes a predicate used to filter rows resulting from the GROUP BY clause. Because it acts on the results of the GROUP BY clause, aggregation functions can be used in the HAVING clause predicate.
▪The ORDER BY clause identifies which column[s] to use to sort the resulting data, and in which direction to sort them (ascending or descending). Without an ORDER BY clause, the order of rows returned by an SQL query is undefined.
▪The DISTINCT keyword eliminates duplicate data.
Example of a simple query of the Patient file:
Select
PATIENTS.Firstname,
PATIENTS.LASTNAME,
PATIENTS.CITY,
Patients.LASTEMR
From Patients
Where PATIENTS.PATIENTNO = 15
The select statement list the various fields from the Patients table the user wants (note that each field is separated by a comma indicating the list of fields to be retrieved. The last field does not have a comma indicating it is the last item to be retrieved.).
The From Statement list the tables to be used for retrieving the requested field in this case the Patients table.
The Where statement identifies the specific patient for which this information is to be retrieved.
All reports and forms use queries to retrieve information. by examining reports and forms you can view the queries we created to retrieve specific information. You can use these as a starting point to creating the query you need. Accrs Med Software also provides custom report services if you wish to have a particular report or form created.
.
