.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Win Surprise Gifts!!!

Top 5 Contributors of the Month
Gaurav Pal
Post New Web Links

SharePoint Calculated Columns with Conditional Formatting

Posted By:      Posted Date: April 10, 2011    Points: 0   Category :C#
I am working on a custom list in SharePoint 2007. I have a calculated column that is formatted as a percentage. When opened in SP Designer and converted to a XSLT data view to apply conditional formatting, the column is rendered as text and not numeric which is a known issue with SharePoint. How can I apply formatting to this column and render it as a numeric value? My conditional formatting relies on If x is <= y then apply yellow and so forth so it has to be read as a numeric value. I have attached the conditional formatting code as well as the format of the data property. Any help would be appreciated.
<TD Class="{$IDAS4QMC}">
																<xsl:attribute name="style">
		<xsl:if test="@_x0025__x0020_Variance_x0020_C &gt; '-.11' and @_x0025__x0020_Variance_x0020_C &lt;= '0'">background-color: #00FF00;</xsl:if>
		<xsl:if test="@_x0025__x0020_Variance_x0020_C &gt;= '.1'">background-color: #FF0000;</xsl:if>
		<xsl:if test="@_x0025__x0020_Variance_x0020_C &lt; '-.11' or @_x0025__x0020_Variance_x0020_C &gt; '0'">background-color: #FFFF00;</xsl:if>
		<xsl:if test="@_x0025__x0020_Variance_x0020_C &gt;= '-.11' an

View Complete Post

More Related Resource Links

Conditional formatting on a joined subview using SharePoint Designer 2007 does not work

I have 2 SharePoint lists, one is a parent task list and the other a child task lists.  In SharePoint Designer 2007 I have created a combined datasource from these 2 lists.  From there I created a new ASPX page inserting the parent list as a dataview and the child as a subview.  So far so good, all is functioning as you would expect.    Now I need to apply conditional formatting to cells and rows in both the parent and child lists.  I can create a very simple condition on a row or cell of the parent list such as "If ID Equals '2988' format the background color as green" but no conditional formatting appears for the row where ID = 2988.  Incidentally, if I change the condition to "not equals 2988" the background changes to green. The line in the code view of Designer appears as follows:  <xsl:if test="../../../Rows/Row/@ID = '2988'">background-color: #00FF00;</xsl:if> I've seen this question asked here and in blogs but never answered.  If anyone has a solution, I would love to know what it is. Thanks; Dave  UPDATE:  it doesn't seem to matter if you add the additional fields as a joined subview or not.  If I add 2 data views, both using the linked data source which is configured to JOIN the contents of the data sources the conditional formatt

Sharepoint Designer - Conditional Formatting Problem


I've been creating some basic conditional formatting scenarios using Designer ... but I'm very new to this and need help with two topics that I can't get to work.

Problem 1:

When my condition criteria is met I want to display a graphic in the background, but the left of the actual text for that column. 

Example: I have a list of orders and when the priority column says rush, I want to display a small graphic to the left of the text for that column and shift the text to the right by the width of the 40 pixel graphic.  I've looked through all the different settings in the "modify style" dialog box and have used trial and error, but the darn graphic keeps displaying behind the text only. UGH!  I've been working on this for 2 hours now and can't figure it out. 

Can someone tell me what parameters to set so the text is shifted to the right 40 pixels so the background that is left justified is visible?


Problem 2:  

In this case, I can't figure out how to write the formula to tell   If  [DUE DATE] is less then 5 days from today I want the background cell to change color. I know how to change the color, but can't figure out the formula for the life of me.  Can someone give me the formula? 

Here's what I tried using in the xpath advanced

SP Designer 2010 Conditional Formatting with Hidden Values/Columns


I have a list with 30~40 columns. I want to do conditional formatting on it using SharePoint 2010 Designer (instead of creating a visual webpart with vs2010).

Of the 30-40 columns, i am only viewing 5 columns that i need (ie: first name, last name, etc...)

I can create the XSLT ListView Webpart on the page within SharePoint 2010. I can edit it with SharePoint 2010 Designer, i can also apply conditional formating to the row (ie: select row within XSLT webpart > Options > Conditional Formatting).

However, i cannot apply the conditional formatting too columns that are not listed in the <ViewFields> of the <XmlDefinition>. What gives? I want to do conditional formatting on the entire row of the item using the hidden ten or so columns and i don't want the user to see all the columns, but i want to do conditional formatting on them (its for æsthetics reasons, i don't want a grid list of 15+ columns on someones poor 1024 by 768 resolution/monitor... plus they don't need to see it until they click into the list item....)


