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.
That helped, thanks
Hello Marc,
ASP.Net Dropdown list control
1. Two drop down list controls ,data source and data view webpart
2. I have configured data source to dropdown
3. Configured Data view parameters and filters.
Here I’m getting values like title(CRName) and CommentBy but here duplicates values ,How can I remove the duplicates in drop down.
Not able to post/attach the screen shots here
Thanks
Reddy
Hard to say exactly, Reddy. In a DVWP, I’d use ddwrt:NameChanged to identify when there’s a new value and only emit when it is.
M.
Hey Marc, I know this is an old thread but I have a real issue. I need to filter a list based on date range for 7 days. Each date is in a differnt column of the item and each column must fall with in the filtered date range. If I could use the filter in SPD it would be 14 lines. Example:
Mon_Date >= {$StartDate} and
Mon_Date = {$StartDate} and
Tue_Date <= {$EndDate} or
Each day is grouped. So there would be 7 groups containing 14 filters. Now I have tried to modify the select command as I read in one post but that still limits me to 10 filters as it ignors the last two when testing the page. I am looking for a way to accomplish this either using XSLT filtering (can't figure out how to group in XSLT) or some other creative way. Hoping you can help me out with sample code to get me started. Thanks in advance.
Scott:
First off, you may want to reconsider your architecture. Having 7 dates like Mon_Date, Tue_Date, etc. sounds like a bad idea.
That said, your filtering should look something like this:
<xsl:for-each select="$Rows"> <xsl:if test="(@Mon_Date >= $StartDate and @Mon_Dated >= $StartDate) or (@Tue_Date <= $EndDate and @Tue_Date <= $EndDate) or ... "> <xsl:call-template name="dvt_1.rowview"/> </xsl:if> </xsl:for-each>M.
Mark,
I have a people list with people belonging to different working groups. Each working group has its own page. So on the working group page i want to add a dataview webpart that displays only the people belonging to this working group. I don’t want to create individual filtered dvwp for each working group page. So I want to create one dvwp that displays all people but on each working group page I want the site publishers to be able to provide the filter parameter to display only the people in that working group. I don’t want a page filter for users to select but it should already be filtered for the user. Can this be done ? Can I be able to provide a blank filter field with the webpart where site publishers be able to edit the webpart and provide the value ? Thanks in advance.