Sunday, April 13, 2008

Extending Usage Analysis Reporting in MOSS 2007

You probably have seen the nicely looking Usage Analysis pages in MOSS which display different types of info using Reporting Services reports.

If you want to explore how these are implemented you might want to take a look at these two PDFs of ASP.NET Pro articles which were written by Matt Ranlett and Brendon Schwartz (For more info  about them check out their blogs at Atlanta .NET Regular Guys):

  • Part I: explains about the fundamentals about the Search Usage Analysis reporting (see p. 6 to 14)
  • Part II: shows how to implement a custom control to display top search terms. (see p. 38 to 43)

I started with the info in these articles and took it a little bit further.

There are a number of application pages (pages which you find in the _layouts directory) which show usage analysis data - for this example I will only focus on the one you find in the SSP about search queries, SPUsageSspSearchQueries.aspx .

You will probably notice that these pages implement classes which are found in the Microsoft.SharePoint.Portal.Analytics.UI namespace (The SDK states that the classes in the Microsoft.SharePoint.Portal.Analytics namespace are intended for internal use only, ... nothing found about the UI subnamespace though).

Let's take a look at the server controls on the Search Queries page (at SSP level):

  • QueriesByDayReportControl: queries over previous 30 days
  • QueriesByMonthReportControl: queries over previous 12 months
  • TopSiteCollectionsReportControl: top query origin site collections over previous 30 days
  • QueriesByScopeReportControl: queries per scope over previous 30 days
  • SspTopQueriesReportControl: top queries over previous 30 days

When you take a look at the code for the SspTopQueriesReportControl in the Microsoft.SharePoint.Portal.dll with Reflector, you will notice that it inherits from TopQueriesReportControl which in itself inherits from QueryTopLargeListReportControl. The QueryTopLargeListReportControl uses an RDLC file (compact Reporting Services report definition file - take a look at converting RDL and RDLC files) and the data is retrieved using a stored procedure, proc_MSS_QLog_TopQueries.

It is possible to build a component which derives from QueryTopLargeListReportControl and use this component to expose the data which is retrieved from the database - you will need to follow these steps to do this:

  • Create a new custom class which inherits from QueryTopLargeListReportControl
namespace SharePoint.Extensions.MOSSAnalytics
public class GetTopQueries : QueryTopLargeListReportControl


  • You need to implement the two properties RdlFileName (you can leave this blank) and StoredProcedureName.

protected override string RdlFileName
get { throw new NotImplementedException(); }

protected override string StoredProcedureName
get { return "proc_MSS_QLog_TopQueries"; }

  • Add 2 additional properties which are used by the control TitleText and StoredProcedureParameters. You can override some of the parameters in your own class such as the TopResultsCount which is default set to 300.

protected override string TitleText
get {return "GetTopQueriesControl";}

protected override Collection<SqlParameter> StoredProcedureParameters
Collection<SqlParameter> storedProcedureParameters = new Collection<SqlParameter>();
Guid guid = SPControl.GetContextSite(this.Context).ID;
storedProcedureParameters.Add(new SqlParameter("@siteGuid", guid));
storedProcedureParameters.Add(new SqlParameter("@isSspLevel", this.IsSspLevel));
storedProcedureParameters.Add(new SqlParameter("@topResultsCount", this.TopResultsCount));
return storedProcedureParameters;

  • Create a new method GetData which calls into the inherited method LoadReportData which returns a Datatable.

Now your custom component is created and ready to be used in your own webparts. The full source code is available on Codeplex in my newly created SharePoint Extensions project - . The project is created using Visual Studio 2008 and the structure is structured in such a fashion so that you can use WSPBuilder to build a SharePoint Solution file.


Matt Ranlett said...

Thanks for the great extension of the articles we wrote. This is exactly what we hoped to see - take our simple concept and run with it!

PS - Brendon managed to make it to the Summit if you're there. Look him up!

Anonymous said...

The links to the Part 1 and Part II pdfs do not work. Could you update the links?

jopx said...

Unfortunately, the links have been changed - now it is members only content

Anonymous said...

Thanks. Great article.
I've converted my website to sharepoint. Before i used Webtrends.
One of the biggest limitations MOSS 2007 Reporting has is only providing activity on the last 30 days.
Is there a way to have report on activity over one year?

Thank you.

jopx said...

For what type of information do you want to see a one year overview... Please prioritize - I might want to take this up in the SharePoint extensions on codeplex

Anonymous said...

I think the Common.cs file is missing from the source code on CodePlex.

Am I missing something?

Siddipet BLOGSSSSSSSSSS..... said...

hi can i get the site usage report( unique users accessed th site) for last 5 months /4 months like in sharepoint UI.. or setting for usage reports to show based on the duartion

Bradley Chetty said...

those links are still not visible eventhough i signed up .....
can u please help with this regard

Nick Kharchenko said...

MAPILab provides a very good SharePoint usage reporting solution: MAPILab Statistics for SharePoint. Detailed reports on visitors, documents, lists, search, etc. You can try its free trial version, or look through the online demo: