Display item counts in a Data View Web Part

In SharePoint 2010, you can now do inner and left joins in CAML queries. But, if you can manage that via SharePoint Designer I’ve yet to figure out how. (I haven’t given up yet – anyone have any great ideas?) Instead, in SharePoint Designer all of our “joins” happen via XSLT.  It’s not really a “join” at all. Marc Anderson explains all this really nicely. So, if we want to get a count of all the child items in a parent/child relationship, we need to modify the XSLT.

The good news is that it’s actually really easy.

The Set Up

In my example, I have 3 lists – Interview Questions, Program Area, and Skills/Assets. The idea is that we have a list of questions that might be used, and a list of the skills or assets they assess and Program Areas they’re related to. One interview question can be relevant for multiple skills or program areas, so we have a look up columns in the Interview Questions table that allows multiple values from the Skills/Assets list or the Program Area list. Then, on a page in our site, we want to provide links for each skill and program area that goes to a page that filters the Interview Questions by that skill or program area. I’ve already done the skills. We’ll do the program area together.

We want to get item counts for Program Areas

We want to get item counts for Program Areas

So, if you’re playing along, we’ll need 3 lists:

  1. Skills/Asset (1 column: Title)
  2. Program Area (1 column: Title)
  3. Interview Questions (3 columns: Title, Program Area, Skill/Asset)
Columns in the Interview Questions Table

Columns in the Interview Questions Table

Add some data to each of those tables. Be sure to have more skills and/or program areas than you use. (This helps with finding the empty template later.)

Now create a page. This can be a wiki page or a web part page, and it doesn’t really matter what it’s called. I did this right on my site’s home page.

Fire up SharePoint Designer. Before we get into editing the page, we first have to create a linked data source. Microsoft has general information about doing this. But, I created a quick video to show you exactly what to do for this tutorial:

The Solution

Now that we have a linked data source and a page, it’s time to use our data source  in a Data View Web Part (DVWP) on our page.

So, find your page and jump into edit mode. I always use split mode (code and design visible). The first step is to insert an empty Data View Web Part. Place your cursor where you want the DVWP. Click Insert -> Data View -> Empty Dataview.

Insert an Empty Data View Web Part

Insert an Empty Data View Web Part

Click the link that says, “Click here to select a data source.” and select the linked data source we created earlier.

Choose our Linked Datasource

Choose our Linked Data source

The Data Source Details pane on the right side will populate with the columns and data from our linked data source. Select the Title column from the Program Area data source and select “Insert Selected Fields as…” -> “Multiple Item View.”

Insert the Program Area as a Multiple Item View

Insert the Program Area as a Multiple Item View

By default, SPD adds all DVWP’s as tables. For our purpose, we don’t want a table. We want a bulleted list. So, click the Design Tab and choose the Bulleted List style. This is also the point at which you can sort and filter, if you need to do any of that. I’m sorting by Title. You may get a message to “Click to set the display text if no matching items are found.” Go ahead and click it and set it to “(0).”

Setting the Design to Bulleted List

Setting the Design to Bulleted List

Okay, so far we haven’t done anything too impressive. Now is when the fun starts. Position your cursor at the end of one of the Program Area titles, add a space, and then move over to the Data Source Details panel. We’re going to add our Interview Questions Data Source as a Joined Subview. It doesn’t matter which field you choose, as we’re going to delete all the field information anyway. I’m using the title, since it’s a required field. A dialog box will pop up asking you to select the fields on which to join. In our case, we want the Title field from the left row and the “Program_x0020_Area.” field from the right row. You can click “More Fields” to get a secondary dialog box that will show you the actual field values. That can be very helpful. (Note that sometimes SPD will give you a blank dialog box for the joined subview. That’s SPD looking ahead and realizing there are no matches. Try placing the cursor after an item in your main list that you know is used in the look up column.)

Join the Subview

Join the Subview

Now we should see a table inserted after each item in our bulleted list, that displays either the matching rows or our “no match” text. But, wait – nothing matches! That’s okay. We can fix that. If we were using a single select look up, the best way to fix that is to follow these directions. But, since we’re using a multiple select look up, we need to modify the filter on the XSLT.

Click into the code view and do a search for <xsl:variable name=”Rows” select=. You’re looking for the following line of code:

<xsl:variable name="Rows" select="../../../Interview_Questions/Rows/Row[@Program_x0020_Area.=$dvt_ParentRow/@Title]" />

This line of code tells the XSLT to match any rows where the Program_x0020_Area. field equals the parent’s Title field. We’ll never return any rows this way, because they’ll never match exactly. However, the Program_x0020_Area. field will CONTAIN the parent’s Title field. So, we need to change this from an “equals” operation to a “contains” operation. The XSLT contains operation syntax looks like this:

<xsl:variable name="Rows" select="../../../Interview_Questions/Rows/Row[contains(@Program_x0020_Area.,$dvt_ParentRow/@Title)]" />

Change that field and you should see your DVWP update to display a table of rows under each bullet point for which there are matches. We don’t want a table of rows, so let’s modify our XSLT some more. Right below where you found the select statement we just modified, you should find an xsl:choose statement. Like so:

