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?
Hi,It's an internal class which manages the string resources. You can replace SR.GetString("AllPeriods") with a string constant which will appear as a caption of the root member of the hierarchy.
Hi Plamen,
Since I updated to SR2094, I have noticed that the dates don't sort properly anymore. Could you please look into it and let me know if something has changed.
Here is the code I use to sort the dates:
// Date Hierarchy HierarchyDescriptor dateTimeDescriptor = new HierarchyDescriptor { AppliesToPropertiesOfType = typeof(DateTime) }; dateTimeDescriptor.AddLevel<DateTime>(dt => "AllPeriods", "All Periods");
dateTimeDescriptor.AddLevel<DateTime>(dt => dt.Date, "Dates"); // set the sort order of months Expression<Func<DateTime, DateTime>> expression = pd => pd.Date; dateTimeDescriptor.LevelDescriptors[1].OrderByKeyExpression = expression;
flatDataSource.HierarchyDescriptors.Add(dateTimeDescriptor);
// Format Date cubeMetaData.DimensionSettings.Add(new DimensionMetadata() { SourcePropertyName = "End Date", DimensionType = DimensionType.Dimension, AutoGenerateField = true, DisplayFormat = "dd-MMM- yyyy", }); flatDataSource.CubesSettings.Add(cubeMetaData);
Here is the screen shot:
Everything you have done is ok. There was a requirement to not change the order that members appear into the data source, so we had to revert the behavior like it is in 10.3. In order to get members sorted in right alpha-numeric order you have to sort your items source before pass it to FlatDataSource.ItemsSource. But what I remember is that in your scenario you have used ExcelDataConnectionSettings, right? In this case you have to order the items source after it’s populated into ExcelDataConnectionSettings:
// initialize your excel data settings and wait for ItemsSourceChanged event
ExcelDataConnectionSettings excelDataSettings =
new ExcelDataConnectionSettings
FileStream = fileStream,
GeneratedTypeName = ExcelRowDataTypeName,
WorksheetIndex = 0
excelDataSettings.ItemsSourceChanged += this.ExcelDataSettings_ItemsSourceChanged;
private void ExcelDataSettings_ItemsSourceChanged(object sender, ItemsSourceChangedEventArgs e)
ExcelDataConnectionSettings excelDataSettings = (ExcelDataConnectionSettings) sender;
this.SetExcelFormatMappings();
FlatDataSource flatDataSource = new FlatDataSource();
// items source here is List<T>
// use the extension method defined below to order the items source
Type itemsType = excelDataSettings.ItemsSource.AsQueryable().ElementType;
var sortedItemsSource = excelDataSettings.ItemsSource.OrderBy("[Your Property Name Here]", itemsType, ListSortDirection.Ascending);
flatDataSource.ItemsSource = (IEnumerable)sortedItemsSource;
this.xamPivotGrid.DataSource = flatDataSource;
this.xamPivotDataSelector.DataSource = flatDataSource;
public static class Extensions
public static object OrderBy(this IEnumerable items, string propertyName, Type itemsType, ListSortDirection sortDirection)
PropertyInfo prop = itemsType.GetProperty(propertyName);
// Parameter
// We need this for input parameter (t) for expression t => t.Property
var body = System.Linq.Expressions.Expression.Parameter(itemsType, "dataObject");
// MemberExpression
// We need this in order to access propety member t.Property
var memberExpression = System.Linq.Expressions.Expression.Property(body, prop.Name);
// Link the expression parts together
var lambda = System.Linq.Expressions.Expression.Lambda(memberExpression, new[] { body });
string methodName = "OrderBy";
if (ListSortDirection.Descending == sortDirection)
methodName = "OrderByDescending";
MethodInfo methodInfo = typeof(Enumerable).GetMethods().Where(m => m.Name == methodName && m.IsGenericMethod).First();
methodInfo = methodInfo.MakeGenericMethod(itemsType, prop.PropertyType);
return (IEnumerable)methodInfo.Invoke(null, new object[] { items, lambda.Compile() });
Note that here ExcelDataConnectionSettings instance is used only like an helper we get the items from. I hope that will work for you.
Regards.
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.
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.