1

I need to have jqGrid with subgrid, in which I will be loading all jqGrid data (both parent & subgrids) from single stored procedure. Wherein my SP is returning me the table with following columns.

Login | ReadingID | Role | Update Time | Comments

I want to show Login name in Parent jqGrid. This will be the only column in parent Grid. & for each of this record I need to have subgrid showing Reading ID records corresponding to that Login. The Reading ID may have repeated entries for any Login. There is one to many cardinality between Login & ReadingID. Now I want to show distinct Reading ID count & UpdateTime count for each Login in subGrid footer. & further I want to have total of all this subgrids footer totals in footer of Parent Grid.

I hope the requirement is clear enough. Did anybody have implemented such jqGrid or created demo.

Update to describe requirement & chosen solution enter image description here

I am using the grid definition as follows

mygrid = $("#RptDocRelease");

    // create the grid without filling it (datatype: "local")
    mygrid.jqGrid({
        datatype: 'local',// to revent initial loading of the grid
        postData: {
            FromDate: function () { return $("#FromDate").val(); },
            ToDate: function () { return $("#ToDate").val(); }
        },
        url: '@Url.Action("DocReleaseProductivityList", "Reports")',
        jsonReader: { repeatitems: false, root: "DocRows" },
        colNames: ['@VirtuOxAdmin.DocReleaseProductivity_Grid_RptDocRelease_Login'],
        colModel: [{ name: 'Login', index: 'Login', }],
        idPrefix: mainGridPrefix,
        subGrid: true,
        gridview: true,
        viewrecords: true,
        pgbuttons: false, pginput: false,
        recordtext: 'Total Rows:{2}',
        emptyrecords: 'Total Rows:0',
        pager: '#LogPager',
        caption: '@VirtuOxAdmin.DocReleaseProductivity_Grid_RptDocRelease_Title',
        height: 'auto',
        width: 770,
        userDataOnFooter: true,
        hidegrid: false,
        headertitles: true,
        loadError: function (xhr, status, error) {
            alert(status + " " + error);
        },
        beforeProcessing: function (data) {
            var rows = data.DocRows, l = rows.length, i, item, subgrids = {};
            for (i = 0; i < l; i++) {
                item = rows[i];
                if (item.id) {
                    subgrids[item.id] = item.ReadingList;
                }
            }
            data.userdata = subgrids;
        },
        subGridRowExpanded: function (subgridDivId, rowId) {
            var $subgrid = $("<table id='" + subgridDivId + "_t'></table>"),
                pureRowId = $.jgrid.stripPref(mainGridPrefix, rowId),
                subgrids = $(this).jqGrid("getGridParam", "userData");

            $subgrid.appendTo("#" + $.jgrid.jqID(subgridDivId));
            $subgrid.jqGrid({
                datatype: "local",
                data: subgrids[pureRowId],
                jsonReader: { repeatitems: false },
                colNames: ['@VirtuOxAdmin.DocReleaseProductivity_Grid_RptDocRelease_ReadingID',
                            '@VirtuOxAdmin.DocReleaseProductivity_Grid_RptDocRelease_Role',
                            '@VirtuOxAdmin.DocReleaseProductivity_Grid_RptDocRelease_UpdateTime',
                            '@VirtuOxAdmin.DocReleaseProductivity_Grid_RptDocRelease_Comments'
                ],
                colModel: [
                    { name: 'ReadingID', index: 'ReadingID', width: 80, fixed: true, sorttype: "integer" },
                    { name: 'Role', index: 'Role', width: 100, fixed: true },
                    {
                        name: 'UpdateTime', index: 'UpdateTime', width: 80, fixed: true, sorttype: "date", formatter: "date",
                        formatoptions: { srcformat: "m/d/Y h:i:s", newformat: "m/d/Y h:i:s A" }
                    },
                    { name: 'Comments', index: 'Comments' }
                ],
                cmTemplate: { align: 'center', sorttable: false, cellattr: function () { return 'style="white-space: normal!important;' } },
                height: 'auto',
                width: '100%',
                hidegrid: false,
                viewrecords: true,
                pgbuttons: false, pginput: false,
                recordtext: 'Total Rows:{2}',
                emptyrecords: 'Total Rows:0',
                pager: rowId + '_Pager',
                userDataOnFooter: true,
                headertitles: true,
                gridview: true,
                loadError: function (xhr, status, error) {
                    alert(status + " " + error);
                },
                gridview: true,
                idPrefix: rowId + "_"
            });
            $("#" + rowId + "_Pager").navGrid('#LogPager', { edit: false, add: false, del: false, search: false, refresh: false })
        }
    });
    mygrid.navGrid('#LogPager', { edit: false, add: false, del: false, search: false, refresh: false })
    .closest(".ui-jqgrid").hide();

