10 Answers, 1 is accepted
I believe that the best way to hide the ALL column header and content column is indeed the dataBound event. One way that works well is to address the <col> elements and set their width to zero with the jQuery css() method.
dataBound: function(e) { this.columnsHeader.find("table colgroup col:last-child").css({width:0}); this.content.find("table colgroup col:last-child").css({width:0}); this.element.find(".k-grid-footer").hide();}Here is a Dojo that I tested with - have a look and let me know if you need anything else:
https://dojo.telerik.com/@bubblemaster/AVoyuqOg
Kind Regards,
Alex Hajigeorgieva
Progress Telerik
Rank 1
Hi Alex,
Definitely on the right track. On the initial page load of the pivotgrid, it does look exactly as desired (see initialLoad.PNG).
However, I'm getting some odd behavior in my KendoUICore implementation (I can't replicate the first error in dojo example)
1. Not sure if this is a kendoUI bug or a Me-bug, but when expanding a column the subtotal does not calculate (see afterExpandColumn.PNG) until I either collapse/expand the parent or expand the other columns (see afterCollapseExpand.PNG).
2. I have a configurator. If the user adds or changes one of the columns, things kind of go wacky and what get's displayed is highly dependent on the order in which I expand/collapse. Using the configurator functionality is the whole point (for me) to be using the pivotgrid. NOTE: If I refresh the page after making a configuration change, it shows correctly for an initial load.
Here is my cshtml file. If I need to make this a ticket and send entire (stripped down) project let me know.
@{ ViewData["Title"] = "CustomReporting";}<h1>Pivot View Custom Reporting</h1>@(Html.Kendo().PivotConfigurator() .Name("configurator") .Filterable(true) .Sortable())@(Html.Kendo().PivotGrid<ICPCore.Models.ViewModels.ReportsVM>() .ColumnHeaderTemplateId("headerTemplate") .DataCellTemplateId("dataCellTemplate") .Name("pivotgrid") .Configurator("#configurator") .ColumnWidth(120) .Filterable(true) .Reorderable(true) .Sortable() .DataSource(dataSource => dataSource .Ajax() .Transport(transport => transport .Read("Test_Read", "Reports")) .Schema(schema => schema .Cube(cube => cube .Dimensions(dimensions => { dimensions.Add(Model => Model.AssessmentYear).Caption("All Years"); dimensions.Add(model => model.AssessmentDate.ToShortDateString()).Caption("All Assessments"); dimensions.Add(model => model.OutcomeName).Caption("All Outcomes"); dimensions.Add(model => model.AttributeName).Caption("All Attributes"); dimensions.Add(model => model.SumAssessmentValue).Caption("Values"); dimensions.Add(model => model.RegionName).Caption("All Regions"); dimensions.Add(model => model.CountryName).Caption("All Countries"); dimensions.Add(model => model.SiteName).Caption("All Sites"); }) .Measures(measures => measures.Add("Average").Field(model => model.SumAssessmentValue).AggregateName("average")) )) .Columns(columns => { columns.Add("RegionName").Expand(true); columns.Add("OutcomeName"); }) .Rows(rows => { rows.Add("AssessmentYear").Expand(true); }) .Measures(measures => measures.Values("Average")) .Events(e => e.Error("onError")) ) .Events(events => events.DataBound("onDatabound")))<style> #pivotgrid { display: inline-block; vertical-align: top; width: 100%; } #configurator { display: inline-block; vertical-align: top; }</style><script> function onError(e) { alert("error: " + kendo.stringify(e.errors[0])); }</script><script id="rowTemplate" type="text/x-kendo-template"> # if (member.name.indexOf("AssessmentDate") === 0 && member.name !== "AssessmentDate") { # #: kendo.toString(kendo.parseDate(member.caption), "d") # # } else { # #: member.caption # # } #</script><script id="dataCellTemplate" type="text/x-kendo-template"> # var value = kendo.toString(kendo.parseFloat(dataItem.value) || "N/A", "n"); # #: value #</script><script> function onDatabound(e) { this.columnsHeader.find("table colgroup col:last-child").css({ width: 0 }); this.content.find("table colgroup col:last-child").css({ width: 0 }); this.element.find(".k-grid-footer").hide(); }</script>
We have a feature request for this functionality to be available via the configuration options and I have already cast a vote for it on your behalf here:
https://feedback.telerik.com/kendo-jquery-ui/1359577-kendo-pivotgrid-make-all-row-column-hideable
As for the task at hand, previously we have stated that if we hide the grand totals, then we defeat the widgets main purpose:
https://www.telerik.com/forums/how-to-remove-all-elements-in-a-pivotgrid#-b_uGMiK802u9R2IyBChtg
Whilst I am not certain on the outcome due to the presence of the configurator and the numerous possibility it provides for drill down variations, I would gladly inspect a stripped down version of the project, perhaps with some accompanying screenshot what we should hide for clarity. If the column, row and footer is always in the same position, we can definitely hide them and resize the widget as it seems to me that the columns are starting to misalign.
Finally, you mentioned that the current results are incorrect, if you have a moment to give an example of the current behaviour vs the expected behaviour, this is definitely something that we can look into if it is a bug in the source.
Kind Regards,
Alex Hajigeorgieva
Progress Telerik
Rank 1
Hi Alex,
Sorry for delayed response, I've been traveling.... I'll send a stripped down version soon.
In response to "the widget's main purpose": Thanks for adding my upvote. But I would add that IMO the main value/purpose of the widget is 1) the matrix grid (the data cells) produced with its aggregate value (the summaries are just bonus to me) and 2) the flexibility to quickly interpret data by changing rows and columns. I hate to tip the hat to MS on this one, but Excel is a very good target for the Telerik Team. Good luck and I look forward to future improvements.
As for the "incorrect" results- what I am getting at is not so much that the widget is wrong, but user beware! If you are doing an aggregate of "average", then the sub-total column in the widget will give the average of the averages- NOT the average of all the values that made up the sub averages.
For example if Steve owns two ice cream shops run by Mike and Bob, respectively. If Mike and Bob were to report the ice cream cones they sold on the days they were open. the pivot data would be
Mike
Day1: 1
Day2: 3
Mike's average sale is (3+1)/2 = 2
Bob
Day1: 4
Day2: 6
Day3: 8
Bob's average = (4+6+8)/3 = 6
The average of the averages is (2+6)/2 = 4 (I believe this is what the widget reports)
But, the ACTUAL Average Cones per day is (1+3+4+6+8)/5 = 4.5 (which is what Steve wants to know)
The only time an average of averages is correct is when the number of elements in each data set are equal (or when they are zero).
So... the widget works in that it does calculate the average of the averages, but that is not the number Steve cares about.
Anyway... I can figure out how to work around that. I'll try to send you a small project soon.
Thanks Alex!
ps It's always possible I got that wrong or am misusing the widget. Let me know if you understand it differently or can straighten me out!
Rank 1
Oops. Make that an average of 4.4 :-)
bd
Thank you for the example.
Indeed the data is very important to produce a quality aggregation, there should not be missing gaps.
In the example below, Mike should have a Day 3 as well with total sold 0. That way the calculation will be correct.
http://runner.telerik.io/fullscreen/oKEsetIF
Kind Regards,
Alex Hajigeorgieva
Progress Telerik
Rank 1
Hi Alex,
We don't really need to focus on the "average" issue any more but setting a value to zero when it is actually "null" (maybe Mike was only open 2 days) is not correct. The fact is, the average of a data set is the sum of the values divided by the number of values. You do not get to create zero values. Anyway, I'm going to move on from that issue. I'll get a simple project file to you soon. Just got back from mission work in Africa and trying to get life back together :-) Stay tuned!
I hope you are feeling better after the mission in Africa.
I tested the same data with Excel Pivot and I got the same average result.
I believe because Mike was only open for 2 days and the rest is missing or null, it does not get included in the average, unless you fill in the missing values. I checked this tutorial on Excel pivoting and they mentioned the importance of not having missing data in point 2:
https://exceljet.net/excel-pivot-tables
Kind Regards,
Alex Hajigeorgieva
Progress Telerik
Rank 1
Hi Alex,
Yep, all better after a bit of catching up following my Africa travels.
YIKES! I was wrong :-). You are right. The value reported (both by excel and the widget) is the correct average. But your example is a little too simplistic to see the nuance. FWIW, I changed "unitPrice" to "UnitsSold" to better represent a value that would be averaged (ie, the unit price would probably be the same for every unit sold.... not that the name really matters to the math for our made up example). I also put months as the row value instead of SoldBy (just to illustrate the point more clearly). I simply added two more rows (for John) to your data so that when looking at a grouped month, it will take the average of unitsSold for productname by SoldBy. I don't see how to paste a picture so I've attached a jpeg.
In your example, John has sales of 5 and 3, which correctly average to 4 (as you show).
If you look at the attached, you will see that in January, John averaged 3 units ((5+1)/2) and Tim averaged 7. A person MIGHT think that the average sales for January would then be 5 = (3+7)/2. But no, the average (which again I emphasize is correct in the widget) is 4.33 because the average is actually calculated as (5+1+7)/3.
All that to say, the widget presents the data correctly. It does NOT take the average of the averages (which as you point out ONLY works if the number of data points is the same for all groupings).
Lastly... on my post back on Jun 20, I stated that I was getting wonky column behavior. Must've been a chrome (PEBKAC) issue cuz I can't replicate it now. Thank you for your patience and help!!
BD
Thank you for the very concise and excellently clear explanations of the tests that you ran. I am very pleased to hear that there is no issue with the way the aggregating works.
Can you think of a way we can improve the clarity for those who might get confused, people who may not have a complete grasp on how data aggregation works in Excel either. Perhaps a tooltip?
https://runner.telerik.io/fullscreen/@bubblemaster/EQaxIsUq
$("#pivotgrid").kendoTooltip({ filter: "td.k-first, th.k-header", //this filter selects where the tooltip will be shown position: "right", content: function(e){ var text = e.target.text(); var content = kendo.format("Only items which have data are calculated for {0}", text); return content; }}).data("kendoTooltip");Kind Regards,
Alex Hajigeorgieva
Progress Telerik