Hello PPilev, thanks for the quick answer.

I initially had dismissed the possibility of using a FlatDataSource, because i would have to load the entire database to memory, ie,  in a IEnumerable. 

I'd like to get the data from the database on demand, ie, from the user actions in the pivotGrid;

This is possible with FlatDataSource? Or there's another way?

Best regards.

--

Ricardo Cruz

Hi,

Has any further progress been made in the area of binding a XamPivotGrid to a custom data source?

I have my own aggregation server and I would like to bind the resultant data provided by this service to the pivot control. My server does not formally publish itself as an XMLA data source as this is somewhat too generic for my needs, but what I do have does provide resultant data in a form similar to the following:

public class FlatPivotTable
{
 public FlatPivotAxis XAxis { get; set; }
 public FlatPivotAxis YAxis { get; set; }
 public AggregatedValue[][] Cells { get; set; }
}

where my axis is somewhat crudely defined in a fashion similar to this (note the hierarchy):

public class FlatPivotAxis
{
 public string HeaderValue { get; set; }
 public int Position { get; set; }
 public int Level { get; set; }
 public int Width { get; set; }
 public IDictionary<string, FlatPivotAxis> Children { get; set; }
}

The point to stress is that my cell values are all pre-aggregated according to the definition of my two axes. eg: the root position in my x-axis would typically have a position of zero, implying that the first column is the grand total, etc.

I'm having some difficulties understanding how I can map data in the above form into something that is displayed correctly on the pivot grid. I've considered writing my own class that derives from DataSourceBase (in a fashion similar to FlatDataSource), however this seemed a little excessive given that my FlatPivotTable can be converted (with some effort) into the existing IResult interface used by the existing data sources. My other worry going down this route (for the moment) is that its not completely clear to me whether this approach would work.

I've had a look at the CustomDataProviderSample.zip, which overrides XmlaDataSource to seemingly achieve a similar effect (using RIA services behind the scenes, but it seems that's neither here nor there). I've had a few issues getting this to work:

1. It seems that the visible result is implicitly bound to the cube schema itself, and its not immediately clear to me how I can coerce a schema into place that will map to particular result-sets in the form given above. My attempts so far have only been partially successful. eg: In some cases I've got data displayed, but column expansion/hiding isn't, etc.

2. Its also not clear to me how I programmatically instruct my custom view model to refresh data from the data-provider (using the command). It seemed to work, partially, when I made use of the data selector, but I don't really want to use this control as I have my own user-interface around the control that I want to retain.

Basically, and this might sound ridiculous, what I would really prefer is to be able to simply tell the grid to display the data I want it to display (in a similar fashion to a regular data-grid), except that the data is obviously structured according to two hierarchical axes. I was hoping that I could construct an IResult in some form and simply bind that, but this doesn't seem to be working as I expect.

Would it be possible for you to shed some light on how I might achieve this using your control? If implementing my own DataSource by deriving from DataSourceBase directly is the only option then it would be helpful to have some hints as to how one goes about doing this (particularly as my source data is just about in the right format - no real conversion required).

Many thanks, Kevin

Hello Kevin,

The best approach for you I think would be to have your own implementation of IOlapViewModel but unfortunately there are few places where XamPivotGrid is targeting its data source as DataSourceBase which restricts you to use DataSourceBase. XamPivotGrid basically is aware about DataSourceBase.ResultChanged event and DataSourceBase.Result property. However you'll need to override some of the base functionalities in order to get your data source to have all features working properly.

Would it be possible for you in first place to provide us with a sample where you generate a sample instance of your FlatPivotTable so we can look at it and we can try to convert it to instance of Result class which XamPivotGrid will recognize and can operate with.

Regards.
Plamen.

Hi Plamen,

Thank you for your prompt response. I have attached, hopefully, a fairly crude example which I believe will demonstrate what I'm trying to do. When run you'll see the pivot grid partially populates, however the IResultAxis objects I'm creating are obviously incorrect. Also - the cell values aren't being displayed...I'm wondering whether this is because of a lack of some cube schema that I may have to manually specify.

If you could have a look and make any changes you see fit. I guess first priority is to understand how I create the IResult correctly for hierarchical data...and then the next step is to understand how I create the DataSource in a way that allows me to control when results are updated (according to my own criteria and rules).

I look forward to your response.

Regards,
Kevin

Hi Kevin,

Please look at the attached sample. I know it has a lot of new code added. I'll try to answer to all of the new questions I guess you'll come with.

About the modifications:
1. Connection settings class is added in order to support the configuration model which DataSourceBase is working with.
2. Data provider class is added to feed your flat data source with all metadata it needs. This data provider is able to work with your raw data and then can provide result built over this raw data.
3. I have added data selector to the sample just to get more visual about the metadata.
4. Your flat data source has its rows/columns/measures areas disabled for editing because you need all these three items (1 row, 1 column, 1 measure) added at the places you'll see them. Also the filtering from the drop down is not supported. It will need extra effort.
5. Expand/collapse as far as I was able to test it works fine. Both the value sorting and the labels sorting are like to work as expected.

Best regards.
Plamen.

Hi Plamen,

Thank-you very much indeed for providing your enlightening sample! I had previously attempted to override DataSource by making use of an XmlaCommand and then building up the levels, etc within that (slightly more than I was doing in my original submission to you) - however I think where I was going wrong was related to the fact that my actual data-source schema was not being changed at all.

I'm going to spend a bit of time over the next day or two looking at your approach in further detail, and I'll try hook the concepts in to my real application to see what pops out the other end. Either way, your example has been extremely helpful.

One small question I do have relates to your implementation of LoadDimensionsAsync - specifically your comment. What do you mean by "just one row and one column". Are you referring to one row hierarchy and one column hierarchy specifically? In my case, it looks like I change the schema whenever a new request is made to my back-end (with whatever row/columns groupings are requested) - but that in all cases, I will only ever have the one resultant hierarchy for each of the two axes. Does this sound right to you given my data structure?

Once I've had a more detailed look - I'll confirm that its working as expected - and obviously if I have any further questions I'll let you know.

Kind regards,
Kevin