Locale bug in ddwrt:FormatDate not resolved for external data

XsltListViewWebPart is a powerful out-of-the-box SharePoint 2010 capability to render data, using Xslt to format the data display as you like. One of the standard Xslt functions, ddwrt:FormatDate, exhibits an annoying bug when used within XsltListViewWebPart. In case the locale of SharePoint server is other than US, then day and month are switched if day is < 13. The rootcause is – according to Microsoft - that ddwrt:FormatDate expects the datetime value to be in UTC format, but in reality XsltListViewWebPart preformats datetime values to the SharePoint server Locale before the Xslt-transformation takes place.
The SharePoint 2010 February CU 2012 contains an hotfix for this bug, and introduces the ListViewWebPart property ‘EnableOrginalValue’.

<property name="EnableOriginalValue" type="bool">True</property>

The effect of this property setting is that XsltListViewWebPart will include multiple formats per datetime value in the preformatted data. By using the ISO8601 datetime variant, the display bug can be resolved:

<xsl:value-of select="ddwrt:FormatDate(@Name,'.ISO8601')]),1033, 3)" />

However, this does not hold in case of external data. XsltListViewWebPart ignores the ‘EnableOriginalValue’ flag when rendering external data; and does not preformat the 'ISO8601' variant. Inspecting the sourcecode of super class DataFormWebPart proofs this to be true:

Conclusion is that XsltListViewWebPart by design does not support the hotfix for external data. Reasoning is that SharePoint BCS expects datetime values received from external systems to be in UTC format. And if not in UTC (DateTime.Kind unequal ‘UTC’), SharePoint BCS converts the received DateTime value into UTC:

"External systems commonly standardize on Coordinated Universal Time (UTC) as the time zone in which to store data. While this works well for data storage, end users prefer to work with times shown in their local time zone. External lists and the External Data Web Parts tackle this problem by converting time zones before presenting DateTime fields to end users, as well as before submitting DateTime values to the external system.
After retrieving a DateTime field from an external system, the BDC runtime examines the DateTime.Kind property to determine its time zone. If it is UTC or Unspecified, no action is taken. If it is marked as Local, BDC converts it to UTC by applying an offset based on the SharePoint front-end web server’s time zone, as specified by its Windows settings.
Now that the time zone has been standardized to UTC, the external list (aka XsltListViewWebPart) examines the user profile settings for the current user; if a time zone has been specified, the time is converted from UTC to the user's time zone. If a time zone has not been specified (which is the default state), the time zone of the site on which the external list is hosted is used to convert the external data. The converted times are then presented to the user in the UI in a format (for example dd/mm/yyyy hh:mm:ss) based on the site locale."

Source: Professional Business Connectivity Services – Scot Hillier

Thus external data received within XsltListViewWebPart will only contain UTC datetime values; therefore the issue should not occur; and the hotfix is on purpose disabled for external datasource.

But this is a design fault. We have a situation in which we retrieve SAP data [using Duet Enterprise + SharePoint BCS] in UTC format; and then still encounter the issue in case day < 13. Not surpisingly, UCT merely applies an offset in hours to the locale datetime; day and month remain in the same range; with at maximum 1 day earlier or later. Therefore the ddwrt:FormatDate also manifest itself for UTC datetime in case day < 13. As the XsltListViewWebPart hotfix on purpose does not fix that, we need an own Xslt FormatDate function to properly render the UTC datetime received from external system.

Notification + data flow in Duet Enterprise Workflow mobile App

The Duet Enterprise Workflow capability can be used to expose SAP workflow tasks to SharePoint context. Once in SharePoint, it can be further built upon. You can extend the associated SharePoint workflow with additional workflow steps and business processing. Another option is to use the SharePoint Duet Enterprise Workflow site as integration layer to expose and handle the SAP tasks in a mobile application.

System architecture

In such system architecture, following systems/layers are present:

1. SAP backend / workflow engine: the host environment of the SAP workflows
2. SAP Gateway: general purpose SAP integration layer; here used to publish SAP workflows from backend to workflow notification subscribers
3. SharePoint Duet Enterprise Add-On: functions here as workflow notification subscriber; host of Duet Enterprise workflow instances, and task repository for the mobile application
4. Mobile view: retrieve plus display tasks, and propagate task approval decision back to the SharePoint task repository

Note that in this system architecture, the (mobile) view and SAP landscape operate disconnected; SharePoint is the glue between the backend and frontend parts.

Notification and data flow for exposing SAP task to non-SAP view

Sequence steps:

1. The SAP ERP workflow execution arives on a user decision task, that is configured for notification to Gateway (tx SPRO, Gateway workflow customization);
2. SAP Document Publisher composes a XML Payload document with task data: workitem identification plus business data. The addition of the workflow specific business data is optional in Gateway Workflow notification. If applicable, you can achieve this by extanding the workflow customization with the plug-in of a custom OutboundHandler;
3. SAP Document Publisher calls the SharePoint OBAWorkflowServer.asmx::CreateTask operation, with inputparameters the task(type) identifier, workitem identifier and XML PayLoad document;
4. SharePoint OBAWorkflowServer.asmx::CreateTask method:
   1. Queries the TaskLocator List in the Duet Enterprise Workflow rootsite, and determines on basis of tasktype and user language the Duet Enterprise Workflow subsite to which the received SAP task must be routed;
   2. Instantiates a new SharePoint workflow instance in the identified Duet Enterprise Workflow subsite, and associates this instance with the SAP workitem identifier;
   3. And uploads the received XML PayLoad document into the Workflow Business Data DocLibrary in the Workflow subsite;
5. The SharePoint Duet Enterprise Workflow contains by default only 1 step that represents the decision-making for the SAP task. It is possible to extend the SharePoint Workflow with additional steps and functionality. This will operate isolated from the SAP Workflow handling. The default approval task in the SharePoint Duet Enterprise workflow is the only connectionpoint between the SharePoint workflow and the SAP decision workflow task. Upon the Duet Enterprise Workflow arriving at the approval task, it appears in the SharePoint tasklist.
6. User opens the tasklist in the mobile application;
7. The tasklist App call SharePoint List.svc service (DataAccess Layer) to retrieve the open tasks for the logged-on user
8. SharePoint List.svc queries the SharePoint workflow tasklist, to determine and retrieve the open task items for logged-on user
9. SharePoint List.svc returns the task items in Odata format
10. The Duet Enterprise Workflow Approval Task and the XML PayLoad document are in the SharePoint Workflow administration logical associated to each other. If additional business extended properties are notified for the SAP workitem via OutboundHandler, the associated Business Data document must be opened from the SharePoint document library and its contents parsed to retrieve the additional properties.

Control and data flow for handling task decision from non-SAP mobile App to SAP workflow context

Sequence steps:

1. User makes a decision in the task management App;
2. Task management App calls SharePoint Workflow.asmx webservice to propagate the task approval decision to SharePoint task item;
3. SharePoint workflows.asmx updates the task item with approval decision;
4. The update on SharePoint taskitem triggers the TaskItemEventReceiver;
5. Triggered TaskItemEventReceiver::ItemUpdating method checks whether the SharePoint task item is completed; that is a approval decision is made. If completed, TaskItemEventReceiver calls [through SharePoint Business Connectivity Services] the Update method of External ContentType  Duet Enterprise Workflow Task;
6. SharePoint BCS calls for Duet Enterprise Workflow Task Update the wfUpdate method of SAP workflow consumer webservice; with input parameters workitem identifier, decision value and comments;
7. SAP workflow consumer webservice propagates task approval decision to the associated SAP workflow instance, and executes apply decision on the SAP workitem;
8. SAP workflow completes this workitem step, and proceeds to the next step in SAP workflow. If it was the last step in workflow, the SAP workflow is now completed;
9. TaskItemEventReceiver updates the status of SharePoint taskitem to completed. In default (generated) Duet Enterprise Workflow is this the only step/task, and the SharePoint workflow is completed. The SharePoint taskitem is removed from the users open tasks list.

Selective enable ScriptManager presence in SharePoint Publishing Site

It is a well-known symptom of standard SharePoint Publishing that it renders bloathed HTML pages. The cause is that SharePoint publishing pages serve 2 modes: the end-user view, and the editorial view. To serve the latter, considerable extra html and SharePoint javascript files are included that are of no use for the end-user [view].

Approach to mitigate this effect is by separating the editorial from the end-user view. Examples of this approach are Macaw Dual Layout and Mavention Flex Layout. At high level the working is to have a distinct masterpage for end-user view, and another for the content editor. Only the latter must have the means to enable SharePoint WCM functionality; the former can be designed ‘clean’: no Form runat=”server”, ScriptManager, and include of SharePoint javascript files (init.js, core.js, …). Besides loading faster, additional advantage is that pure HTML sites are also more SEO-friendly.

However, there typically remain situations in a SharePoint publishing site in which you do need the presence of ScriptManager and the standard SharePoint client-side scripts. Multiple of the standard SharePoint controls are dependent on ScriptManager: CalendarView, TaskList, External List (XstlListView), Business Data / BCS webparts, to name some. Also custom developed controls might depend on presence of ScriptManager, e.g. rotating banner control using jQuery libraries for client-side user interface behaviour.

You have basically 2 options to enable ScriptManager et al for Publishing Pages on with controls are placed that need the ScriptManager:

1. Duplicate the ‘clean’ end-user masterpage; and augment it with the required entities.
2. Create an additional Publishing PageLayout; and augment that the required entities.

Option 1 suffers from the following disadvantages:

• As consequence that a SharePoint SPWeb can only have 1 masterpage, ‘clean’ content publishing pages and WebControls pages cannot be within the same SharePoint SPWeb. In fact, the type of pages now influences the site structure. This is visible for the end-user. And for the content manager it implies that (s)he is forced to locate new page a priori within the correct SPWeb given the intended type of page: content or control. Also, as standard SharePoint navigation is based on the site structure, it will require custom development to repair the logical site navigation.
• Double maintenance of the site look&feel in the 2 public masterpages.

As in the 2nd option both page types use the same public masterpage, all pages can be within the same SharePoint SPWeb. So with this approach it is non-visible for the end-user, and also the editing experience is far more transparent for the content editor. Not entirely, as (s)he must apply the correct PageLayout given the type of page. But the applied PageLayout is something that in SharePoint publishing can be modified / corrected after initial creation.

From SharePoint technical perspective, the 2nd option is more difficult / challenging to realize as the 1st (this is simple: just copy the public masterpage and add ScriptManager et al). Issues here are following:

• The extended PageLayout must function both in combination with the clean masterpage as with the content-editor enabled masterpage. But the latter also as Form runat=”server” and ScriptManager included; resulting in 2 occurences of them. And this is not allowed in ASP.NET plus SharePoint.
• SharePoint scriptmodel imposes hidden dependencies on the order of including + evaluating/loading SharePoint javascript files (through SOD framework).

You can deal with these issues via following approach and architecture:

• Include Form runat=”server” in the extended PageLayout; but default not visible.
• A server control included in the extended PageLayout to dynamically insert the ScriptManager plus ScriptLink for core.js; but only if no ScriptManager present yet (included from the content-editor masterpage). When runtime determined that the ScriptManager must be inserted, also the invisible server-side Form is made visible; and all siblings of the Form control are moved within the server-side HtmlForm control.
• A server control included in the public masterpage to dynamically insert WpzEmitControl (explanation why needed) if needed; the condition for this is whether the InsertScriptManager control is include in the page context.

Result

Regular content pages still render ‘clean’ html in public view, without the unneeded SharePoint html and javascript. Control pages render in public view including the required ScriptManager and Form runat=”server”; and in editing view also with only occurrence of ScriptManager and server-side HtmlForm.