How to Extend the DotNetNuke Repository with Attribute Statistics

If you are using the Attributes feature of the Repository module and your Repository is get sizeable it might be useful to display some statistics on those attributes.

Anyway here is how I extended the Repository Module with Statistics for Attributes:

1. Create a Stored Procedure to display the statistics for one Attribute

CREATE PROCEDURE dbo.grmGetAttributeStatistics
(
        @AttributeName nvarchar(50)
)
AS
BEGIN

 DECLARE @xTotalObjects int

 SELECT @xTotalObjects = count(*) FROM dbo.grmRepositoryObjects

 SELECT [AttributeValues].[ValueName] as AttributeValue,
   count([ObjectValues].[ObjectID]) as AttributeCount,
   count([ObjectValues].[ObjectID]) * 100 / @xTotalObjects as Percentage

 FROM
  dbo.grmRepositoryAttributes as Attributes,
  dbo.grmRepositoryAttributeValues as AttributeValues,
  dbo.grmRepositoryObjectValues as ObjectValues

 WHERE
  [Attributes].[AttributeName] = @AttributeName and
  [Attributes].[ItemID] = [AttributeValues].[AttributeID] and
  [AttributeValues].[ItemID] = [ObjectValues].[ValueID]

 GROUP BY
  [AttributeValues].[ValueName]

 ORDER BY
  AttributeCount DESC

END
GO

2. Execute this procedure with the name of one of your attributes.

Query:

exec dbo.grmGetAttributeStatistics @AttributeName = 'Modules'

3. Format the Result to your liking. I was using the ListX module from www.bi4ce.com
and this is my setup for the formatting of the results in the ListX module:

Header Group:
<table width=100% >
<tr bgcolor= #5ebeff ><td>Website Location</td><td align=center>websites</td><td align=right> %</td></tr>

Footer Group:
</table>

Detail Item:
<tr>
 <td>[AttributeValue]</td><td align=center>[AttributeCount]</td><td align=right >[Percentage] %</td>
</tr>

Alternate Item:
<tr bgcolor=#c0c0c0 >
 <td>[AttributeValue]</td><td align=center>[AttributeCount]</td><td align=right >[Percentage] %</td>
</tr>

The results can be seen on this Fun with DNN Statistics page for the DotNetNuke Websites Directory