I'm trying to get a table to display so that my groups "group" correctly. Below is what I have tried. It runs, but repeats ALL of the ArrestOfficers under each Arrest Dept.
For example, ArrestDept "City A" should have ArrestOfficer values of "1, 2" while ArrestDept "City B" should have ArrestOfficer values of "3, 4".
But, when I run the application, I am getting:
| City A |
|---|
| 1 |
| 2 |
| 3 |
| 4 |
| City B |
|---|
| 1 |
| 2 |
| 3 |
| 4 |
Here is my code:
<tbody>
@foreach (var deptGroup in Model.Results.GroupBy(x => x.ArrestDept))
{
<tr class="group-header" style="text-align: left">
<td colspan="4">
<span class="h5">@deptGroup.Key</span>
</td>
</tr>
@foreach (var officerGroup in Model.Results.GroupBy(x => x.ArrestOfficer))
{
<tr class="group-header" style="text-align: left">
<td colspan="4">
<span class="h6">@officerGroup.Key</span>
</td>
</tr>
@foreach (var obj in officerGroup)
{
<tr>
<td width="30%"></td>
<td width="30%" align="left">@obj.PILastName, @obj.PIFirstName @obj.PIMiddleInitial</td>
<td width="20%" align="left">@obj.PIDateOfBirth?.ToShortDateString()</td>
<td width="20%" align="left">@obj.ArrestDate?.ToShortDateString()</td>
</tr>
}
}
}
</tbody>
NOTE: Each officer can have MORE THAN ONE record under them (the PILastName, etc)
The result should look like this:
| City A |
|---|
| 1 |
| 1a |
| 1b |
| 2 |
| 2a |
| 2b |
| 2c |
| City B |
|---|
| 3 |
| 3a |
| 3b |
| 3c |
| 3d |
| 4 |
| 4a |
I also need a way to subtotal by ArrestOfficer, then total by ArrestDept and finally a grand total for the report.
Thinking maybe I cannot accomplish this with just the view, that I need to add something to my .cs?
Still learning and have searched and tried several things I found, but as I couldn't find an exact match to my question, this is as far as I have gotten with anything that doesn't just completely error out! Thanks in advance for any assistance!
Just needed another grouping level and a little change to my code. Successive groups should have been built upon prior groups, not the model (ie, "in group", not "in model."). Here is revised code including the count totals:
<tbody>
@foreach (var deptGroup in Model.Results.GroupBy(x => x.ArrestDept))
{
<tr class="group-header" style="text-align: left" bgcolor="Silver">
<td colspan="4">
<span class="h5">@deptGroup.Key</span>
</td>
</tr>
@foreach (var officerGroup in deptGroup.GroupBy(x => x.ArrestOfficer).ToArray())
{
<tr class="group-header" style="text-align: left" bgcolor="WhiteSmoke">
<td colspan="4">
<span class="h6">@officerGroup.Key</span>
</td>
</tr>
@foreach (var detailGroup in officerGroup)
{
<tr>
<td width="30%"></td>
<td width="30%" align="left">@detailGroup.PILastName, @detailGroup.PIFirstName @detailGroup.PIMiddleInitial</td>
<td width="20%" align="left">@detailGroup.PIDateOfBirth?.ToShortDateString()</td>
<td width="20%" align="left">@detailGroup.ArrestDate?.ToShortDateString()</td>
</tr>
}
<tr>
<td width="30%">Total Arrests By Officer</td>
<td width="30%">@officerGroup.Count()</td>
<td width="20%"></td>
<td width="20%"></td>
</tr>
}
<tr>
<td width="30%">Total Arrests By Agency</td>
<td width="30%">@deptGroup.Count()</td>
<td width="20%"></td>
<td width="20%"></td>
</tr>
}
<tr>
<td width="30%">Total Arrests For Reporting Period</td>
<td width="30%">@Model.Results.Count()</td>
<td width="20%"></td>
<td width="20%"></td>
</tr>
</tbody>
That was it.