locked
jqyery datatable is not grouping Please help RRS feed

  • Question

  • User-1355965324 posted

    I have the following code given below to generate the report using  datatable

    HTML

    <div class="col-sm-12 portlet light @ViewBag.IsReport" id="divReport">
                                <table id="tblAttendance" class="table table-bordered table-striped" style="width:100%;font-size:90%">
                                    <thead class="thead-light">
                                        <tr>
                                            <th>
                                                Date
                                            </th>
                                            <th>
                                                Depot Name
                                            </th>
                                            <th>
                                                Employee Name
                                            </th>
                                            <th>
                                                Clocked In
                                            </th>
                                            <th>
                                                Clocked Out
                                            </th>
                                            <th>
                                                N. Attendance
                                            </th>
                                            <th>
                                                Day Off
                                            </th>
    
                                            <th>
                                                Sick
                                            </th>
                                            <th>
                                                Holiday
                                            </th>
                                            <th>
                                                Maternity
                                            </th>
                                            <th>
                                                Time Off
                                            </th>
                                            <th>
                                                Time Off Hrs
                                            </th>
                                            <th>
                                                Deducted
                                            </th>
                                            <th>
                                                Emp Hrs
                                            </th>
                                            <th>
                                                Extra Hrs
                                            </th>
                                            <th>
                                                Remark
                                            </th>
                                            <th>
                                                SortDate
                                            </th>
                                        </tr>
                                    </thead>
                                    <tbody>
                                        
                                        <tr>
                                            <td> FromDate</td>
                                            <td> DepotName</td>
                                            <td> EmployeeName</td>
                                            <td> ClockedIn</td>
                                            <td> ClockedOut</td>
                                            <td> IsNormalAttendance</td>
                                            <td> IsDayOffMarked</td>
                                            <td> IsSickMarked</td>
                                            <td> IsHolidayMarked </td>
                                            <td> IsMaternity </td>
                                            <td> IsTimeOff </td>
                                            <td> TimeOffHrs </td>
                                            <td> IsDeducted </td>
                                            <td> DueHrs </td>
                                            <td> ExtraTimeHrs </td>
                                            <td> Description </td>
                                            <td> SortDate </td>
    
                                        </tr>
     
    
                                    </tbody>
    
                                </table>
                            </div>

    Javascript

    <script>
        $(document).ready(function () {
            
            var report = {};
            report.FromDate = "@Model.FromDate"
            report.ToDate = "@Model.ToDate"
            report.EmployeeIds = "@Model.EmployeeIds"
             
    
            $('#tblAttendance').DataTable({
                
                "ajax": {
                    "url": "/Report/AttendanceReportData",
                    "data": report,
                    "dataSrc": function (json) {
                        return JSON.parse(json);
                    }
                },
                "columns": [
                    { "data": "FromDate" },
                    { "data": "DepotName" },
                    { "data": "EmployeeName" },
                    { "data": "ClockedIn" },
                    { "data": "ClockedOut" },
                    { "data": "IsNormalAttendance" },
                    { "data": "IsDayOffMarked" },
                    { "data": "IsSickMarked" },
                    { "data": "IsHolidayMarked" },
                    { "data": "IsMaternity" },
                    { "data": "IsTimeOff" },
                    { "data": "TimeOffHrs" },
                    { "data": "IsDeducted" },
                    { "data": "DueHrs" },
                    { "data": "ExtraTimeHrs" },
                    { "data": "Description" },
                    { "data": "SortDate" }
                    
                ],
    
                order: [
                    [1, 'asc'],
                    [2, 'asc'],
                    [16, 'asc'],
                ],
                rowGroup: {
                    dataSrc: [2, 1],
                    startRender: function (rows, group) {
                        return $('<tr/>')
                            .append('<td class="td-left" >' + group + '</td>');
    
                    }
                },
               
                "pageLength": 40,
                scrollY: "500px",
                scrollX: true,
                 paging: true,
                 dom: 'Bfrtip',
                buttons: [
                    'copy',
                    'csv',
                    'excel',
                    {
                        extend: 'pdfHtml5',
                        orientation: 'landscape',
                        pageSize: 'LEGAL' }
    
                ]
    
            });
        });
    </script>

    Controller

    public JsonResult AttendanceReportData(ReportViewModel report)
            {
                DateTime fromDate = new DateTime();
                DateTime toDate = new DateTime();
                if (report.FromDate != "" && report.ToDate != "" && report.FromDate != null && report.ToDate != null)
                {
                    fromDate = DateTime.ParseExact(report.FromDate, "dd/MM/yyyy", CultureInfo.InvariantCulture);
                    toDate = DateTime.ParseExact(report.ToDate, "dd/MM/yyyy", CultureInfo.InvariantCulture);
    
                }
                else
                {
                    fromDate = DateTime.ParseExact(DateTime.UtcNow.ToShortDateString(), "dd/MM/yyyy", CultureInfo.InvariantCulture);
                    toDate = DateTime.ParseExact(DateTime.UtcNow.ToShortDateString(), "dd/MM/yyyy", CultureInfo.InvariantCulture);
    
                }
    
                DataSet ds  = ReportBLL.GetEmployeeAttendanceReport( String.Join(",", report.EmployeeIds), fromDate, toDate, connectionSettings);
                // GetClockingVariance(String.Join(",", report.Employee), fromDate, toDate, connectionSettings);
                List<ReportViewModel> empClockingList = new List<ReportViewModel>();
                foreach (DataRow dr in ds.Tables[0].Rows)
                {
                   
                       
    
                    empClockingList.Add(new ReportViewModel
                    {
                        FromDate = dr["Date"].ToString(),
                        DepotName = dr["DepotName"].ToString(),
                        EmployeeName = dr["EmployeeName"].ToString(),
                        ClockedIn = dr["DateClockedIn"].ToString(),
                        ClockedOut = dr["DateClockedOut"].ToString(),
                        IsNormalAttendance =Convert.ToInt32( dr["Normal"].ToString()),
                        IsDayOffMarked = Convert.ToInt32(dr["DayOff"].ToString()),
                        IsSickMarked = Convert.ToInt32(dr["Sick"].ToString()),
                        IsHolidayMarked = Convert.ToInt32(dr["Holiday"].ToString()),
                        IsMaternity = Convert.ToInt32(dr["Maternity"].ToString()),
                        IsTimeOff = Convert.ToInt32(dr["TimeOff"].ToString()),
                        TimeOffHrs = Convert.ToDecimal(dr["TimeOffHrs"].ToString()),
                        IsDeducted = dr["IsDeducted"].ToString(),
                        DueHrs = Convert.ToDecimal(dr["EmpHrs"].ToString()),
                        ExtraTimeHrs = Convert.ToDecimal(dr["ExtraTimeHrs"].ToString()),
                        Description= dr["Remark"].ToString(),
                        SortDate = Convert.ToDateTime(dr["Date"].ToString())
                        
    
    
    
    
                    });
                }
    
                String jsonResult = JsonConvert.SerializeObject(empClockingList);
                // return Json(new {   data = empClockingList });
    
                return Json(jsonResult);
    
    
            }

    I want to add the grouping   with column  DepotName and then Employee. It was working fine before using datatable but after changing the code using json call, the grouping is  not working. Please any help would be very much appreciated

    Friday, September 13, 2019 1:58 PM

