Will "the Mighty" Strohl

Adding Most Popular Entries View to Your DotNetNuke Blog

Most Popular Blog Entries One of the new features in the updates in my website is the “Most Popular Entries” area at the bottom of each page on my site.  It gets attention for a few different reasons.  First, everyone wants to know how I did it.  Next, people want to know what determines the most popular blog entries.  Finally, people want to know if I would blog about it.  I had always planned to, but since you asked…

First of all, I need to give a quick disclaimer.  I had long forgotten the power behind the XML and Reports Modules.  I used to use them all of the time, but I have been in development mode for about a year now, leaving little room for playing with these two modules like I used to.  Luckily, Joe Brinkman brought it all back to me with his recent visit to the Orlando DotNetNuke® Users Group (ODUG). 

Let’s answer the easiest question first.  The most popular entries are determined by the number of comments left on a blog.  So, if you want to “hack” the results in this module, leave a ton of useful comments on one of m blog entries.  (I say “useful” because I won’t approve any comments that appear to be spam or link builder comments.)  Unfortunately, I didn’t notice any existing queries that will feed me back the information that I need, so I wrote a custom query to be used with the Reports Module.

   1: SELECT TOP 5 
   2:     b.[BlogID],b.[EntryID],b.[Title],b.[Entry],b.[AddedDate]
   3:     ,b.[Published],b.[Description],b.[Copyright],b.[PermaLink], 
   4:     (SELECT COUNT(c.[CommentID]) FROM [dbo].[Blog_Comments] c WHERE c.[EntryID] = b.[EntryID]) [Comments]
   5: FROM [dbo].[Blog_Entries] b 
   6: WHERE b.[Published] = 1 
   7: ORDER BY [Comments] DESC

Hopefully, you’re still paying attention.  I say that because this query has a limitation that you may or may not care about.  It pulls the entries from the entire site, and not just from a specific blog.  You’ll have to adjust the query if that’s what you’re looking for.

Reports Module: Show XML Source Next, I chose to “View XML Source” in the module to show me the output that I’ll be dealing with.  This is an incredibly powerful feature in the Reports Module. as it allows me to write XSL against a known datasource format.  You simply click the link for it, and then begin using your favorite XML/XSL editor.

Here is an example of my XSL using the same methods, and Visual Studio as my editor.

   1: <?xml version="1.0"?>
   2: <xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
   3:     <xsl:template match="/DocumentElement">
   4:         <ul class="wns_news_list">
   5:             <xsl:for-each select="QueryResults[position() &lt; '6']">
   6:                 <li class="Normal">
   7:                     <a href="{PermaLink}">
   8:                         <xsl:value-of select="Title" disable-output-escaping="yes"/>
   9:                     </a>
  10:                 </li>
  11:             </xsl:for-each>
  12:         </ul>
  13:     </xsl:template>
  14: </xsl:stylesheet>

The XSL is used in conjunction with the datasource to render out an unordered list (UL) to the web browser.  You can notice that I make sure to include class names to make the UL manageable through CSS.  Which leads me to the next snippet!

   1: ul.wns_news_list li { 
   2:     border-bottom:1px dashed #FFFFFF; 
   3:     list-style-type:none; 
   4:     padding:2px 0 5px; 
   5: }

The CSS used with this function is actually very simple.  All we do is remove the bullet points, add a separator, and a bit of padding for aesthetics.

Finally, it’s time to put this all together!  Save the CSS in your skin, or the Portal.css.  If you choose to save it in the Portal.css, you can use the Site Settings to update it.

Now, add an instance of the Reports Module to the page.  Paste the T-SQL query from above into the Query field in the module settings.  Further down, you are given the opportunity to select XSL Transformation Visualizer.  When you do, you can upload or choose XSL to run against the query.  I would suggest saving the XSL from above into a local file, and upload it here.

Click the Update link to save your changes.  With the CSS, T-SQL, and query in place, you should see a pretty nice looking list of your most popular blogs!

blog comments powered by Disqus