<FieldRef Name="SomeColumnName" Hidden="True" /> does not work
<FieldRef Name="SomeColumnName" ReadOnly="True" /> does not work either

Passing Calculated Columns from Sharepoint into Word Document Library Template


Hi all...

Another noob question.  I have been able to figure out how to pass single line text, date, etc. data from a Sharepoint List into a Word Template, but I am still trying to figure out how to pass a calculated column.  Is there a workaround?


How to create scrolling table for columns in a SharePoint Designer Data View


I have a fairly complex SharePoint Designer 2007 Data View they have added two more requirements.

1. Have the columns on the right side be able to scroll left to right as there is a large number of columns, while the first 3 columns on the left side stay locked in place.  This is only one list not two, and because of some other requirements I have it must remain to be just one list.

2. Then they also need to have scrolling up and down of the entire list with the headers locked in place.

The first requirement is more important than the second, but preferrably I need to do both.

I am new to XSL so not familar with the syntax. Is this something I can do on a custom Data View? Does anyone have any examples of how?

I have a drawing that show how it needs to look but I don't see an option here to upload a graphic.

So here is a link to my graphic example uploaded to my blog area: http://lindachapman.blogspot.com/  or just the image here: http://4.bp.blogspot.com/_aB01ue__NvQ/TGL42MOvrGI/AAAAAAAAAA4/c3xP4uCy4BU/s1600/Scrolling.png

I failed to mention that 3/4 of the columns are HTML Calculated fields wh

Calculated Columns in a Form Library receive error: Value does not fall within the expected range.


I have a forms library that posts the date the form was created. We would like to create a calculated column that allows us to look at these forms based on the month and year they were created. I had created a calculated column that extracts the month and replaces the number with a text string by using an if statement; "IF(MONTH([Proposal Date])=1,"January","") It goes on to evaluate each month and replace the number with the text string. This formula worked for quite some time, though now when I try to add any calculated columns or edit any existing calculated columns on this form library I receive the following error: Value does not fall within the expected range. Now my existing calculated columns (like the one above) do not work and I can not create new calcualted columns. Any insight would be awesome as this has been plaguing me for a couple of months now.

Creating List Definitions with Custom List Columns for SharePoint Server 2007

Learn to use Visual Studio 2008 Extensions for Windows SharePoint Services to create a SharePoint list definition that implements custom list columns.

Sharepoint: In Calculated Column, Code with multiple "IF" condition does not work.

