How to use the Calendar control as a Slicer for the XamPivotGrid – part 2

Atanas Dyulgerov / Tuesday, January 10, 2012

Last week I blogged about creating a basic slicer that looks like a calendar and upon selection of dates from it the data in the pivot grid is filtered. Here is the link to that article if you’ve missed it.

This week we’re going to build up on what we created before. I’ll start with handling change of level in the calendar (selecting year or particular month or going up a level). Then we’ll tweak the UI to show only the relevant information to our case – removing totals and unnecessary header cells.

In the previous article we mentioned that the calendar ui and the slicer control are not automatically bound and we need to implement the interaction between them ourselves. The event from the calendar that we need to handle is called DisplayModeChanged. The event arguments expose a property NewMode that denotes the level we’re changing to. To bind that level to actual functionality in the slicer we need to modify the target level of the slicer to coincide with the the level of the calendar.

  1. void DisplayModeChanged(object sender, CalendarModeChangedEventArgs e)
  2. {
  3.     var cal = sender as Calendar;
  4.     if (cal.DisplayMode == CalendarMode.Decade)
  5.     {
  6.         this.TargetLevelIndex = DecadeLevelIndex;
  7.     }
  8.     else if (cal.DisplayMode == CalendarMode.Year)
  9.     {
  10.         this.TargetLevelIndex = YearLevelIndex;
  11.     }
  12.     else if (cal.DisplayMode == CalendarMode.Month)
  13.     {
  14.         this.TargetLevelIndex = MonthLevelIndex;
  15.     }
  16. }

Notice here that DecadeLevelIndex, YearLevelIndex and MonthLevelIndex are properties set to the level numbers that correspond to the appropriate hierarchy level in the datasource you use. For Adventure Works sample database those are respectively 0,1 and 4.

Once we do that we have to wait the items to load in the slicer and then we can do some filtering based on them in the grid.

Getting the items after target level change is not straightforward. First we need the number of items that will be loaded. We can get this number from the GetFilterSourcesCompleted event of the SlicerProvider. The event args expose a Result property which is a collection of all FilterMembers that will be the basis for the filtering items loaded later. The number we need to store is the count of the collection’s elements. Why do we need this number, you might ask. We need it because the items that we’ll interact with later are loaded one by one and we need to be able to tell when all the items have been loaded. The event we’ll use is the CollectionChanged event of the Items collection in the slicer. Subscribing to this event however is also not straightforward. In order to overcome the protection issues you need to cast the collection to the INotifyCollectionChanged interface – like this:

  1. ((INotifyCollectionChanged)this.Items).CollectionChanged
  2.                     += SlicerItemsCollectionChanged;

So in that event handler we check every time if the Items’ count is equal to the count we stored in the GetFilterSourcesCompleted and then we can carry out our filtering logic, by marking the appropriate items’ FilterSource.IsSelected to true or false.

If we are in the decade level we want all items to be selected so here is the snippet:

  1. if (this.TargetLevelIndex == DecadeLevelIndex)
  2. {
  3.     foreach (var item in this.Items)
  4.     {
  5.         item.FilterSource.IsSelected = true;
  6.     }
  7. }

Where DecadeLevelIndex is the level that corresponds to Years in your Date hierarchy. For the Adventure Works sample database this number is 0.

Similarly for the year level we have this:

  1. if (this.TargetLevelIndex == YearLevelIndex)
  2. {
  3.     foreach (var item in this.Items)
  4.     {
  5.         if (item.DisplayName.Contains(DisplayDate.Year.ToString()))
  6.         {
  7.             item.FilterSource.IsSelected = true;
  8.         }
  9.         else
  10.         {
  11.             item.FilterSource.IsSelected = false;
  12.         }
  13.     }
  14. }

Notice in this level we actually have filtering taking place. Being in Year level in the calendar means that we have selected a specific year and we see its months in the ui. So we would want to show data only for this particular year in the PivotGrid. That’s why we get the selected display date in the calendar control, take its year and we compare it to all items in the slicer. DisplayDate in this sample is actually a property with the following getter:

  1. get
  2. {
  3.     var cal = GetTemplateChild("PART_Calendar") as Calendar;
  4.     if (cal != null)
  5.     {
  6.         return cal.DisplayDate;
  7.     }
  8.  
  9.     return _displayDate;
  10. }

In adventure works all items on the Year level (1) include the full number of the year so filtering with .Contains() is an adequate solution. However if you have some custom source with non-standard date hierarchy you might have to do some more magic to match each item to the particular year and set the filtersource.isselected property accordingly.

The next step is selecting a month and this is done in exactly the same way. The only modification is the item matching condition. If takes both the selected date and month into consideration:

  1. if (this.TargetLevelIndex == MonthLevelIndex)
  2. {
  3.     foreach (var item in this.Items)
  4.     {
  5.         DateTime date;
  6.         if (DateTime.TryParse(item.DisplayName, out date) ||
  7.             DateTime.TryParse(item.FilterSource.Member.Members.First().ToString(), out date))
  8.         {
  9.             if (date.Year == DisplayDate.Year && date.Month == DisplayDate.Month)
  10.             {
  11.                 item.FilterSource.IsSelected = true;
  12.             }
  13.             else
  14.             {
  15.                 item.FilterSource.IsSelected = false;
  16.             }
  17.         }
  18.     }
  19. }

Once those filter modifications are done we only need to call the RefreshGrid method of the SlicerProvider:

  1. (this.SlicerProvider as DataSourceBase).RefreshGrid();

So far we have a Calendar Slicer that filters by selected date or month and year that are being displayed. Here is the solution for this intermediate step.

CalendarSlicer Solution

Next is how to expand the Date hierarchy in the PivotGrid to reflect the calendar level changes.