Complex report with links to different object types in FastReport

Consider a report on overdue project and process tasks.

There are two ways to add links: specify a link address in SQL and divide tasks by types after selecting data.

Specifying a link in an SQL query

This method can be used in most cases. The constant part of the link is specified in the SQL query:

select p.Id, ’/Projects/ProjectTask/Execute/’ as link,  t.Subject as Name, t.CreationAuthor as AuthorID, t.EndWorkDate as Closed, t.EndDate as Due Date
from ProjectTask as p
Left join TaskBase as t on t.Id=p.Id
Where (t.EndWorkDate > t.EndDate OR t.EndDate<GetDate())

’/Projects/ProjectTask/Execute/’ as link  - write the constant part to the link field.

Create a hyperlink in the report and insert a simple formula

[Data.link]+[Data.Id]

The resulting report will display all the overdue project tasks with links.

Now add the selection of similar data on process tasks to the SQL query. The SQL query will look like this:

select p.Id, ’/Projects/ProjectTask/Execute/’ as link,  t.Subject as Name, t.CreationAuthor as AuthorID, t.EndWorkDate as Closed, t.EndDate as Due Date
from ProjectTask as p
Left join TaskBase as t on t.Id=p.Id
Where (t.EndWorkDate > t.EndDate OR t.EndDate<GetDate())
 
Union all
 
select w.Id, ’/Workflow/WorkflowTask/Execute/ ’ as link,  t.Subject as Name, t.CreationAuthor as AuthorID, t.EndWorkDate as Closed, t.EndDate as Due Date
from WorkflowTask as w
Left join TaskBase as t on t.Id=w.Id
Where (t.EndWorkDate > t.EndDate OR t.EndDate<GetDate())

The resulting report will display all the overdue project and process tasks in a single list with links.

Specifying a link after data selection

This method can be used for the reports, in which different kinds of data are selected to a single list.

To search and identify a task type, use the task type UID. You can find it in the database using a simple script, or in case of tasks on the advanced search form.

Go to the Tasks section, open the Advanced Search and click EQL Search.

 In the EQL query field enter "TypeUid =". The system will open a list of task types with Uids.

To learn more about EQL search, read Help.

The SQL query will look like this:

Select t.CreationAuthor as AuthorID, t.EndWorkDate as Closed, t.EndDate as Due Date, t.TypeUid as Uid,  t.Id  as Link
from TaskBase as t
Where (t.EndWorkDate > t.EndDate OR t.EndDate<GetDate()) AND (t.TypeUid =  (’20404079-49d9-4068-9de5-4ecd2c750868’) OR t.TypeUid = (’555a587d-25c1-4f99-ade6-3176b4fc9a6c’))

This SQL query selects all the overdue tasks on projects (t.TypeUid =  (’20404079-49d9-4068-9de5-4ecd2c750868’)) and processes (t.TypeUid = (’555a587d-25c1-4f99-ade6-3176b4fc9a6c’)).

Open the Report Layout in FastReport and create a link: add the required fields to the form, right click on the required field and select Hyperlink.

In the opened window, click on the formula icon in the second field.

The main difference from the simple case is that you have an Id field with the task ID, but the link constant part is different for project and process tasks. To correctly generate the link, add the following condition to the formula:

(Condition)  ? (action if true) : (action if false)

To learn more about conditional operators, follow this link. 

([Data.Uid] = = new Guid("20404079-49d9-4068-9de5-4ecd2c750868")) ? "/Projects/ProjectTask/Execute/"+[Data.Id] : "/Workflow/WorkflowTask/Execute/"+[Data.Id]

Thus, if the object type Uid matches the project task Uid, the link will be generated as "/Projects/ProjectTask/Execute/"+[Data.Id], otherwise "/Workflow/WorkflowTask/Execute/"+[Data.Id]

The resulting report on overdue project and process tasks with links: