Example of creating a simple report

This article describes the entire process of creating a simple report: creating a query for the database (DB), configuring the report parameters, modeling a report layout. As an example, we are going to create a report on contractors, which displays information on the industry and the responsible manager.

Designer

In Designer, open the Reports section and create a new report, which will display information on contractors, industry and the responsible users. Use the Contractor, User and Industry objects. You can find these objects on the Report Data Sources tab, in the Objects unit. In the Objects unit you can find the required object by the Displayed name field and then check the database table name in the Name field.

Editing a data source

1.1. In the toolbar click Edit and select Internal (SQL).

1.2. On the parameters tab, add the Responsible parameter (Type: User; Link: Single).

1.3. Write a query that will display the contractor name, industry and responsible user. All the specified fields are stored in one table, Contractors, but some of the fields store only the IDs of other tables, therefore link all the required tables in the query. 

Query text:

select Contractor.Name, ContractorIndustry.Industry, [User].FullName
from Contractor
left join [User] on Contractor.Responsible=[User].Id
left join ContractorIndustry on Contractor.Industry=ContractorIndustry.Id
{if {$Responsible} <> Null }
where
[User].Id = {$Responsible.Id}
{end if}

Query description:

select Contractor.Name, ContractorIndustry.Industry, [User].FullName – select the Name field from the Contractor table (Contractor name), the field Industry from the ContractorIndustry table (Contractor industry), the FullName field from the [User] table (Responsible user).

from Contractor – specify the table to take data from.

left join [User] on Contractor.Responsible=[User].Id – join the User table with the Contractor table via the Responsible field from the Contractor table with the Id field from the User table. This is necessary for the report to display the full name of an employee in the Responsible field instead of its ID.

left join ContractorIndustry on Contractor.Industry=ContractorIndustry.Id – join the ContractorIndustry table with the Contractor table via the Industry field of the Contractor table with the Id field from the ContractorIndustry table. This is necessary for the report to display the industry name in the Industry field instead of its ID.

{if {$Responsible} <> Null }

where

[User].Id = {$Responsible.Id}

{end if} - the condition, according to which all the entries are shown if the Responsible field is empty, and only the filtered entries if the parameter is specified.

Report Layout

Go to the Layout tab and create the report layout in FastReport for the web part.

 

Add the received data to the layout, to Data: Data. Rename the columns.

 

To check the report, you can start debugging by clicking on the respective button in the toolbar.

 

 If you do not specify the responsible user, the following result will be displayed:

 

Specify a responsible user and display report again:

 

As the result, the contractors for which the selected user is responsible will be displayed.

Save and publish the report, to make it available in the web part.

Thus, you can create a simple report using one parameter with a layout in FastReport.