SPJS Charts for SharePoint v4: Generate filter values with a function

This post will describe how you can generate the filter values for a chart dynamically in SPJS Charts for SharePoint v4 using a function in the Master Web Part CEWP code.

This is something you will have to do if you want to filter a chart based on a column value that has dynamic contents and you therefore cannot use a static filter set in the chart options.

Step 1
Go to the SPJS Charts for SharePoint v4: Master Web Part, and set the variable “allowEval” to true:

<div class="spjs_chartPlaceholder_master"></div>
<script type="text/javascript">
// Load the Google Visualization API release candidate
var loadRC = false;
// Set this to true to allow for the use of variables in the "Filter setup textarea"
var allowEval = true;

var spjs_chart_license = "XXXX-XXXXXXXXXXXXXXXXXXX";
</script>

Step 2
Add the code for creating the filter to the same CEWP:

<script type="text/javascript">
function spjs_getFilterValue(list,field){
	var q = "<Where><IsNotNull><FieldRef Name='ID' /></IsNotNull></Where><OrderBy><FieldRef Name='"+field+"' /></OrderBy>";
	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("Poll","Answer");
</script>

This example queries the list “Poll” for the unique values in the column “Answer”. You can use any code you like to obtain these values, as long as the result is formatted like an array with this syntax:

[{"f":"Value 1","v":"Value 1"},{"f":"Value 2","v":"Value 2"}]

The “f” property is the “friendly name” that is shown in the dropdown select. This can be modified to make sense for the user. The “v” property is the value you expect to find in the database. For further instructions look at the “instructions” above the filter textarea in the “Edit chart GUI”.

Step 3
Use the variable “myCustomFilterOptions” in the filter textarea in the “Edit chart GUI”:

[{"label":"Answer","urlKey":"Answer","multiselect":false,"size":3,"options":myCustomFilterOptions}]

And use this CAML:

<Where><Eq><FieldRef Name='Answer' /><Value Type='Text'>{filter:Answer}</Value></Eq></Where>

Ask if anything is unclear.
Alexander

43 Comments on “SPJS Charts for SharePoint v4: Generate filter values with a function”

  1. Hi Alexander,
    I used the function for generating filters and it works beautifully!

    Question: Is there a way to share filters between multiple charts? One set of filters to dynamically filter the values in two charts on the same page?

    1. Hi,
      Look at the option “filterAdditionalCharts” in the instructions above the filter textarea in the Chart GUI.

      Please note that I found a small bug with the “Filtering…” overlay when using “filterAdditionalCharts”. You should update to v4.01.

      Alexander

  2. Hi, we am still testing internally some of your solutions and I´ve already updated to version 4.

    Now we need to develop some dashboards, for real, and I am trying to avoid Performance Point. The Dashboard should mix lists and work with some filters. For example, imagine a data model with classrooms, students, presence and grade information. The scenario with major graphs showing all classrooms and all presence and grade info is OK, but if I need to drill down to a single classroom, chosen dynamically by the user, and show its details of the presence and grade by student should require a dashboard.

    Could these graphs receive parameters from an external filter web-part?

    Again, congrats on the development. I am looking forward to support your development, specially if we could use it for our customers. Thanks in advance, alex.

    1. Hi,
      This solution cannot roll up multiple lists and present them in one chart. Drilldown by clicking in the chart is also not possible without vision coding, but you can filter charts (one or more) from one filter.
      If you need help, you can send me some details about your data source and a mockup of what you want to create.
      Alexander

  3. Hi there, this 1 filter for all charts should work. But I do need to mix some google charts with other reports(i plan to develop using reporting services, for example).

    In fact, I am still working on the design/proposal for the solution. I could send you a wireframe of the dashboard required to see if you could point me out to the solution. What is your email?

    Tks in advance (again), Alex.

  4. Alexander,

    I am using this filter option to create a view for each employee where I work. The filtering works great. My concern is in the chart itself. I am using dates as markers along my “X” (horizontal) axis. The items were not inputted based on the calendar as some from Dec 2013 were inputted before I put in historical records from October and November. However, the list is sorted by the Dates. My list reads correctly, however, the dates are not feeding in order. In fact, it almost appears that the sort info for the “X” axis is sorting based on the ID and not by the date as my filtered list does. Is there something that I need to change to correct this? I can send you an image via email if you like. Thank you.

    George

    1. Hi,
      When you use Custom CAML you no longer get the filter or sort from the view. To sort the items by anything other then ID you must enter this in the Custom CAML textarea before (or after) the “Where”

      <OrderBy><FieldRef Name="Status" /></OrderBy>
<Where>...</Where>

      Hope this helps,
      Alexander

    3. Alexander,

      Excellent. Just an FYI for anyone who reads this, be sure that “Status” is replaced with the internal field name in order for it to show up properly.

      Also, is there any way to only show the date and drop the time stamp from the date fields in my chart? I’ve got a bunch fo 00:00:00 that do not need to be a part of the X-axis. Thank you once again, you have done an awesome job in all that you make available for us.

      George

      2. Alexander,

        Thank you so much for your guidance. You sure do a wonderful job here in helping and responding to quickly. I hope you have a great weekend.

        George

  5. HI, Alexander,

    I’ve got two questions for you using this filtering. You’ve done such a great job and have helped me so much. I hope these questions are possible. Thank you.

    1: In cases where I have a Master and multiple slave charts on the same page and all charts use the filtering. Is there a way to have one filter done at the top and all related slave charts filter to match the top one instead of having to filter in every single chart? I have some pages where there are 5 or more charts and all have to have the filter set in order to look at the same individual in all charts. If there is any way of incorporating a one and done approach for all those charts, it would be great.

    and 2: Is there a way to set the filtering based on what names show up in a view for that list? I have three management groups, and they would each like their own page. However, with the filtering as I have it, they have to select from the filtering based on all individuals instead of just the ones from their filtered view. Can the filtering be adjusted to show only those from the List view? (Especially since my list view is filtering to just the people that the manager want’s to see.)

    Alexander, thank you once again and I look forward to your responses.

    Sincerely, George

    1. Sorry, I’m using version 4.05. Also, I do not see the instructions as you stated in Gilbert’s post from earlier about the FilterAdditionalCharts. This is why I am asking again. Thank you.

    2. Hi,
      Q1: You find the setup for “filterAdditionalCharts” by clicking “instructions” above the filter textarea under “Advanced options > Use custom CAML > Create filter(s) above the chart.

      Q2: The chart can consume filter values from a list view when you filter the chart manually using the column header filters. It cannot read the filters from a stored list view. You can however try to set up the chart using a filtered view as data source without using custom CAML and use the column you want to “filter on” as x axis with “Format” String – merge duplicates.

      Hope this helps,
      Alexander

      1. Alexander,

        Please forgive me, however, I do not understand the instructions to set up the charts for multiple filtering. I added the code in between the first “]” & “}” at the end:
        “filterAdditionalCharts”:”86b441b4-0d47-4866-b059-93e9f3967e49″

        Obvisously, I did something wrong as I got an error after the refresh. I will be adding multiple charts. Can you please demonstrate where I need to place this array and the correct manner in which it should be included?

        Also, how do I set up the custom CAML to consume the filter values for the additional charts?

        Thank you so much for your patience in helping me to understand what to do with the CAML info as I am new to it. Thank you.

      2. Hi,
        You were almost there, but missed the square brackets:
        “filterAdditionalCharts”:[“ChartID_1″,”ChartID_2”]

        The CAML in the additional charts should be the same as the Master if this is possible. If not, the CAML must be built to fit the list and then you use the “{filter:urlKey}” to pick up the filter value.

        Alexander

      3. Alexander,

        Excellent. I have it working now. Thank you so much for this. I am going to incorporate it in several areas. Thank you once again and I hope you have an awesome week.

        George

  6. HI Alex,
    Could you please let me know whether we can select ALL as a value when the page loads. Ex: i am creating a filter for username using function but on load i would like to select all the usernames by default.

  7. Hi,
    I was wondering if it is possible to show more data points on the x axis. Currently on my x axis I have Months but its only showing 5 months, I would like it to show all 12 months.

      1. Hello Alex,

        When I hover over the the lines in the Area chart the data points are there for all the months, I have about 36 months worth of data. Only 5 vertical lines are going through the chart hence why only 5 months through out the 3 years are showing. What I’m trying to figure out is how can I increase the amount of vertical lines in the chart to show more month labels on the bottom.

      1. Thanks for the reply,

        I have found that but i’m still unsure how to input that value into the chart. Do I just add a new option then under option put hAxis.showTextEvery and under value put 1?

  9. Hi Mr. Alex,

    I have multiple list and create the charts in sigle page and created the date filter. All the charts are working absolutely fine except that all the slave charts filter date displayed the master chart date filter.

    Below is the var that I insert in the CEWP.

    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(“{7F409EBB-DC20-48C3-AA27-242EA7E2D844}”,”Date”,”{EFD5EF9F-A8F2-4DD8-BED1-B5FDFAFD334A}”,”Date”,”{CBE9934D-07A1-4E3D-B046-CF9C6AA2217D}”,”Date”,”{3DE38E73-F09F-40E7-A580-835513FBB638}”,”Date”);

    Is it possible to use indipendent filter for each of the charts in single page?

    Thank you so much.

  10. Hi,
    The function “spjs_getFilterValue” takes only two arguments.

    To pull in multiple filters you must call the function multiple times like this:

    var myCustomFilterOptions1 = spjs_getFilterValue(“{7F409EBB-DC20-48C3-AA27-242EA7E2D844}”,”Date”);

var myCustomFilterOptions2 = spjs_getFilterValue(“{EFD5EF9F-A8F2-4DD8-BED1-B5FDFAFD334A}”,”Date”);

    Alexander

  12. I get it. just insert “b.push({‘f’:’All Items’,’v’:’*’});” before “$.each(res.items,function(i,item){” in spjs_getFilterValue function. Thanks Alex

  13. I have requirement like Timer job in sharepoint .weekly once i have to trigger a email to user using javscript/js.But i have completed the requirment when the button click eail will be send to user.But this is manual work.i need automated work like every wednesday can we call the function using javscript?if it is possible can you help on this.?

    1. Hi,
      Unfortunately JavaScript cannot do this as it is client side code (living in the browser). What you can do is to use a workflow that is pausing for 14 days, and then triggers an email.

      It can be a bit tricky to have this reset to send again in 14 days, but you can use a list set with a “send date” and add one line in this list for every 14 days. Then set up the workflow to pause until this date and send the email.

      Hope this helps,
      Alexander

  14. Hey, How about cascading filters. Is there a way to set up multiple dropdowns so can filter by one or two values.

    I have location and department and I would like to be able to do filter by either one or both.

    It is possible and can you point me to details on how?

    thanks again

    1. Sorry, but cascading dropdowns with filter values are not supported, but you can have multiple parallel filter dropdowns. See the description in the filter area in the configuration for the chart for details on how to set it up.

      Alexander

      1. perfect thanks! so now I am trying to use the myCustomFilterOptions, but I keep getting an error
        SyntaxError: Expected ‘)’
        ******************************
        If you are using variables in the filter text area….

        I have allowEval set to true.
        I am trying to run 2 filters. If I manually enter the options, they work. not when I switch to the myCustomFilterOptions it fails. any suggestions?

      2. Sorry again for wasting your time. I got it. too many braces once I removed the manual options.

        Thanks million

  15. new question today. What is the easiest way to filter by year? I already have two filters, neither are date filters. I want to display two charts, current year and last year. how can I get the data in the charts to always show current year and last year?
    I thought about filtered views but that will not allow me to filter by current or last year. already having to filters in the custom caml I wasn’t sure how to proceed.
    any suggestions?

      1. I’m glad you figured it out. I have long had plans to get the v5.x version out – and properly document all the features, but have been so busy with DFFS that I simply haven’t had the time.

        Alexander

Leave a Reply

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