ParamQuery grid support forum

General Category => Help for ParamQuery Pro => Topic started by: pquser on September 25, 2017, 07:28:09 pm

Title: Show sum of columns resp. selected cells at extra row
Post by: pquser on September 25, 2017, 07:28:09 pm

How can I display a row at the bottom of the table where the sum of float and integer values of the columns is always visible?
And how do I replace this sum with the sum of selected cells when I do so?

Title: Re: Show sum of columns resp. selected cells at extra row
Post by: paramvir on September 25, 2017, 10:21:19 pm

This is an example as per your description.

https://paramquery.com/pro/demos/summary_json

it can't provide sum of selected cells but it can provide sum/ aggregate of filtered rows.

Title: Re: Show sum of columns resp. selected cells at extra row
Post by: pquser on September 26, 2017, 03:19:04 pm

Thanks.

Now I wonder how to do this with dynamically generated report where I cannot make any assumption about the data and number of cells.

Like creating my colModel:

Code: [Select]

createColModel = function () {
                    var colModelArray = [];
                    var alignReportData;

                    alignReportData = function () {
                        alignLeft = function (index) {
                            colModelArray.push(
                                {
                                    filter: {
                                        type: 'textbox',
                                        condition: 'begin',
                                        listeners: ['keyup']
                                    },
                                    title: reportHeadings[index],
                                    align: "left"
                                }
                            )
                        };

                        alignRight = function (index) {
                            colModelArray.push(
                                {
                                    filter: {
                                        type: 'textbox',
                                        condition: 'begin',
                                        listeners: ['keyup']
                                    },
                                    title: reportHeadings[index],
                                    align: "right",
                                    format: '#.###,00',
                                    dataType: "float"
                                }
                            )
                        };


                        $.each(dataArray[0], function (index, value) {

                            if (value === null || value.match(/^\D/)) {
                                alignLeft(index);
                            } else {
                                alignRight(index);
                            }

                        });
                    };

                    alignReportData();

                    return colModelArray;
                };

                reportTable.colModel = createColModel();

Thanks.

Title: Re: Show sum of columns resp. selected cells at extra row
Post by: paramvir on September 26, 2017, 04:58:44 pm

What assumptions do you think you need to make about data and number of cells to include summary feature.

may be I didn't understand your question, can you please elaborate your question.

Title: Re: Show sum of columns resp. selected cells at extra row
Post by: pquser on September 27, 2017, 02:21:41 am

In your example there are cells referenced as "C:C", "D:D", etc.

Code: [Select]

summaryData: [
            { rank:'Total', summaryRow: true, pq_fn:{revenues:'sum(C:C)', profits:'sum(profits)', diff:'sum(E:E)' }},
            { rank:'Average', summaryRow: true, pq_fn:{revenues:'average(C:C)', profits:'average(D:D)', diff:'average(E:E)' }},           
            { rank:'Std deviation', summaryRow: true, pq_fn:{revenues:'stdev(C:C)', profits:'stdev(D:D)', diff:'stdev(E:E)' }}               
        ]

Is there any way to reference cells from an array with cell heading names?

To make it clear: I may have the column VAT but I don't know how much columns are displayed before and after this column. So I need a way to reference all cells with a number value dynamically. The only reference I have is the column heading name.

Title: Re: Show sum of columns resp. selected cells at extra row
Post by: paramvir on September 27, 2017, 11:44:22 am

In that case this example would be easier for you. https://paramquery.com/pro/demos/group_summary

Just set header: false and dataIndx: [] in groupModel, that would remove grouping but still show grand summary at the bottom.

Title: Re: Show sum of columns resp. selected cells at extra row
Post by: pquser on September 27, 2017, 04:12:24 pm

Thanks. This is at least a step further ;-)

But: I have some problems getting it to work.

Neither

Code: [Select]

reportTable = {
                    title: report.description,
                    editable: false,
                    maxWidth: '100%',
                    height: '100%',
                    flex: {
                        on: true,
                        one: true,
                        all: true
                    },
                    groupModel: {
                        on: true,
                        dataIndx: [],
                        merge: true,
                        grandSummary: true
                    },
                    showBottom: false,
                    resizable: true,
                    filterModel: {
                        on: true,
                        header: true,
                    },
                    toolbar: {
                        items: [
                            {
                                type: 'select',
                                label: '',
                                attr: 'id="export_format"',
                                options: [{xlsx: 'Excel', csv: 'Csv', htm: 'Html', json: 'Json'}]
                            },
                            {
                                type: 'button',
                                label: "Export",
                                icon: 'ui-icon-arrowthickstop-1-s',
                                listener: function () {
                                    this.exportData({
                                        url: "

<?php echo $this->url( 'export' ) ?>

",
                                        format: $("#export_format").val(),
                                        render: true
                                    });
                                }
                            }]
                    }
                };

                createColModel = function () {
                    var colModelArray = [];
                    var alignReportData;

                    alignReportData = function () {
                        alignLeft = function (index) {
                            colModelArray.push(
                                {
                                    filter: {
                                        type: 'textbox',
                                        condition: 'begin',
                                        listeners: ['keyup']
                                    },
                                    title: reportHeadings[index],
                                    align: "left"
                                }
                            )
                        };

                        alignRight = function (index) {
                            colModelArray.push(
                                {
                                    filter: {
                                        type: 'textbox',
                                        condition: 'begin',
                                        listeners: ['keyup']
                                    },
                                    title: reportHeadings[index],
                                    align: "right",
                                    format: '#.###,00',
                                    dataType: "float",
                                    summary: {
                                        type: "sum"
                                    }
                                }
                            )
                        };


                        $.each(dataArray[0], function (index, value) {

                            if (value === null || value.match(/^\D/)) {
                                alignLeft(index);
                            } else {
                                alignRight(index);
                            }

                        });
                    };

                    alignReportData();

                    return colModelArray;
                };

                reportTable.colModel = createColModel();
                reportTable.dataModel = {
                    data: dataArray
                };

            };
        }

and

Code: [Select]

reportTable = {
                    title: report.description,
                    editable: false,
                    maxWidth: '100%',
                    height: '100%',
                    flex: {
                        on: true,
                        one: true,
                        all: true
                    },
                    showBottom: false,
                    resizable: true,
                    filterModel: {
                        on: true,
                        header: true,
                    },
                    toolbar: {
                        items: [
                            {
                                type: 'select',
                                label: '',
                                attr: 'id="export_format"',
                                options: [{xlsx: 'Excel', csv: 'Csv', htm: 'Html', json: 'Json'}]
                            },
                            {
                                type: 'button',
                                label: "Export",
                                icon: 'ui-icon-arrowthickstop-1-s',
                                listener: function () {
                                    this.exportData({
                                        url: "

<?php echo $this->url( 'export' ) ?>

",
                                        format: $("#export_format").val(),
                                        render: true
                                    });
                                }
                            }]
                    }
                };

                createColModel = function () {
                    var colModelArray = [];
                    var alignReportData;

                    alignReportData = function () {
                        alignLeft = function (index) {
                            colModelArray.push(
                                {
                                    filter: {
                                        type: 'textbox',
                                        condition: 'begin',
                                        listeners: ['keyup']
                                    },
                                    title: reportHeadings[index],
                                    align: "left"
                                }
                            )
                        };

                        alignRight = function (index) {
                            colModelArray.push(
                                {
                                    filter: {
                                        type: 'textbox',
                                        condition: 'begin',
                                        listeners: ['keyup']
                                    },
                                    title: reportHeadings[index],
                                    align: "right",
                                    format: '#.###,00',
                                    dataType: "float",
                                    summary: {
                                        type: "sum"
                                    }
                                }
                            )
                        };


                        $.each(dataArray[0], function (index, value) {

                            if (value === null || value.match(/^\D/)) {
                                alignLeft(index);
                            } else {
                                alignRight(index);
                            }

                        });
                    };

                    alignReportData();

                    return colModelArray;
                };

                createGroupModel = function () {
                    var colModelArray = [];

                    $.each(dataArray[0], function (index, value) {

                        if (value === null || value.match(/^\d/)) {
                            colModelArray.push(
                                {
                                    on: true,
                                    dataIndx: [],
                                    merge: false,
                                    grandSummary: true,
                                }
                            )
                        }
                    });

                    return colModelArray;
                };
               
                reportTable.colModel = createColModel();
                reportTable.groupModel = createGroupModel();
                reportTable.dataModel = {
                    data: dataArray
                };

            };
        }

