We are continuing the series of articles "The Right Way to Build Applications on the QuintaDB Service". In the previous article, you learned how to create the correct database structure. If you haven't read the first article in this series yet, we recommend to starting with How to create a database for a Home Appliances Store.

Using the same database for a home appliance store as an example, we will consider the following questions:

- How to transfer completed orders to a separate table?

- How to set up flexible access rights for managers to customer orders?

The first thought is to clone^[1] or create another table and transfer completed orders to it somehow. Then create separate tables for each manager, etc.

But this is the wrong way. With this approach, you will only waste valuable time and create unnecessary entities that contain redundant data. Instead, we will create Reports, which will help to solve all the tasks described above. The idea is simple - a report is a data selection from a table according to specified criteria. Or in other words, it's a data representation that is stored in the database as a separate object, but at the same time is not independent.

We can say that report is a "virtual" (logical) table, created on the basis of a "real" table. You can work with reports as with a regular table: read, edit and create new records. The content of reports changes dynamically based on data in real tables. Any changes made to the data in the table are immediately reflected in all reports. One table can contain multiple reports.^[2]. 

Reports provide the following benefits:

• Working with the application becomes easier with organized and optimized data;
• Searching for records becomes easier. A database search^[3] can contain many criteria. It's enough to create a report with a ready-made data selection, instead of performing a complex search repeatedly each time;
• It becomes more convenient to hide extra data from users;
• Users access rights settings is simplified;
• You can create reports to display the results in an easy-to-print^[4] view.

Using this example, you will learn how to create your own reports from tables. Let's start with the task: How to transfer completed orders to a separate table?

Add the Date and Status fields (radio button) to our Sales form:

Now you can specify the order date and status. For convenience, let's create formatting rules^[5] in the table, to easily distinguish completed orders from current ones:

In the table, you need to do a search by the Status column in order to select only completed orders:

We got a search result. This will be our first report. Click the Save report button and specify the name. Now this report will include only those records which contain the Completed value in the Status column. These can be new records and previously added ones, with updated status.

Please note that the formatting rules we created earlier for the Sales table do not apply to the report. This was done to enable custom formatting for each report.

Create a report for orders with the Processed status in the same way.

Reports can be used as widgets^[6] and connected as a module in the Portal menu and on the Dashboard^[7] . If you have previously customized a report widget embedded on your website, but now you need to change the selection or grouping, you should not delete the report and create a new one. You can change the report parameters at any time. The widget code on the page remains the same.

Just open the table to go to the "Records" menu. To view all table reports, open the All reports tab or use the left-side menu.

Here you can change the report parameters. To do this, open the context menu on the report tile and select the Change parameters:

Note: if you need users to be able to edit the records in the widget, just hide the column^[8] with a status, so that users cannot change the value in this column. Otherwise, the record will disappear from the report. For Portal users, you need to configure field-level access separately.

Next task: How to set up flexible access rights for managers to customer orders?

Let's say we have three manager groups in our store. We can simply add a field with a groups list, and using a selection for this column, generate separate reports (in the same way as we did a selection by Status column above).

To generate monthly reports on completed orders, we use additional search criteria in the Date and Status columns. For example, let's create a report for Group 2, which will contain only completed orders for November:

1. Perform a search;

2. The first criterion is Group = Group 2;

3. The second criterion - Date in the range from 11-01-2021 to 11-30-2021;

4. The third criterion is Status = Complete.

There must be an "and" operator between all the criteria. Thus, only those records will be found that match all three criteria.

Note: the best way to restrict user access rights is Portal. Read here how to configure access rights to certain records for users.

Read also how to use the Record Owners^[9] field to set up access rights for portal users.

Summing up, we can conclude that reports are irreplaceable helpers for working with a database, which greatly simplify data processing and save time.

Useful links:

1. Cloning tables

2. How to create and work with Reports in QuintaDB

3. Database search

4. How to print a report

5. Conditional formatting

6. Report widgets

7. Portal and Dashboard

8. How to hide columns inside a report widget

9. Record Owners field