Posted tagged ‘calculated columns’

Customize Look And Feel With Calculated Columns

April 14, 2009

Ever wanted to ‘Color Code’ a SharePoint calendar? Or have certain list items display differently then other list items based on their column values?

One of those things that every user needs is the ability to customize the look and feel of many SharePoint elements, and sometimes it just doesn’t make sense to wait for a SharePoint team, developers, or architects to work out a long term solution.

Sometimes you just need a quick and dirty way of doing something as a one off to illustrate a point or to deal with a situation while you work out the full solution.

My favorite ways of dealing with most of these things is the use of codeplex projects, jQuery, javascript, content editor webparts, and SharePoint Designer which I post pretty often about. It’s quick, normally easy to reverse, and can often be managed by a business user.

Another personal favorite of mine is a method that (to my knowledge) Christophe came up with using calculated columns, some conditions, simple HTML and a bit of JavaScript. All of which turns into completely customized (look and feel) views of lists. (Read his original article here: http://pathtosharepoint.wordpress.com/2008/09/01/using-calculated-columns-to-write-html/)

The concept is simple:
Using SharePoint calculated columns create conditional statements that display different HTML based on the value of the column. Then (because of how it is rendered) use JavaScript to transform the rendered content to it’s equivalent HTML.

The power of this is that it can apply to a great many different scenarios. Take for example one of the common requirements of ‘color coding’ a calendar. In Christopher’s post last november he cleverly illustrates exactly how you could do this with relatively little effort: http://pathtosharepoint.wordpress.com/2008/11/15/sharepoint-calendars-color-coding-hover-effects-etc/

image

This is a simple and effective way to deal with many look and feel issues quickly and easily. If used with a bit of direction this can really help users understand, consume, and review information much more quickly.

image

(Above image from another example of how it can be used: http://pathtosharepoint.wordpress.com/2008/09/01/apply-color-coding-to-your-sharepoint-lists/)

Update: Adorable little calculated column builder (based on what I describe in this post) – http://www.pathtosharepoint.com/HTMLcc/default.aspx. Could save you a bit of time.

Hopefully with a glance at some of these you can begin to see the usefulness and power such a method can provide for your SharePoint deployment, I know I use it fairly often and I am sure you will too,
Richard Harbridge

Calculated Column Dynamically Updated (When using [Today])

October 3, 2008

Today I was asked a fairly common question that everyone who uses calculated columns eventually runs into. “How do you get a column to calculate automatically when using a reference for a date, or something that dynamically changes like [Today]?” Out of the box this isn’t available because the calculated columns ONLY calculate when an item is updated.

The solution is simple, you write a custom solution that updates all items as desired. So this could be something that runs daily or every time you view the data (using a dashboard). You could even write the code so it adds and removes the column instead of iterating through the items. Do some performance testing to see which is faster in your case.

A terrific Article was posted by Dink that answers exactly how to do it too with free samples: http://www.sharepointblogs.com/dez/archive/2008/07/31/dynamically-updating-a-sharepoint-calculated-column-containing-a-quot-today-quot-reference.aspx

There is also a post here by Chris where he uses the DataView and JavaScript: http://pathtosharepoint.wordpress.com/2008/08/25/a-countdown-for-tasks-lists/

Hopefully this helps someone out,
Richard Harbridge

Using Calculated Columns for Powerful Views

July 25, 2008

The other day someone asked me if it was possible to create monthly views and whether it would be possible to sort and filter by the month (jan, feb, etc).  What was special about this request was that it had to include ALL content from previous years for that month.

The reason in this case was to compare market figures and sales trends by month, especially helpful if you are trying to see how much you improved or what demand might be expected to be for certain products or services.

The problem was, they wanted the ability to filter and sort a column of this data in line (in this case month), not just see views of the information.

The solution in this case was simple, just use a calculated column for the month view and you will get the in line sorting and filtering of SharePoint for that column.

  1. Create a column called month.
  2. Set the calculation in this case to be “=MONTH([DateColumn])”.
    (Some examples of other common formulas: http://office.microsoft.com/en-us/sharepointtechnology/HA101054791033.aspx?pid=CH100650061033)

That’s it.

Now his users could filter the records by month without having to change views.

This could also be easily used to create a column that calculated the season as an example to give them both the ability to maybe group quarterly results and total them. (Note: To total a calculated column see my total calculated columns post.)

Make your users experience more powerful and provide them the details they need in the way they are used to it, or would prefer to see it by applying a bit of thought to the application of calculated columns.

Enjoy,
Richard

Total Calculated Columns in SharePoint

July 22, 2008

Out of the box SharePoint does not allow you to create totals in a SharePoint view for calculated columns or calculated fields.

Here is a work-around to total a SharePoint Calculated Columns or the Calculated Fields. (Note this will also create subtotals when using Group By options in a standard list view.)

Let’s say we have a field called PROFIT that is a calculated field set to subtrack the expense field from the revenue field in our list. ([Revenue]-[Expense])

  1. Create a regular number field (or currency field) called TOTAL PROFIT (or any other name you want) in that list.
  2. Using SharePoint Designer create a workflow for your list that runs when list items are created and when list items are changed.
  3. The workflow will have only one step name it something like set TOTAL PROFIT.Leave the condition field blank.
  4. In the action section choose “Set field in the current item”.
  5. Click on the word “field” and choose TOTAL PROFIT (the name of our column).
  6. Click on the word “value” and click on the “fx” button to do a lookup.
  7. Choose “Current Item” for the source.
  8. Choose “PROFIT” for the field. (This is the calculated column.)
  9. Click Finish.

Now whenever someone creates or modified a list item in that list the non-calculated field will automatically be updated with the data from the calculated field.

This means that by adding the TOTAL PROFIT field to any view you can total it and get a sum or any other calculation since you are no longer using a calculated field (directly).

Hope this helps someone,
Richard