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
{
      get
       {
           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 - http://www.codeplex.com/sharepointextensions . 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.

Tags van Technorati: sharepoint+2007,sharepoint,moss+2007,moss,usage+analysis,development,csharp,microsoft,codeplex