<xsl:choose>
<xsl:when test="$dvt_IsEmpty">
<xsl:call-template name="dvt_2.empty" />
</xsl:when>
<xsl:otherwise>
<table border="0" width="100%" cellpadding="2" cellspacing="0">
<tr valign="top">
<xsl:if test="$dvt_2_automode = '1'" ddwrt:cf_ignore="1">
<th class="ms-vh" width="1%" nowrap="nowrap"></th>
</xsl:if>
<th class="ms-vh" nowrap="nowrap">Title</th>
</tr>
<xsl:call-template name="dvt_2.body">
<xsl:with-param name="Rows" select="$Rows" />
<xsl:with-param name="dvt_ParentRow" select="$dvt_ParentRow" />
</xsl:call-template>
</table>
</xsl:otherwise>
</xsl:choose>

(Note: your template may be named dvt_1. SharePoint Designer increments template names as DVWPs are added to the page. It’s always a good idea to rename these to something more descriptive, such as dvt_[listname].)

The first thing we want to do is remove the call-template to the dvt_2.empty template and replace it with a simple text output of “(0)”. Like so:

<xsl:choose>
<xsl:when test="$dvt_IsEmpty">
(0)
</xsl:when>
<xsl:otherwise>

Next, we want to highlight everything between the <otherwise> tags, and replace it with the the following:

(<xsl:value-of select="$dvt_RowCount" />)

The dvt_RowCount is a variable that SharePoint Designer sets based on the count of the rows returned from our modified select statement.

Finally, delete the rest of the dvt_2 templates. Your complete dvt_2 template should now look like this:

<xsl:template name="dvt_2">
<xsl:variable name="dvt_StyleName">Table</xsl:variable>
<xsl:variable name="dvt_ParentRow" select="current()" />
<xsl:variable name="Rows" select="../../../Interview_Questions/Rows/Row[contains(@Program_x0020_Area.,$dvt_ParentRow/@Title)]" />
<xsl:variable name="dvt_RowCount" select="count($Rows)" />
<xsl:variable name="dvt_IsEmpty" select="$dvt_RowCount = 0" />
<xsl:choose>
<xsl:when test="$dvt_IsEmpty">
(0)
</xsl:when>
<xsl:otherwise>
(<xsl:value-of select="$dvt_RowCount" />)
</xsl:otherwise>
</xsl:choose>
</xsl:template>

And, your rendered DVWP should look something like this:

XSLT Rendering Child Item Counts

XSLT Rendering Child Item Counts

I also added a link to each Program Area Title, which passes the title as a query string parameter to another page.

Add a Link and Pass the Title as a Query String Parameter

Add a Link and Pass the Title as a Query String Parameter

While this tutorial has a lot of steps, once you’ve done it once, you’ll see how easy it really is – 15 lines of XSLT to generate the item count of your joined subview!

Hope that helps you out. And if any one figures out that CAML join syntax in SPD, let me know.

About these ads
Leave a comment

11 Comments

  1. Gustavo

     /  November 6, 2012

    very good! helped me a lot here in Brazil … congratulations!

    Reply
  2. Shafaqat Ali

     /  January 3, 2013

    Hi

    I want to use some other variable in sub view xslt like $dvt_ParentRow/Path etc but it shows blank values, i can use only $dvt_ParentRow/@ID, how can i get value of other fields?

    Reply
    • Have you made sure that the other variable you’re trying to get actually exists? For instance, “path” is a shortcut name in SPD, but the underlying XSL node is @dirfileref (I think – without double-checking).

      Reply
      • Shafaqat Ali

         /  January 3, 2013

        Thank you for reply

        How can i get the underlying XSL nodes? Actually i am trying to get the URL of the list. I can see @Path variable name in sharepoint designer has the URL i need but when i try to display its value, it shows empty string. I have tried @dirfileref also but again shows blank string. If i use @Title it works well.

      • If you’re in Sharepoint Designer and you can see the node you want in the right hand-column (datasource) you can right click on it and get the xpath to that node. If it’s really the parent node you need, you’ll need to adjust the start of the node to reach the parent. But, the node itself should be the same.

      • Shafaqat Ali

         /  January 3, 2013

        Yes it worked now, thank you.
        another way to find out the underlying name is to hover the mouse on the value of the node in data source pane and it will show the xpath.

  3. Rich

     /  May 5, 2013

    Great Post! The only question I have now is… can you then sort the DVWP by the count of items in the sub view? looking around google – it doesnt look like you can?
    Rich

    Reply
    • Well, I played around a bit and I can’t get the xslt sort to work on a count of a joined node. I can get the counts, and in fact, this is probably a better way to do it than I did in the blog post. All you need to do to get the counts is to do this:


      <xsl:variable select="@Title" name="ThisTitle" /$gt;
      <xsl:value-of select="count(/dsQueryResponse/Presentation_Topics/Rows/Row[contains(@Program_x0020_Area., $ThisTitle)])" />

      But, if I try to use that in a sort, nothing happens.

      Probably the easiest way would be to use a table format and then apply a jQuery sort to the table based on your count column.

      Reply
      • Rich

         /  May 6, 2013

        Thanks Deanna – Jquery it is!

  1. Joining the Task List with Related Content in a DVWP « Blah-de-blah-blog
  2. Linked Data Source/Lists | Drift Bottle

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

%d bloggers like this: