If you are using the Repository Module it may be useful to display the Categories and how many Downloads each Category has received.
Anyway here is how I did it:
1. Create a Stored Procedure to display the Top 10 Categories by number of Downloads:
CREATE PROCEDURE [dbo].[grmGetTopCategoriesByDownload] AS
BEGIN
DECLARE @xTotalDownloads int
SELECT @xTotalDownloads = sum(Downloads) FROM dbo.grmRepositoryObjects
SELECT TOP 10 [Categories].[Category] as Category,
sum([Objects].[Downloads]) as DownloadCount,
sum([Objects].[Downloads]) * 100 / @xTotalDownloads as Percentage
FROM
dbo.grmRepositoryCategories as Categories,
dbo.grmRepositoryObjects as Objects,
dbo.grmRepositoryObjectCategories as ObjectCategories
WHERE
[Categories].[ItemId] = [ObjectCategories].[CategoryID] and
[ObjectCategories].[ObjectID] = [Objects].[ItemID] and
[Categories].[Category] <> 'ALL'
GROUP BY
[Categories].[Category]
ORDER BY
DownloadCount DESC
END
GO
2. Execute this procedure
Query:
exec dbo.grmGetTopCategoriesByDownload
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>Category</td><td align=center>Visits</td><td align=right> %</td></tr>
Footer Group:
</table>
Detail Item:
<tr>
<td>[Category]</td><td align=center>[DownloadCount]</td><td align=right >[Percentage] %</td>
</tr>
Alternate Item:
<tr bgcolor=#c0c0c0 >
<td>[Category]</td><td align=center>[DownloadCount]</td><td align=right >[Percentage] %</td>
</tr>
The results can be seen on this Fun with DNN Statistics page of the DotNetNuke Websites Directory