I'm developing a WPF app where I load a flat data source from an Excel spreadsheet file into an xamPivotGrid Control. I have been able to programmatically add filters, rows, columns and measures, but the default AggregateType for my measure is "sum" and I want "count". I have a button in my GUI and in the event handler for the button I have the following code snippet:IMeasureViewModel mvm = FindMeasureViewModel("Serial_Number");if (mvm != null){ mvm.Measure.AggregatorType = AggregatorType.Count;}
The FindMeasureViewModel method looks like this:// Searches the data source looking for the specified measureprivate IMeasureViewModel FindMeasureViewModel(string measureName){ if (fDataSource == null) return null; for (int i = 0; i < fDataSource.Measures.Count; i++) { if (((IMeasureViewModel)fDataSource.Measures[i]).Caption == measureName) { return fDataSource.Measures[i] as IMeasureViewModel; }
} return null;}
The problem I'm having is that the above code appears to have no effect on the measure once the cube is built and the data source is attached to the pivot grid control. In other words, the pivot grid is still displaying the measure as a "sum" instead of the desired "count". What am I doing wrong? Do I need to tell the grid to refresh somehow or would it normally do that on its own?
Hello ilevin,
I have been looking into your requirements and I can suggest you change the dimension settings of the cube at the moment you define the measures. In order to achieve this you can try the following snippet :
CubeMetadata cubeMetadata = new CubeMetadata();
cubeMetadata.DimensionSettings.Add(new DimensionMetadata()
{
SourcePropertyName = "Percentage1",
DisplayName = "Percentage1",
DimensionType=DimensionType.Measure,
AggregatorType=AggregatorType.Count
});
....
flatDataSource.CubesSettings.Add(cubeMetadata);
This way you will add a new dimension named Percentage1 and you will define it as measure with total’s aggregator count instead of sum.
If this doesn’t suit your requirements, please let me know.
I've incorporate the above snippet into my code (see below). I changed "Percentage1" to "Serial_Number" - the name of the dimension that I want to use as a measure. This fixes the problem in the sense that it now displays a "count" of the Serial_Number instead of a "sum". However; the fix also has the adverse side effect of removing Serial_Number from my columns list! In addition, it now appears that the Serial_Number field is no longer considered a valid selection in the xamPivotDataSelector control for dragging and dropping to the columns list interactively.
Here's the code in my app where I create the filters, rows, columns and measures:private FlatDataSource CreateDataSource(Stream fileStream){ FlatDataSource flatDataSource = new FlatDataSource(); try { ExcelDataConnectionSettings excelDataConnectionSettings = new ExcelDataConnectionSettings() { FileStream = fileStream, GeneratedTypeName = "ExcelData", WorksheetName = "RawData" }; flatDataSource.Cube = DataSourceBase.GenerateInitialCube("ExcelData"); // Select the filters, row and column hierarchies, and measures flatDataSource.Filters = DataSourceBase.GenerateInitialItems("[Site].[Site], [DriveInLibrary].[DriveInLibrary],[FirstTicket].[FirstTicket]"); flatDataSource.Rows = DataSourceBase.GenerateInitialItems("[FSC_2].[FSC_2]"); flatDataSource.Columns = DataSourceBase.GenerateInitialItems("[Firmware].[Firmware], [Serial_Number].[Serial_Number]"); flatDataSource.Measures = DataSourceBase.GenerateInitialItems("Serial_Number"); CubeMetadata cubeMetadata = new CubeMetadata { DataTypeFullName = "ExcelData", DisplayName = "RawData"}; cubeMetadata.DimensionSettings.Add(new DimensionMetadata() { SourcePropertyName = "Serial_Number", DisplayName = "Serial_Number", DimensionType = DimensionType.Measure, AggregatorType = AggregatorType.Count }); flatDataSource.ConnectionSettings = excelDataConnectionSettings; flatDataSource.CubesSettings.Add(cubeMetadata); } catch (Exception ex) { DispExceptionBox(ex); } return flatDataSource;}
Hello,
Probably you need to add the cube metadata before setting the connection settings.
flatDataSource.CubesSettings.Add(cubeMetadata);flatDataSource.ConnectionSettings = excelDataConnectionSettings;
Let me know if that helps.
Regards.Plamen.
Also you can try to remove the DimensionType=DimensionType.Measure, from the snippet that I sent to you, because this way you set the Serial_Number dimension to appears only in your Measure collection, and this way it won’t be available for your rows and columns.
Please let us know if after making this modification the issue still persist.
The above two suggestions (adding cube metadata before setting connection settings and commenting out the Dimension.Type = DimensionType.Measure) appear to have no effect.
I couldn’t reproduce your case. I have the same code and it works fine.
FlatDataSource flatDataSource = new FlatDataSource();
ExcelDataConnectionSettings excelDataSettings =
new ExcelDataConnectionSettings
FileStream = fileStream,
GeneratedTypeName = "ExcelData",
WorksheetIndex = 0
};
flatDataSource.Cube = DataSourceBase.GenerateInitialCube("ExcelData");
flatDataSource.Columns = DataSourceBase.GenerateInitialItems("[Date].[Date], [Units].[Units]");
flatDataSource.Measures = DataSourceBase.GenerateInitialItems("Units");
CubeMetadata cubeMetadata = new CubeMetadata
DataTypeFullName = "ExcelData",
DisplayName = "Sheet1 Data"
DimensionMetadata numberOfUnitsMetadata = new DimensionMetadata
SourcePropertyName = "Units",
AggregatorType = AggregatorType.Count
cubeMetadata.DimensionSettings.Add(numberOfUnitsMetadata);
flatDataSource.ConnectionSettings = excelDataSettings;
this.pivotGrid.DataSource = flatDataSource;
this.dataSelector.DataSource = flatDataSource;
That gives me this result:
Could you check how your data selector's metadata tree looks like?Plamen.
I was finally able to get it working. First I tried your code, and it does work fine as stated. The problem with my code was that I was not removing the DimensionType = DimensionType.Measure when adding to the cubeMetadata.DimensionSettings collection. I thought that I had removed that line, but it was still in there. As you said earlier in this thread, if you set the DimensionType to Measure it will remove the dimension from the list of available items in the metadata tree (i.e. it will show up in the metadata tree under Measures, but not under the root). If you remove it, it works fine.
In any case, I'm OK now. Thanks for all your help in resolving this!