Data View Web Part Parameters Based on Server Variables
I wanted to get at the site name to use in a Data View Web Part’s (DVWP) filter. An example is where you have a site per project, with the project number being the site name and you want to based various filters on that value. To do this, you can set up a parameter within the DVWP based on the value of the IIS Server Variable "URL" and parse out the project number using XSL.
Here are the steps (you will be doing all of this within SharePoint Designer — I’ll assume that you know how to set up DVWPs already):
- Once you have your DVWP set up on your page, click on the Common Data View Tasks (the little > twiddle button in the upper right of the DWVP when you’ve highlighted it) and choose "Parameters…"
- Click the "New Parameter" button
- Give your parameter a name, and set the "Parameter Source" to "Server Variable"
- For "Server Variable Name", type the text value of the Server Variable. There’s no help here from Designer; you need to type the value exactly right or it won’t work. See the link below for your options. In this case, we’ll type "URL" (without the quotes). Click OK when you are finished.
- Now click on "Filter:". Under "Field Name", select the column that you’d like to filter on, the "Comparison" (in this case "Equals"), and choose the Parameter that you just created in the dropdown for "Value". Check the "Add XSLT Filtering" checkbox and click "Edit".
- Enter the XSL to trim the value that you want out of the URL. for example, if all of your projects are below http://servername/Projects/, then the XSL would look like [substring-after(substring-before($URL,’/default.aspx’), ‘/Projects/’)]
Now the filter that you wanted should be in place. XSL has its vagaries, so if you get an error at any step, use your old friend Ctrl-Z to step back and try again.
A full reference of the available IIS Server Variables is at this link:
http://msdn2.microsoft.com/en-us/library/ms524602.aspx
Note: I previously blogged on this method referencing a post from Maarten Eekels, but there are many more options, as outlined in the link above.
Very helpful post, well explained -thanks. I seem to have a problem though as no files are shown in the DVWP even though files meeting the criteria exist in the list. I checked the
– xsl output by using the xsl code in a field in the DVWP and it is correct
– the filtering by not using the parameter and xsl fileting, but just specifying the project name
It just doesn’t seem to work all combined. I have been have mysterious problems with SPD lately, so maybe this is another instance. In case it is not, maybe you would have some ideas of things to check (it all seems so straight forward). Cheers.
Katie:
I guess the only thought I have is to output all of the components you are using in your filtering to be sure they have the values you expect. It could be something as insignificant as a trailing slash (/) missing or something.
M.
Thanks Marc. The output does seem to be o.k. Maybe I will just have to wait for SPD to get a better mood.
The nice thing about computers is that they don’t really have moods. I’ve had stuff like this so many times, and it’s always been me. Take a walk and look at it later.
M.
Help me, Marc-wan-kenobi, you’re my only hope.
I have a DVWP pulling from a linked source which is a list/library combo. The primary source is the list, then I have a joined subview pulling in the documents which reference that list item.
I want to filter thw whole DVWP to only display items where Created by (for the list item) = Current user. I have tried EVERYTHING, after scouring posts by yourself and others trying to use server variable parameter to get this to work. No matter what I do, the DVWP gives the “no matching items found” message even though there are items in the list created by myself! I should be seeing these in the result, after the filter is applied. No dice.
Is there some trick to this type of filter when using joined subviews? Is the fact that the DV is bringing in data from 2 different sources (via the lined source) “confusing” the filter?
In the filter interface under Common Data View Tasks,I am going to “more fields” and selecting “Created by”, which returns “@Author” then “equals” then my parameter (“CurrUser”) which is the LOGON_USER server variable. Argh.
This is making me insane and I appreciate any help you may be able to give or advice on how I should be handling this. Thanks!
Use the force, Nancy! I’ll help if you’re in Westerville, OH. (I grew up in Warren.)
Just kidding. So basically, what you want to do it filter the list (as opposed to the Document Library). If the list items are filtered out, then the corresponding documents ought to also be filtered out. What is the “key” which is shared by the two lists? (Joins in a DVWP are sort of a fallacy; the XSL is just written such that the values need to match. If you’re a real database person, this can drive you batty, but it basically works.)
If you just want to filter by the current user being the original item creator (@Author = [Current User]), your selectcommand should look like this (note that the escaping has been removed here):
selectcommand="<View><Query><Where><Eq><FieldRef Name="Author"/><Value Type="Integer"><UserID/></Value></Eq></Where></Query></View>"M.
I AM in Westerville. Yay Ohio!
My key (common value) is called SiteID. Bear with me- So do I use the line you provided, just replacing “value” with my shared value (SiteID)?
What’s round on the ends and high in the middle? (My 5yo still thinks that’s funny.)
No, what you want to have happen is that each of the two DataSources (the list and the Document Library) should have their selectcommand CAML set so that you are pulling back as few items as possible which also meet your needs. When you first create a DVWP, before you add any filtering or anything, the selectcommand will look like this (this will be true of the two DataSources in an AggregateDataSource, too):
This just pulls back every item with all of the columns. Not terribly efficient, but most of the time, it’s just fine.
What you want to do is filter the list, so you want your selectcommand to look like this:
(This is the same CAML I showed before, just escaped.)
The image which you sent me in email will add the filtering on the rowset, more like this:
This type of filtering works, too, but it won’t limit what is returned from the list. This makes it less efficient, but again, it can work fine. It just doesn’t work well with @Author and $UserID because of the way the two values are stored. Note in the CAML I show above to do the filtering, the Author value Type is set to integer. UserID is also an integer, so this comparison works. You’d have to also convert the @Author value to an integer in the rowset filtering.
I’ve found that the best practice with Linked Data Sources is usually to set up the DVWP with just the primary DataSource, getting the columns and filtering right, and then copy in the other DataSource(s). This isn’t what a lot of the documentation or other people’s blogs would suggest, but it seems more reliable. That said, I always just go and write the XSL at this point, so your mileage may vary.
Go forth and prosper.
M.
That did it! Thank you so much!! Go Bucks!
Hi Marc,
You are supplying an answer to *exactly* what I am trying to do, right down to the project number on the project sites. I was so hopeful, but when I tried your solution I get the immediate error in SharePoint Designer: “Failed setting processor stylesheet:….: A reference to variable or parameter ‘URL’ cannot be resolved. The variable or parameter may not be defined, or it may not be in scope.” Is it possible that I don’t have access to URL server variable?
Thanks!
Jillian
You definitely should have access to the URL server variable. It’s possible that you’ve typed something wrong or that I missed a step somewhere above.
Switch to Split View in SharePoint Designer (always the view I work in) and find the ParameterBindings section in the DVWP. If you followed the steps above, you should have a line in that section that looks like this:
also, below the xsl:stylesheet line (usually a few lines down), you should see this:
My guess is that one or both of those peices have a typo or something.
M.
I still can’t get it. I named my variable “PageTitle”. So I would expect that the line in my source code should look like this:
(and it does).
Does that also mean that my XSL in the filter dialog should read:
[substring-after(substring-before($PageTitle,’/default.aspx’), ‘/Projects/’)] ?
Initially I had used $URL in the expression above, and that’s when I got the error saying there was no such variable — I imagine that you named your parameter URL (same as the ServerVariable)?
Unfortunately, now I don’t get an error, but I get “There are no items to show in this view”.
What I can’t seem to understand is the sequence. I imagine my parameter PageTitle get sthe value of the URL server variable first. Then the XSL filter gets applied to the PageTitle and I end up with a substring of the URL. But then what happens? Is that URL substring then stored back in the PageTitle parameter, before PageTitle is compared against the entries in the column I’m trying to filter on?
Jillian:
I’m still not sure what’s going on for you. You might want to deconstruct the filter piece and just debug it instead of trying to take my advice verbatim. There maybe be something I’m not seeing in what you’re posting.
M.
Hi Mark,
Is there a CAMLVariable / Server variable that we can use in DVWP to get hold of a SharePoint Group name ?
Thanks
Astatke:
I guess it depends on what group you are looking for. Is it a group that has something to do with the current user? A list? A site?
M.
Marc – thanks for the prompt reply.
This is something to do with getting the current user group name. As we can use $UserID for getting the current user, I want to catch the current user group name so that I can use it in my logic.
BTW – sorry for misspelling your name.
Astatke
There isn’t a ‘current user group’, really. The current user can belong to groups, though. Can you explain more about what you are trying to do?
M.
Great Article. However, I would like to add followings if the suggested filter does not work for anyone.
I tried the filter method just like Marc suggested and it was not working. After tried it for 2 days then I came to learn that I can do following and it worked like magic.
Add a variable so it can be accesible through out.
I modified the following line and added @Dept=$myDept with proper syntax.
Wala….. following link gives you what all ddwrt namespace has to offer by default (like page url, url, functions, etc.)
http://msdn.microsoft.com/en-us/library/dd583143(office.11).aspx
Thanks
Shahin Mridha
Shahin:
I’m not sure what you mean, but it may be because some code you posted didn’t make it through from WordPress.
M.
Hi,
I’ve gotten everything to work, but the actual filtering.
I’m at the point where you edit the XSLT Filtering.
I’m entering this
[substring-after(substring-before($URL,’/collateral_tracker.aspx’), ‘/SitePages/’)]
But whenever I hit OK, it never keeps it. If I go back to Edit, the area is blank again.
Does that look right?
This looks right, but it’s hard to know exactly without seeing some more of the surrounding code. It’s hard to post code here in WordPress, so why don’t you create a question over at EndUserSharePoint.com‘s SharePoint Designer and the Data View Web Part forum at Stump the Panel and we can work it through there.
M.