Hi Bev,


It is not possible to use a simple Order by expression since we cannot know what time period you want and some more details.

You can use a repeater with custom query and access the web analytics tables to get the data. The web analytics must be enabled and working.

Please see the structure of database tables: Web analytics database tables

You can see the built-in queries in CMSDesk -> Tools -> Reporting -> select Web analytics reporting folder and one of reports -> General -> and see the queries for an inspiration.

A sample query can be:

SELECT DocumentNamePath, NodeName, NodeAliasPath, articleTitle, datestamp, smallDescription, SUM(HitsCount) FROM 
Analytics_Statistics, Analytics_DayHits, View_SDay_Articles_Joined 
WHERE (StatisticsSiteID = 1) AND (StatisticsCode='pageviews') AND 
(StatisticsID = HitsStatisticsID) AND (StatisticsObjectID = NodeID) 
AND (StatisticsObjectCulture = DocumentCulture) 
AND (HitsStartTime >= DATEADD(dd,-1, GETDATE())) 
 
GROUP BY DocumentNamePath, NodeName, NodeAliasPath, articleTitle, datestamp, smallDescription 
ORDER BY SUM(HitsCount) DESC 

You can also create a custom functionality that you will add +1 to a custom field of documents by code when a page is loaded and then you can use a simple Order by condition about this field. You would also need to handle deletion of old data there.


Best regards,
Helena Grulichova

I've implemented this functionality on nearly all of the websites I have done.

Instead of using the web analytics, I created a couple of new tables in the database (I did not use a customtable, but you could if you wanted to) that store this data

Table 1 stores individual hit information with these columns:

NodeGuid, ImpressionDateTime

Table 2 stores 1 row for each NodeGuid with a running count of views. This table was added for performance reasons so that I can query the total number of views for a document without running an expensive Count() aggregation.
Table 2 has these columns:

NodeGuid, ViewCount

I also added a trigger int he database on Table 1 that inserts/updates the records in Table 2 on Insert.

Now comes the part that puts data in there.

Because I didn't want search engine crawler hits to be included, I created a web part that uses Javascript and an Ajax webservice call to a function that I have in a webservice that I added to the project. (you can use an ashx handler instead if you want). The webpart just ads some javascript that makes a call to the web service on page load and passes the webservice the NodeGuid of CMSContext.CurrentDocument.

You don't have to do that if you don't want to, you can just make a non-visible web part that inserts a record into Table 1 any time a page is loaded.


I then have a View with this query called View_Popularity. This query calculates the popularity based on how many views per day the document has:

Select ViewCount/DaysOld as Popularity, NodeID from(
Select Case When DATEDIFF(day,T.DocumentCreatedWhen,GetDate()) = 0 then 1 else DATEDIFF(day,T.DocumentCreatedWhen,GetDate()) END as DaysOld, 
Case When (C.ViewCount = 0 OR C.ViewCount IS null) then 1 else C.ViewCount END As ViewCount,T.* from View_CMS_Tree_Joined T
LEFT OUTER JOIN tPostViewCount C on C.PostID = T.NodeGUID
) PViews

Then you can have a where condition like this in your web part: (My example queries the top N most popular blog posts. Make sure you specify a value for Select top N documents or else you will get a SQL error because you can't order subqueries without a TOP N specified)

NodeID IN (Select ##TOPN## NodeID from 
View_Popularity WHERE ClassName in ('cms.blogpost')
ORDER BY Popularity DESC)

This works very well for me. I hope it also works well for you.