Show SharePoint Content Ratings with a Calculated Column


Ratings in SharePoint allow users to mark content with a rating, which can then be used to display content by popularity for instance.

For more information on the ratings feature in SharePoint see this reference, http://office.microsoft.com/en-us/sharepoint-server-help/enable-users-to-rate-content-HA101791797.aspx.

Enabling Ratings

To enable ratings on your list or library go to the list settings page and choose Ratings Settings under General Settings.

enable ratings

The ratings columns “Rating (0-5)” and “Number of Ratings”, which are added to your list or library, are not updated in realtime when users rate content items. This is done by 2 timerjobs;

  1. User Profile Service App – Social Data Maintenance Job
  2. User Profile Service App – Social Rating Synchronization Job

These 2 timerjobs together cause ratings columns to be updated in lists and libraries to reflect users ratings.

Running the Jobs Manually

For developers running these jobs manually I usually find that the jobs must be run in sequence in the order shown above.

Using the Ratings Columns

The 2 ratings fields have the following internal column names;

  • AverageRating for “Rating (0-5)” – this is a decimal number in the range 0 – 5
  • RatingCount for “Number of Ratings” – this is an integer number

These can be used for a variety of purposes such as aggregation and display. They can also be used with ‘HTML’ type calculated columns as shown below;

calculated column based on ratings

Here I’ve created a calculated column which converts the 0-5 rating to a percentage which is then used as a pixel value setting the width of a <DIV/> element with a background color.

I also use the fractional part of the 0-5 rating to create a font size scaling when displaying the rating value inside the <DIV/>.

Start by creating a calculated column on your list or library (or preferably as a site column);

create a calculated column

Set the type to Calculated, enter the Formula and set the “The data type returned from this formula is” to Numeric.

Setting the output type to Numeric causes SharePoint to just emit the formula result, using Single line of text causes SharePoint to HTML encode the output.

The formula I used is;

=CONCATENATE("<DIV style='font-size:8px;border-left:2px solid black;width:200px;'><DIV style='padding:2px 2px;font-weight:bold;font-size:",([Rating (0-5)]-INT([Rating (0-5)]))+1,"em;color:white;background-color:green;width:",(([Rating (0-5)]/5)*100)*2,"px;'>",[Rating (0-5)],"</DIV></DIV>")

Enjoy.

Published by

Phil Harding

SharePoint Consultant, Developer, Father, Husband and Climber.

One thought on “Show SharePoint Content Ratings with a Calculated Column

  1. Hi Phil, this is great, thanks for the article.
    I’d like to further bolster my use of the ratings – perhaps you can help. I’d like to have a calculation run on either the rating column or the replies column to indicate whether a given topic is “hot”. For instance, if a given topic gets rated 10x in an hour, it would be nice to highlight that topic somehow.

    I was thinking a calculation could be fun and if the value was positive, a small fire icon would display. The icon itself I know how to do but the logical rule to enable it, eludes me.

    thanks!

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s