{"id":4224,"date":"2021-12-13T06:07:51","date_gmt":"2021-12-13T06:07:51","guid":{"rendered":"https:\/\/himbap.com\/blog\/?p=4224"},"modified":"2021-12-13T06:07:51","modified_gmt":"2021-12-13T06:07:51","slug":"creating-aggregated-view-using-datatable","status":"publish","type":"post","link":"https:\/\/himbap.com\/blog\/?p=4224","title":{"rendered":"Creating aggregated view using DataTable"},"content":{"rendered":"

Requirement<\/strong>
\nLet’s say we want to show aggregated total of the records based on the grouping. let’s understand this with below example:
\n\"tablev1\"<\/a><\/p>\n

In above screenshot group 1 as what we have in our entity and 2 is what we want to show on the dashboard, just want to show single entry for the duplicate record and calculate total.<\/p>\n

Details<\/strong>:
\nI am going to use data table<\/a> here to show this data on dashboard. We need to create HTML web resource
\nusing any html editor. First we need to add reference for the cdn and script in our web resource.<\/p>\n

\r\n<script src="ClientGlobalContext.js.aspx" type="text\/javascript"><\/script>\r\n    <link rel="stylesheet" href="https:\/\/cdn.datatables.net\/1.10.16\/css\/jquery.dataTables.min.css">\r\n    <link rel="stylesheet" href="https:\/\/cdn.datatables.net\/select\/1.2.5\/css\/select.dataTables.min.css">\r\n    <link rel="stylesheet" href="https:\/\/maxcdn.bootstrapcdn.com\/bootstrap\/3.3.7\/css\/bootstrap.min.css">\r\n    <script src="https:\/\/code.jquery.com\/jquery-1.12.4.js"><\/script>\r\n    <script src="https:\/\/cdn.datatables.net\/1.10.16\/js\/jquery.dataTables.min.js"><\/script>\r\n<\/pre>\n
Next we need to fetch data from CRM based on our query<\/p>\n
\r\nfunction LoadVendorGrid() {\r\n            debugger;\r\n            var query = "?$apply=groupby((new_name),aggregate(new_amount with sum as total))&$filter=new_amount gt 0 and new_name ne null";\r\n            window.parent.Xrm.WebApi.retrieveMultipleRecords("new_vendor", query).then(\r\n                function success(results) {\r\n                    var result = results.entities;\r\n                    if (result != null) {\r\n                        $('#vendordt').DataTable({\r\n                            "bInfo": false,\r\n                            data: result,\r\n                            columns: [\r\n                                { data: 'new_name' },\r\n                                { data: 'total', render: $.fn.dataTable.render.number(',', '.', 0, '\u20b9') }\r\n                            ]\r\n                        });\r\n                    }\r\n\r\n                },\r\n                function (error) {\r\n                    console.log(error.message);\r\n\r\n                }\r\n            )\r\n        }\r\n<\/pre>\n
In above code I am fetching details from the vendor entity and grouping data based on the name as well as checking condition to render records where amount is >0 and name is not null to avoid and error in data table rendering. Once we got result we can pass our fields to the data table column like above. Finally we need to make call to this method. Here is the full code of the html web resource.<\/p>\n
\r\n<html>\r\n<head>\r\n    <script src="https:\/\/ajax.googleapis.com\/ajax\/libs\/jquery\/3.5.1\/jquery.min.js"><\/script>\r\n    <link rel="stylesheet" type="text\/css" href="https:\/\/cdn.datatables.net\/1.11.3\/css\/jquery.dataTables.min.css">\r\n    <script type="text\/javascript" charset="utf8" src="https:\/\/cdn.datatables.net\/1.11.3\/js\/jquery.dataTables.min.js"><\/script>\r\n    <script src="ClientGlobalContext.js.aspx" type="text\/javascript"><\/script>\r\n    <meta charset="utf-8">\r\n    <title><\/title>\r\n    <script type="text\/javascript">\r\n        $(document).ready(function () {\r\n            LoadVendorGrid();\r\n        });\r\n        function LoadVendorGrid() {\r\n            debugger;\r\n            var query = "?$apply=groupby((new_name),aggregate(new_amount with sum as total))&$filter=new_amount gt 0 and new_name ne null";\r\n            window.parent.Xrm.WebApi.retrieveMultipleRecords("new_vendor", query).then(\r\n                function success(results) {\r\n                    var result = results.entities;\r\n                    if (result != null) {\r\n                        $('#vendordt').DataTable({\r\n                            "bInfo": false,\r\n                            data: result,\r\n                            columns: [\r\n                                { data: 'new_name' },\r\n                                { data: 'total', render: $.fn.dataTable.render.number(',', '.', 0, '\u20b9') }\r\n                            ]\r\n                        });\r\n                    }\r\n\r\n                },\r\n                function (error) {\r\n                    console.log(error.message);\r\n\r\n                }\r\n            )\r\n        }\r\n    <\/script>\r\n   <\/head>\r\n<body onfocusout="parent.setEmailRange();" style="overflow-wrap: break-word;">\r\n\r\n<table id="vendordt" class="cell-border">\r\n\r\n<thead>\r\n\r\n<tr>\r\n\r\n<th>Vendor Name<\/th>\r\n\r\n\r\n<th>Amout<\/th>\r\n\r\n            <\/tr>\r\n\r\n        <\/thead>\r\n\r\n    <\/table>\r\n\r\n<\/body>\r\n<\/html>\r\n<\/pre>\n
Now we can place this code in html web resource and place it to the dashboard. While rendering it should look like below:
\n\"vendorgrid\"<\/a><\/p>\n
This is how we can use data table and use it’s out of the box search, sorting and paging features.<\/p>\n
Hope it will help someone !!
\nKeep learning and Keep Sharing !! <\/strong><\/p>\n","protected":false},"excerpt":{"rendered":"
Requirement Let’s say we want to show aggregated total of the records based on the grouping. let’s understand this with below example: In above screenshot group 1 as what we have in our entity and 2 is what we want to show on the dashboard, just want to show single entry for the duplicate record and calculate total. Details: I… Read more »<\/a><\/p>\n","protected":false},"author":1,"featured_media":4230,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":[],"categories":[21,402,522,492],"tags":[924,923,922,928,925,926,929,927,921],"_links":{"self":[{"href":"https:\/\/himbap.com\/blog\/index.php?rest_route=\/wp\/v2\/posts\/4224"}],"collection":[{"href":"https:\/\/himbap.com\/blog\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/himbap.com\/blog\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/himbap.com\/blog\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/himbap.com\/blog\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=4224"}],"version-history":[{"count":5,"href":"https:\/\/himbap.com\/blog\/index.php?rest_route=\/wp\/v2\/posts\/4224\/revisions"}],"predecessor-version":[{"id":4233,"href":"https:\/\/himbap.com\/blog\/index.php?rest_route=\/wp\/v2\/posts\/4224\/revisions\/4233"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/himbap.com\/blog\/index.php?rest_route=\/wp\/v2\/media\/4230"}],"wp:attachment":[{"href":"https:\/\/himbap.com\/blog\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=4224"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/himbap.com\/blog\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=4224"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/himbap.com\/blog\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=4224"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}