Intelligence Among Us

I was recently presented with the following scenario:  As part of the creation of a SharePoint dashboard, my business users are interested in seeing the number of blog comments and their current status by blog.

Our blogs are contained as subsites of a site called Blog Central which reside in a site collection called “Public.”  The dashboard resides in a separate site collection.  Our site collections contain individual content databases.

As a former developer, my initial response is to find an out of the box solution wherever possible. 

1. A content query web part wouldn’t suffice as its scope is limited to the current site collection
2. Adding a new data connection in SP Designer to hook up to a dataview web part (DVWP) won’t work because the lists and libraries (not the web services) are also limited to the current site collection. These can be combined to recursively roll up content in the current site collection however.

I ended up querying the content database directly and presenting that through the DVWP.  So, in SP Designer I created a new Database Connection. 

Browsing to the content database for the Public site collection I wrote the following query in order to retrieve the correct data:

SELECT     Webs.Title,
                    CASE UserData.tp_ModerationStatus
                                   WHEN 0 THEN ‘Approved’ 
                                   WHEN 1 THEN ‘Rejected’ 
                                    WHEN 2 THEN ‘Pending’
                     END AS Status,                        
                      COUNT(*) AS Total 

FROM       AllLists INNER JOIN                       
                    Webs ON AllLists.tp_WebId = Webs.Id INNER JOIN 
                     UserData ON AllLists.tp_ID = UserData.tp_ListId 

WHERE     (AllLists.tp_Title = ‘comments’) AND
                      (Webs.FullUrl LIKE ‘%blogcentral%’) 

GROUP BY UserData.tp_ModerationStatus, Webs.Title 

ORDER BY Webs.Title, UserData.tp_ModerationStatus

I then conditionally formatted the DVWP to show different colours based on status and done.  

If there is a better way to do this, using CAML or some other method,  please let me know and I will share it.

Popularity: 4% [?]