.NET PivotGrid Server Mode & ASP.NET PivotGrid Virtual Scrolling (Coming soon in v14.2)

DevExpress Data Blog
18 November 2014

One of the powerful tools in our analysis toolbox is our PivotGrid. In our effort to continue innovating in this area we are adding two new features in our upcoming 14.2 release.

ASP.NET Virtual Scrolling

Currently the ASPxPivotGrid control has horizontal scrolling. In 14.2 we are introducing the vertical scrolling feature as well. This would be fairly lackluster if I proceeded to explain how to bind to the entire dataset on the server. Au contraire, in the upcoming virtual paging mode, the Pivot Grid control loads portions of data asynchronously when your customers are scrolling through the data in the grid.

PivotVirtualScrolling

Indeed, paging is now for amateurs. Also, you can theoretically scroll through an infinite amount of data, FOREVER! How is this possible you ask? That brings us to the next feature!

Server Mode

The most critical aspect of a control which processes data is performance. Traditionally one would have to set up an OLAP cube if they wanted to get the best performance out of the PivotGrid. This is often fraught with dragons and falling off the edge of the earth type issues. In this release we are introducing an alternative option: Server Mode. The core idea is that we are creating Linq expressions on the fly when doing grouping and aggregating and then sending the requests to the server. In other words, the database is doing all of the heavy number crunching when grouping and aggregating. Trust me, database systems have near 40 years worth of research into how to make these types of thing work fast and well – USE THEM!!

When should I do this you ask? Let’s divide your data into three categories:

  1. Small-ish – use in-memory techniques
  2. Large but not huge – this is the sweet spot in my opinion (the category where the majority of users fall) – use the new Server Mode
  3. Huge – in this case it would definitely be better to use OLAP (remember the 40 years of research?). OLAP was designed primarily for this case.

My suggestion is to go in that order (1 through 3) and only move down the list if you are noticing performance issues. To use this new Server Mode you need to use the following Data Sources when binding to data using the Pivot Grid:

  • ASP.Net - EntityServerModeDataSource and LinqServerModeDataSource;
  • Windows Forms - EntityServerModeSource and LinqServerModeSource;
  • WPF - EntityServerModeDataSource and LinqServerModeDataSource.

Questions?

As always, if there are any comments and/or questions, feel free to get a hold of me!

Seth Juarez
Email: sethj@devexpress.com
Twitter: @SethJuarez

19 comment(s)
Martin Praxmarer - DevExpress MVP
Martin Praxmarer - DevExpress MVP

Wow - great Feature! Are unbound columns supported in this mode?

18 November, 2014
Gustavo Marzioni
Gustavo Marzioni

Great, Will XAF support server mode too?

18 November, 2014
Jonatas Hudler
Jonatas Hudler

Awesome news!

About server mode, it is good to remember that, despite 40 years of research, we all are getting new technologies as alternatives for analytics, such as In-Memory and/or non-SQL databases.

This feature defintely comes in a very good time.

18 November, 2014
Dennis (DevExpress Support)
Dennis (DevExpress Support)

@Gustavo: Not in this release, although it is possible to implement a custom ViewItem based on the PivotGrid control using these server mode data sources. Learn more on integrating custom controls into an XAF app at eXpressApp Framework > Concepts > UI Construction > Using a Custom Control that is not Integrated by Default

18 November, 2014
Alexander Krakhotko (Xafari team)
Alexander Krakhotko (Xafari team)

Now there is a performance problem with the filtering control for huge dimension OLAP cube (300k elements). "pivot server mode"  will support pagination additional loading elements?

18 November, 2014
Paul P (DevExpress)
Paul P (DevExpress)

@Martin: yes, unbound fields are supported

@Alexander: Pivot filter pagination is a different feature and does not directly depend on the data processing mode.

19 November, 2014
Chris Royle (LOB)
Chris Royle (LOB)

@Dennis - will there be a concrete example for XAF usage ?

19 November, 2014
Dennis (DevExpress Support)
Dennis (DevExpress Support)

@Chris: We have not planned this yet. In general, you can set up the pivot grid control exactly as described in its documentation and tutorials. Then, follow one of the integration paths from the XAF help article above. As always, if you experience any difficulties with any part, do not hesitate to contact the corresponding product team via the Support Center. Thanks.

19 November, 2014
Neven Zovko
Neven Zovko

@Dennis - Will the WPF Pivot Grid get the conditional formatting feature?

19 November, 2014
Paul P (DevExpress)
Paul P (DevExpress)

@Neven: Now this feature is not implemented. You can see a similar question that was already discussed in our Support Center: www.devexpress.com/.../S37393

20 November, 2014
Alexander Krakhotko (Xafari team)
Alexander Krakhotko (Xafari team)

will be useful to add support for key <ESC> - to cancel the long-running query, after the changes in the structure of the table. it works in the Excel.PivotTable.

26 November, 2014
Paul P (DevExpress)
Paul P (DevExpress)

@Alexander: I completely agree. Now this feature is not implemented because it is required in some changes of the internal infrastructure. A similar question was already discussed in our Support Center: www.devexpress.com/.../S135833.

26 November, 2014
Jascha
Jascha

@Dennis: "@Gustavo: Not in this release"

Hi Dennis, I find that quite disappointing since XAF supports the concept of server mode and list editors that support it so surely coding the glue that connects the server mode setting from the model to the underlying pivot control is not that big a task and would avoid yet another sense of XAF being a second class citizen?

2 December, 2014
Bassam Abdelaal
Bassam Abdelaal

Great addition , for server mode however , I noticed EF , LINQ sources mentioned , where is XPO ?

4 December, 2014
Sebastien Desmarais
Sebastien Desmarais

Are EntityServerModeDataSource and LinqServerModeDataSource available to use in ASP.NET Web form?

I tried to test them and the ContextTypeName properties are always empty...

10 December, 2014
JulianG
JulianG

Does anyone have a sample on how to use ASP.Net - EntityServerModeDataSource and LinqServerModeDataSource on VS 2010?

11 December, 2014
Paul P (DevExpress)
Paul P (DevExpress)

@Bassam:

Now the XPO server mode is not supported. We will consider implementing it in the scope of the following ticket:

www.devexpress.com/.../T187127

Feel free to leave comments and describe your scenario in detail.

@Sebastien:

@julian:

You can try the following code from our demo:

demos.devexpress.com/.../ServerMode.aspx

This demo is available in the corresponding installation folder ("c:\Users\Public\Documents\DevExpress Demos 14.2\Components\ASP.NET\CS\ASPxPivotGridDemos\Features\ServerMode.aspx.cs").

I've tried it in VS2010, and all works fine on my side.

15 December, 2014
Jiri
Jiri

Hi, what do you mean by "large but not huge?" It is dimension with 200k rows large or huge? :-)

19 December, 2014
jialiang cui
jialiang cui

I have a question.

How to control the FilterValue of the PivotGridField?

Just like Jurisdiction.Thinks

11 January, 2015

Please login or register to post comments.