Category Archives: Filter

Charting for SharePoint using Google Visualization API: Update 05.06.2011


I have posted a new version of the “Charting for SharePoint using Google Visualization API” – v2.9.0. The changes are described here, but the background story and the code is found in original article. This post describes the new features in v2.9.0.

You should read the original post found here, the update for v2.8 posted here and the update for v2.8.5 posted here.


Changes from v2.8.5:

  1. Changed the behavior of “Average” to divide the grand total on the number of columns actually containing a value. This means any columns being blank or “null” will not be considered into the “total column count”. Thanks to Ayodele Ayodeji for pointing this out to me.
  2. Changed the “Chart option parser” to use eval on the Chart Options. YES i know it’s not the smartest thing to do, but as the Google Visualization API changes, my crude “option parser” failed to swallow some of the object literation formatted options.
  3. Added new chart types: Combo Chart and Candlestick Chart
  4. Added support for PatternFormat
  5. Pulled the variables: “showFilterInfo”, “showItemCount”, “itemCountPrefix”, “itemCountSuffix”, “labelIfEmpty”, introduced in v.2.8.5 in from the CEWP code and into the Chart configuration. This update should override the CEWP settings and these obsolete settings should not interfere.
  6. Added a few minor GUI tweaks like auto open the configuration for a newly added chart – no more “Table has no columns” error. Changed some of the error message. And a few other small fixes.
  7. Changed the solution to check for any missing columns in the configuration list and add them as needed. No more need to manually add columns to the configuration list when updating from a previous version!
  8. I have changed the code for the “save configuration function” as jQuery v1.6 introduced a new approach on DOM attributes and properties which affected the value returned by .attr(“checked”). This made saving “checked” checkboxes impossible when using jQuery v1.6.x.

NOTE: I have done one change in the way the formatters are targeted to a column. I originally went for a numbering of the columns starting with 1, but changed this to use 0 for the first column. This has to do with the columns being 0-indexed in the DataTable “behind the scenes” and this making things easier. To those of you using formatters now, you might end up with an error message, but it is just a matter of notching the “index” down by one.

If you use this solution, please consider donating a few dollars.

Charting for SharePoint using Google Visualization API: Update 27.03.2011

05.05.2011 I have released version 2.8.5. Read about it here


I have posted a new version of the “Charting for SharePoint using Google Visualization API” – v2.8. The changes are described here, but the background story and the code is found in original article. This post describes the new features.


1. Filter the chart using a choice column from the list

IMG
The dropdown is created from the choice options supplied in the list settings for that column. You set the filter in the GUI like this:
IMG
You must use “custom CAML” and select “Create a filter field above the chart using”. In the dropdown you will find all the single-choice columns in your list. Click on the text “Build CAML from selected filters” to create the appropriate CAML query.


2. Manually build the dropdown filter

If you want to make a dropdown filter for your chart, filtering by a text in a single line choice column you can create it like this. Click “Create a filter field above the chart using”, and select “manual filter setup”. You can then build the filter manually. See the description below the “filter options” textarea for formatting options for the dropdown.

When using a manual filter setup and using “Build CAML from selected filters”, you must change the “FieldInternalNameToMatch” with the proper FieldInternalName of your target field in the CAML. In this example, switch the text “FieldInternalNameToMatch” with “Region”.
IMG
IMG


3. Filter the chart using a value from the URL query string

Use {url:ParameterName} as placeholder in the CAML.
IMG
Before the list is filtered, the chart looks like this:
IMG
Pass the filter in the URL like this:
IMG
IMG


4. Filter the chart by filtering the “attached” view

This only applies if the chart is in a page with a single list view. It reads the filter provided by the list filter action and filters the chart accordingly. There are no real connection between the list and the chart – it merely reads the URL and looks for a filter value matching the placeholder in the CAML.

Use {list:FieldInternalName} as placeholder in the CAML.
IMG
Before the list is filtered, the chart looks like this:
IMG
Filter the list by the appropriate column to render the chart.
IMG
IMG


Important

Users upgrading from v2.7 must add three columns to the configuration list:

  • UseCustomQueryFilter: Yes/No column (boolean)
  • CustomQueryFilterField: Single line text
  • CustomQueryFilterChoices: Multi line plain text

For new users, this list is automatically created, but for users upgrading from older versions these fields must be added manually. In that case, compare your configuration list with this image:
IMG


Bugfixes:
If a column name had “&” or “/” in it, the chart “broke”. Thanks to Eric Guy for finding the bug.

I might very well have forgotten something in this walktrough so please post any comment or question below.

Alexander

Filter a calendar view based on user profile property

I got this request from Carel Schotborgh:

Using WSS 3.0

Not have been able to find it anywhere so, since it is possible to filter a list items (in all event/task/etc view) based on membership in SharePoint group (By Alexander). I would like to request the possibility to filter an Agenda View on a single line text, based on a field of the current user information field (for instance department). I import data into SharePoint list what contains names of people. So users do not create the list items and there are multiple columns with names like Judge1 Judge2 Lawyer etc. As you all know you can’t use [ME] value in a single line text. This can only be used with columns: Created by, Modified by, Assigned to and People picking data. Problem with agenda view is also that it only displays 1 field (in general description of appointment for instance).

What do I want?
Filter (a) specific column(s) on the basis of the current user that is logged on (department field for example) in an agenda view.

The agenda should only show items that match the same text as entered in the department field of the user information field of the user that is currently logged on.

In my case IF Judge1 = Mr. J. Jenkins display it OR IF Judge2 = Mr. J. Jenkins display it. The OR statement is very important since SharePoint also filters with AND statement. So I would like to filter multiple columns with department field (any other also ok, as long I can fill in a value that is connected to the current user).

I already managed to create something that filters all event/tasks/etc list view, searching all the columns based upon the current user department field with or statement idea, but not specific columns. Also in agenda view my code and Alexanders code will not work.

If it is not possible to do this on each specific column it is also ok to search all of the columns since the names in my list are unique for each column. Other columns do not contain their names in such a way anyway. I’m already using my script for the not agenda view and works perfectly except user with slow internet will see the entire list my filter starts working.

Excuse me for long text, but it is complex. Much appreciated any help, suggestion or solution.


Here is one suggestion on how this can be done. It pulls the “Department” from the user profile and searches for a match in one of the fields specified in the script (in the variable “arrOfFieldInternalNames”).

This solution does:

  • Filters a calendar view based on a text retrieved from the current users profile (from People and Groups)
  • Filters with an “OR” statement looking for a match in multiple text fields
  • Handles “single day items” and items spanning over multiple days.
  • Hide the calendar view until it has been filtered

This solution doesn’t:

  • If multiple elements are located in one day, the standard calendar view will display a “x more items” link to expand all items that day. My filter solution will filter the items, but may display a link saying there are “3 more items”, but when clicking the link there may no items to display.

The solution
To keep a nice even background when removing items spanning over multiple days, i had to change the background for the “not in this month items” to white.

Add this code to a CEWP below the calendar view:

<style type="text/css">
.ms-cal-nodataMid,.ms-cal-nodataBtm2{
	background-color:#FFFFFF;
}
</style>
<script type="text/javascript" src="/test/English/Javascript/jquery-1.3.2.min.js"></script>
<script type="text/javascript" src="/test/English/Javascript/interaction.js"></script>
<script type="text/javascript" src="/test/English/Javascript/stringBuffer.js"></script>
<script type="text/javascript">

// Set variables
listGuid = 'FilterCalendarView'; // Use list Name or List Guid
listBaseUrl = L_Menu_BaseUrl;
userListGuid = "{570D772F-0EAB-45A8-8C54-9CCD4EC6A0AF}"; // The "People and Groups" list
userListBaseUrl = ''; // Modify if on a managed path
// The FieldInternalNames to search for information matching the selected user property
arrOfFieldInternalNames = ['Judge1','Judge2'];

// Hide calendar view until it is filtered
$(".ms-cal-gempty").hide();

// Get the UserInfo
var userInfoObj = getUserInfo();
var dep = userInfoObj.Department; // User profile property "Department"

// Find all "relevant" items
var myItems = getMyItemsID(dep,arrOfFieldInternalNames);
// Filter view
filterCalendarView(myItems);

function getMyItemsID(find,findInArr){
	wsBaseUrl = listBaseUrl + '/_vti_bin/';
	var query = "";
	$.each(findInArr,function(i,fin){
		query += "<Eq><FieldRef Name='"+fin+"' /><Value Type='Text'>"+find+"</Value></Eq>";
		if(i>0)query = "<Or>" + query + "</Or>";
	});
	query = "<Where>" + query + "</Where>";
	var arrToReturn = [];
	res = queryItems(listGuid,query,['ID']);
	$.each(res.items,function(i,item){
		arrToReturn.push(item.ID);
	});
	return arrToReturn;
}

function filterCalendarView(arrOfIDs){
	$(".ms-cal-gempty a[href*='DispForm.aspx?ID=']").each(function(){
		var currID = $(this).attr('href').match(/ID=(d+)/);
		if($.inArray(currID[1],arrOfIDs)==-1){
			// remove
			var thisParentTd = $(this).parents('table:first').parents('td:first');
			var colspan = thisParentTd.attr('colspan');
			if(colspan>1){		
				for(i=1;i<colspan;i++){				
					thisParentTd.after("<td class='ms-cal-workitem'> </td>");
				}
				thisParentTd.replaceWith("<td class='ms-cal-workitem'> </td>");
			}else{
				thisParentTd.html(' ');			
			}		
		}
	});
// Show calendar view after it has been filtered
$(".ms-cal-gempty").show();
}

function getUserInfo(UserId){
wsBaseUrl = userListBaseUrl + '/_vti_bin/';
var uiObj = {};

if(typeof(UserId)=="undefined" || UserId=='')UserId = _spUserId;

var arrOfFields = ['ID', 'Name', 'Title', 'EMail', 'Department', 'JobTitle', 'Notes', 'Picture',
'IsSiteAdmin', 'Created', 'Author', 'Modified', 'Editor', 'SipAddress', 'Deleted'];

var item = getItemById(userListGuid,UserId,arrOfFields);
    if(item != null){
	    for(i=0;i<arrOfFields.length;i++){
	    	if(item[arrOfFields[i]]!=null){
	    		uiObj[arrOfFields[i]] = item[arrOfFields[i]];
	    	}else{
	    		uiObj[arrOfFields[i]] = '';
	    	}
	    }
       	return uiObj;
    }else{
        for(i=0;i<arrOfFields.length;i++){
    		uiObj[arrOfFields[i]] = "User with id " + UserId + " not found.";
    	}
		return uiObj;
	}
}	
</script>

Modify the parameters:

  • listGuid: The list name or list Guid of the list to filter.
  • listBaseUrl: The base URL of the list to filter.
  • userListGuid: The list Guid of the user list (People and Groups).
  • userListBaseUrl: The user list base URL – Most likely an empty string “”. If the site resides on a manage path this must be reflected.
  • arrOfFieldInternalNames: Array of the FieldInternalNames to search for a match on the search string-

The jQuery-library is found here. The sourcecode refers to jquery-1.3.2.min.js. If you use another version, please update the reference in the code.

The scripts “interaction.js” and “stringBuffer.js” is created by Erucy and published on CodePlex.

Read here how to add a CEWP to NewForm or EditForm, and how to get the guid for a list.

I do not think this is a “production environment solution” at this stage, but please test it and post back any comments or suggestions and i will try to adapt the script.

