1,688 articles and 12,612 comments as of Tuesday, September 7th, 2010

Monday, April 5, 2010

SharePoint: Performing Aggregate Counting for an Issues List

Guest Author: Chris Quick

Now that I’ve moved into full time SharePoint consulting, I come across requests that help me really understand the power that SharePoint provides without having to “write code”. Recently, a client turned to SharePoint to handle their help desk operations because they wanted to simplify the collection and tracking of issues. However, they still needed a report that their old system delivered that didn’t seem straight forward in SharePoint. The report needs to simply show the number of issues opened during the current month and the number of issues closed during the same month.

After white-boarding some ideas, it became evident that SharePoint could still deliver the report with a little modification to the Issues List and some very creative use of the Data View Web Part. Now the team can quickly show how many issues have been opened and handled during the current month, and it came at just the right time as the leadership wanted to evaluate the value that the help desk was bringing to the organization. When finished, this is the result:


Tracking Issues

First, a standard issue tracking list was created from the tracking lists available in SharePoint. This provided the basis for the entire help desk issue tracking system. The default issues list provides a lot of very useful information, but the solution still needed a way to track when the issue was first opened and a way to know when the issue was closed. The team decided that the created column would be the key to know when the issue was first opened. However, the day the issue was opened was not needed; the only thing needed is to know the month and year since the report being generated is broken down by year and month. This means that the date format should be YYYY/MM.

Calculating Open/Close Month and Year

This is a perfect candidate for calculated columns.  A new calculated column was created and simply called Open Date. The formula for the column was set to:

=Year([Created])&”/”&Right(Month([Created])+100,2)

The column is set to render as a single line of text. There are two functions in this formula that are used to get values out of the created date. The first one is Year() and it gets the year portion a provided date. The second one is Month() and it gets the month of provided date. The two values are combined by using  &”/”& that simply combines the two values with a ‘/’ between them. The final function is Right() and is used to insure that all months have two digits. If this was not used, for January you would get 2010/1 instead of 2010/01.

For simplicity, the modified column is used for the closed date; however this provided a little more troublesome as the client only wanted a value in the field if the issue status is set to Closed. With calculated columns, it is easier to handle this request than it might sound. In the simplest terms, a way is needed to determine if the status is closed and then provide a value.

We can reuse or formula from the Open Date column, but this should only be calculated when the issue is closed. SharePoint provides a way to do this using the following formula:

IF(Condition, True Value, False Value)

Since we need to know the status of the issue, our condition needs to test the current status of the issue.  The column that stores this information is Issue Status and it has any of the following values: Active, Resolved, Closed.  This means that our condition should be:

[Issue Status]="Closed"

The value we are testing needs to be contained within single quotes.  Any issue that is set to closed will show the True Value with any other status showing the False Value.

Now that the condition is set, a formula similar to the way Open Date is calculated is used for the True Value. This will tell SharePoint to place the YYYY/MM value in for items that are closed:

YEAR([Modified])&"/"& RIGHT(MONTH( [Modified])+100,2)

Finally, SharePoint needs to know what to show in the False Value. This is simply set to ‘N/A’. Our full formula looks like:

=IF([Issue Status]="Closed",YEAR([Modified])&"/"&MONTH([Modified]),"N/A")

Now when the issues list is opened, there are two new columns displaying information that can be used by the Data View Web Part:


Building a Data View Web Part

I always recommend creating a resources document library that will be used for SharePoint Designer customizations. This eliminates a side effect of modifying SharePoint’s pages called “ghosting”. If there is ever a problem with the site, the site administrator may reset the site back to the site definition. This means that any pages you’ve modified with SharePoint designer may get lost.

After opening up SharePoint Designer to the SharePoint site, start with a new blank ASPX page in the Resources document library. This can be done by right clicking on the Resources document library and choosing New > ASPX.


Provide a name (IssueCounts.aspx) for the page and you’re ready to begin.  Make sure you do not change the file extension of the page.


Open the page and get ready to get your hands dirty as you will be making modifications to the XSL that will be contained in the data view web part. There are a lot of great resources that Marc Anderson has been providing on this site, so I recommend reading his series on XSL.

