Click here to Skip to main content
Click here to Skip to main content
Go to top

SharePoint Most Viewed Content Web Part

, 25 Mar 2009
Rate this:
Please Sign up or sign in to vote.
Create a Web Part that displays the most viewed content

Introduction

I'm currently working for a cable company that wants to set up a public SharePoint site that will allow customers to view content that will help them set up their home systems (network, XBox, etc.). One great idea that a co-worker came up with is to have a Web Part displayed on the default page to show the most viewed content by our customers. This will allow our customers to have easy access to solutions for the most frequent problems that other customers have.

Background

If you know SharePoint well enough, then you probably already know about Usage Reports. The first thing you are going to need to do is setup Usage Reports for your site collection. A great walkthrough that will help you do this is available here. If you have used them, then you know they are very limited in what they can do. So, I dug down (reflected SharePoint DLLs) and found that the Usage Analysis Job that runs by default every night populates the ANL<suffix> tables that are located in your SharedServicesDB.

Anl_Tables.png

  • ANLResource - A row represents any given content item in SharePoint whether it be a PDF, ASPX page, etc. There will be no duplicates in this table.
  • ANLHit - A row represents a single hit to a specific resource made by a user.

The ANLResource table is great because it contains two very important fields, the WebGuid and SiteGuid. The will allow us to scope the query to the site that the user is currently browsing to. Also, another important field is the ANLHit.UserId because it allows us to scope our query to a specific user. Then, we'll be able to show only content that the user has clicked. Great!! Right?

Don't forget that the Usage Report runs nightly. So, keep in mind that even if you click a link a thousand times, no changes will be reflected in the Web Part until the job has run.

Installation

Hopefully, most of you already know how to install Web Parts, but if you need help, follow these steps:

  1. GAC both Mullivan.Shared.dll and Mullivan.SharePoint.WebParts.dll.
  2. Register Mullivan.SharePoint.WebParts.dll in the SharePoint Web Config.

    Go to C:\inetpub\wwwroot\wss\VirtualDirectories\<Port Number>\web.config. Add the following in between the SafeControls node:

    <SafeControl 
     Assembly="Mullivan.SharePoint.WebParts, Version=1.0.0.0, 
               Culture=neutral, PublicKeyToken=c37a514ec27d3057"
     Namespace="Mullivan.SharePoint.WebParts" TypeName="*" Safe="True" />
  3. Go to Site Settings -> Webparts -> click New on the menu.

    Scroll to the bottom and check Mullivan.SharePoint.WebParts.MostViewedWebPart, and then scroll back to the top and click Populate Gallery.

Done! You should see it in your Web Parts collection.

Configuration

Most_Viewed_Content_Config.png

Shown above is the property pane for this Web Part. I'll explain each field below:

  • User Only - If checked, it scopes the query to only display content that the current user has clicked.
  • Scope - Sets the query to either pull content from all the sites or just the site that the Web Part is located in.
  • Return Count - The max items that should be returned from the query.
  • Span - The amount of days that the query should go back from now and determine the hit count from.
  • Extensions - A comma delimited list of file extensions that limits the query to specific file types. So, if you want to display only PDF and Word docs, then you would use "pdf, doc, docx".

The Code

Are you ready?? I'm a bit mischievous and spend a lot of time reflecting DLLs. I'm not one who likes to spend time reading words in a book when I can just get right to it and see what the heck everything is doing. :P