Regards
Alexander

Show active list filter above list view

I got this request:

Hi Alexander:
Another “is it possible?” question…

Is it possible to have a specific column’s filter choice appear in a CEWP that I would place above a list view?

Example: Filter the page by [Customer]
CWEP: would contain text + [Customer]

Thanks-

Charlie Epes


To pull the list filter from the URL and display it above the web part, put this code in a CEWP below the webpart (change the reference to jQuery if necessary):

<script type="text/javascript" src="/test/English/Javascript/jquery-1.3.2.min.js"></script>
<script type="text/javascript">
/* This code must be placed below the webpart it shall reflect */
// Get all querystring parameters
var queryString = getQueryParameters();

// Get all DisplayNames for the columnFilter
objFinDisp = {};
$(".ms-viewheadertr th").each(function(){
	objFinDisp[$(this).find('table:first').attr('name')] = $(this).find('table:first').attr('displayname');
});

// Loop trough and extract all querystring parameters that has "Filter" in it
myFilterStr = '';
$.each(queryString,function(param,val){
	if(param.indexOf('FilterField')==0 || param.indexOf('FilterValue')==0){
		// Switch the FieldInternalName for the DisplayName
		if(objFinDisp[val]!=undefined)val=objFinDisp[val];
		// Write the filter
		if(param.indexOf('FilterField')==0){
			myFilterStr += decodeURI(val)+" = ";
		}else{
			myFilterStr += decodeURI(val)+"<br>";
		}
	}
});

// Put filter string in the top of the page
$(".ms-bodyareaframe").prepend("<div>"+myFilterStr+"</div>");

// Function to separate each url search string parameters
function getQueryParameters(){
qObj = {};
var urlSearch = window.location.search;
	if(urlSearch.length>0){
		var qpart = urlSearch.substring(1).split('&');
		$.each(qpart,function(i,item){
			var splitAgain = item.split('=');
			qObj[splitAgain[0]] = splitAgain[1];
		});
	}
return qObj;
}
</script>

Alexander

Filter list view based on membership in SharePoint group

This script filters a list view based on membership in SharePoint groups. The filtering has nothing to do with item level security, it is merely a method for filtering by comparing a users group membership against a text field containing a group name. This column “VisibleTo” must contain the actual group names from the “People and Groups” list in SharePoint.

This method gives you the ability to filter the list view based on multiple criteria. If the user is member of multiple groups, the list will display all items relevant to any of the groups. You may also specify that elements where the column “VisibleTo” is empty will be displayed for all visitors. The “VisibleTo” column can be a multiple choice checkbox type column.

As always we start like this:
Create a document library to hold your scripts (or a folder on the root created in SharePoint Designer). In this example i have made a document library with a relative URL of “/test/English/Javascript” (a sub site named “test” with a sub site named “English” with a document library named “Javascript”):
IMG

The jQuery-library is found here. The pictures and the sourcecode refers to jquery-1.3.2.min. If you download another version, be sure to update the script reference in the sourcecode.

The scripts “interaction.js” and “stringBuffer.js” is created by Erucy and published on CodePlex.

The script “AccessUserProfileInWSS.js” is found here.

Note:

  • If used in grouped views, you must have the groups initially expanded, otherwise the elements are not rendered and cannot be filtered.
  • Only one level of grouping is currently supported.
  • Boxed view are not supported.
  • The filter does not apply in a datasheet view.

Add a CEWP below the list view webpart, and add this code:

<script type="text/javascript" src="../../Javascript/jquery-1.3.2.min.js"></script>
<script type="text/javascript" src="../../Javascript/interaction.js"></script>
<script type="text/javascript" src="../../Javascript/stringBuffer.js"></script>
<script type="text/javascript" src="../../Javascript/AccessUserProfileInWSS.js"></script>
<script type="text/javascript" src="../../Javascript/FilterListViewByGroupMembership.js"></script>
<script type="text/javascript">

// Find the user login name
var ui = getUserInfo(); 
var userLoginName = ui['Name'];
// Get the group collection for the current user
var ug = getGroupCollectionObjFromUser(userLoginName);
// Set the filter - if you want the results to include elements in a specific group by default, specify it in the array below
filterVal = [];
// Define the group - find the group ID in "People and Groups"
var groupIdTeam1 = 145;
var groupIdTeam2 = 146;
// Build the filter - add more if's to complete your filter
// Is the user member of the group with id 145?
if(ug[groupIdTeam1]!=undefined){
	filterVal.push(ug[groupIdTeam1]);
}
// Is the user member of the group with id 146?
if(ug[groupIdTeam2]!=undefined){
	filterVal.push(ug[groupIdTeam2]);
}

// Call the function
filterListViewByGroupMembership('VisibleTo',filterVal,true,true,true);
</script>

The sourcecode for the file “FilterListViewByGroupMembership.js”:

/* Filter list view by membership in SharePoint group - hide the column header and the filter column if specified
 * -----------------------------
 * Created by Alexander Bautz
 * LastMod 15.02.2010
 * -----------------------------
 * Include reference to:
	jquery - http://jquery.com
	interaction.js - http://spjslib.codeplex.com/
	stringBuffer.js - http://spjslib.codeplex.com/
	AccessUserProfileInWSS.js
	FilterListViewByGroupMembership.js
 * -----------------------------
   If used in grouped views, you must have the groups initially expanded, otherwise the elements are not rendered and cannot be filtered.
   Note: only one group level is currently supported.
*/

function filterListViewByGroupMembership(FieldInternalName,filterValueArr,includeEmpty,hideFilterCol,collapseGroupedViews){
filterOK = true;
	// Build object of all headings
	if(typeof(filterColIndex)=='undefined'){
		if(typeof(FieldInternalName)=='string'){
			intName = FieldInternalName;
			hideTD = hideFilterCol;
			fvArr = filterValueArr;
			inclEmpty = includeEmpty;
		}
		filterColIndex = '';
		$(".ms-viewheadertr th").each(function(){
			if($(this).find('table:first').attr('name')==intName){
				filterColIndex = $(this).attr('cellIndex');	
				displayName = $(this).find('table:first').attr('displayname');
				// If the parameter "hideFilterCol" is true, remove the column
				if(hideTD){
					$(this).remove()
				}
			}
		});
		
		if(filterColIndex==''){
			filterOK = false;
			var str = "<font color='red'>The column with FieldInternalName  "" + intName + "", must be in the view for the filter to work.</font>";
			$("td.ms-toolbar[width='99%']").append("<div id='hoverDelayInfo' class='ms-listheaderlabel' style='text-align:center;margin-top:-15px'>" + str + "</div>");
		}
	}
	
	// Apply filter
	if(filterOK){
		// Build view header text
		if(inclEmpty && fvArr.length==0){
			fvArr.push('');
			var fv = displayName + " = empty";
		}else if(inclEmpty && fvArr.length>0){
			fvArr.push('');
			var fv =  displayName + " = " + fvArr.join(' or ') + " or empty";
		}else if(!inclEmpty && fvArr.length==0){
			var fv = "No filtervalues found - all items hidden";
		}else if(!inclEmpty && fvArr.length>0){
			var fv =  displayName + " = " + fvArr.join(' or ');
		}		
		// Insert view header text
		if($("#filterInfo").length==0){
			$("td.ms-toolbar[width='99%']")
				.append("<div id='filterInfo' class='ms-listheaderlabel' style='text-align:center;margin-top:-15px'>" +
						"<span style='cursor:default'><img src='/_layouts/images/filter.gif'> " + fv + ".</span></div>");
		}

		// Find all rows and apply filter
		$("table.ms-listviewtable tbody:not([id^='aggr']) tr:has(td.ms-vb2) >td[cellIndex=" + filterColIndex + "]").each(function(){	
			var thisTd = $(this);
			// Build array from all values in "Visible to" - column
			var arrCurrTDtext = thisTd.text().split(';');
			var keeper = false;
			// Find all keepers
			$.each(arrCurrTDtext,function(i,val){
				var thisVal = $.trim(val);
				if($.inArray(thisVal,fvArr)>-1){
					keeper = true;
				}
			});	
			// Remove the ones that do not match
			if(!keeper){
				thisTd.parents('tr:first').remove();				
			}			
			// If the parameter "hideFilterCol" is true, remove the column			
			if(hideTD){
				$(this).remove()
			}
		});
		
		// Test whether the view is grouped twice and give alert
		if($("td.ms-gb2").length>0)alert("Sorry, but this script does not handle views grouped twice.");
		// Loop trough every tbody to remove empty and to update item count
		$("tbody[isloaded='true'][beenthere!='true']").each(function(){
			$(this).attr('beenthere',true)
			var gId = $(this).attr('id');
			var gIdPart = gId.substring(4,gId.length-1);
				// Number of elements remaining
				var trLength = $(this).find("td.ms-vh-group").length;				
				if(trLength==0){	
					// Remove the group heading and remaining elements for the group			
					$("#titl"+gIdPart).remove();
					$("#foot"+gIdPart+"_").remove();
					$(this).remove();
				}else{
					// Write the new count to the group heading
					$("#titl"+gIdPart).find('td.ms-gb span:last').html("‎(" + trLength + ")");
				}
		});
		
		// Collapse all groups - they must be expanded by default for the filter to apply
		if(collapseGroupedViews){
			$("td.ms-gb").each(function(){
				$(this).find('a:first').click();
			});
		}
		
		// Fix highlighting of every other row
		var classToFind = '';
		if($("table.ms-listviewtable").find('tr.ms-alternating').length>0){
			var classToFind = 'ms-alternating';
		}else if($("table.ms-listviewtable").find('tr.ms-alternatingstrong').length>0){
			var classToFind = 'ms-alternatingstrong';
		}
		if(classToFind!=''){
			$("table.ms-listviewtable tr:has(td.ms-vb2)").each(function(i,row){
				if(i%2==0){
					$(this).addClass(classToFind);
				}else{
					$(this).removeClass(classToFind);
				}
				
			});
		}
		
	}
}

/*
 * Function to get the groupCollectionfrocurrent user.
 * Requires the scripts interaction.js and stringBuffer.js to be loaded
*/
function getGroupCollectionObjFromUser(userLoginName){
	var result = {};
	innerPost(wsBaseUrl + 'usergroup.asmx', 
		'http://schemas.microsoft.com/sharepoint/soap/directory/GetGroupCollectionFromUser',
		'<GetGroupCollectionFromUser xmlns="http://schemas.microsoft.com/sharepoint/soap/directory/"><userLoginName>' + userLoginName + '</userLoginName></GetGroupCollectionFromUser>',
		function(data){		
			$('Group', data).each(function(idx, itemData){
				result[$(itemData).attr('ID')] = $(itemData).attr('Name');
			});
		});
	return result;
}

// Attaches a call to the function to the "expand grouped elements function" for it to function in grouped listview's
function ExpGroupRenderData(htmlToRender, groupName, isLoaded){
	var tbody=document.getElementById("tbod"+groupName+"_");
	var wrapDiv=document.createElement("DIV");
	wrapDiv.innerHTML="<TABLE><TBODY id="tbod"+groupName+"_" isLoaded=""+isLoaded+"">"+htmlToRender+"</TBODY></TABLE>";
	tbody.parentNode.replaceChild(wrapDiv.firstChild.firstChild,tbody);
javascriptFilterView();
}

Save as “FilterListViewByGroupMembership.js”, mind the file extension, and upload to the scriptlibrary as shown above.

Let me know if something is unclear.

Regards
Alexander