Archive for July, 2011

Conditional Formatting in SharePoint Designer 2010 doesn’t compare dates correctly

I wrote previously about not being able to pass dates in British format from Time Intelligence Connection Formula filter to Reporting Services report, because although SharePoint displays the dates in British format, but on the server-side it doesn’t deal with them in the same format.  Here is the link to the post.

The same occurred to me when I was trying to apply very simple conditional formatting to add traffic lights for overdue items as below

* Current date = 22/07/2011

As it can be noticed in the above picture that the condition is false for 29/07/2011 but true for 05/08/2011. If I go to advance view the condition it generates behind the scenes is the one below

I forced the Due Date to the correct format in this condition I used “ddwrt:FormatDate” function

ddwrt:DateTimeTick(ddwrt:GenDisplayName(ddwrt:FormatDate(
string($thisNode/@DueDate), 2057, 1))) <
ddwrt:DateTimeTick(ddwrt:GenDisplayName(string($Today)))  

In the above line, 2057 is the LCID for British English, and “1” is a simple date format.

And it worked for me

 

1 Comment

More than two conditions in CAML query

CAML doesn’t support more than two conditions in a query. I just realized that I never needed more than two conditions in a query, but I just did when I was designing an SSRS report against a SharePoint list.
But there’s nothing to worry about, here is the workaround;

<Query>

<Where>

<Or>

    <Contains>

    <FieldRef Name=”Comment” />

    <Value Type=”Text”>    <Parameter Name=”SearchParam” />     </Value>

    </Contains>

<Or>

    <Eq>

    <FieldRef Name=”ID” />

    <Value Type=”Integer”><Parameter Name=”SearchParam” /></Value>

    </Eq>

<Or>

<Contains>

    <FieldRef Name=”Title” />

    <Value Type=”Text”><Parameter Name=”SearchParam” /></Value>

 </Contains>

<Contains>

    <FieldRef Name=”Record_x0020_Number” />

    <Value Type=”Text”>    <Parameter Name=”SearchParam” /></Value>

</Contains>

</Or>

</Or>

</Or>

</Where>

</Query>

Keep nesting for more conditions.

Leave a comment

Remove hyperlink from Lookup column in SharePoint

For whatever reason if you don’t want users to be able to click on a lookup column in a list, there’s no straight forward method of disabling that link. Here’s the way I disable them in SharePoint 2010, and it wasn’t any different in 2007 either apart from fancy UI.

In the image below, Country is a lookup type of column

 

 Open the list view in SharePoint designer for modification

 

 Select the lookup column item and in the document map bar in the bottom, choose the context menu for “xsl:value-of”, and select “Edit Tag…”

You will see the “Edit Tag” window

The above XPath “$thisNode/@*[name()=current()/@Name]” when executed, returns the following hyper link tag

<a onclick=”OpenPopUpPage(‘http://servername/_layouts/listform.aspx?PageType=4&ListId={9CF20D94-56E4-426B-AAA3-97CEA2B23570}&ID=3&RootFolder=*’,RefreshPage); return false;”href=”http://servername/_layouts/listform.aspx?PageType=4&ListId={9CF20D94-56E4-426B-AAA3-97CEA2B23570}&ID=3&RootFolder=*”>United States</a>

In order to get the clean value which is in this case “United States”, we need to remove the decoration around it, so we are going to remove everything before ‘>‘ and after ‘<‘ by changing the tag in the Quick Tag Editor to

<xsl:value-of select=”substring-before(substring-after($thisNode/@*[name()=current()/@Name],’&gt;’), ‘&lt;’)” disable-output-escaping=”yes”>

Then press the tick button on the “Quick Tag Editor” to save the modified tag, save the page and view it in browser

Links are gone.

Neat, isn’t it?

21 Comments

PerformancePoint Time Intelligence Filter with SSRS report connection error

Recently while designing a Dashboard in SharePoint 2010, I created a SSRS 2008 R2 report, which had a parameter of type date, “no problem, it would work great with Time Intelligence Filter”, that’s what I thought, but it didn’t.

My SharePoint site is using UK regional settings, but my reporting services are installed with default options, means it uses US settings.

In the picture below it can be seen that both of these controls understand UK date format, which is logical that they are considering the client’s regional settings.

 

 

 

 

But when I connect these two, it seems that the date which is being passed to the report is not in the UK format. I can confirm this by selecting an earlier date, for example 10/07/2011, the report interprets it as 7th Oct.

 

 

 

 

 

 

 

 

 

 

 

I haven’t found the solution to this problem yet, but I am using a workaround which is to deploy the dashboard without filter and then edit the page in browser and use SharePoint date filter, it works straights away.

So in this case I would blame the Time Intelligence Filter, because the report is working fine with filter web part in the browser.

 

 

 

3 Comments

Broken list references in SharePoint Designer 2010 reusable workflow when deployed

Has this ever happened to you that a list is already on your live system and you are asked to create a workflow for that? I know it happens quite often. In this case list workflow is not an option, unless you are creating it directly on live server, very unlikely, but still I have seen this happening.

If you don’t have to go for Visual Studio based workflow, then reusable workflow in Designer is the only option. Usually the course of action is as follows;

  • Get the list template from live server, for which you want to create the workflow, if you don’t already have the updated list on development
  • Create the a list with the same name as on live server based on the template on the development server
  • Create a reusable workflow and reference the list all over it
  • Test the workflow, it works
  • Be happy J
  • Save the workflow as a template solution
  • Upload that solution on live system, and activate the related workflow feature in the web site
  • Open the workflow designer, feel happy that your workflow is there
  • Open the workflow for edit

And to you surprise all the references to the list are broken

You go back on live system, check the list name, you match it with the list name you have on development server and they are exactly same

Then why my references are broken, one of the biggest possibilities is because the list on production server was renamed after it was created, and workflow references lists by their path i.e

{$ListId:Lists/YourCustomList;}

It’s because the URL of the list always remains as it was first created, even if you change its title.

But it’s too late to know this at least for this workflow, but might save a lot of time on next ones.

There is another possibility that you renamed the list name on the development machine for any reason after its creation, and then original names of both of the list don’t match.

Create the list on development which matches the original name when it was first created on the live one, and it will be fine when deployed. You can find the original name in the list URL.

Remember renamed columns, lists or libraries are always painful for developers, if possible delete and recreate these items instead of changing their names.

Leave a comment