Today we look at how you can use dbFront to create quick reports. For an example we will be building a report to view the age of submitted expense reports using the Expense Report app.
Setup
To create a new report, click on the Quick Print button. By default this button is only available to Administrators but you can easily enable it for your regular users.
From here, you can now prepare the report. If it is merely a one-time report, click run after you have selected the template and table columns.
If you are going to need to report on the same thing repeatedly, you can save the report as a button. Just provide a caption for the button and save your report.
Report Type
There are two report types to select from:
Multirow Report: this combines multiple records into a single table; this is used when you want to look at a cross-section of your data such as a Staff Listing or ToDo List.
Single Row Report: this returns a single record in a specific table with all related rows. This is used when you need to report on the selected record (for example printing an invoice with its details).
Output
This sets how the report will be outputted. When the report button is first created, you can only have one method of output. Available options are: Csv, Excel (if you want to export the data), PDF, Quick View, and Print at Server.
If the report is saved then you can add more output options:
When you add PDF as an output option, you will also be able to setup the page format for the PDF file:
When Excel is added as an output option, an Excel tab is added that allows you to use an existing Excel spreadsheet template for outputting to. For more information on how to use this feature, please refer to the article on Excel templates in the knowledge base.
Details
Here you can customize what fields to report on as well as how you want the information ordered.
Administrators can add SQL filter expressions to further refine the report if necessary. An example of an expression:
Child Tables
If the table you are reporting on has child tables, you can add rows from those tables to the report. These child tables will show up in the Options list as Table_name::Rows:
Adding these to the Field/Rows section will add new tabs that you can now use to add specific rows and columns from the child tables to the current report.
Report
If the report tab is visible it means that there are available report templates that you can choose to use. These templates can be edited to further customize the output.
A final note about these reports. While the Quick View and PDF allow for a formatted, static view of the specific data, keep in mind that the CSV/Excel output will be more useful in terms of allowing users to work with the data directly and create graphs and reports within Excel that are better suited for their purposes.
The Example
We are going to build a report to show all expenses that have an age of at least 20 days. As we want to see all expenses, we want a multirow report.
In the Details tab, we add the “Age” field in the Fields/Rows section, add ‘vAge” into the Sort Order as we are going to sort on age in descending order and then add an expression in the Filter field so that we only get items that have an age of 20 days or more. We are also going to remove the “Purpose” and “Expense Report ID” from the Fields/Rows section.
With the report now setup, we can run it and see what the results are:
This is an example of a report using single row as the report type and having a child table (in this case, data from the Expense Detail table) added to the report:
Wrap Up
With the ability to create quick custom reports and exports, the user has another tool to better visualize the data that they need to work with whether as a static one-time report or outputted data in a spreadsheet. To check out how these features work, go to dbFront.com, download and explore these features and more.