SPJS Charts for SharePoint: date range filter

In this article I will show you how to add a date range filter to a chart created using SPJS Charts for SharePoint v4. The code demonstrated below will query the list and get the highest and lowest date in the field specified in the query. It will then create the filter dropdowns based on the interval you select. Currently you can use “m” for month, or “y” for year.

IMG

How to set it up

To use this method for filtering a chart, you must set the variable “allowEval” to true in the CEWP code like this:

// Set this to true to allow for the use of variables in the "Filter setup textarea"
var allowEval = true;

Then you add this code to the same CEWP:

function spjs_getDateRangeFilter(list,field,interval,format){
	var q, res, start, end, split, startDate, endDate, b, d, m, y;
	// Find the start date
	q = "<Where><IsNotNull><FieldRef Name='"+field+"' /></IsNotNull></Where><OrderBy><FieldRef Name='"+field+"' Ascending='TRUE' /></OrderBy>";
	res = spjs.charts.qItems({"listName":list,"query":q,"viewFields":[field],"rowLimit":1});
	start = res.items[0][field];
	// Find the end date
	q = "<Where><IsNotNull><FieldRef Name='ID' /></IsNotNull></Where><OrderBy><FieldRef Name='"+field+"' Ascending='FALSE' /></OrderBy>";
	res = spjs.charts.qItems({"listName":list,"query":q,"viewFields":[field],"rowLimit":1});
	end = res.items[0][field];
	split = start.split(/-| |:/);
	startDate = new Date(split[0],parseInt(split[1],10)-1,split[2]);	
	split = end.split(/-| |:/);
	endDate = new Date(split[0],parseInt(split[1],10)-1,split[2]);
	b = [];
	switch(interval){
		case "m":	
			endDate.setMonth(endDate.getMonth()+1);					
		break;		
		case "y":
			endDate.setFullYear(endDate.getFullYear()+1);
		break;
		default:
			alert("The interval \""+interval+"\" is not supported. Use \"m\" for month or \"y\" for year.");
			return [{"f":"","v":""}];	
	}
	while(startDate<=endDate){
		d = startDate.getDate();
		d = d < 10 ? "0"+d : d;
		m = startDate.getMonth()+1;
		m = m < 10 ? "0"+m : m;
		y = startDate.getFullYear();
		b.push({"f":format.replace("yyyy",y).replace("MM",m).replace("dd",d),"v":String(startDate.getFullYear()+"-"+(startDate.getMonth()+1)+"-"+startDate.getDate()+" 12:00:00")});
		switch(interval){
			case "m":					
				startDate.setMonth(startDate.getMonth()+1);				
			break;		
			case "y":
				startDate.setFullYear(startDate.getFullYear()+1);
			break;		
		}
	}
	return b;
}

var myCustomFilterOptions = spjs_getDateRangeFilter("ListNameOrListGuid","FieldInternalName","m","MM/dd/yyyy");

The variable “myCustomFilterOptions” is used in the filter textarea in the chart. You can name it anything you like, preferably something that identifies the filter.

The arguments to the function “spjs_getDateRangeFilter” is as follows:
list: The DisplayName or the list GUID of the list you are querying.
field: The field internal name of the field – must be type “Date and time”.
interval: The date interval for the filter. Use “m” for month, or “y” for year.
format: The format of the visible date in the dropdown. Use any combination of dd (for day), MM (for month) and yyyy (for year).

Look her to learn how to find the list GUID and the FieldInternaleName.

This is the setup for the chart shown in the top of this article:
IMGIMG

The text in the filter textare is this:

[{"label":"Date from","urlKey":"date1","multiselect":false,"size":5,"options":myCustomFilterOptions},{"label":"Date to","urlKey":"date2","multiselect":false,"size":5,"options":myCustomFilterOptions}]

Please note the name of the variable “myCustomFilterOptions” from the CEWP code.

And the text from the Custom CAML-query textarea is this:

<Where><And><Geq><FieldRef Name='Date' /><Value Type='DateTime'>{filter:date1}</Value></Geq><Leq><FieldRef Name='Date' /><Value Type='DateTime'>{filter:date2}</Value></Leq></And></Where>

You must change the name of the filed to match the FieldInternalName of the field in your list.

Leave a comment below if you like it, or you need help setting it up.

Alexander

22 Comments on “SPJS Charts for SharePoint: date range filter”

  1. I have followed all the steps above, but I am getting the following error:

    Filter error in “……..”

    TypeError: Cannot read property ‘length’ of undefined

    If you are using variables in the filter textarea:
    Use “allowEval = true” in the Master Web Part CEWP code.

  2. This is how I called the function:

    var myCustomFilterOptions = spjs_getDateRangeFilter(“F15A4AA1-2BEB-4D69-90DB-4DF16CF3D68C”,”DateOfService”,”m”,”MM/dd/yyyy”);

    And this is the CAML query:

    {filter:date1}{filter:date2}

    1. Hi,
      You must verify that the variable “myCustomFilterOption” is not undefined. Try alerting it like this:

      alert(typeof myCustomFilterOption);

      Whet does the alert read?

      If it is “undefined” you have an error in the query and must verify the list name and the FieldInternalName.

      Alexander

      1. Thanks for taking the time to help me. I placed the code within the script and did not get the alert, however when I debug the scrip, I see the following error: “SCRIPT5009: ‘spjs’ is undefined”.

      2. Sorry for the late reply. You must reorder the scripts in the CEWP so that the reference to spjs-charts-v4.js is placed above the script block where you are using “spjs.charts.qItems”.

        Alexander

  3. Good day Sir Alexander,

    I use the below code to filter the charts and my calculated column DATEFILTER in sharepoint list is =TEXT(DATE, “DD/MMM/YYYY”) and is working fine, but in the charts the filter shows string;#21/Sep/2014 and because of that it return No items match this filter…

    function spjs_getFilterValue(list,field){
    var q = “”;
    var res = spjs.charts.qItems({“listName”:list,”query”:q,”viewFields”:[field]});
    var b = [];
    var fObj = {};
    $.each(res.items,function(i,item){
    if(fObj[item[field]] === undefined){
    fObj[item[field]] = 1;
    b.push({“f”:””+item[field]+””,”v”:””+item[field]});
    }
    });
    return b;
    }
    var myCustomFilterOptions = spjs_getFilterValue(“{3DE38E73-F09F-40E7-A580-835513FBB638}”,”DateFilter”);

    Thank you very much.

    2. How do you get around haivng to specify a filename? What if I want the form to work like a list form when the user fills it out and clicks okay, it goes into a list. I know you can do that with content types and lists, but can you do it with an infopath-built content type?

  4. Hi Alexander,

    I’m having some trouble using the filters as above. I’m using almost everything as you have posted as is, with the following modifications.

    myCustomFilterOptions:
    var myCustomFilterOptions = spjs_getDateRangeFilter(“8D8806CB-A607-4684-8A9C-44B28748F59D”,”Created”,”m”,”dd/MM/yyyy”);

    Custom CAML:
    {filter:date1}{filter:date2}

    The filter appears to load correctly except that once I change the filter range from the default, it sits on ‘Filtering…’ and I receive the below error in the Chrome console.

    ‘Invalid column index 9. Should be an integer in the range [0-7]’

    Do you have any idea what this might be about? It’s a fairly large list of 1000+ items.

      2. That was the issue. Somehow I was on 4.05 when I thought I was on the latest. Sorry to have not checked this myself! Thanks for your time.

  5. Hi Alexander,

    I am trying to figure out the differences between the OOB Chart Web Part and your SPJS Chart Web Part, is there any section of your blog that details the differences?

    Thanks.

  6. Hi Alexander – I was able to get the date range filter set up and working. I have set the range to be by month. Currently, when the page loads, the chart filters to the earliest month in my list. I would like it to default to the most recent (latest month). Is there a way to accomplish this? Thank you so much for all of your work on this.

  7. Hi,
    If you use the example snippet from the article, the easiest is to reverse the order of the items in the dropdown like this:

    q = "<Where><IsNotNull><FieldRef Name='"+field+"' /></IsNotNull></Where><OrderBy><FieldRef Name='"+field+"' Ascending='FALSE' /></OrderBy>";

    Note that “Ascending” is FALSE.

    I have not tested this so let me know how it works out.

    Alexander

    1. Hi Alexander –
      Unfortunately that solution did not work for me. The dropdown then only showed the most recent month, but no other options. What did work for me was modifying a line in the text file from return b; to return b.reverse();. I was then able to modify the function to show the start date of current month and end date of current month +1. Thank you for your help with this.

      Sarah

  8. Hey A, back to bug you again. I added the date filter. after I save I get an error
    TypeError: Unable to get property ‘length; of undefined or null reference
    some of my date fields are blank. would this cause the error? If yes, how could I get around that so it would throw an error?

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.