So, I thought long and hard (in my world, that's 5 minutes) about how I'm going to create a connection to the Shared Services database. Well, using Reflection, I found that the TopPages control located on SPUsageSite.aspx uses an internal property called "AnalyticsSqlSession" on the PortalContext object. So, I just decided to use Reflection to dig that out and gain access to it. You may be thinking that's crazy, but it kept me from having to store some kind of connection string to that database.

private SqlDataReader GetSqlReader(SqlCommand cmd)
{
    PortalContext pContext = PortalApplication.GetContext();
    Type tContext = pContext.GetType();
    PropertyInfo pSqlSession = tContext.GetProperty("AnalyticsSqlSession", 
                 BindingFlags.NonPublic | BindingFlags.Instance);
    object sqlSession = pSqlSession.GetValue(pContext, null);

    Type tSqlSession = sqlSession.GetType();
    MethodInfo mExecute = tSqlSession.GetMethod("ExecuteReader", 
                          new Type[1] { typeof(SqlCommand) });
    return mExecute.Invoke(sqlSession, new object[1] { cmd }) as SqlDataReader;
}

So now, we are going to want to generate our query string. We want to make sure that values that may frequently change might be built into our query as SQL parameters. So, we are going to use the start date, user's name, and website GUID as SQL parameters. The TOP and file extensions are not going to be changed, so the query should cache and be reused in the SQL Server database.

private string GetQueryText(bool isUserQuery, int returnCount, 
               MostViewedScope scope, string[] extensions)
{
    string query = @"
        SELECT TOP {0} ANLResource.ResourceId AS ResourceId, 
              ANLResource.DocName AS DocName, 
              ANLResource.FullUrl AS FullUrl, 
              ANLResource.WebGuid AS WebGuid,
              COUNT_BIG(*) AS HitCount 
        FROM ANLHit 
        INNER JOIN ANLResource 
        ON ANLHit.ResourceId = ANLResource.ResourceId";
                    if (isUserQuery)
                    {
                        query += @"
                            INNER JOIN ANLUser
                            ON ANLHit.UserId = ANLUser.UserId";
                    }

    query += @"
        INNER JOIN ANLDay
        ON ANLHit.DayId = ANLDay.DayId

        WHERE 

        ANLDay.FullDate > @StartDate";

    if (scope == MostViewedScope.CurrentSite)
        query += "AND ANLResource.WebGuid = @WebGuid";
    else
        query += "AND ANLResource.SiteGuid = @SiteGuid";

    if (isUserQuery)
    {
        query += @"AND ANLUser.UserName = @UserName";
    }

    if (extensions != null && extensions.Length > 0)
    {
        query += @"AND (";
        for (int i = 0; i < extensions.Length; i++)
        {
            if (i != 0)
                query += " OR ";

            query += string.Format("(CHARINDEX('.{0}', ANLResource.DocName) > 0)", 
                                   extensions[i].Trim());
        }

        query += ") ";
    }

    query += @"
        GROUP BY ANLResource.ResourceId,
                 ANLResource.DocName,
                 ANLResource.FullUrl,
                 ANLResource.WebGuid
 
        ORDER BY HitCount DESC";

    return string.Format(query, returnCount); ;
}

I know that looks a little messy, but that's how it needs to be done. The following is an example of the query after it is generated:

SELECT TOP 10 ANLResource.ResourceId AS ResourceId, 
              ANLResource.DocName AS DocName, 
              ANLResource.FullUrl AS FullUrl, 
              ANLResource.WebGuid AS WebGuid,
              COUNT_BIG(*) AS HitCount 
FROM ANLHit 
INNER JOIN ANLResource 
ON ANLHit.ResourceId = ANLResource.ResourceId
INNER JOIN ANLUser
ON ANLHit.UserId = ANLUser.UserId
INNER JOIN ANLDay
ON ANLHit.DayId = ANLDay.DayId

WHERE 

ANLDay.FullDate > @StartDate
AND ANLResource.WebGuid = @WebGuid
AND ANLUser.UserName = @UserName
AND ((CHARINDEX('.aspx', ANLResource.DocName) > 0) 
OR (CHARINDEX('.pdf', ANLResource.DocName) > 0) 
OR (CHARINDEX('.docx', ANLResource.DocName) > 0) 
OR (CHARINDEX('.doc', ANLResource.DocName) > 0)) 

GROUP BY ANLResource.ResourceId,
         ANLResource.DocName,
         ANLResource.FullUrl,
         ANLResource.WebGuid
         
ORDER BY HitCount DESC

OK.. So now, let's execute our query by calling the method GetReportData().

private DataTable GetReportData()
{
    string currentUser = null;

    if (this.IsUserQuery
        && this.Page.User != null
        && this.Page.User.Identity != null)
        currentUser = this.Page.User.Identity.Name;

    DataTable table = null;
    using (SqlCommand command = new SqlCommand())
    {
        SqlDataReader reader = null;
        command.CommandText = GetQueryText(!string.IsNullOrEmpty(currentUser), 
                              this.ReturnCount, this.Scope, this.Extensions);
        command.CommandType = CommandType.Text;

        if (!string.IsNullOrEmpty(currentUser))
        {
            SqlParameter spUserName = 
              new SqlParameter("@UserName", SqlDbType.NVarChar, 50);
            spUserName.Value = currentUser;
            command.Parameters.Add(spUserName);
        }

        SqlParameter spScope = null;
        if (this.Scope == MostViewedScope.CurrentSite)
        {
            spScope = new SqlParameter("@WebGuid", SqlDbType.UniqueIdentifier);
            spScope.Value = SPControl.GetContextWeb(this.Context).ID;
        }
        else
        {
            spScope = new SqlParameter("@SiteGuid", SqlDbType.UniqueIdentifier);
            spScope.Value = SPControl.GetContextSite(this.Context).ID;
        }

        command.Parameters.Add(spScope);

        SqlParameter spStartDate = new SqlParameter("@StartDate", SqlDbType.DateTime);
        spStartDate.Value = DateTime.Today.Subtract(
                              TimeSpan.FromDays(Convert.ToDouble(this.SpanDays)));
        command.Parameters.Add(spStartDate);

        table = new DataTable();
        table.Locale = CultureInfo.InvariantCulture;
        reader = GetSqlReader(command);
        try
        {
            table.Load(reader);
            return table;
        }
        finally
        {
            if (reader != null)
            {
                reader.Dispose();
            }
        }
    }
}

Alright, so now, let's render our data inside of our part.

protected override void OnLoad(EventArgs e)
{
    try
    {
        _data = GetReportData();
    }
    catch (Exception ex)
    {
        _errorMessage = ex.ToString();
    }

    base.OnLoad(e);
}

protected override void Render(System.Web.UI.HtmlTextWriter writer)
{
    if (!string.IsNullOrEmpty(_errorMessage))
    {
        writer.Write(HttpUtility.HtmlEncode(_errorMessage).Replace("\n", "<br/>"));
        return;
    }

    StringBuilder sb = new StringBuilder();

    #region "Html"
    string htmlItem = @"
        <tr>
            <td class=""ms-propertysheet"" style=""padding-left:1px"">
                <table cellspacing=""0"" cellpadding=""0"" width=""100%"" border=""0"">
                    <tr>
                        <td valign=""top"" nowrap=""nowrap"" 
                                    class=""ms-descriptiontext"" 
                                    width=8px style=""padding-top:5px;"">
                            <IMG src=""{2}"" width=16px height=16px alt='' > 
                        </td>
                        <td valign=top class=""ms-descriptiontext"" 
                          style=""padding-top:7px;padding-left: 3px;"">
                            <a href=""{0}"" title=""{3}"">{1}</a>
                        </td>
                    </tr>
                </table>
            </td>
        </tr>
        ";

    string htmlTable = @"
        <table width=100% cellpadding=0 cellspacing=0 border=0>
            <tr>
                <td nowrap class=""ms-linksectionheader"" 
                           style=""padding: 4px;"" width=""100%"">
                    <H3 class=""ms-standardheader"">
                        {0}
                    </H3>
                </td>
            </tr>
            <tr>
                <td height=""1px"">
                    <IMG SRC=""/_layouts/images/blank.gif"" width=1 height=1 alt="""">
               </td>
            </tr>
            <tr>
                <td width=""100%"" style=""padding: 0px 4px 4px 4px;"" colspan=""2"">
                    <table cellpadding=""0"" cellspacing=""0"" border=""0"">
                        <tr>
                            <td valign=""top"" class=""ms-descriptiontext"" 
                                style=""padding-top:5px"">
                            </td>
                        </tr> 
                        {1}
                    </table>
                </td>
            </tr>
            <tr>
                <td height=""15px"">
                 <IMG SRC=""/_layouts/images/blank.gif"" 
                      width=1 height=15 alt="""">
                </td>
            </tr>
        </table>
        ";
    #endregion "Html"

    //DataRow dr1 = _data.NewRow();
    //dr1["DocName"] = "Pages/mypage1.aspx";
    //dr1["FullUrl"] = "http://sullyserver2008/sites/mullivan/Pages/mypage1.aspx";
    //dr1["HitCount"] = 2345L;
    //dr1["WebGuid"] = new Guid("{db7e891b-18c4-4c00-8c99-160ecbcee67f}");
    //_data.Rows.Add(dr1);
    foreach (DataRow dr in _data.Rows)
    {
        string docName = dr["DocName"].ToString();
        string fullUrl = dr["FullUrl"].ToString();
        long hitCount = (long)dr["HitCount"];
        string desc = string.Format("This file has been requested {0} " + 
                      "times in the past {1} days.", hitCount, this.SpanDays);
        Guid webGuid = (Guid)dr["WebGuid"];
        string extUrl = "/_layouts/images/icgen.gif";

        try
        {
            SPSite site = SPControl.GetContextSite(this.Context);
            using (SPWeb web = site.AllWebs[webGuid])
            {
                string relativeUrl = fullUrl;
                int idx = relativeUrl.IndexOf(web.ServerRelativeUrl,
                    StringComparison.InvariantCultureIgnoreCase);

                if (idx > -1)
                    relativeUrl = 
                       relativeUrl.Substring(idx, relativeUrl.Length - idx);

                SPListItem spListItem = web.GetListItem(relativeUrl);
                docName = spListItem.Title;

                object icon = spListItem["DocIcon"];
                if (icon != null)
                    extUrl = string.Format("/_layouts/images/ic{0}.gif", 
                                           Convert.ToString(icon));
            }
        }
        catch
        {
            //Item doesn't exist
            continue;
        }

        sb.Append(string.Format(htmlItem, fullUrl, docName, extUrl, desc));
    }

    writer.Write(string.Format(htmlTable, this.Title, sb.ToString()));
}

OK.. That's pretty much it. There is an Editor attached to this Web Part for configuration, but I'm not going to get into that. There are many blogs out there that will help understand how that works.

Points of Interest

There are two other Web Parts included in the project. The weather Web Part was taken from a blog made by the Mossman located here. The other is a Web Part that I blogged about yesterday. It's a really great Web Part that displays a navigation bar, check it out here.

Conclusion

Let me know what you think. If you have any ideas, then I'll be sure to take a look and see if I can provide an update. I hope you found this Web Part useful.

History

  • 15th January, 2009: Initial post
  • 19th March, 2009: Updated source code
  • 24th March, 2009: Updated source code

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

Sike Mullivan
Software Developer (Senior)
United States United States
I am a Sr SharePoint Developer in Saint Louis, MO. I've been developing professionally for about four years now. For three years I worked for a Document Imaging company that developed applications for Scanning, Indexing, Migrating, and Searching SharePoint (MOSS, WSS). I'm now working on a team for a Cable company that customizes their internal and external SharePoint implementations.

Comments and Discussions

 
QuestionHaving problem with SharePoint 2010 Pinmembercwalex30-Nov-12 17:44 
AnswerRe: Having problem with SharePoint 2010 Pinmemberalx910-Jan-13 10:36 
QuestionHow to make Scope changes Pinmembernareshveeragoni27-Apr-10 11:19 
AnswerRe: How to make Scope changes PinmemberSike Mullivan3-May-10 8:47 
Generalthe data base is empty PinmemberWiLLSTi26-Nov-09 3:27 
GeneralWeb part doesn show any pages PinmemberWiLLSTi22-Nov-09 23:09 
QuestionANL Tables PinmemberJeanna Bash7-Oct-09 7:58 
AnswerRe: ANL Tables PinmemberSike Mullivan7-Oct-09 8:08 
GeneralRe: ANL Tables Pinmembereaswari11-Feb-10 18:50 
GeneralWebPart doesn't work Pinmemberanngyta25-Aug-09 9:47 
GeneralRe: WebPart doesn't work Pinmembershambon24-May-11 22:32 
GeneralWeb Part Not Wrok when i set the Extensions whit a aspx Pinmembervictorporras17-Jun-09 6:15 
GeneralRe: Web Part Not Wrok when i set the Extensions whit a aspx PinmemberSike Mullivan17-Jun-09 6:52 
GeneralRe: Web Part Not Wrok when i set the Extensions whit a aspx Pinmembervictorporras17-Jun-09 7:10 
GeneralRe: Web Part Not Wrok when i set the Extensions whit a aspx PinmemberSike Mullivan17-Jun-09 8:32 
GeneralRe: Web Part Not Wrok when i set the Extensions whit a aspx Pinmembervictorporras18-Jun-09 4:46 
GeneralRe: Web Part Not Wrok when i set the Extensions whit a aspx PinmemberSike Mullivan18-Jun-09 7:28 
GeneralRe: Web Part Not Wrok when i set the Extensions whit a aspx Pinmembervictorporras18-Jun-09 11:31 
GeneralAudit report for webparts PinmemberS Sansanwal6-Apr-09 20:40 
GeneralRe: Audit report for webparts PinmemberSike Mullivan7-Apr-09 13:27 
Questionanonymous access Pinmemberroytje922-Mar-09 23:51 
AnswerRe: anonymous access PinmemberSike Mullivan23-Mar-09 3:47 
GeneralRe: anonymous access Pinmemberroytje923-Mar-09 4:53 
GeneralRe: anonymous access PinmemberSike Mullivan23-Mar-09 6:58 
GeneralRe: anonymous access Pinmemberroytje923-Mar-09 22:17 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Mobile
Web04 | 2.8.140916.1 | Last Updated 25 Mar 2009
Article Copyright 2009 by Sike Mullivan
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid