Compound Filtering in Data View Web Parts (DVWPs) with SharePoint Designer
When you build a Data View Web Part (DVWP) in SharePoint Designer, there are times when you might need compound filtering. By this, I mean something like:
Show me the items where (City=”Abington” or Approval Status=”Approved”) and Approval Status!=”Pending”
It’s sort of a silly example, but you should get the gist. Where you put the parentheses matters. The silly example below says the same thing, but I’ve moved the parentheses:
Show me the items where City=”Abington” or (Approval Status=”Approved” and Approval Status!=”Pending”)
This would give you a different result, so you need some way to tell SharePoint Designer where the “parentheses” should go.
When you go to create this type of filter in SharePoint Designer, it’s virtually impossible to figure out how to do it. The UI for this is horrid.
What you need to do is select the rows you’d like to group together by using Shift-Click on each row. The rows must also be adjacent. Then you can either right click on the highlighted rows and choose Group or click the Group button below.
Selecting the rows is the horrid UI part. One would have thought that this would have gotten better in SharePoint Designer 2010, but…no such luck.
Once you’ve done that, you’ll see a little blue bracket on the left, as below. The grouping is sort of like parentheses.
If you want to ungroup, you Right-Click again and choose Ungroup or highlight one of the rows and click the Ungroup button.
Changing the And to an Or or vice versa is a little bit easier to figure out, but it’s still horrid UI. You can click on the And, which will display a dropdown which allows you to change it to Or.
Depending on what selections you make, the filtering you set up this way will end up in the CAML for the DVWP, which is the most efficient way to filter. In some cases, your filtering will end up happening in the definition of the Rows variable, something like this:
<xsl:variable name="Rows" select="/dsQueryResponse/Rows/Row[@City = 'Abington']"/>
This is *usually* less efficient, but there are exceptions to everything; you still need to understand your information architecture to know what’s best. You can add this sort of filtering manually (I typed it myself in the example above) or check the “Add XSLT Filtering” box and then the “Edit” button.
In all of these cases, the UI leaves a lot to be desired, but you can get the job done if you persevere.
Great post as usual! I’ll bet this is something a lot of people don’t know about. It’s pretty cool how much more flexible your filtering can be using the grouping function you detailed above; I wish the web UI would allow grouping when creating filtered views.
I generally try to filter as much as possible using the CAML if the list has a lot of items, then further refine using XSLT filtering if needed.
U2U’s CAML Builder is also a great tool for compound filters. The UI is a little better than SPD’s, but you have to escape the CAML (I use http://htmlentities.net/ for that) before pasting it into the SPDataSource SelectCommand attribute.
Josh:
It certainly goes to show you that UI matters. I’m guessing a LOT of SharePoint Designer users have no idea that this type of filtering is even possible, and it’s entirely a UI issue.
M.
Do you know if it’s possible to have multiple levels of grouping (i.e. nested grouping) on a filter. It’s not possible via the UI but I wondered if it could be done using the XSLT filter (not having any experience with that and there being precious little clue in the interface (and nothing i can find on the web) I’m a bit lost.
I’ve inherited a page where the ‘customer’ wanted a query on a list of project milestones that was basically if the project field matches 1 of 4 values and the Actual Completed Date field is later than today, or if the project field matches one of that same list of values and the Actual Comple Date is null and the Expected completed date is later than today show the row. The guy who create the page created the filer as (I’ve shown grouping using {braces}):
I think, I haven’t been able to get a straight answer on this from him (other than, “It should work! why doesn’t it work?”), that he’s presuming that there’s an implicit grouping around that OR in the middle. The filter isn’t working as expected. I can’t think of a way to create a filter to do what the ‘customer’ wants without using nested grouping.
Ideally I’d want to be able to create the filter as:
Do you know if this is possible? Could you please point me to a reference that will help me?
Thank you
Stephen:
The answer almost has to be “yes”. XSL is (to me) a programming language and you can write code that accomplishes what you want.
There are different efficiencies based on whether you do the filtering in the CAML for the DVWP or on the definition of the Rows variable (or whatever other variable name you choose).
Date arithmetic in XSL is problematic, as it is in most places. I have several posts on it, and there are some good XSL templates available you can use to help.
M.
This is a huge time saver! Right click saves the day. Thanks.
On a side question on DVWP’s…
Is there a way to make dataviews globally updateable? Meaning after they are placed across 50 pages, how can I update it in one spot and it auto-populate across those 50 pages?
The reusable content list has this functionality… so why can’t dataviews?
Josey:
You can store the XSL centrally and include it in your DVWPs with xsl:import. this allows you to centrally manage it; any changes will be reflected immediately.
M.
With what? Your code cut out.
Just fixed that. If you search my blog, you’ll see information about xsl:import.
M.
I have a .webapart file for a dataview part from another developer and have to add a filter in the selectcommand .
How can I achieve it:
How can I recreate the webpart so that I can look into the property of the file.
On importing the file webpart into the page does not let me change the selectcommand.
Any help will be appreciated
HI Marc
I have the following scenario
In List A there are 5 candidates and in list B the status of only three candidate is stored along with their candidate Id , so I would like to show only those candidates of list A to whom there are entry in list B as status =’Saved’
I am using linked list data source for DVWP but not able to achieve the desired result even I added CAML query in selectcommand of data source but that didn’t help
Thanks
Abhi:
This will require some custom XSL, not CAML. Basically you’ll grab all of the items of interest from list A and the filter based on the values in list B in your XPath that defines the rowset.
M:
Marc
Thanks for ur replying
Can you please give me some links or some related source where I can find more about custom XSL
Thanks
Abhi
Abhi:
There’s lots of info here on my blog, and I have an eBook you can purchase at the top right of the page.
M.
You’re right about it being obscure, I’ve been tearing my hair out for months trying to get a compound filter to work, I couldn’t see the ‘group’ button for staring at it, let alone figure out that it meant ‘insert brackets’ kinda. Thanks so much for this.
Linda:
Sometimes the tiniest little things… I’m glad the post helped.
M.
Thank you Thank you Thank you. I’ve been looking for a simple solution like this for a long time. You have solved my problem.
Mary Jo
Mary Jo:
You’re welcome, you’re welcome, you’re welcome.
M.
Hi Marc,
One of my users has a list with several columns, one of which is Amount. He needs me to build a solution which a ‘sort of’ search feature for the amount. He wants to be able to enter, ‘=$80,000’ or ‘>$80,000 AND <$100,000' etc.
And, by default, he wants all records to appear and only limit records when the filter is applied.
Any suggestions?
Rob:
I’d probably do this with a DVWP and create two input fields to “feed” the values on the query string.
Alternatively, you could use script to filter live on the client side. One consideration would be how many items there are in the list and whether client side filtering would make sense with the volume of data.
M.
Marc,
Sorry for the delay replying. Thanks for the comment. Do you know of any resources you can point me to that will demo what you suggest.
Also, in pursuit of a different solution, my search led me to the spservices.codeplex site. I read the Home page and clicked on the FAQs link and it gave me a 404 error. Here’s the link: http://www.sympraxisconsulting.com/wikipage?title=FAQs&referringTitle=Home
Rob
Rob:
Thanks for the heads up on the FAQ link. I can’t even remember the last time I edited that page, so it’s probably been broken for a really long time.
I can’t think of a demo off the top of my head, but it you search here for “Query String”, you’ll find all sort of stuff.
M.
That is Excellent