Answers

  • User-1355965324 posted

    I sorted the issue by adding

    $('#tblAttendance').DataTable({
                "ajax": {
                    "url": "/Report/AttendanceReportData",
                    "data": report,
                    "dataSrc": function (json) {
                        return JSON.parse(json);
                    }
                },
    
                "columns": [
                    { "data": "FromDate" },
                    { "data": "DepotName" },
                    { "data": "EmployeeName" },
                    { "data": "ClockedIn" },
                    { "data": "ClockedOut" },
                    { "data": "IsNormalAttendance" },
                    { "data": "IsDayOffMarked" },
                    { "data": "IsSickMarked" },
                    { "data": "IsHolidayMarked" },
                    { "data": "IsMaternity" },
                    { "data": "IsTimeOff" },
                    { "data": "TimeOffHrs" },
                    { "data": "IsDeducted" },
                    { "data": "DueHrs" },
                    { "data": "ExtraTimeHrs" },
                    { "data": "Description" },
                    { "data": "SortDate" }
    
                ],
                order: [[2, 'asc'], [1, 'asc'], [16, 'asc']],
                rowGroup: {
                    dataSrc: ["EmployeeName", "DepotName"],
                    startRender: function (rows, group) {
                        return $('<tr/>')
                            .append('<td class="td-left" >' + group + '</td>');
                    }
                },
    
    
                "columnDefs": [
                    {
                        "targets": [1],
                        "visible": false
                    },
                    {
                        "targets": [2],
                        "visible": false
                    },
                    {
                        "targets": [16],
                        "visible": false
                    },
                    {
                        "className": "dt-center", "targets": "_all"
                    },
                    { "width": "10%", "targets": 0 },
                    { "width": "10%", "targets": 4 },
                ],
                "pageLength": 40,
                scrollY: "500px",
                scrollX: true,
                 paging: true,
                 dom: 'Bfrtip',
                buttons: [
                    'copy',
                    'csv',
                    'excel',
                    {
                        extend: 'pdfHtml5',
                        orientation: 'landscape',
                        pageSize: 'LEGAL' }
    
                ]
    
            });

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, September 16, 2019 10:51 AM

