SharePoint 2007 Usage Reports

For the past few weeks I was trying to develop some custom reports to track SharePoint site usage. The built-in reports show only the current statistics. But if someone wants to view old statistics there is no way that person can generate a report.

I started by going through the MOSS object model to find out some method where I can generate these reports easily. Then I found SPWeb.GetUsageData method. But it was giving enough problems and I decided to look for some other mean.

Finally I end up with a happy solution. It was so easy, you just connect to SharePoint shared services database and it has all the tables and views you need to generate the reports.

 

 

SharePoint Shared Services database

 

 

Available views

 

 

All the tables starting with ‘ANL’ contains site usage data.

 

I simply wrote few queries and developed required reports using SSRS. Or even you can use any reporting tool which is available in the market. Since I used SSRS I simply published it into a SharePoint site.

Following is a sample query I wrote to view the most popular documents in a SharePoint site.

 

SELECT ANLResource.DocName, COUNT_BIG(*) AS HitCount

FROM ANLResourceHits INNER JOIN

ANLWeb ON ANLResourceHits.WebGuid = ANLWeb.WebGuid INNER JOIN

ANLResource ON ANLResourceHits.ResourceId = ANLResource.ResourceId

WHERE (CHARINDEX(‘.aspx’, ANLResource.DocName) = 0) AND (CHARINDEX(‘.swf’,

ANLResource.DocName) = 0)

GROUP BY ANLResource.DocName

ORDER BY HitCount DESC

Advertisements

How to freeze a column in RS 2005

I got a request from a user to freeze a column in a report, just like in XL. This is because users are so used to XL reports they expect such features from other reporting systems.

Thanks toy RS2005 its just a click away to freeze a column in a matrix report.

Select the column you want to freeze and select edit group.

Then in the property window select option group header should be remain visible while scrolling.

That’s all. We are done.