Collapsible panels and grouping in reports

A convenient representation of large amounts of information is an important aspect of designing interfaces. This article describes how to group data and place it in collapsible panels using .NET Razor. To learn more about creating reports, read this article.

Grouping

To start working, open the Display Settings tab of your report and select .NET Razor in the Report Layout unit. A markup pane will open. You do not need to create markup manually, the toolbar contains the Template Wizard button (for more information, read this article), which generates the base layout code. It looks like this:

@using EleWise.ELMA.BPM.Web.Reports.Extensions
@using System.Data
 
@model EleWise.ELMA.BPM.Web.Reports.Models.ReportParametersInfo
 
@{
    //Get the data source by name
    var data = Model.DataSources["Data"];
    //Execute the HQL or SQL query from the data source and get the result
    DataTable items = data.Get();
}
 
<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>
<p style="font-size:20px; text-align:center;"></p>
 
@* Enables per-page display of the data source *@
@(Html.Pager(Model, data))
@* Report column headers. Column names are taken from the column names of the table that contains the data source execution result *@
<table class="list" width="100%" style="margin-top: 10px; font-size: 11px;">
    <tr>
        <th scope="col">@items.Columns["Subject"]</th>
        <th scope="col">@items.Columns["Name"]</th>
        <th scope="col">@items.Columns["FullName"]</th>
    </tr>
 
    @* The data source execution result is a table. Go throught the table and display the column values *@
    @foreach (DataRow row in items.Rows)
    { 
        <tr valign="top">
            <td>
                @* Get the value of the Subject column from the current row *@
                @row["Subject"]
            </td>
            <td>
                @* Get the value of the Name column from the current row *@
                @row["Name"]
            </td>
            <td>
                @* Get the value of the FullName column from the current row *@
                @row["FullName"]
            </td>
        </tr>
    }
</table>
</div>

The resulting report will look like this:

First, you need to disable paging, since sorting on different pages is impossible. To do so, delete the following structure (you can find it by the comment):

@* Enables per-page display of the data source *@

@(Html.Pager(Model, data))

Then you get the data that must be sorted by some field. Add this line:

var groupedItems = items.Rows.Cast<DataRow>().GroupBy(r => r["FullName"]);

after the line:

//Execute the HQL or SQL query from the data source and get the result

DataTable items = data.GetAll();

Where ["FullName"] is the field that will be used for grouping.

After that, you need to find the start of the cycle for entering data, which looks like this:

@* The data source execution result is a table. Go through the table rows and display the column values *@

@foreach (DataRow row in items.Rows)

Change it to:

@foreach (DataRow row in gi)

Then find:

@* Report column headers. The column names are taken from the column names of the table that contains the data source execution result *@

And insert another cycle under the comment:

@foreach(var gi in groupedItems)

{

The closing brace of the cycle must be placed at the end of the report markup, between the </table> and </div> tags

</table>

}

</div>

The code will look like this:

@using EleWise.ELMA.BPM.Web.Reports.Extensions

@using System.Data
 
@model EleWise.ELMA.BPM.Web.Reports.Models.ReportParametersInfo
 
@{
    //Get the data source by name
    var data = Model.DataSources["Data"];
    //Execute the HQL or SQL query from the data source and get the result
    DataTable items = data.Get();
    var groupedItems = items.Rows.Cast<DataRow>().GroupBy(r => r["FullName"]);
 
}
 
<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>
<p style="font-size:20px; text-align:center;"></p>
 
 
@* Report column headers. Column names are taken from the column names of the table that contains the data source execution result *@
@foreach(var gi in groupedItems)
{
<table class="list" width="100%" style="margin-top: 10px; font-size: 11px;">
    <tr>
        <th scope="col">@items.Columns["Subject"]</th>
        <th scope="col">@items.Columns["Name"]</th>
        <th scope="col">@items.Columns["FullName"]</th>
    </tr>
 
    @* The data source execution result is a table. Go throught the table and display the column values *@
    @foreach (DataRow row in gi)
    { 
        <tr valign="top">
            <td>
                @* Get the value of the Subject column from the current row *@
                Task subject
            </td>
            <td>
                @* Get the value of the Name column from the current row *@
                @row["Name"]
            </td>
            <td>
                @* Get the value of the FullName column from the current row *@
                @row["FullName"]
            </td>
        </tr>
    }
</table>
}
</div>

The report will look like this:

As you can see, the data are grouped by the executor full name.

Collapsible panels

Now let's hide data groups in collapsible panels.

First, add this namespace:

@using EleWise.ELMA.Web.Mvc.Html

And then this structure:

@(Html.CollapsiblePanel()

.Header(string.Format("{0}", SR.T("Panel")))

.SaveState(false)

.Expanded(false)

.Class("Input_Separator")

.Content(@<text>

</text>).Render())

Inside the <text> </text> tag place the data to hide, and in the line .Header(string.Format("{0}", SR.T("Panel"))) specify the panel name. At the moment, this is just a Panel row, which can display, for example, the grouping object name (in this case, the executor full name). For this you can replace the Panel line with

@gi.Key.ToString()

I.e. the name of the key column used for grouping. Since the collapsible panels will contain all the report data, you need to place the part of the report markup responsible for data display inside the <text> </text> tag. Therefore, the first part

@(Html.CollapsiblePanel()

.Header(string.Format("{0}", SR.T("Panel")))

.SaveState(false)

.Expanded(false)

.Class("Input_Separator")

.Content(@<text>

</text>).Render())

Must be placed right after the first foreach cycle

@foreach(var gi in groupedItems)

{

@(Html.CollapsiblePanel()

.Header(string.Format("{0}", SR.T(@gi.Key.ToString())))

.SaveState(false)

.Expanded(false)

.Class("Input_Separator")

.Content(@<text>

Close at the very end:

</table>

</text>).Render())

}

</div>

Before closing the cycle.

The code will look like this:

@using EleWise.ELMA.BPM.Web.Reports.Extensions
@using System.Data
@using EleWise.ELMA.Web.Mvc.Html
@model EleWise.ELMA.BPM.Web.Reports.Models.ReportParametersInfo
 
@{
    //Get the data source by name
    var data = Model.DataSources["Data"];
    //Execute the HQL or SQL query from the data source and get the result
    DataTable items = data.Get();
    var groupedItems = items.Rows.Cast<DataRow>().GroupBy(r => r["FullName"]);
 
}
 
<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>
<p style="font-size:20px; text-align:center;"></p>
 
 
@* Report column headers. Column names are taken from the column names of the table that contains the data source execution result *@
@foreach(var gi in groupedItems)
{
@(Html.CollapsiblePanel()
                      .Header(string.Format("{0}", SR.T(@gi.Key.ToString())))
                      .SaveState(false)
                      .Expanded(false)
                      .Class("Input_Separator")
                      .Content(@<text>
<table class="list" width="100%" style="margin-top: 10px; font-size: 11px;">
    <tr>
        <th scope="col">@items.Columns["Subject"]</th>
        <th scope="col">@items.Columns["Name"]</th>
        <th scope="col">@items.Columns["FullName"]</th>
    </tr>
 
    @* The data source execution result is a table. Go throught the table and display the column values *@
    @foreach (DataRow row in gi)
    { 
        <tr valign="top">
            <td>
                @* Get the value of the Subject column from the current row *@
                Task subject
            </td>
            <td>
                @* Get the value of the Name column from the current row *@
                @row["Name"]
            </td>
            <td>
                @* Get the value of the FullName column from the current row *@
                @row["FullName"]
            </td>
        </tr>
    }
</table>
</text>).Render())
}
</div>

The report:

Using parameters

You may want to configure changing the report grouping right on the report web page, without making changes in the designer. For this, you can use parameters.

To use parameters in the report markup, use the Controller Code section (may take long to open). Instead of the commented:

//result.Model.UseCustomModel = true;
//result.Model.CustomModel = new string[] {"one", "two", "three"}

add the following lines:

result.Model.UseCustomModel = true;

result.Model.CustomModel = parameters;

At the beginning of the markup, add:

dynamic parameters = Model.CustomModel;

Now using the parameters variable, the report parameters can be referenced. In this example, Grouping drop-down list with two values - Executor and Activity Type - is used.

Add conditions to the grouping type and panel names

if(parameters.Grouping.Value == "Activity")

{

groupedItems = items.Rows.Cast<DataRow>().GroupBy(r => r["Name"]);

}

By default, the groupedItems variable is declared with grouping by FullName

var groupedItems = items.Rows.Cast<DataRow>().GroupBy(r => r["FullName"]);

If Activity Type is selected in the drop down list, data will be grouped by activity. And as a result of specifying the key column

.Header(string.Format("{0}", SR.T(@gi.Key.ToString())))

as the panel name, the name will be changing automatically.

The markup will look like this:

@using EleWise.ELMA.BPM.Web.Reports.Extensions
@using System.Data
@using EleWise.ELMA.Web.Mvc.Html
@model EleWise.ELMA.BPM.Web.Reports.Models.ReportParametersInfo
 
@{
    //Get the data source by name
    var data = Model.DataSources["Data"];
    //Execute the HQL or SQL query from the data source and get the result
    dynamic parameters = Model.CustomModel;
    DataTable items = data.Get();
    var groupedItems = items.Rows.Cast<DataRow>().GroupBy(r => r["FullName"]);
    if(parameters.Grouping.Value == "Activity Type")
    {
    groupedItems = items.Rows.Cast<DataRow>().GroupBy(r => r["Name"]);
    }
 
 
}
 
<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>
<p style="font-size:20px; text-align:center;"></p>
 
 
@* Report column headers. Column names are taken from the column names of the table that contains the data source execution result *@
@foreach(var gi in groupedItems)
{
@(Html.CollapsiblePanel()
                      .Header(string.Format("{0}", SR.T(@gi.Key.ToString())))
                      .SaveState(false)
                      .Expanded(false)
                      .Class("Input_Separator")
                      .Content(@<text>
<table class="list" width="100%" style="margin-top: 10px; font-size: 11px;">
    <tr>
        <th scope="col">@items.Columns["Subject"]</th>
        <th scope="col">@items.Columns["Name"]</th>
        <th scope="col">@items.Columns["FullName"]</th>
    </tr>
 
    @* The data source execution result is a table. Go throught the table and display the column values *@
    @foreach (DataRow row in gi)
    { 
        <tr valign="top">
            <td>
                @* Get the value of the Subject column from the current row *@
                Task subject
            </td>
            <td>
                @* Get the value of the Name column from the current row *@
                @row["Name"]
            </td>
            <td>
                @* Get the value of the FullName column from the current row *@
                @row["FullName"]
            </td>
        </tr>
    }
</table>
</text>).Render())
}
</div>

The report, grouped by activity type: