Is it possible to add a measure dynamically bypassing th data source altogether.
For example, If an Excel file is loaded in the cube and displayed in the grid. Is it possilble to have the user create a new measure and added into the collection of measure. This measure will be essentialy a sum and a product of two other measure. Also, we want to display it right next to other measure in the grid.
Thanks.
Hello,
Yes you can do that using your own aggregator. Here is the sample code:
1. Create and add a cube metadata to the flat data source
CubeMetadata cubeMetadata = new CubeMetadata { DataTypeFullName = "ExcelData", DisplayName = "My excel cube" };
flatDataSource.CubesSettings.Add(cubeMetadata);
2. Create and add a dimension metadata to the cube metadata
DimensionMetadata calcMeasureMetadata = new DimensionMetadata
{
SourcePropertyName = "Cost",
DimensionType = DimensionType.Measure,
DisplayName = "Final Price",
Aggregator = new CustomMeasureAggregator()
};
cubeMetadata.DimensionSettings.Add(calcMeasureMetadata);
3. Define your own aggregator:
public class CustomMeasureAggregator : Aggregator<double>
public override IAggregationResult<double, double> Evaluate(IAggregationResult<double, double> oldResult, IAggregateable aggregateable, IEnumerable items)
double result = 0;
IList<object> dataItems = items.Cast<DataRowMetadata>().Select(drm => drm.DataObject).ToList();
if (dataItems.Count > 0)
Type itemsType = dataItems[0].GetType();
PropertyInfo unitsPropertyInfo = itemsType.GetProperty("Cost");
Func<object, object> func = ReflectionHelper.CreatePropertyGetMethod(unitsPropertyInfo);
foreach (object dataItem in dataItems)
// you can use IAggregateable.GetValue(object) instead
// but it operates only over the property specified into DimensionMetadata.SourcePropertyName
/*result += (double)aggregateable.GetValue(dataItem);*/
result += (double)func(dataItem);
}
return new SingleResult<double>(result * 1.05);
public override IAggregationResult<double, double> Evaluate(IAggregationResult<double, double> oldResult, double value)
// you don't need this one
throw new NotImplementedException();
Because of the usage of excel file the items’ type is type which is dynamically generated. That’s why you need to use reflection in order to extract the values from DataRowMetadata.DataObject property. If your calculations are based over a single property you can use IAggregateable.GetValue(object). It has the same Func<object, object> defined internally and it returns the value of the property you have set as source property of DimensionMetadata.
Best regards.
PPilev.
Following this example, I can no longer see 'Cost' Measure in the measures in the data selector on the right..Is it the intended bahavior..? What If I still wante d to Raw Cost numbers too.
It does not work for me.
I want to be able to add the dimension once the grid is bound the and user chooses to add a measure (dynamically generated and based on other measures) to the grid/UI.
As soon as I add this new measure..it still removes the one it is based on.
Please ignore my last post, it was a little in accurate.
I want to be able to add the Measure once the grid is bound the and user chooses to add a measure (dynamically generated and based on other measures) to the grid.
Right now, as soon as I add this new measure..it still removes the one it is based on.
If I understand you correct you want a measure which operates and uses as a source the measures which the user adds to Measures collection of the FlatDataSource.
Have a look at the snapshot – the Compound measure is calculated using both Cost and Units (assuming we have these two columns in the excel file) measures as for each instance it calculates the product of their values.
I have attached the file with the source code. There is no matter which is the source property used for your calculated measure but you still need to specify it when you create the dimension metadata for it. I think it be easy for you to modify it and implement the logic you want.
The new aggregator accepts an instance of the FlatDataSource in its constructor so constructing the dimension metadata is changed. You need these lines of code:
flatDataSource.DimensionsGenerationMode = DimensionsGenerationMode.Mixed;
DimensionMetadata costMetadata = new DimensionMetadata
DisplayName = "Cost",
DisplayFormat = "{0:C3} "
DisplayFormat = "{0:C3}",
DisplayName = "Compound measure",
Aggregator = new CustomMeasureAggregator(flatDataSource)
cubeMetadata.DimensionSettings.Add(costMetadata);
cubeMetadata.DimensionSettings.Add(calcMeasureMetadata)
Hi Plamen,
would it be possible for you to translate this code to vb.net?
That is exactly what I need, but couldn't make it work in Vb.net for the last 2 days...
Thanks
Fabian Zandomeni
Have you tried the translator that .NET Reflector offers?
Just load the assembly, then navigate to the class method you are interesting in and then use the drop down to select the appropriate language for you.Hope that helps.
Regards.Plamen.