work.

There is no additional line displayed at the bottom of the table.

I'm sure it (I) only need(s) a little tweaking/(hint).

Title: Re: Show sum of columns resp. selected cells at extra row
Post by: paramvir on September 28, 2017, 11:29:40 am

groupModel options look fine in the first one. Which version of grid are you using and can you please share a jsfiddle?

http://jsfiddle.net/0jgmu7vq/

Title: Re: Show sum of columns resp. selected cells at extra row
Post by: pquser on September 29, 2017, 12:10:06 pm

I use 4.0.1

Tried JsFiddle but it doesnt really work as my code is highly dynamic with embedded PHP statements and AJAX requests.

Sorry.

Should I try the latest version 3 branch as it is still installed on my dev server?

Title: Re: Show sum of columns resp. selected cells at extra row
Post by: pquser on September 29, 2017, 12:56:36 pm

Latest 3 branch isn't working too.  :(

Title: Re: Show sum of columns resp. selected cells at extra row
Post by: paramvir on September 29, 2017, 05:32:33 pm

this feature works fine in 4.x and latest 3.x

Just try to make a small and simple test case or POC ( proof of concept ), not necessarily the same as your main source code and share it on jsfiddle if that doesn't work.

That would help locate the issue.

Title: Re: Show sum of columns resp. selected cells at extra row
Post by: pquser on September 29, 2017, 06:25:21 pm

Made a simple test case out of you JsFiddle.

Also no summaries visible there. Please have a look if you can elaborate what's wrong.

Thanks.

Title: Re: Show sum of columns resp. selected cells at extra row
Post by: paramvir on September 29, 2017, 07:52:43 pm

Please share url of your jsfiddle.

Title: Re: Show sum of columns resp. selected cells at extra row
Post by: pquser on September 29, 2017, 09:15:21 pm

Ah. I just modified the fiddle you posted in post 8.

Title: Re: Show sum of columns resp. selected cells at extra row
Post by: paramvir on October 02, 2017, 09:26:10 am

Every jsfiddle has a different url. I guess you mean http://jsfiddle.net/0jgmu7vq/1/

colModel and column summary.type is missing from your jsfiddle.

Please add them.

Title: Re: Show sum of columns resp. selected cells at extra row
Post by: pquser on October 02, 2017, 03:39:22 pm

At first: I want to thank you very much for your kindful help and patience. I'm very grateful for that.

To my code:
As you see in post #7 I create an array of objects dynamically and give that to paramquery to create the colModel.
You wrote the first example looks correct to you.

I isolated one of this objects out of the array and added a screenshot of the object to this post.

For me it looks correct. But please have a look as it's still not working.

Title: Re: Show sum of columns resp. selected cells at extra row
Post by: paramvir on October 02, 2017, 11:26:41 pm

It doesn't matter whether colModel is assigned statically or dynamically, I guess I was able to find out the issue in your jsfiddle.

showBottom: true is required for fixed rows at the bottom.

Please check this: http://jsfiddle.net/0jgmu7vq/2/

I see showBottom: false is mentioned in your earlier post with inline code but I was unable to notice it. :(

it's important to share jsfiddle for non-trivial issues.

Title: Re: Show sum of columns resp. selected cells at extra row
Post by: pquser on October 03, 2017, 02:59:43 pm

Thanks. ShowBottom was the culprit. Now it works as expected ;-)