Example of creating a complex report

This article describes the entire process of creating a complex report: creating a query to the database (DB), configuring the report parameters, developing the report layout. As an example, we will use the report on contractors, created in the article "Example of creating a simple report". In the new report, we will display a sub-report - Scheduled relationships with a contractor.

Designer

In the Reports section of the Designer, create a report that will show information on contractors, industry and the responsible user. Add a field for the sub-report link. Name it "Scheduled Activities". On this link display all the scheduled relationships of a contractor. 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.

select distinct
Contractor.Name as Contractor
, Contractor.Id
, ContractorIndustry.Industry as Industry
, [User].FullName as Responsible
, Contractor.CreationDate
, ’More’ as LINK
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 distinct // exclude duplicate rows

Contractor.Name as Contractor // new name Contractor for the Name field

, Contractor.Id// Id of the contractor, used for linking to the sub-report

, ContractorIndustry.Industry as Industry // Industry name for the Industry field

, [User].FullName as Responsible// Responsible name for the user responsible for the contractor

, Contractor.CreationDate

, ’More’ as LINK// add a LINK column for the links to the sub-report.

fromContractor // specify the table to take data from.

leftjoin [User] onContractor.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}

{endif}// 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 Display Settings tab and select .NET Razor in the Report Layout unit.

Go to the Report Layout tab to create a report in .NET Razor. You can write code for displaying data in HTML or use the Template Wizard.

Move the available fields to the Selected Fields column and click OK. The fields will be displayed in the report.

As the result, code will be generated in Designer.

 

To check the report you can start debugging using the respective button in the toolbar.

  

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

 

The column names are displayed as specified in the query. You can rename the columns in the code:

 

<th scope="col">@items.Columns["LINK"]</th>

Target value:

<th scope="col">Scheduled Activities</th>

Result:

select tbl.* 
from (
    select c1.Name as Name, rs1.Theme as Theme, rs1.Description as Description, ’Call’ as DescriptionF, rs1.StartDate as StartDate
    from RelationshipCall rsc1 
    left join Relationship rs1 on rsc1.id = rs1.id
    left join Contractor c1 on rs1.Contractor = c1.Id
    where  c1.id=@CID and rs1.DoneDate is null
union
        select c2.Name as Name, rs2.Theme as Theme, rs2.Description as Description, ’Mail’ as DescriptionF, rs2.StartDate as StartDate
    from RelationshipMail rsc2 
    left join Relationship rs2 on rsc2.id = rs2.id
    left join Contractor c2 on rs2.Contractor = c2.Id
    where  c2.id=@CID and rs2.DoneDate is null
union
    select c3.Name as Name, rs3.Theme as Theme, rs3.Description as Description, ’Meeting’ as DescriptionF, rs3.StartDate as StartDate
    from RelationshipMeeting rsc3
    left join Relationship rs3 on rsc3.id = rs3.id
    left join Contractor c3 on rs3.Contractor = c3.Id
    where  c3.id=@CID and rs3.DoneDate is null
) tbl

@{String temp = "other";}

 to check the relationship type ‘Call’, ‘Mail’, ‘Meeting’. Check the condition in the cycle:

<text>@if (row["DESCRIPTIONF"].ToString() != temp)
 
{
 
<tr style=’background: lightgreen’>
 
<td>@row["DESCRIPTIONF"]</td>
 
<td></td>
 
<td></td>
 
<td></td>
 
<td></td>
 
</tr>
 
}</text> 

//a header will be rendered if <span >String </span> is not ‘Call’, ‘Mail’, ‘Meeting’.

Next, the remaining rows and columns are rendered. At the end set the current value to the columns

<text>@{temp = row["DESCRIPTIONF"].ToString();}</text>

Add a link to the sub-report. Add a link for displaying the sub-report using the Create Link button. The link is added to the column, which should contain the link to the sub-report in the code. In this example, it is LINK.

In the Link Type field select Report, in the Report Name field specify the report name - Scheduled Activities. 

Next, specify the value for the sub-report parameter CID – ID, i.e. Contractor ID is passed to the CID parameter of the sub-report, in order to display the scheduled activities on the specific contractor:

In Link Name Field, specify the field, to which the link will be bound in the parent report – LINK. For convenience, check the Open in New Window box:

As the result, you get the following link:

@Url.ReportLink(row,Model.Debug,"Scheduled Activities",new {CID="Id"},"LINK",true)

To check the report, start debugging:

When you click More, the Scheduled Activities sub-report will open in a new tab:

To hide the CID parameter from users, configure the parameter display on the Display Settings tab in the Parameter Input Form unit, select The form is defined by the user (.NET Razor):

Open the Parameter Form Layout tab, click Template Wizard; it will automatically generate the code for displaying parameters. Make the parameter not visible by wrapping it in div:

@using EleWise.ELMA.BPM.Web.Reports.Extensions
@using System.Data
@model EleWise.ELMA.BPM.Web.Reports.Models.ReportParametersInfo
<style>
.list th {
    background: none repeat scroll 0 0 #666666;
    color: #FFFFFF;
    padding: 5px;
    text-align: left;
}
.list td {
    border-bottom: 1px solid #CCCCCC;
    padding: 3px 5px;
    vertical-align: middle;
}
</style>
 <div style="display:none"> 
<table style="width:100%" class="listTaskExec">
    @Html.EditorFor(m => m.Parameters)
</table> </div>

Thus, you can create a more complex report using one parameter and .NET Razor layout.