In order to make a “portable” web part, begin by inserting a SharePoint Web Part Zone. This will allow you to export the web-part so it can be imported anywhere it is needed within the site. This is done by clicking on Insert > SharePoint Controls > Web Part Zone.


Open the Data Source Library task pane. This will show all of the available lists on the current SharePoint site.


Highlight the issues list and choose to show data:


This will bring up the Data Source Details task pane showing the structure of the data in the Issue List. It really doesn’t matter which column you choose, but select a single column and then click on Insert Selected Fields As… > Multiple Item View.


SharePoint designer will show the items in the issues list repeating in a table. Make sure that your data view does not have paging enabled by clicking on the widget next to the data view and choosing Paging.


Choose to display all items.


Modify the XSLT

Swap to the code view of the data view web part and locate the following line. It is near the top of the data view web part.

<xsl:template match="/">
<xsl:call-template name="dvt_1"/>
</xsl:template>
Change it to read as follows:
<xsl:template match="/">
<xsl:call-template name="IssueCounts"/>
</xsl:template>
Below these lines and add the following:
<xsl:template name="IssueCounts">
<xsl:variable name="Month">
<xsl:value-of select="substring(ddwrt:TodayIso(),6,2)"/>
</xsl:variable>
<xsl:variable name="Year">
<xsl:value-of select="substring(ddwrt:TodayIso(),1,4)"/>
</xsl:variable>
<xsl:variable name="FilterValue">
<xsl:value-of select="$Year"/>/<xsl:value-of select="$Month"/>
</xsl:variable>
<xsl:variable name="ClosedItems" select="/dsQueryResponse/Rows/Row[@Close_x0020_Date=$FilterValue]"/>
<xsl:variable name="OpenedItems" select="/dsQueryResponse/Rows/Row[@Open_x0020_Date=$FilterValue]"/>
<h3>This Month</h3>
<table>
<tr>
<th>Opened: </th>
<td><xsl:value-of select="count($OpenedItems)"/></td>
</tr>
<tr>
<th>Closed: </th>
<td><xsl:value-of select="count($ClosedItems)"/></td>
</tr>
</table>
</xsl:template>

At this point, if you go back to the display view you should see the following:


No styling has been applied to the data view, so this would be a good time to apply any styling desired. For brevity, these steps are omitted.

Save the page and preview the page in your browser by pressing F12. We now need to export the web-part so it can be placed on the homepage of the IT help desk site. Look for the web part control box arrow. It may be hard to spot since no styling has been applied to the page.


Choose to export the web part and save it to a location on your computer.


Import The DVWP

Navigate to the location you wish to display the Issue Counts. It must be within the current site. The best place for the client is the homepage of the site. Edit the page and choose to add a web part. Select the Advanced Web Part Options.


Select to Import a web part by clicking on Browse and choosing Import:


Browse for the file you exported and then upload it. It should show up as a web part that can be dragged into your SharePoint page.


Select the zone you wish to place the web part into and then drag it onto the page. Exit edit mode and now the issue counter will update in real-time.


Guest Author: Chris Quick

Christopher Quick is based in Dallas/Fort Worth as a Senior Technical Consultant with Tribridge. He has been an end-user and administrator of SharePoint since 2004 and began developing solutions for SharePoint in 2007.

 

Please Join the Discussion

3 Responses to “SharePoint: Performing Aggregate Counting for an Issues List”
  1. Don Kirkham says:

    Great post Chris. Very easy to follow for both experienced and inexperienced SharePoint users/devs. I especially like the tip about storing Designer changes in a library. Keep up the good work. (I’m off to create a Resources library!)

  2. Christophe says:

    For the record, counting can also be done in design view, without having to switch to code view, by using the grouping option.

  3. VirtualGeek says:

    Great blog post, thanks. I needed this for the same exact reason.

    There’s a minor error in the code for the formula for the closed date. It should read:

    =IF(Issue Status=”Closed”,YEAR(Modified)&”/”&RIGHT(MONTH(Modified)+100,2),”N/A”)

    Otherwise it displays the closed date with only a signle digit, and doesn’t count it toward the current month.

    Thanks again.


Notify me of comments to this article:


Speak and you will be heard.

We check comments hourly.
If you want a pic to show with your comment, go get a gravatar!