& Server data will be returned in following structure

 public struct JQGridResult
    {
        public int page;
        public int total;
        public int records;
        public List<ReportData> DataRows;
        public List<DocRelease> DocRows;
        public object userdata;
    }


 public struct DocRelease
    {
        public string Login { set; get; }
        public List<Readings> ReadingList { set; get; }
        public object userdata;
    }

public struct Readings
    {
        public int ReadingID { set; get; }
        public string Role { set; get; }
        public DateTime UpdateTime { set; get; }
        public string Comments { set; get; }
    }
Shaggy
  • 315
  • 2
  • 9
  • 23

1 Answers1

2

It seems me the mostly easy to use userdata together with userDataOnFooter: true option for the main grid and subgrids (see the old answer). In the case you can make all required calculation of summary row values on the server side and just include the results of the calculations in userdata part. The answer, this one, this one and many other provide examples of loading of whole grid together with subgrid at once. You can use loadonce: true to simplify the loading. I think that you will be able to implement your requirements based on the referenced answers. Don't forget to use idPrefix option in subgrids.

UPDATED: After you post the picture with your data I think that you should use grouping instead of subgrids. You can still use footerrow: true and userDataOnFooter: true options to set total footer row. I just get the demo created for the answer and added the options footerrow, userDataOnFooter and userData. As the result we get the demo which display the data which have the same structure like you need:

enter image description here

Community
  • 1
  • 1
Oleg
  • 220,925
  • 34
  • 403
  • 798
  • I got the idea about how should I define grid & subgrid to load it with single server call and what data to retrieve from server. But there is one confucsion regarding footerdata of main grid. Will the sum of all subgrids footer count [may it be distinct count of Reading ID or just count of UpdateTime] be displayed below counts displyed in last subgrid footer. I meant to say that the final count on main grid should be displayed in such a way that it should be along the Reading ID & UpdateTime columns of subgrid. – Shaggy Oct 24 '13 at 12:23
  • @Shaggy: You have full control over usage of footer in any subgrid or in the main grid. I hope you use [subgrid as grid](http://www.trirand.com/jqgridwiki/doku.php?id=wiki:subgrid_as_grid). In the case the subgrid is just a grid having all options like a standard grid. So you can implement *all what you want*. If you use `userDataOnFooter: true` in subgrids and use `userdata` in the input data then you will have summary row in the subgrid. You can use additionally the footer in the main grid. So I can repeat that you can implement all what you want. – Oleg Oct 24 '13 at 12:50
  • Thanks Oleg for your help. Instead of clearing my all doubt before implementing this requirements; I think I should start implementing it & then as it progress further I will keep in touch with you if I come across any problem. Thank You Very Much!! – Shaggy Oct 24 '13 at 13:06
  • I am defining my grid as per the first answer you suggested [here](http://stackoverflow.com/questions/14194754/jquery-grid-subgrid-for-parent-child-relation/14205263#14205263). But I m confuse in beforeProcessing event where you are setting userdata with subgrid ids. Now my question is I will be sending userdata from server side for totals to display in main grid footer as per my requirement. So I want to know how should I handle this situation. – Shaggy Oct 25 '13 at 10:29
  • @Shaggy: You should fill `userdata` on the server and you can remove the part from `beforeProcessing`. Instead of that you can set *additional* property of `userdata` on the server. jqGrid just save `userdata` object from the server response in the internal `userData` parameter of the grid. If you use `footerrow : true, userDataOnFooter: true` then the footer will be filled with the data from `userData`. In the case *only the properties of `userData` with the names like column names will be used. So if you would use other property names you can return any custom information like subgrids. – Oleg Oct 25 '13 at 11:06
  • I think, there could be another problem in my current grid w.r.t data overflow. Because The data I m going to return from server side is huge. So as in regular grid (withou subgrid) I m using server side paging & sorting; I dont want that in my current grid with subgrid. So what I can do for that as with our current implementation we are using loadonce true. – Shaggy Oct 25 '13 at 11:48
  • You start your question with "I need to have jqGrid with subgrid, in which I will be loading all jqGrid data (both parent & subgrids) from single stored procedure". Now you write about server side paging & sorting. One can implement both scenario, but the implementation is a little different. In general the approach with `userdata` and usage `footerrow : true, userDataOnFooter: true` will work in the case too. – Oleg Oct 25 '13 at 11:53
  • Oh Sorry for my previuos description on data overflow problem. I wanted ask that with our current implementation we are using loadonce set to true. so If my stored procedure returns bulky data whether the response can be catch at client side or it will cause error. – Shaggy Oct 25 '13 at 12:00
  • Hi, please look at the question update & suggest me any changes required in defining grid or data strucutre. – Shaggy Oct 25 '13 at 12:51
  • @Shaggy: It seems to me that you should use *grouping* of data instead of subgrids. Look at [the demo](http://www.ok-soft-gmbh.com/jqGrid/Quarath_.htm) from **UPDATED** part of my answer. You can modify the demo so that the data will be get from the server without usage `loadonce: true`. – Oleg Oct 25 '13 at 17:36
  • Ohh! I forgot this feature of jqGrid. Actually I had used this one. But again small question about displaying count of individual group; as I specified I want Distinct count in ReadingID column for each login(group), will this requirement can be satisfied with grouping. & One more thing Client has changed the way to display final summary on footerrow of main grid. Now they want two figures of totals with caption, can I implement this one? – Shaggy Oct 26 '13 at 06:39
  • @Shaggy: [The answer](http://stackoverflow.com/a/5402655/315935) shows how to move the footer row on top of grid. One can many custom changes in the footer row inside of `loadComplete`. [The answer](http://stackoverflow.com/a/13703037/315935) for example shows how to make two-rows footer. It's clear that more simple changes can be done too. [The answer](http://stackoverflow.com/a/7622718/315935) shows how to use `summaryType` of grouping defined as function and additionally how to display the summary in the custom format. I'm sure that you can implement all your requirements based on the exam. – Oleg Oct 26 '13 at 08:52
  • Thanks for your help up till now. Finally I stick to subgrid implementation for my requirement as I tried to go with grouping feature; but then I was stuck in getting Distinct count of Reading ID column within each group, with groupSummary. & again I was having trouble in getting count of date column **UpdateTime** It was showing some date & time value in summary footer row. I know that I could have done that working with some efforts of coding. I then I fill having implemented my requirements with subgrid is pretty much straight forward & easy for me. So I gone with subGrid. Thank You So Much – Shaggy Oct 26 '13 at 13:02
  • 1
    @Shaggy: You are welcome! You can ask new question which describe the problem in details which you have. Please try to formulate the new question so that it will be mostly clear and probably have examples (demos). The goal is that your question should have some value for other visitors. You should take in consideration that one other users don't find information from comments. – Oleg Oct 26 '13 at 13:15
  • Hi, please have look at [this](http://stackoverflow.com/questions/19628996/how-to-define-grid-as-subgrid-only-once-in-subgridrowexpanded-event-instead-defi) question w.r.t current requirement & your answers. – Shaggy Oct 28 '13 at 09:51