Hi,
How can I format a DataTime column in XamPivotGrid.
Thanks
Sangeetha
Hello,
Currently the default datetime hierarchy descriptor is defined as follows:
HierarchyDescriptor dateTimeDescriptor = new HierarchyDescriptor { AppliesToPropertiesOfType = typeof(DateTime) };
dateTimeDescriptor.AddLevel<DateTime>(dt => SR.GetString("AllPeriods"), "All Periods");
dateTimeDescriptor.AddLevel<DateTime>(dt => dt.Year, "Years");
dateTimeDescriptor.AddLevel<DateTime>(dt => dt.SemesterShort(), "Semesters");
dateTimeDescriptor.AddLevel<DateTime>(dt => dt.QuarterShort(), "Quarters");
dateTimeDescriptor.AddLevel<DateTime>(dt => dt.MonthShort(), "Months");
// set the sort order of months
Expression<Func<DateTime, int>> expression = pd => pd.Month;
dateTimeDescriptor.LevelDescriptors[4].OrderByKeyExpression = expression;
dateTimeDescriptor.AddLevel<DateTime>(dt => dt.Date.ToShortDateString(), "Dates");
As you can see the Dates level here operates as it returns string values. You can modify Dates level descriptor to returns a value with type of DateTime. Just copy the code above and modify only Dates level descriptor:
dateTimeDescriptor.AddLevel<DateTime>(dt => dt.Date, "Dates");
flatDataSource.HierarchyDescriptors.Add(dateTimeDataDescriptor);
When the data is visualized the specified format string will be applied to this value. You can set the format string using an attribute applied to the property
[DisplayFormat(DataFormatString = "ddd dd MM yyyy")]
public DateTime Date
{
get; set;
}
Or adding a dimension metadata:
CubeMetadata cubeMetadata = new CubeMetadata { DataTypeFullName = "[Your data type full name]", DisplayName = "My Cube Display Name" };
flatDataSource.CubesSettings.Add(cubeMetadata);
DimensionMetadata dateMetadata = new DimensionMetadata
SourcePropertyName = "Date",
DisplayName = "Dates",
AutoGenerateField = true,
DisplayFormat = "ddd dd MM yyyy"
};
cubeMetadata.DimensionSettings.Add(dateMetadata);
Best regards.
Plamen.
Great. Thank You. I have one problem. I the following line, What is the SR object? I googled and found that it belongs to Web classes. My App is not a web app, so is there an alternative to SR object?
I worked with the support on this issue. It turns that in 11.2 for DateTime type formatting is not used. In order to get it work, a new HierarchyDescriptor should be created and used in XAML.
public class DateHierarchyDescriptor : HierarchyDescriptor {
public DateHierarchyDescriptor() {
this.AppliesToPropertiesOfType = typeof (DateTime);
this.AddLevel<DateTime>(dt => "All", "All Periods");
this.AddLevel<DateTime>(dt => dt.Year.ToString(), "Years");
this.AddLevel<DateTime>(dt => dt.Month, "Months");
this.AddLevel<DateTime>(dt => dt, "Dates");
XAML:
<FlatData:FlatDataSource >
<FlatData:CubeMetadata DataTypeFullName="DateTimeHierarchyWPF.PartnerPercentage" DisplayName="Partner Percentage">
<FlatData:DimensionMetadata SourcePropertyName="LastChangeDateTime" DisplayName="Last Change DateTime" DisplayFormat = "G"/>
<FlatData:DimensionMetadata SourcePropertyName="DueDateTime" DisplayName="Due DateTime" DisplayFormat = "G"/>
<FlatData:DimensionMetadata SourcePropertyName="VersionDate" DisplayName="Version Date" DisplayFormat = "d" />
</FlatData:CubeMetadata>
</FlatData:FlatDataSource.CubesSettings>
<FlatData:FlatDataSource.HierarchyDescriptors>
<DateTimeHierarchyWPF:DateHierarchyDescriptor SourcePropertyName="DueDateTime" HierarchyName="Due DateTime"/>
<DateTimeHierarchyWPF:DateHierarchyDescriptor SourcePropertyName="LastChangeDateTime" HierarchyName="Last Change DateTime"/>
<DateTimeHierarchyWPF:DateHierarchyDescriptor SourcePropertyName="VersionDate" HierarchyName="Version Date"/>
</FlatData:FlatDataSource.HierarchyDescriptors>
</FlatData:FlatDataSource>
More on this in Support Request CAS-80145-JW5Y32.
I still have a question about formating date and time. What is simplest way to show date with time? For example, I have date ‘1/1/2012 15:15:12’ and I have this DimensionMetadata:
<FlatData:DimensionMetadata SourcePropertyName="VersionDtSys" DisplayName="Version Date" DisplayFormat="G"/>.
I’m getting the date time show as ‘1/1/2012 12:00:00 AM’ – the time portion is lost. Is my DisplayFormat set to correct value?
Thanks,
Sergey.
Hi Plamen,
What is the best way to do the following after dataset is set. It needs to be expanded before being sorted so layout updated event gives me plenty of problems.
DataSourceBase.SwitchMemberSortDirection(IMember)
This is a great answer - very informative. It solved my problem.
The default ordering depends on the order of appearance of each member in items source. And what you can do is to pass ordered collection as items source.
If you have enabled headers sorting then you have to override the default alphabetical behavior as setting HierarchyLevelDescriptor.OrderByKeyExpression property of each hierarchy level descriptor.
Look at the code below:
HierarchyDescriptor<ProductData> daysOfMonthDescriptor = new HierarchyDescriptor<ProductData>(pd => pd.Date);
daysOfMonthDescriptor.HierarchyName = "Days Of Month";
daysOfMonthDescriptor.AddLevel(pd => "All Days Of Month", "All");
daysOfMonthDescriptor.AddLevel(pd => pd.Date.DayOfMonth(), "Days Of Month");
// provides the right items order based on their numeric values
Expression<Func<ProductData, int>> orderByExpression = d => d.Date.DayOfMonthOrderKey();
daysOfMonthDescriptor.LevelDescriptors[1].OrderByKeyExpression = orderByExpression;
This code creates an hierarchy descriptor and adds three levels to it. If you are using the helper methods when you create the level descriptor then it will be necessary to access your level descriptor by its index through LevelDescriprors collection.
Later when the data is processed when we hit a new member this OrderByKeyExpression is evaluated just once, only for the very first instance producing the existence of that member.
The DayOfMonthOrderKey() method above is extension method as follows:
public static int DayOfMonthOrderKey(this Nullable<DateTime> dateTime)
if (!dateTime.HasValue)
return 32;
return dateTime.Value.Day;
Once you have this set you can change the header sort order from code behind calling DataSourceBase.SwitchMemberSortDirection(IMember)
Note that initially the columns always are loaded in their order of appearance in the data source.
In order to allow header sorting in the grid UI you have to set:
<ig:XamPivotGrid x:Name="pivotGrid" AllowHeaderColumnsSorting="True" AllowHeaderRowsSorting="True"/>
This sorting indicator will appear in the UI
Clicking it for the first time will sort the columns ASC based on the key evaluated using OrderByKeyExpression, so if your columns are initially loaded in ASC order you wouldn’t see any change on the first click. This behavior is by design.
Regards.Plamen.