Gavin’s Digital Diner

In previous versions of SharePoint, I was often frustrated with how difficult it was to do things I thought should be simple. It was probably my ignorance, or it could have been SharePoint’s obscurity and overall lack of good documentation, but it seemed a herculean task to simply filter information by a dynamic variable like “current user.”

The trouble was even though it had a setting for “current user” buried deep in some of the “filter” options; it returned the information in the form “Domain\LOGON_USER” or the common name (Firstname Lastname). I never could figure out how to easily extract just the portions I needed and to use that portion in any of the off-the-shelf web parts or data views. As I said, perhaps it was just ignorance.

Instead, I ended up going the long way round and built out some special code to make a connectable web part that could be used to filter by username. It was a lot of work. And, of course, it broke in the transition from SPS2003 to MOSS 2007. Connectable web parts are great — but this was just too much work.

Consequently, in this version, I vowed to do something simpler. I knew it could be done; it was just a question of beating my head against it until I either:

A) beat myself senseless and forgot what I was doing, or

B) found the keys to the kingdom.

I suppose there was always option “C) Hire a consultant” but my experience with consultants, with a few notable exceptions, has not always been that satisfactory. Consultants seem to like to borrow your watch to tell you what time it is. The good ones, of course, then keep the watch.

The goal: simple, a stand-alone, self-filtering web part that displays data based upon the current user. If I did it right, the code should be reusable, or at least easy to duplicate.

I’m happy to report that I succeeded. Moreover, once I figured out where to do it, it was easy — and it opened up a whole passel of possibilities. (And I vowed to write it down so I wouldn’t forget it. I’m keeping my promise.)

About DVWPs:

Before I talk about “the how,” let me digress a minute to talk about “data views” or “data view web parts.” They’re the other half to this puzzle, and they’re real special, magical even. They are one of the more powerful SharePoint tools. Affectionately called DVWP’s, these little beasts do magic. And they’re right cute too. Like love, and like all magic, it’s kind of hard to explain unless you see it; even then, you’re not too sure how it works.

Please note: To play with DVWPs you need SharePoint Designer (MOSS 2007) or Visual Studio. The cost of admission is worth it. I use SharePoint Designer, but beware it is buggy as hell and seems to crash all the time. You get used to it, and learn not to do some things in some sequences. Such is life or Microsoft or both.

DVWP’s display data from databases such as SQL Server (or any ODBC-compliant data source) on a SharePoint page. In a nutshell, DVWPs give you a table of data, like a data grid, or a form. It’s that easy, by the way, to set up a web-front end to a database, for both viewing and editing.

If you’re looking to build some sort of dashboard, aggregating existing data, and displaying it dynamically on a web page, well, here is one easy way to do it.

Once set up, you can display data; you can shape it, mold it, and filter it. With the flick of a mouse, you can put it inside of handy dandy expanding and contracting headers, or sort it, or change the format or the display, based upon any of the values in the data itself. You can hide things or expose things, or change their color and format, based upon the values in the table. It’s just damn neat — and easy.

Here’s a simple sample (below), a DVWP showing “open” helpdesk support tickets dynamically pulled from our helpdesk system called TrackIT. Total Time to create: 2 minutes. I’ve redacted names to avoid tormenting the paranoid.

I warn you now, that while creating and tweaking DVWPs is easy as pie, the tough part is figuring out where to stop. All of a sudden, all that data that’s locked up in LOB databases can be easily displayed on a web page — without code, without anything other than (some) knowledge of the data structure and a connection to the SQL server.

As I mentioned, you need SharePoint Designer. From there, it’s easy. Just a few menu clicks, and some mousing around. I’m not going to cover them here, but, trust me, they’re phenomenally easy. Next stop, filtering. Buckle your seatbelts. The management is not responsible for headaches, eye strain, or slaps to the side of your head.

Before we go any further — and before you tell me that there are already ways to filter in MOSS — let me explain a bit more. You’re correct: built in to MOSS is a parameter called [Current User]. Unfortunately, for my purposes, it doesn’t work. It returns the current user in the form “First name Last name” (e.g., John Smith). We don’t use that. (D’oh) Where it’s appropriate, we tag data using the so-called USERNAME, in the form “JSMITH” (first initial concatenated with last name). It’s ubiquitous to all our databases. I know, because I made it so. Standards! I’m just a jeepster for standards. [You have to remember T-Rex for that to make any sense at all, not that it makes any sense even if you know the reference.]

