Hi,
We are looking at the new PivotGrid in order to display hierarchical data that has values that need to be aggregated when collapsed and the PivotGrid seems to do exactly what we want, but I am trying to get this to work for local data rather then from an xmla service.

The problem I am having is in defining the hierarchy structure in my rows such that it will expand to multiple levels.

Do you have any examples of using local data and defining a row hierarchy? This is not adding multiple fields to the rows, just having a single field and the underlying object has child element.

Hello Paul,

Currently, local binding of the PivotGrid does not support custom hierarchies. That is why hierarchy configuration is not available.

In general, we believe that OLAP solutions are better approach when it comes to a complex categorized data (many levels of hierarchy).

Could you please elaborate more on your current requirements? What will be amount of data that you would like to show? Why OLAP will not be a solution for your case? How the hierarchy is defined, what is the logical connection between the category levels?

Regards,
Georgi Krustev
Telerik

Hi,
Thanks for the reply. We are building an accounting like package where we have a hierarchy of accounts and the leaf accounts have actual values associated with them. We are looking at total of about 100-150 account codes with a depth of 3 at this stage. We have a transnational database at the moment and we were looking for a solution that would allow the user to see the totals at the root, then drill down until they get to the actual account values. We looking at having 12 months worth of values shown at a time in the columns, so a max of 12 columns.

The pivot grid provided some nice functionality for this in the examples where the basic usage demo seemed to have fields that resulted in a hierarchy and when we tried with a flat structure as per the local data example the grid was providing some nice summing of the values when the rows where collapsed. 

I was hoping that we could combine the two and get this working without using OLAP which is probably overkill for what we are trying to do.

Cheers

HI,
I have been play with this control a bit more and have managed to get something sort of working. You can see an example here

http://dojo.telerik.com/akexI

I now have multiple fields in the rows with a flat datasource and it gives me the appropriate mappings and hierarchy. The only problem with this is that the control add is "extra" columns called "all" and these are what the control uses to expand and collapse.

If I do pretty much the same thing in excel (see image) there is no entry for "all" the the user expands and collapses on the items in the data itself (eg "Expenses" or "Income"). Is there any way to make the Piviot grid display the same way that Excel does and remove the need for the "All" entries. 

Cheers
Paul.

Hello Paul,
 
You are absolutely right, OLAP solution for 150ish records will be overkill and the best solution will be a local binding. Unfortunately, as I mentioned custom hierarchy declaration is not supported. We will consider it (further investigate the technical possibilities and road-blocks) for some of our future releases.

The your proof-of-concept demo (thanks for that as it shows one possible way to accomplish this task) uses multiple dimensions per axis. This is the reason why you got three row fields and of course the three "All" section. This is expected for our rendering as every field represents a slice of the cube and its "All" row shows the grand total value. The best way to achieve the  look and feel of Excel Pivot Table is using custom hierarchy declaration, which as I mentioned currently is not supported.

Let me know if other questions arise.

Best regards,
Georgi Krustev
Telerik