Implemantation:  Calculate expected resolve date exclude "Saturday" and "Sunday"  based on Priority and Severity.Columns are:1. Priority:2. Severity:3. Raised on: Date record createdCode: Below is the code which contains multiple "If" conditions. For quick undersatnding, i separated major If condition. Below code does not work and sharepoint gives me Sytax error message. =IF(OR(Priority="Critical",Severity="Critical"),IF(TEXT(WEEKDAY([Raised on]+1),"dddd")="Saturday",[Raised on]+3,[Raised on]+1),     IF(OR(Priority="HIGH",Severity="HIGH"), IF(TEXT(WEEKDAY([Raised on]+1),"dddd")="Saturday",[Raised on]+4, IF(TEXT(WEEKDAY([Raised on]+2),"dddd")="Saturday",[Raised on]+4,[Raised on]+2)) ,   IF(OR(Priority="MEDIUM",Severity="MEDIUM"), IF(TEXT(WEEKDAY([Raised on]+1),"dddd")="Saturday",[Raised on]+5, IF(TEXT(WEEKDAY([Raised on]+2),"dddd")="Saturday",[Raised on]+5, IF(TEXT(WEEKDAY([Raised on]+3),"dddd")="Saturday",[Raised on]+5,[Raised on]+3)) ) ,   IF(OR(Priority="LOW",Severity="LOW"), IF(TEXT(WEEKDAY([Raised on]+1),"dddd")="Saturday",[Raised on]+6, IF(TEXT(WEEKDAY([Raised on]+2),"dddd")="Saturday",[Raised on]+6, IF(TEXT

Use Sharepoint Calendar Start Time field in calculated column

I'm trying to limit the calendar view to current week using the Sharepoint defined field Start Time. However, when I display the serial number behind the Start Time it looks like it is actually giving me the serial number for the Created Date field. I believe it is because the appointment is reoccurring but I'm hoping there is a way around it. I created a new testdate field and manually entered the date and set those appointments as all day events however they are not reocurring appointments. The testdate returns the correct serial number however the start time returns an incorrect serial number. SerialNumber1=DATE(YEAR([Start Time]),MONTH([Start Time]),DAY([Start Time])) SerialNumber2=DATE(YEAR([testdate]),MONTH([testdate]),DAY([testdate])) Start Time  SerialNumber1 Testdate  SerialNumber2 8/31/2010 40420 8/31/2010 40421 8/31/2010* 40405    9/1/2010* 40405    9/2/2010* 40405    9/7/2010 40427 9/7/2010 40428 9/13/2010* 40405    9/14/2010 40434 9/14/2010 40435 9/14/2010* 40405    9/15/2010* 40405    *These are reoccurring appointments scheduled as all day events. The others were entered as one time appointments scheduled as all day events.

How to sum Calculated Columns

Hi, I want to sum the calculated column (currency format). I found some ways to do that in SharePoint 2007 but how to achieve this in SharePoint 2010? I  try to use SPD 2010 but no result. Thanks in advance!   

Show wiki page library columns on wiki pages- SharePoint 2010

I created a wiki page library in SharePoint 2010 and added a few columns to the library. However, these columns do not show on the actual wiki pages as they did in SharePoint 2007. I looked through the library settings but didn't find anything to turn on this feature. Does anyone know how to do this or if this feature was even included in SharePoint 2010? Thanks.

Managed Metadata columns in Sharepoint Workspaces

Hello,   all our document lists use Managed Metadata columns. They are required also.    Sharepoint Workspaces list current values for these columns, but do not allow changing them. Thus effectively making Sharepoint Workspace unusuable, because we can't check-in new documents.   Am I missing something?

Query Sharepoint list, rich text columns returning HTML tags

Hi! So I have an issue that maybe someone of you have encountered as well. I am querying a Sharepoint list and creating a report from the data collected from this list. This all works fine except that one of the columns in this list is of the type "Multiple lines of text" and also supports "rich text" with different fonts, sizes and so on. This has the effect that the text returned from this column is wrapped in HTML tags that specifies how the text should be formatted. Naturally I do not want this HTML code to appear in my report so for now I have used custom code to do a string.replace and replaced for example <div></div> and <br> with "". However, this column also has url references to documents and these links shows up like this "/servername/site/subsite/Gemensamma%20dokument/Mwh.docx">http://servername/site/subsite/Gemensamma%20dokument/Mwh.docx There is some sort of double reference to the document that I guess like the HTML tags are embedded in the answer from the rich text column and this is not so easy to do a string replace on because the url varies with the different document and document names. My question really is if anyone of you have done a similar report that I am doing, and have encountered this fenomen with this sharepoint column and how you did to solve this? I have tried to creat

Conditional Formatting

Hi I've recently published a web database from Access into a Sharepoint site using Access Services and my conditional formatting has not come through.  Looking at the help system, it states that conditional formatting is a desktop only feature in Access. Can someone just confirm definitely for me that this is their understanding that it's not available, even in Access 2010, as I've been asked to check?  Is there any other way to get conditional formatting to appear in a report within the web database? Thanks Nickie

Sharepoint export to spreadsheet gives two extra columns in excel 2007 named as type and path, is th

When i export a list in excel 2007, it creates two extra columns named as type and path. At the same time this behaviour is not exhibited in office 2000. Is there any way to remove these two columns without using code ? Is there any configuration settings required for this, to remove it on the fly ? Thanks Ravish Verma

Lookup columns in a SharePoint Custom list

Hi, I would like to know the maximum lookup columns we can create in a SharePoint Custom list. Thanks and regards, Usha   

InfoPath 2007 - Publishing Creates multiple columns on SharePoint Form Library (GUIDS apparently are

I have been working this issue for 2 days now.  This has happened to me in the past and I was able to work through it.  It doesn't appear that this time I am going to be so lucky. Issue Description: When publishing an InfoPath form a new column is created that has the same name as the correct one, but a new GUID is created. Troubleshooting steps: I have read this article: http://www.delphi-ts.com/blogs/lozzi/post/2009/06/03/Duplicate-site-columns-in-MOSS-from-InfoPath-Forms.aspx   This was the solution the last time I had this problem.  However, no matter what I do, it is not working now.  I have a column named User Name which is the username of the person that submitted the InfoPath form for use with my Visual Studio workflow.  When the workflow kicks off, it reads this column and attempts to get the value.  The value is blank because a new column with the name User Name now exists and that is where the value is stored.  I attempted to fix it by remapping the GUIDs as described in the article.  I also reverted the code to a previous version from TFS.  None of these worked.   I now have 3 columns called User Name and I can remap the GUID to the second column and third column.  But when I try to map it to the first column (the one I need) it doesn't work. The first column that is circled is the correct column. 
ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  Silverlight  Others  All   

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend