How can I create a derived measure field dynamically, that calculates the distinct count of another field?
For example, take the flat table below that contains raw data depicting a list of men who went to an events and met women:
Male_Name
Event_Name
Female_Name
Jack
Concert
Ashley
Brittney
Party
Steph
Linda
Mixer
Amber
Bob
Ginger
Gail
Sophia
Chad
The following Pivot Grid shows the Event_Name as columns, Male_Name as rows and Number_Of_Girls_Met as the measure which is derived from the distinct count of Female_Names:
2
1
3
0
How do I make the measure field Number_Of_Girls_Met of type integer, without setting this up in the database?
Hello,
I have been looking into your question and I can suggest you look into the following sample from our website :
https://ko.infragistics.com/samples/silverlight/pivot-grid/#/flatdata-aggregators
Here custom aggregators are used.
If you need any further assistance, feel free to ask.
I don't think that's the same thing I'm asking for Nikolov. I specifically need a field that would show up in the "Measures" section of the tree that you will just have to drag into the measures section of the pivot grid. That Field should be, in sql terms, a DISTINCT COUNT of an existing field.
If you are using XmlaDataSource you need to create/set up this in BI studio.
If the case is with FlatDataSource if you want to deal with this with less effort you'll need at least numeric property exposed by your data object, so you can create a measure for that property. That's because of the way the default measures are created.
<olap:FlatDataSource.CubesSettings>
<olap:CubeMetadata
DataTypeFullName="YourDataClassFullName"
DisplayName="My Data">
<olap:DimensionMetadata SourcePropertyName="YourNumericPropertyName"
DimensionType="Measure"
DisplayName="DisplayNameForYourMeasure"
DisplayFormat="{}{0}"
AggregatorType="Count"/>
</olap:CubeMetadata>
</olap:FlatDataSource.CubesSettings>
This code will create a measure that will act in the way you have described and will calculate the common count of the members involved.
However, if it's not acceptable for you to create another property for your class then you'll need to create a custom aggregator as Yanko has proposed. It seems the online sampe is missing the code of the custom aggregator, so I have attached it. In your case the TopCountCache class just need to store the absolute number of AddCount method is called.
Regards.Plamen.
Plamen, your suggestion sounds more like what we need. This is what I did so far. All the default fields show up, I did not get any errors BUT I don't see the measure "Female Count" showing up on the data selector. Am I missing anything? I'm using code behind because I'm having a hard time getting the XAML binding to work, that'll be a separate post later.
CubeMetadata cubeMetadata = new CubeMetadata(); cubeMetadata.DataTypeFullName = typeof(BoyMeetsGirl).FullName; cubeMetadata.DisplayName = "Boy Meets Girl"; cubeMetadata.DimensionSettings.Add( new DimensionMetadata(){ SourcePropertyName ="Female_Name", DimensionType = DimensionType.Measure, DisplayName = "Female Count", DisplayFormat ="{}{0}", AggregatorType = AggregatorType.Count });
FlatDataSource dataSource = new FlatDataSource();dataSource.ItemsSource = DatingViewModel.BoyMeetsGirlObservableCollection;dataSource.CubesSettings.Add(cubeMetadata);pivotDataSelectorSampleDetails.DataSource = dataSource;pivotGridSampleDetails.DataSource = dataSource;
NOTE: with all my code and solutions, please refer to my original posting for the fictional flat data source.
As I was looking at the available AggregatorTypes, I realized that DistinctCount fits more of what I'm trying to accomplish. However, I get a compile time error saying : 'Infragistics.Olap.AggregatorType' does not contain a definition for 'DistinctCountCount' even though it shows up on intellisense.
please ignore that last post, it's late and i didn't notice the double "count"
Hello,The cube settings should be added before items source is set because all metadata is processed when items source is set.FlatDataSource dataSource = new FlatDataSource();
dataSource.CubesSettings.Add(cubeMetadata);dataSource.ItemsSource = DatingViewModel.BoyMeetsGirlObservableCollection;
Looks like something that I am looking too. I have a class Customer. How can I expose the property(Amount) of Order as a measure, if I create a Cube out of List of Customers(basically FlatDataSource).
public class Customer
{
string Name {get; set;}
Order CustOrder {get; set;}
}
public Order
string OrderNum {get; set;}
double Amount {get; set;}
Plamen, YOU ARE A GENIUS!!!
This is exactly what I need. Although I was hoping that your first suggestion would work, because it made a lot of sense. I am happy with the solution you've provided. It's given me extra things to add to the application.
Thank You!!!
I have modified the TopCountAggregator so it can follow different behaviors now. You can find a working sample attached which I hope will cover your needs.
Best regards.Plamen.
Thanks for all the help Plamen. However, that did not do anything. For the following example, I'm using this data model (obviously with a modified namespace)
http://help.infragistics.com/Help/Doc/Silverlight/2012.2/CLR4.0/html/SalesDataSample.html
I'm beginning to suspect this cannot be done with a simple xaml tag although I'm still hoping there's an easy answer. I tried attaching my solution but i've been getting a 500 server error. so here's the main xaml file:
<Grid x:Name="LayoutRoot" Background="White"> <Grid.Resources> <models:SalesDataSample x:Key="dataSample"/> <olap:FlatDataConnectionSettings x:Key="FlatDataConnectionSettings" ItemsSource="{StaticResource dataSample}" /> <olap:FlatDataSource x:Key="flatDataSource" ConnectionSettings="{StaticResource FlatDataConnectionSettings}" Cube="Sale" Rows="[Seller].[Seller]" Columns="[Product].[Product]" Measures="AmountOfSale" > <olap:FlatDataSource.CubesSettings> <olap:CubeMetadata DataTypeFullName="PivotGridSample.Models.Sale" DisplayName="Sale of the Greatest">
<!-- NOTE TO INFRAGISTICS: This is the derived measure I would like to get - A distinct count (Seller Count) of the dimension field (Seller),
: note that this measure does not exist in the model, I want to derive it from an existing dimension.
: Using your suggestions, this field does not show up on the measures tree of the data selector.
: Could it be because we need to make some kind of custom aggregator that we need to set in the "Aggregator" property? -->
<olap:DimensionMetadata SourcePropertyName="Seller"
DisplayName="Seller Count"
AggregatorType="DistinctCount"
DimensionType="Measure"/>
</olap:CubeMetadata> </olap:FlatDataSource.CubesSettings> </olap:FlatDataSource> </Grid.Resources>
<Grid.ColumnDefinitions>
<ColumnDefinition></ColumnDefinition>
<ColumnDefinition Width="Auto"></ColumnDefinition>
</Grid.ColumnDefinitions>
<ig:XamPivotGrid HorizontalAlignment="Left" Name="xamPivotGrid1" VerticalAlignment="Top" Grid.Column="0" DataSource="{StaticResource flatDataSource}"/>
<ig:Expander Grid.Column="1">
<ig:XamPivotDataSelector HorizontalAlignment="Left" Name="xamPivotDataSelector1" VerticalAlignment="Top" DataSource="{StaticResource flatDataSource}"/>
</ig:Expander>
</Grid>
</UserControl>