All replies

  • User665608656 posted

    Hi polachan,

    I want to add the grouping   with column  DepotName and then Employee. It was working fine before using datatable but after changing the code using json call, the grouping is  not working. Please any help would be very much appreciated

    I found that you also sorted the two columns which you want to group separately.

    When you sorted them, they displayed the data in the grouping format. I don't know what the grouping you mentioned didn't work.

    Can you provide detailed data screenshots to tell us your specific needs?

    Here is my data based on your needs, it shows the grouping format for the sort setting.

    Best Regards,

    YongQing.

    Monday, September 16, 2019 7:28 AM
  • User-1355965324 posted

    I sorted the issue by adding

    $('#tblAttendance').DataTable({
                "ajax": {
                    "url": "/Report/AttendanceReportData",
                    "data": report,
                    "dataSrc": function (json) {
                        return JSON.parse(json);
                    }
                },
    
                "columns": [
                    { "data": "FromDate" },
                    { "data": "DepotName" },
                    { "data": "EmployeeName" },
                    { "data": "ClockedIn" },
                    { "data": "ClockedOut" },
                    { "data": "IsNormalAttendance" },
                    { "data": "IsDayOffMarked" },
                    { "data": "IsSickMarked" },
                    { "data": "IsHolidayMarked" },
                    { "data": "IsMaternity" },
                    { "data": "IsTimeOff" },
                    { "data": "TimeOffHrs" },
                    { "data": "IsDeducted" },
                    { "data": "DueHrs" },
                    { "data": "ExtraTimeHrs" },
                    { "data": "Description" },
                    { "data": "SortDate" }
    
                ],
                order: [[2, 'asc'], [1, 'asc'], [16, 'asc']],
                rowGroup: {
                    dataSrc: ["EmployeeName", "DepotName"],
                    startRender: function (rows, group) {
                        return $('<tr/>')
                            .append('<td class="td-left" >' + group + '</td>');
                    }
                },
    
    
                "columnDefs": [
                    {
                        "targets": [1],
                        "visible": false
                    },
                    {
                        "targets": [2],
                        "visible": false
                    },
                    {
                        "targets": [16],
                        "visible": false
                    },
                    {
                        "className": "dt-center", "targets": "_all"
                    },
                    { "width": "10%", "targets": 0 },
                    { "width": "10%", "targets": 4 },
                ],
                "pageLength": 40,
                scrollY: "500px",
                scrollX: true,
                 paging: true,
                 dom: 'Bfrtip',
                buttons: [
                    'copy',
                    'csv',
                    'excel',
                    {
                        extend: 'pdfHtml5',
                        orientation: 'landscape',
                        pageSize: 'LEGAL' }
    
                ]
    
            });

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, September 16, 2019 10:51 AM