So, I needed to work some magic — pretty simple magic if you have access to any sort of programming language — but I wanted to do this without opening the hood. How it’s done turns out to be simple, it’s done with XSLT, and I show you the code, and tell you where to put it. [I don't mean that the way it sounds.]

Personalizing a DVWP:

I’m going to use a real-life example here. I’m going to walk you thru creating a web part that displays data from our Grants Management system — specifically information about employee charitable giving, and the organization’s matching contribution. Our goal is a simple, stand-alone, portable web part that would display data filtered by the current user, in this case, me. What we want is a list of charitable gifts, sorted and grouped by year, showing the amount, the match, and the name of the organization.

What we’re not going to cover how to set up DVWPs or connections to a database. That’s pretty self explanitory.

The data is in the database, all we have to do it get it out, format it on the screen, and filter it by the Windows Server variable “LOGON_USER.” In the end, it should look like this: Again, I’ve redacted so you don’t see my gifts to folks like the Rosicrucians, the L5 Society, and the Center for Epistemological Inconsistencies and Eccentricities (I’m a charter member!).  Obviously, I’m an equal opportunity push-over.

Pretty simple really. You might note, I got a little fancy. I use some simple DVWP tricks put up a little red “new” flag if the gift date is less than a month old.

Here is how it’s done:

• Step 1:     Set up a connection to the database using the DVWP wizard.
• Step 2:    Select the table or view you want, and select the appropriate fields.
• Step 3:    Insert the data into an appropriate web part zone, and move the items around, until you get them the way you want them.
• Step 4:    Set up the groupings and headers and all that jazz.
• Step 4:    Set up the filter and sit back and admire your work.

By the way, while you’re working with a DVWP, I recommend you adjust a couple of settings right away. To get to the settings (and where you work the magic) click the little “menu” arrow thingy on the right side of the DVWP. Here’s a before and after shot, showing the task pane.

Accessing the Data View Tasks Menu Before	Accessing the Data View Tasks Menu After

I set the various defaults as follows:

1. Click the box labled “Show with sample data” — this will populate the fields with sample data. This is important, especially if you use a filter that has no current results.
2. Set the the Data View Preview to hide all filters. Otherwise, if you filter out all the results, you have no current results.
3. The “Sort and Group” option lets you pick sort fields, and select to group on a certain field of set of fields. This is also where you set the expanding/contracting header groupings.
4. Notice the menu item labeled “Parameters.” This is where we set up the dynamic variable that pulls in the LOGON_USER.
5. Finally, we’re going to tweak the first parameter, the one labled “Filter.” This is where the magic happens.

Now we’re ready for the filter.

First we need to set up a “parameter” — click the menu item on the “Common Tasks” menu (above) labeled “Parameters.” You should see the following window:

We’re going to set up a parameter called “LogonUser” with the following attributes:

• Name:     LogonUser
• Parameter Source:    Server Variable
• Server Variable name:    Logon_User

When you’re done, it should look like the screen above. What this gets us is a dynamic parameter we can use elsewhere in the DVWP. It contains the full Windows Logon_User string, in the form “Domain\Logon_User.”

Next we’re going to turn it into something we can use by stripping off the domain and the slash (\) and converting it all to upper case. We do this right in the filter statement.

As shown above, from the “Common Data View Tasks” list, click the “Filter” menu option. It’s the first one on the list. You should see the “Filter Criteria” dialog box. It’s probably blank, and looks much like this:

Note the little check box at the bottom of the window. This is where we add the filter magic. Check the box, and then click the button to edit the XSLT filtering. Ignore the birght yellow warning that says: ”Using XSLT Filtering can reduce performance.” This is just a typical “If-you-do-this-your-naughty-bits-might-just-fall-off” warning. I just ignored it. Nothing fell off yet.

Finally we get to the filter – shown below in all its glory! Yep, that’s it. That whole line of gobbledygook. I’ll explain what it does, working from the inside out. (And quit your squinting – I put the final formula below so you can just copy and paste it into your DVWP.)

Constructing the Filter (from the inside out):

In the examples below, I show variables and formula in blue, and the current value of that statement in reddish brown. We start first in the center of the gobbledygook, with the $LogonUser parameter.

$LogonUser = Domain\JSmith

In the center of the expression is the parameter we created, called LogonUser, only now it is prefaced by a “$,” indicating that it is a parameter. This evaluates as the string “Domain\JSmith”

(Substring-After(”Domain\JSmith”,”\”) = JSmith

Surrounding the LogonUser parameter is a function to extract a substring from the parameter, using the “\” as the starting place in the string. The function extracts all the text following the “\”. We now have a parameter with just the name, e.g., JSmith.

Translate(JSmith’),’abcdefghijklmnopqrstuvwxyz’,'ABCDEFGHIJKLMNOPQRSTUVWXYZ’) = JSMITH

Now that we have the logon_user name handy, we have to convert it to upper case. XSLT is really clumsy here. Instead of a simple “UCase” statement or some such, we have to use a “Translate” function to convert every occurrence of a lower case letter to an upper case letter. Luckily I only need to do this once, and now I’ll just cut and paste it.

@Staff_ID = JSMITH

Finally, we can use an “Equals” operator to compare the variable @Staff_ID (pulled from the data source, to the LogonUser parameter we’ve constructed (or deconstructed, as the case may be).

The full statement is shown below: (note I’ve broken the line into two for display purposes). The brackets and everything else is important.

[@Staff_ID = Translate(Substring-After($LogonUser,'\'), 'abcdefghijklmnopqrstuvwxyz','ABCDEFGHIJKLMNOPQRSTUVWXYZ')]

That’s it. If you run Gifts, for example, and use the same format for STAFFID, you should be able to take this stuff verbatim and drop it into any part you set up.

Trackback URI | Comments RSS