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

Recently we had a customer that had a requirement to build an app using the Infragistics Pivot Grid and filter the data in it using a calendar. He needed to be able to select specific dates that should be included in the results of the pivot grid and show results for selected month, year or all periods. As additional functionality he wanted to hide the totals and all header cells that are not on the level that need to be shown in the Pivot Grid UI. For example if year 2008 is selected in the calendar the hierarchy in the pivot grid should show only the specific months and not the 2008 total, half year and quarters groups and results. This article will show you in two parts how to realize all those requirements – implement a slicer control that has the UI of a calendar, hide total columns and unneeded header cells in the columns and rows. This first part will do the basic filtering based on selected dates and the second part will show the rest.

So let’s get straight to it. What is the easiest way to filter the data in the PivotGrid using a control that provides a fast, accurate and easy to comprehend alternative to the standard filtering mechanisms, separate from the PivotGrid’s own UI? The answer to that question is no doubt using a slicer. In short a slicer is a control that lists the available values for a given level in a given hierarchy and allows you to select the ones that you want to include in the results of the bound SlicerProvider (this is the DataSource instance that the PivotGrid uses). You can read details about the existing XamPivotDataSlicer control here: http://help.infragistics.com/Doc/DV/2011.2/CLR4.0/?page=xamPivotGrid_DataSlicer.html. The default template shows all items in a ListBox and internally the selection of those items triggers the filtering. In our case however we know that those items will be years, months and days and we want them shown on a calendar instead of a ListBox. Retemplating is what we need to do. We’re going to replace the list box with a calendar and hook up the events of the calendar with the filtering logic we need to make it filter. In this article we will create a separate generic control that we’ll call CalendarSlicer. With very little modification and with the same success we can use a UserControl or just work with an existing XamPivotDataSlicer. I hope it will be much cleaner and understandable however to have it as a separate control.

The first step is to create an empty control that inherits from XamPivotDataSlicer and edit its template. In the template we’ll add a calendar control and set it a TemplatePart named “PART_Calendar”. Here is how this part of the code in Themes/Generic.xaml will look like:

Note that the local namespace is where you have defined the CalendarSlicer control and mscontrols is where the calendar is:

If we run the app using this newly created slicer we will have a slicer that looks just like a calendar, but nothing will happen when you start interacting with the calendar.

The second step is to initialize the calendar properly and hook up the events we want to work with. To get the instance of the calendar we need to override the OnApplyTemplate method and in it we’ll have the instance easily. Before I show you the code how to do that exactly we need to establish what we need to handle. If you just want to filter based on selected dates and not care about whole months or years you just need to subscribe to the Calendar’s SelectedDatesChanged event. The SelectionMode property on the calendar controls whether you can select single dates only, a whole range or non-continuous ranges of dates. The list of all selected dates is contained in the SelectedDates collection.  If you want to take action based on which mode you are in you need to deal with the DisplayMode property and DisplayModeChanged event. The valid values for DisplayMode are: Decade, Year and Month. The event is raised when you click on a year or month, or the button to go up one level in the UI of the calendar. Another thing that is important, especially if you use XMLA data source is the min and max dates that should be displayed in the calendar. They are set through the DisplayDateStart and DisplayDateEnd properties on the calendar. If you allow the display of dates that are outside the min and max date in the analysis server while using an XMLA data source you will get an exception on clicking those dates, because the MDX query constructed by the slicer will be invalid. A normal not retemplated slicer does not allow situation in which no items are selected it will also take care to show all the valid values, but since we’re replacing the ListBox that shows them with a calendar we need to take care of that limitation ourselves.

Having said all that I can show you the whole OnApplyTemplate method:

Note how I get instance of the calendar and then set all the values I’d need. You can replace those with actual values or use properties set from the parent code as in this demo. You’ll be able to see the definitions of those in the full solution at the end of the article.

Now that we know when a date is selected in the calendar or the mode has changed, lets do some actual filtering. The third step is to implement the SelectedDatesChanged event handler.

The sender for that event handler is the calendar itself. From it we can take the collection of dates that are selected. Once we do that we can cycle through the Items collection of the slicer and check if this date is in the list with selected dates. If the date is selected we need to set the IsSelected property of the FilterSource behind the current item to true. If not – to false. While we are cycling through the list of items we also monitor if there actually are selected items. In the end after the cycle is done we have to call the RefreshGrid method of the DataSource to reflect the changes in the PivotGrid UI. This will be done only if the selection is valid on the server, otherwise there would be an exception. Here is the code that does all this:

Note that we are parsing the DisplayName property of the item. This is the actual caption of the member behind that item. In most analysis databases that will be the date string. In the Adventure Works sample data it is and if you use flat data source it will also probably be like that. But if for some reason this is not a parsable value you will need to access the member of the item and do the comparison in your own custom way.

So far we have created a slicer inheriting control that looks like a calendar and on selection of a number of dates in the Month DisplayMode it will filter the data in the pivotgrid, provided that the proper SlicerProvider, Hierarchy Name and Level are specified.

In many cases this will be a sufficient functionality for a calendar slicer. If you want to delve more into this scenario lookout for part two of this article in the next few days. We’ll continue on with implementing month and year filtering, expanding to the proper level and hiding unnecessary columns and cells. And you can find the solution with all the code that was explained in this article here:

CalendarSlicer Solution

When you open the solution make sure you provide the right assembly references to the dlls you have. It does not matter if they are trial or not. Also in MainPage make sure the values for ServerURI, Database, etc are set according to the data you want to experiment with. It is predefined with the Infragistics’ sample data server. If you use your own data the TargetHierarchyName and level index might also need to be changed accordingly.

I hope this has been useful and interesting.