Second Life of a Hungarian SharePoint Geek

May 29, 2016

Reference Default Calendar Scopes in a URL

Filed under: JavaScript, SP 2013, Views — Tags: , , — Peter Holpar @ 05:41

As you know, you can create calendar views with various default scopes (e.g. Day / Week / Month, see screenshot below).


It means a single .aspx page per view, for example CalendarDay.aspx, CalendarWeek.aspx, CalendarMonth.aspx. You can then link these pages in HTML hyperlinks or send a link in mail by referring the given page.

But if you try to avoid creating and administering these three views for each of your calendars, is it possible to have only a single, universal page (like Calendar.aspx), and still have the ability to create a URL for the page that determines, with which scope the calendar would be displayed by default?

Unfortunately, it is not possible out-of-the-box, but with some scripting we can find a way.

Add a Script Editor Web Part (category: Media and Content) to the calendar view page after the calendar itself. Include the following script in the web part:

  1. <script type="text/javascript">
  3. function getQueryStringParameter(paramToRetrieve) {
  4.     var params =
  5.     document.URL.split("?")[1].split("&");
  6.     var strParams = "";
  7.     for (var i = 0; i < params.length; i = i + 1) {
  8.         var singleParam = params[i].split("=");
  9.         if (singleParam[0] == paramToRetrieve)
  10.             return singleParam[1];
  11.     }
  12. }
  14. var timer;
  16. function registerMoveView() {
  18.     var cirInstance = SP.UI.ApplicationPages.CalendarInstanceRepository.firstInstance();
  20.     if (cirInstance) {
  21.         MoveView(startView);
  22.         window.clearInterval(timer);
  23.     }
  24.     else if (!timer) {
  25.         timer = window.setInterval(registerMoveView, 100);
  26.     }
  27. }
  29. var viewNames = ["month", "week", "day"];
  31. var startView = getQueryStringParameter("StartView")
  33. if (viewNames.indexOf(startView) != -1) {
  34.     SP.SOD.executeOrDelayUntilScriptLoaded(registerMoveView, "sp.ui.applicationpages.calendar.js");
  35. }
  37. </script>

After you save the changes on the page, one can access the calendar with the daily scope using a URL like this:


One can access the monthly and weekly scopes as well via URL, simply by using the StartView query string values “month” and “week” respectively.

Note 1: The query string values “month”, “week” and “day” are strictly case sensitive, using a value like “Day” displays the default scope defined for the view (see the screenshot above).

Note 2: The sample above assumes you had a single calendar view on your you page. If you happen to have more, it would affect only the first one, see call to the firstInstance method in the script.

Note 3: The calendar instance is first available in the script after the real default scope completely rendered by the browser. We introduced a timer to overcome that issue, but if your browser is slow, you can still experience a blinking effect when switching to the new “default” scope, the one you selected via the StartView query string parameter.

May 30, 2013

Configurable Column Widths in SharePoint Views

Filed under: jQuery, REST, SP 2010, Views — Tags: , , , — Peter Holpar @ 18:32

In my recent post I wrote about a solution that enables users to resize the column width of SharePoint views. In this post I provide a sample for configuring the width of the columns through a helper list.

I’ve created a custom list called ColumnWidths and added a string (ColumnName) and a numeric field (ColumnWidth) to it for the name and the desired column width of the field. Note, that you should use the same name for the field, as it is referred to in the view / HTML source, for example, a title with context menu is called LinkTitle. The Title field of the configuration list item contains the site relative URL of the view that we would like to customize.


The script is based on jQuery and the same LINQ for JavaScript (ver.3.0.3-Beta4) library that I used in these samples as well. The script was injected to the target view page (for example into the /Lists/Test/AllItems.aspx) through the Content Editor Web Part.

In this case I used REST to query the configuration list, a similar result could be achieved through the ECMAScript Client Object Model.

Code Snippet
  1. <script src="/_layouts/jQuery/jquery-1.8.3.min.js"></script>
  2. <script src="/_layouts/jQuery/linq.js"></script>
  3. <script src="/_layouts/jQuery/linq.jquery.js"></script>
  5. <script language="ecmascript" type="text/ecmascript">
  7. $(document).ready(startScript);
  9. function startScript() {
  11.     var pageUrl = document.URL;
  12.     var pageUrlLenght = pageUrl.length;
  13.     var siteUrl = ctx.HttpRoot;
  14.     var siteUrlLength = siteUrl.length;
  15.     var siteRelativeUrl = pageUrl.substring(siteUrlLength, pageUrlLenght);
  16.     var url = siteRelativeUrl.substring(0, siteRelativeUrl.lastIndexOf("?"));
  18.     $.ajax({
  19.         type: 'GET',
  20.         contentType: 'application/json;odata=verbose',
  21.         url: siteUrl + "/_vti_bin/listdata.svc/ColumnWidths()?$filter=Title eq '/Lists/Test/AllItems.aspx'&$select=ColumnName,ColumnWidth",
  22.         headers: {                    
  23.             "Accept": "application/json; odata=verbose"
  24.             },
  25.         dataType: "json",
  26.         complete: function (result) {
  27.             var response = JSON.parse(result.responseText);
  28.             if (response.error) {
  29.                 alert("Error: " + response.error.code + "\n" + response.error.message.value);
  30.             }
  31.             else {
  32.                 var columnWidths = response.d.results;
  33.                 Enumerable.from(columnWidths).forEach(function(x) {
  34.                     $('[name="' + x.ColumnName + '"]').closest('th').width(x.ColumnWidth);
  35.                 });
  36.             }
  37.         },
  38.         error: function(xmlHttpRequest, textStatus, errorThrown) {
  39.                     alert(errorThrown);
  40.         }
  41.     });
  43. }
  45. </script>

Here is the original formatting of our Test list view. This is the same output as the view is displayed before the script is executed:


And here is the view after the page is loaded completely and the script is executed:


Through this method the width of the columns can be relative easily configured without using SharePoint Designer.

May 27, 2013

Creating resizable Columns in SharePoint Views

Filed under: jQuery, SP 2010, Views — Tags: , , — Peter Holpar @ 22:44

Recently I was experimenting with solutions that would enable users to resize the width of the columns of a SharePoint view. As you might know, SharePoint calculates the width of columns automatically, and the result is not always optimal. Although it’s relative easy to set the width using SharePoint Designer I was looking for a method that one can use through the web UI, without any external applications.

One of the methods I’ve tried out was to attach a keyboard event handler to the div element of the field header caption. I chose the div, and not the parent th element, because other event handlers were already registered for th, and I didn’t want to interfere with them. When the field caption has the focus (see image below), and the user presses Shift + Left arrow keys, the width of the column is decreased. When the user presses Shift + Right arrow keys, the width of the column is increased.


I used jQuery and the following script (injected through the Content Editor Web Part into the view page) to achieve the goal:

Code Snippet
  1. <script src=”/_layouts/jQuery/jquery-1.8.3.min.js”></script>
  2. <script language=”ecmascript” type=”text/ecmascript”>
  3. $(document).ready(startScript);
  4. function startScript() {
  5.     $(‘’).keydown(function (e) {
  6.         if (e.keyCode != 16) {
  7.             var widthChange = 20;
  8.             var th = $(e.srcElement).closest(‘th’);
  9.             var widthTh = $(th).width();
  10.             if (e.keyCode == 37) {
  11.                 $(th).width(widthTh – widthChange);
  12.             }
  13.             else if (e.keyCode == 39) {
  14.                 $(th).width(widthTh + widthChange);
  15.             }
  16.         }
  17.     });
  18. }
  19. </script>

The result was quite promising, however it has a definitive shortage as well: the settings are not persisted, so the user has to repeat the resizing each time. Theoretically we could store the customized widths in cookies or in SharePoint lists (per user), and reapply the settings on each page load.

May 25, 2013

Updating list views from PowerShell

Filed under: PowerShell, SP 2010, Views — Tags: , , — Peter Holpar @ 08:22

Recently I had to update a set of SharePoint list views. All of the views were the default “All Items” views of various lists, or views derived from this kind of view. That means, all of the views were ordered by the ID of the items.

My task was to create a solution for altering the “order by” field for these views to the Title field (or other arbitrary text fields). Since there are a lot of views and several environments (developer, test, production) for the alteration, I decided to create a PowerShell script to help the automation of the process. Originally I planned to use regular expressions for replacing the Name attribute of the FieldRef node, but finally I chose the XPath way. Here is the resulting function I used:

function updateView($list, $viewName, $orderByField)

  Write-Host "Updating ‘$viewName’ view of list ‘$list’ to be ordered by field ‘$orderByField’"
  $view = $list.Views[$viewName]
  [xml]$query = $view.Query
  $node = $query.selectSingleNode("//OrderBy/FieldRef")
  Write-Host // Original value was $node.GetAttribute("Name")
  $node.SetAttribute("Name", $orderByField)
  $view.Query = $query.OuterXml
  Write-Host Update finished.

And here is a sample for usage:

$site = Get-SPSite("http://MySharpointSite&quot;)
$web = $site.OpenWeb()
$list = $web.Lists["MyList"]
updateView $list "All Items" "Title"

Note, that this script is intended to update views having a well-formed CAML / XML value in the Query property, and a single “order by” field in that query. If you have no OrderBy node in the CAML query of your views, or have more than one, feel free to extend my solution.

October 5, 2011

Creating a view that filters list items based on user permissions using CAML

Filed under: Bugs, CAML, SP 2010, Views — Tags: , , , — Peter Holpar @ 23:17

Recently I got a task that was about creating a SharePoint view that displays only list items that the logged-in user has editor permissions for. Sounds a bit tough at first, but if you have ever heard about the PermMask field (not very well documented, but you can read more about that here and here) then it gives you some hope.

So I’ve created a test list (called SecTest) and created two items. One of my test users has admin rights, the second one has editor permission for the first item and read permission for the second one. The list inherits the permissions of the parent site, the permission inheritance is broken for the second item.

First I’ve tried to use SharePoint designer to customize the existing All Items view of the list.

Added the PermMask to the FieldRefs:

<FieldRef Name="PermMask" />

And created the filter part for the Query:

        <FieldRef Name=’PermMask’ />
        <Value Type=’Computed’>somevalue</Value>

(I’ve tried different permission mask values, all with the same result.)

Saving the view in SPD resulted the following warning:


Pressing the Details I’ve got the following message:

soap:ServerException of type ‘Microsoft.SharePoint.SoapServer.SoapServerException’ was thrown.0x80131904


(I’ve tried to use Text instead of Computed as the Type attribute of the Value in the Where section of the CAML expression, but it did not help.)

Checking the view in the browser produced an error as well.

I’ve found the following related error message in the SharePoint log (one for the saving in SPD and another one for opening the view in IE):

System.Data.SqlClient.SqlException: Conversion failed when converting the nvarchar value ‘0xb008431061’ to data type int.     at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)     at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)     at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)     at System.Data.SqlClient.SqlDataReader.HasMoreRows()     at System.Data.SqlClient.SqlDataReader.ReadInternal(Boolean setTimeout)     at Microsoft.SharePoint.SPSqlClient.ExecuteQueryInternal(Boolean retryfordeadlock)     at Microsoft.SharePoint.SPSqlClient.ExecuteQuery(Boolean retryfordeadl…    
Unable to execute query: Error 0x80131904    
Unknown SPRequest error occurred. More information: 0x80131904    

FYI, the hexadecimal value causing the conversion error (see the bolded section above, 0xb008431061 = 756052856929 in decimal) corresponds the permission mask of the Read permission level (see the value of the PermMaskfield in the Roles table of the content database, where in the record where Title is Read, that is SELECT PermMask FROM [YourContentDB].[dbo].[Roles] WHERE Title = ‘Read’)

Next, I’ve tried the same from code using a test console application:

  1. SPList list = web.Lists["SecTest"];
  2. SPQuery query = new SPQuery();
  3. // tried with and without these lines
  4. //query.IncludeAllUserPermissions = true;
  5. //query.IncludePermissions = true;
  6. query.Query = "<Where><Eq><FieldRef Name='PermMask' /><Value Type='Computed'>0xb008431061</Value></Eq></Where>";
  7. query.ViewFields = "<ViewFields><FieldRef Name='PermMask' /></ViewFields>";
  9. SPListItemCollection items = list.GetItems(query);
  11. // exception is thrown at the next line
  12. foreach (SPItem item in items)
  13. {
  14.     Console.WriteLine(item["PermMask"]);
  15. }

The code produced the same error.

I’ve started SQL Server Profiler to create a trace of the SQL side during the code execution. The trace captured the following errors:

Error: 245, Severity: 16, State: 1
User Error Message
Conversion failed when converting the nvarchar value ‘0xb008431061’ to data type int.

The source of the error was the following parameterized query:

exec sp_executesql N’DECLARE @DocParentIdForRF uniqueidentifier SELECT TOP 1 @DocParentIdForRF = Docs.Id FROM Docs WHERE Docs.SiteId = @SITEID AND Docs.DirName = @FDN AND Docs.LeafName = @FLN;   SELECT ScopeId, Acl, AnonymousPermMask FROM Perms WITH (INDEX=Perms_Url) WHERE SiteId=@SITEID AND DelTransId = 0x AND ScopeId IN (SELECT tp_ScopeId FROM Lists WITH (NOLOCK) WHERE tp_WebId=@L7 AND tp_ID = @L2 UNION SELECT t1.ScopeId FROM UserData INNER JOIN Docs AS t1 WITH(NOLOCK) ON (UserData.[tp_ID]=t1.[DoclibRowId]) AND (UserData.[tp_RowOrdinal] = 0) AND (t1.SiteId=UserData.tp_SiteId) AND (t1.SiteId = @SITEID) AND (t1.ParentId = UserData.tp_ParentId) AND (t1.Id = UserData.tp_DocId) AND (UserData.tp_ListId = @L2) WHERE ( (UserData.tp_Level = 1) ) AND (UserData.tp_SiteId=@SITEID AND (UserData.tp_ParentId=@DocParentIdForRF)) AND (UserData.tp_RowOrdinal=0) AND ((UserData.[tp_ID] = N”0xb008431061”) AND t1.SiteId=@SITEID AND (t1.ParentId=@DocParentIdForRF))) OPTION (MAXDOP 1)’,N’@LFFP uniqueidentifier,@SITEID uniqueidentifier,@L2 uniqueidentifier,@FDN nvarchar(4000),@FLN nvarchar(4000),@LISTID uniqueidentifier,@RequestGuid uniqueidentifier,@L7 uniqueidentifier’,@LFFP=’00000000-0000-0000-0000-000000000000′,@SITEID=’D2F4F094-9626-4B84-87D3-260B6B9213EF’,@L2=’C0768FD2-B3D6-478E-ADF3-4A1550DF4CBA’,@FDN=N’Lists’,@FLN=N’SecTest’,@LISTID=’C0768FD2-B3D6-478E-ADF3-4A1550DF4CBA’,@RequestGuid=’E75EC6C1-5CDE-4368-B55C-A9AAA661E539′,@L7=’3ED0DC82-1AC6-4A29-A177-56DA059B2EF8′

I’ve found that removing the bolded condition above helps to run the query without any exception.

In my case the result was (or would be?)  the next one:


The ScopeId field identifies the entity the permission is set on. See the Perms table in the content DB to decode it. In my case the first row is related to the root web, the second on is to the second item in the list, for which we set individual permissions. The Acl field stores the access control list for the entity in a binary format.

It seems that if you include the PermMask field in the CAML query, SharePoint tries to read up the related permission settings but fails due to a bug.

I’ve tried to locate the source of the issue, but was not fully successful. From the stack trace it seems that the ExecuteQueryInternal method of the internal SPSqlClient class (Microsoft.SharePoint namespace, Microsoft.SharePoint assembly) is called to run the SQL query, this method calls the ExecuteReader method of the Microsoft.SharePoint.Utilities.SqlSession, but in the ReadInternal method of System.Data.SqlClient.SqlDataReader the exception is thrown. However I’ve not found the method where the query is incorrectly assembled. I assume it is related to the SetCommand method of the SPSqlClient class but I’ve not yet found the caller of the method. The signature of the method (IntPtr queryText, uint lenQueryText, bool isStoredProc) and the Marshal.PtrToStringUni call within the method suggest that the caller might be even an unmanaged code. See the similar SetCommand method of the SPSqlClientClass class (Microsoft.SharePoint.Library namespace, Microsoft.SharePoint.Library assembly):

[MethodImpl(MethodImplOptions.InternalCall, MethodCodeType=MethodCodeType.Runtime)]
public virtual extern void SetCommand([In] IntPtr pstrQueryText, [In] uint cchQueryText, [In] bool bStoredProcedureSyntax);

Very likely that I have to find an alternative approach to create my permission based view. I have some further ideas, but more about them in a possible post later.

September 14, 2011

Conditional formatting of totals in a view

Filed under: Conditional formatting, SP 2010, Totals, Views — Tags: , , , — Peter Holpar @ 23:32

Applying conditional formatting for a list view is not hard at all using SharePoint Designer. Most of the samples on the web shows how to conditionally format rows or individual cells, but we can format total values either.

In this post I will continue to work on my former example about moving totals to the bottom of the view.

The goal is to apply a red background for the sum of actual numbers if it is below the sum of planned values, and for the maximal value of finished dates if it is over the maximum of due dates.

First, select the total you want to format, then from Conditional Formatting choose Format Selection.


Start the advanced condition editor through the Advanced… button, and apply the following XPath expression:

sum($Rows/@ActualNumber) < sum($Rows/@PlannedNumber)


Click OK, then set the red background by clicking Set Stlye, finally click OK once more.

SPD generates the following, quite complex code for this simple conditional formatting:

  1. <xsl:template match="FieldRef[@Name='ActualNumber']" mode="aggregate" ddwrt:dvt_mode="body" ddwrt:ghost="">
  2.   <xsl:param name="Rows" select="."/>
  3.   <xsl:param name="GroupLevel" select="1"/>
  4.   <td class="ms-vb2">
  5.     <xsl:variable name="fieldName" select="@Name"/>
  6.     <xsl:if test="$XmlDefinition/Aggregations/FieldRef[@Name=$fieldName]">
  7.       <xsl:variable name="title">
  8.         <xsl:choose>
  9.           <xsl:when test="$XmlDefinition/Aggregations/FieldRef[@Name=$fieldName]/@Type='COUNT'">
  10.             <xsl:value-of select="'Count'"/>
  11.           </xsl:when>
  12.           <xsl:when test="$XmlDefinition/Aggregations/FieldRef[@Name=$fieldName]/@Type='SUM'">
  13.             <xsl:value-of select="'Sum'"/>
  14.           </xsl:when>
  15.           <xsl:when test="$XmlDefinition/Aggregations/FieldRef[@Name=$fieldName]/@Type='AVG'">
  16.             <xsl:value-of select="'Average'"/>
  17.           </xsl:when>
  18.           <xsl:when test="$XmlDefinition/Aggregations/FieldRef[@Name=$fieldName]/@Type='MAX'">
  19.             <xsl:value-of select="'Maximum'"/>
  20.           </xsl:when>
  21.           <xsl:when test="$XmlDefinition/Aggregations/FieldRef[@Name=$fieldName]/@Type='MIN'">
  22.             <xsl:value-of select="'Minimum'"/>
  23.           </xsl:when>
  24.           <xsl:when test="$XmlDefinition/Aggregations/FieldRef[@Name=$fieldName]/@Type='STDEV'">
  25.             <xsl:value-of select="'Std Deviation'"/>
  26.           </xsl:when>
  27.           <xsl:when test="$XmlDefinition/Aggregations/FieldRef[@Name=$fieldName]/@Type='VAR'">
  28.             <xsl:value-of select="'Variance'"/>
  29.           </xsl:when>
  30.           <xsl:otherwise>
  31.             <xsl:value-of select="'Count'"/>
  32.           </xsl:otherwise>
  33.         </xsl:choose>
  34.       </xsl:variable>
  35.       <xsl:variable name="aggregateLevel">
  36.         <xsl:choose>
  37.           <xsl:when test="$GroupLevel = 1">.agg</xsl:when>
  38.           <xsl:when test="$GroupLevel = 2">.agg2</xsl:when>
  39.           <xsl:otherwise/>
  40.         </xsl:choose>
  41.       </xsl:variable>
  42.       <xsl:variable name="aggregateName">
  43.         <xsl:choose>
  44.           <xsl:when test="$XmlDefinition/Aggregations/FieldRef[@Name=$fieldName]/@Type='COUNT'">
  45.             <xsl:value-of select="concat(@Name, '.COUNT', $aggregateLevel)"/>
  46.           </xsl:when>
  47.           <xsl:when test="$XmlDefinition/Aggregations/FieldRef[@Name=$fieldName]/@Type='SUM'">
  48.             <xsl:value-of select="concat(@Name, '.SUM', $aggregateLevel)"/>
  49.           </xsl:when>
  50.           <xsl:when test="$XmlDefinition/Aggregations/FieldRef[@Name=$fieldName]/@Type='AVG'">
  51.             <xsl:value-of select="concat(@Name, '.AVG', $aggregateLevel)"/>
  52.           </xsl:when>
  53.           <xsl:when test="$XmlDefinition/Aggregations/FieldRef[@Name=$fieldName]/@Type='MAX'">
  54.             <xsl:value-of select="concat(@Name, '.MAX', $aggregateLevel)"/>
  55.           </xsl:when>
  56.           <xsl:when test="$XmlDefinition/Aggregations/FieldRef[@Name=$fieldName]/@Type='MIN'">
  57.             <xsl:value-of select="concat(@Name, '.MIN', $aggregateLevel)"/>
  58.           </xsl:when>
  59.           <xsl:when test="$XmlDefinition/Aggregations/FieldRef[@Name=$fieldName]/@Type='STDEV'">
  60.             <xsl:value-of select="concat(@Name, '.STDEV', $aggregateLevel)"/>
  61.           </xsl:when>
  62.           <xsl:when test="$XmlDefinition/Aggregations/FieldRef[@Name=$fieldName]/@Type='VAR'">
  63.             <xsl:value-of select="concat(@Name, '.VAR', $aggregateLevel)"/>
  64.           </xsl:when>
  65.           <xsl:otherwise>
  66.             <xsl:value-of select="concat(@Name, '.COUNT', $aggregateLevel)"/>
  67.           </xsl:otherwise>
  68.         </xsl:choose>
  69.       </xsl:variable>
  70.       <xsl:if test="not($title='')">
  71.         <nobr>
  72.           <b>
  73.             <span>
  74.               <xsl:attribute name="style">
  75.                 <xsl:if test="sum($Rows/@ActualNumber) &lt; sum($Rows/@PlannedNumber)" ddwrt:cf_explicit="1">background-color: #FF0000;</xsl:if>
  76.               </xsl:attribute>
  77.               <xsl:value-of select ="$title"/>=
  78.               <xsl:value-of select="$Rows/@*[name()=$aggregateName]" />
  79.             </span>
  80.           </b>
  81.         </nobr>
  82.       </xsl:if>
  83.     </xsl:if>
  84.   </td>
  85. </xsl:template>

Fortunately we can make it a lot simpler, just should analyze the variables and conditions.

In our case the value of the fieldName variable is the name of the field, stored at the Name attribute, that is ActualNumber (it should be, as we applied our template with the filter FieldRef[@Name=’ActualNumber’]).

<xsl:variable name="fieldName" select="@Name"/>

To get the values used during the XSL transformations, we should apply some specific XSL templates, as I’ve done in my former post.

You can get the XML result returned by the query if you apply the following template:

  1. <xsl:template match="/">
  2.   <xmp>
  3.     <xsl:copy-of select="*"/>
  4.   </xmp>
  5. </xsl:template>

This is the resulting XML. Note, that it contains the aggregated values in each rows, that is pretty redundant, but provides an easy way to access the totals:

  1. <dsQueryResponse ViewStyleID="" BaseViewID="1" TemplateType="100" RowLimit="30">
  2.   <Rows>
  3.     <Row ID="1" PermMask="0x7fffffffffffffff" Attachments="0" Title="Item 1" FileLeafRef="1_.000" FileLeafRef.Name="1_" FileLeafRef.Suffix="000" FSObjType="0" Created_x0020_Date="0;#2011-08-31 22:57:36" Created_x0020_Date.ifnew="" FileRef="/Lists/TotalList/1_.000" FileRef.urlencode="%2FLists%2FTotalList%2F1%5F%2E000" FileRef.urlencodeasurl="/Lists/TotalList/1_.000" File_x0020_Type="" HTML_x0020_File_x0020_Type.File_x0020_Type.mapall="icgen.gif||" HTML_x0020_File_x0020_Type.File_x0020_Type.mapcon="" HTML_x0020_File_x0020_Type.File_x0020_Type.mapico="icgen.gif" ContentTypeId="0x0100AAC7CF7AEC7754468D8B7EBC03DED857" PlannedNumber="12" PlannedNumber.="12.0000000000000" ActualNumber="34" ActualNumber.="34.0000000000000" DueDate="8/18/2011" FinishedDate="9/3/2011" PlannedNumber.SUM="175" DueDate.MAX="9/2/2011" FinishedDate.MAX="9/3/2011" ActualNumber.SUM="145"></Row>
  4.     <Row ID="2" PermMask="0x7fffffffffffffff" Attachments="0" Title="Item 2" FileLeafRef="2_.000" FileLeafRef.Name="2_" FileLeafRef.Suffix="000" FSObjType="0" Created_x0020_Date="0;#2011-08-31 22:58:07" Created_x0020_Date.ifnew="" FileRef="/Lists/TotalList/2_.000" FileRef.urlencode="%2FLists%2FTotalList%2F2%5F%2E000" FileRef.urlencodeasurl="/Lists/TotalList/2_.000" File_x0020_Type="" HTML_x0020_File_x0020_Type.File_x0020_Type.mapall="icgen.gif||" HTML_x0020_File_x0020_Type.File_x0020_Type.mapcon="" HTML_x0020_File_x0020_Type.File_x0020_Type.mapico="icgen.gif" ContentTypeId="0x0100AAC7CF7AEC7754468D8B7EBC03DED857" PlannedNumber="32" PlannedNumber.="32.0000000000000" ActualNumber="24" ActualNumber.="24.0000000000000" DueDate="9/2/2011" FinishedDate="8/25/2011" PlannedNumber.SUM="175" DueDate.MAX="9/2/2011" FinishedDate.MAX="9/3/2011" ActualNumber.SUM="145"></Row>
  5.     <Row ID="3" PermMask="0x7fffffffffffffff" Attachments="0" Title="Item 3" FileLeafRef="3_.000" FileLeafRef.Name="3_" FileLeafRef.Suffix="000" FSObjType="0" Created_x0020_Date="0;#2011-08-31 22:58:27" Created_x0020_Date.ifnew="" FileRef="/Lists/TotalList/3_.000" FileRef.urlencode="%2FLists%2FTotalList%2F3%5F%2E000" FileRef.urlencodeasurl="/Lists/TotalList/3_.000" File_x0020_Type="" HTML_x0020_File_x0020_Type.File_x0020_Type.mapall="icgen.gif||" HTML_x0020_File_x0020_Type.File_x0020_Type.mapcon="" HTML_x0020_File_x0020_Type.File_x0020_Type.mapico="icgen.gif" ContentTypeId="0x0100AAC7CF7AEC7754468D8B7EBC03DED857" PlannedNumber="131" PlannedNumber.="131.000000000000" ActualNumber="87" ActualNumber.="87.0000000000000" DueDate="8/13/2011" FinishedDate="8/19/2011" PlannedNumber.SUM="175" DueDate.MAX="9/2/2011" FinishedDate.MAX="9/3/2011" ActualNumber.SUM="145"></Row>
  6.   </Rows>
  7. </dsQueryResponse>

There are a lot of references for $XmlDefinition, we can check its value by applying this template:

  1. <xsl:template match="/">
  2.   <xmp>
  3.     <xsl:copy-of select="$XmlDefinition"/>
  4.   </xmp>
  5. </xsl:template>

And it is the output:

  1. <View Name="{288E815A-F55D-49DC-BF6C-06F46C5A4719}" DefaultView="TRUE" MobileView="TRUE" MobileDefaultView="TRUE" Type="HTML" DisplayName="All Items" Url="/Lists/TotalList/AllItems.aspx" Level="1" BaseViewID="1" ContentTypeID="0x" ImageUrl="/_layouts/images/generic.png">
  2.   <Query>
  3.     <OrderBy>
  4.       <FieldRef Name="ID"></FieldRef>
  5.     </OrderBy>
  6.   </Query>
  7.   <ViewFields>
  8.     <FieldRef Name="Attachments" Type="Attachments" FieldType="Attachments" DisplayName="Attachments" ID="67df98f4-9dec-48ff-a553-29bece9c5bf4"></FieldRef>
  9.     <FieldRef Name="LinkTitle" Type="Computed" FieldType="Computed" DisplayName="Title" ID="82642ec8-ef9b-478f-acf9-31f7d45fbc31" ClassInfo="Menu" ListItemMenu="TRUE" ListItemMenuAllowed="Required" LinkToItemAllowed="Prohibited"></FieldRef>
  10.     <FieldRef Name="PlannedNumber" Type="Number" FieldType="Number" DisplayName="PlannedNumber" ID="02ee2591-a1e4-4e18-b6d9-fe17eeca6758"></FieldRef>
  11.     <FieldRef Name="ActualNumber" Type="Number" FieldType="Number" DisplayName="ActualNumber" ID="4cc7c15e-c05a-48fb-a95a-9e4b4e850928"></FieldRef>
  12.     <FieldRef Name="DueDate" Type="DateTime" FieldType="DateTime" DisplayName="DueDate" ID="3f90a631-89dd-45c1-959a-e519c5d1b787"></FieldRef>
  13.     <FieldRef Name="FinishedDate" Type="DateTime" FieldType="DateTime" DisplayName="FinishedDate" ID="df22e8d1-6220-410c-8ce9-d820ff471673"></FieldRef>
  14.   </ViewFields>
  15.   <RowLimit Paged="TRUE">30</RowLimit>
  16.   <Aggregations Value="On">
  17.     <FieldRef Name="PlannedNumber" Type="SUM"></FieldRef>
  18.     <FieldRef Name="DueDate" Type="MAX"></FieldRef>
  19.     <FieldRef Name="FinishedDate" Type="MAX"></FieldRef>
  20.     <FieldRef Name="ActualNumber" Type="SUM"></FieldRef>
  21.   </Aggregations>
  22.   <Toolbar Type="Standard"></Toolbar>
  23.   <List Direction="none" TemplateType="100" title="TotalList" description="" basetype="0" RootFolder="" version="4" name="{BBD560B6-5ED0-495D-AB88-AA1600584DD1}" moderatedlist="0" SendToLocationName="" SendToLocationUrl="" WriteSecurity="1" EnableMinorVersions="0" VersioningEnabled="0" ForceCheckout="0" WorkflowsAssociated="0" DefaultItemOpen="1" RecycleBinEnabled="1" ExternalDataList="0" OfficialFileNames="" EnableSyndication="1" enablecontenttypes="0"></List>
  24. </View>

As you can see this is the XML definition for the view. Note, that the Aggregations node contains the type of totals per field in the Type attribute.

Based on these XMLs we can get the values for the variables used in the XSL. First, let’s check the title variable.

  1. <xsl:variable name="title">
  2.   <xsl:choose>
  3.     <xsl:when test="$XmlDefinition/Aggregations/FieldRef[@Name=$fieldName]/@Type='COUNT'">
  4.       <xsl:value-of select="'Count'"/>
  5.     </xsl:when>
  6.     <xsl:when test="$XmlDefinition/Aggregations/FieldRef[@Name=$fieldName]/@Type='SUM'">
  7.       <xsl:value-of select="'Sum'"/>
  8.     </xsl:when>
  9.     <xsl:when test="$XmlDefinition/Aggregations/FieldRef[@Name=$fieldName]/@Type='AVG'">
  10.       <xsl:value-of select="'Average'"/>
  11.     </xsl:when>
  12.     <xsl:when test="$XmlDefinition/Aggregations/FieldRef[@Name=$fieldName]/@Type='MAX'">
  13.       <xsl:value-of select="'Maximum'"/>
  14.     </xsl:when>
  15.     <xsl:when test="$XmlDefinition/Aggregations/FieldRef[@Name=$fieldName]/@Type='MIN'">
  16.       <xsl:value-of select="'Minimum'"/>
  17.     </xsl:when>
  18.     <xsl:when test="$XmlDefinition/Aggregations/FieldRef[@Name=$fieldName]/@Type='STDEV'">
  19.       <xsl:value-of select="'Std Deviation'"/>
  20.     </xsl:when>
  21.     <xsl:when test="$XmlDefinition/Aggregations/FieldRef[@Name=$fieldName]/@Type='VAR'">
  22.       <xsl:value-of select="'Variance'"/>
  23.     </xsl:when>
  24.     <xsl:otherwise>
  25.       <xsl:value-of select="'Count'"/>
  26.     </xsl:otherwise>
  27.   </xsl:choose>
  28. </xsl:variable>

Since for the ActualNumber field the Type attribute is “SUM” the following condition will match:

  1. <xsl:when test="$XmlDefinition/Aggregations/FieldRef[@Name=$fieldName]/@Type='SUM'">
  2.   <xsl:value-of select="'Sum'"/>
  3. </xsl:when>

It means the value of the title is ‘Sum’.

  1. <xsl:variable name="aggregateLevel">
  2.   <xsl:choose>
  3.     <xsl:when test="$GroupLevel = 1">.agg</xsl:when>
  4.     <xsl:when test="$GroupLevel = 2">.agg2</xsl:when>
  5.     <xsl:otherwise/>
  6.   </xsl:choose>
  7. </xsl:variable>

Value of the $GroupLevel variable is 0 (there is no grouping in our view) as we can check using this template:

  1. <xsl:template match="FieldRef[@Name='ActualNumber']" mode="aggregate" ddwrt:dvt_mode="body" ddwrt:ghost="">
  2.   <xsl:param name="Rows" select="."/>
  3.   <xsl:param name="GroupLevel" select="1"/>
  4.   <xmp>
  5.     <xsl:copy-of select="$GroupLevel"/>
  6.   </xmp>
  7. </xsl:template>

So the value of the aggregateLevel variable is “”, thanks to the empty otherwise element.

From the following condition:

  1. <xsl:variable name="aggregateName">
  2.         <xsl:choose>
  3.           <xsl:when test="$XmlDefinition/Aggregations/FieldRef[@Name=$fieldName]/@Type='COUNT'">
  4.             <xsl:value-of select="concat(@Name, '.COUNT', $aggregateLevel)"/>
  5.           </xsl:when>
  6.           <xsl:when test="$XmlDefinition/Aggregations/FieldRef[@Name=$fieldName]/@Type='SUM'">
  7.             <xsl:value-of select="concat(@Name, '.SUM', $aggregateLevel)"/>
  8.           </xsl:when>
  9.           <xsl:when test="$XmlDefinition/Aggregations/FieldRef[@Name=$fieldName]/@Type='AVG'">
  10.             <xsl:value-of select="concat(@Name, '.AVG', $aggregateLevel)"/>
  11.           </xsl:when>
  12.           <xsl:when test="$XmlDefinition/Aggregations/FieldRef[@Name=$fieldName]/@Type='MAX'">
  13.             <xsl:value-of select="concat(@Name, '.MAX', $aggregateLevel)"/>
  14.           </xsl:when>
  15.           <xsl:when test="$XmlDefinition/Aggregations/FieldRef[@Name=$fieldName]/@Type='MIN'">
  16.             <xsl:value-of select="concat(@Name, '.MIN', $aggregateLevel)"/>
  17.           </xsl:when>
  18.           <xsl:when test="$XmlDefinition/Aggregations/FieldRef[@Name=$fieldName]/@Type='STDEV'">
  19.             <xsl:value-of select="concat(@Name, '.STDEV', $aggregateLevel)"/>
  20.           </xsl:when>
  21.           <xsl:when test="$XmlDefinition/Aggregations/FieldRef[@Name=$fieldName]/@Type='VAR'">
  22.             <xsl:value-of select="concat(@Name, '.VAR', $aggregateLevel)"/>
  23.           </xsl:when>
  24.           <xsl:otherwise>
  25.             <xsl:value-of select="concat(@Name, '.COUNT', $aggregateLevel)"/>
  26.           </xsl:otherwise>
  27.         </xsl:choose>
  28.       </xsl:variable>

the next will match:

  1. <xsl:when test="$XmlDefinition/Aggregations/FieldRef[@Name=$fieldName]/@Type='SUM'">
  2.   <xsl:value-of select="concat(@Name, '.SUM', $aggregateLevel)"/>
  3. </xsl:when>

So the value of aggregateName will be ActualNumber.SUM.

Using the values determined above we can simplify the original XSL like this:

  1. <xsl:template match="FieldRef[@Name='ActualNumber']" mode="aggregate" ddwrt:dvt_mode="body" ddwrt:ghost="">
  2.   <xsl:param name="Rows" select="."/>  
  3.   <td class="ms-vb2">
  4.         <nobr>
  5.           <b>
  6.             <span>
  7.               <xsl:attribute name="style">
  8.                 <xsl:if test="sum($Rows/@ActualNumber) &lt; sum($Rows/@PlannedNumber)" ddwrt:cf_explicit="1">background-color: #FF0000;</xsl:if>
  9.               </xsl:attribute>
  10.               Sum=<xsl:value-of select="$Rows/@ActualNumber.SUM" />
  11.             </span>
  12.           </b>
  13.         </nobr>
  14.   </td>
  15. </xsl:template>

We can also use the following condition for the same result:

$Rows/@ActualNumber.SUM &lt; $Rows/@PlannedNumber.SUM

BTW, the aggregated values are displayed in SPD as well:


Even from the basic condition editor, after selecting More Fields…



Note: If you would like to overwrite the title for the total, simply replace “Sum=” in the XSL template with the new value, like “Total:”.

Having the simplified version, it is quite easy to create the required conditional formatting for the date totals:

  1. <xsl:template match="FieldRef[@Name='FinishedDate']" mode="aggregate" ddwrt:dvt_mode="body" ddwrt:ghost="">
  2.   <xsl:param name="Rows" select="."/>
  3.   <td class="ms-vb2">
  4.     <nobr>
  5.       <b>
  6.         <span>
  7.           <xsl:attribute name="style">
  8.             <xsl:if test="ddwrt:DateTimeTick(ddwrt:GenDisplayName(string($Rows/@FinishedDate.MAX))) &gt; ddwrt:DateTimeTick(ddwrt:GenDisplayName(string($Rows/@DueDate.MAX)))" ddwrt:cf_explicit="1">background-color: #FF0000;</xsl:if>
  9.           </xsl:attribute>
  10.           Maximum=<xsl:value-of select="$Rows/@FinishedDate.MAX" />
  11.         </span>
  12.       </b>
  13.     </nobr>
  14.   </td>
  15. </xsl:template>

The following image illustrates the result of the conditional formatting of the aggregated values:


August 31, 2011

Moving totals to the bottom of the view

Filed under: SP 2010, Totals, Views — Tags: , , — Peter Holpar @ 22:43

In my last post I showed you how to add totals to your views programmatically.

Having the totals created, I’ve added a few items to my list that looks like this now:


As you can see, totals are displayed at the top of the view. That is really not the same the users got used to in Excel (or in the case of paper based tables), where aggregations usually displayed at the bottom of the sheet. So it is not a surprise that customers often request us to move the totals to the bottom of the view.

I wish all requirement would be so easy to implement!

In a former post I showed you how to create customized versions of the main.xsl and vwstyles.xsl files to alter view rendering. If you are ready with cloning that files and set the view in SharePoint Designer to use the new the main.xsl as described in that post, open the copy of the vwstyles.xsl and look for the comment:

<!– total first –>

The first occurrence you find is within a template:

<xsl:template match="View[ViewStyle/@ID=’14’]" mode="RenderView">

Find the second one that is in this template:

<xsl:template match="View" mode="RenderView">

Cut the following block:

  1. <!– total first –>
  2. <xsl:if test="Aggregations[not(@Value='Off')]/FieldRef">
  3.   <tr>
  4.     <xsl:if test="$HasExtraColumn">
  5.       <td/>
  6.     </xsl:if>
  7.     <xsl:if test="$InlineEdit">
  8.       <td width="1%"/>
  9.     </xsl:if >
  10.     <xsl:apply-templates mode="aggregate" select="ViewFields/FieldRef[not(@Explicit='TRUE')]">
  11.       <xsl:with-param name="Rows" select="$AllRows"/>
  12.       <xsl:with-param name="GroupLevel" select="0"/>
  13.     </xsl:apply-templates>
  14.   </tr>
  15. </xsl:if>

and paste it to the end of the template block. I suggest to replace the word first to last in the text of the comment.

Save the file and run IISRESET if the customized vwstyles.xsl was already loaded by SharePoint.

The following screenshot shows the result:


August 30, 2011

Creating totals programmatically

Filed under: SP 2010, Totals, Views — Tags: , , — Peter Holpar @ 23:50

Displaying totals is a useful feature of views in SharePoint 2010 that enables users to create aggregations for specific type of fields (like text, date and numeric values) in the view. The types of aggregations available for a field depend on the field type. General discussion of totals are out of scope of this post, however, if you would like to learn more about totals, you can find information a bit more info about the feature here.

For the sake of this post, I’ve created a custom list and added two numerical and two date fields to it.

You could customize the view using the web UI to compute the maximum for the date and sum of the numeric values as illustrated below:


Sometimes we need to achieve the same result programmatically. To understand how the schema of the view is modified when we make the changes manually, I made the modifications through the UI, then checked the SchemaXml property of my view. The following nodes were added by the change:

  1. <Aggregations Value="On">
  2.   <FieldRef Name="PlannedNumber" Type="SUM"/>
  3.   <FieldRef Name="DueDate" Type="MAX"/>
  4.   <FieldRef Name="FinishedDate" Type="MAX"/>
  5.   <FieldRef Name="ActualNumber" Type="SUM"/>
  6. </Aggregations>

You can see that the type of the aggregation is set by the Type attribute of the FieldRef node, while the Name attribute refers to the field name in the view.

The SPView class contains two properties related to totals, the Aggregations and AggregationsStatus.

To set the totals we should run the following code:

  1. // view is a reference to the SPView instance
  2. // you would like to alter
  3. view.Aggregations = "<FieldRef Name='PlannedNumber' Type='SUM'/><FieldRef Name='DueDate' Type='MAX'/><FieldRef Name='FinishedDate' Type='MAX'/><FieldRef Name='ActualNumber' Type='SUM'/>";
  4. view.AggregationsStatus = "On";
  5. view.Update();

It is important to set the properties in the order illustrated above, since setting the Aggregations property creates the Aggregations node in the schema XML. If that node does not exist, setting the AggregationsStatus property throws an ArgumentException.

Note: There is a method called GetAggregationString that expects a single string parameter, the name of the field in the aggregations, and returns the type of the aggregation. In our case:


returns MAX.

December 20, 2009

Addressing the Edit View page from code

Filed under: SharePoint, Views — Tags: , — Peter Holpar @ 03:58

The Edit View page is easy to access from the user interface by clicking the Modify this View item in the menu.

It is a bit harder to get the correct URL programmatically. The following code snippet will show you how to solve this issue:

  1. SPView view = web.Lists["Tasks"].Views["All Tasks"];
  3. String url = String.Format("{0}/_layouts/ViewEdit.aspx?List={1}&View={2}&Source={3}",
  4.     SPContext.Current.Site.Url,
  5.     SPEncode.UrlEncode(view.ParentList.ID.ToString("B").ToUpper()),
  6.     SPEncode.UrlEncode(view.ID.ToString("B")).ToUpper(),
  7.     SPEncode.UrlEncode(SPEncode.UrlEncode(SPContext.Current.Web.Url + view.ServerRelativeUrl))).ToUpper();

In the example above I get the URL of the page that edits the All Tasks view in the Tasks list. Of course, if you need the URL of other views, you should replace the name of the list and the view.

Addressing a personal view page from code

Filed under: SharePoint, Views — Tags: , — Peter Holpar @ 03:44

When a shared view is created in SharePoint, a new .aspx file having the name of the view is created.

It’s a bit different, when you create a personal view. In this case a page called PersonalViews.aspx is used to display the view to the user. To enable the page to display different views for the same or to other users, a query string parameter called ShowWebPart is used to specify the GUID of the view in the {dddddddd-dddd-dddd-dddd-dddddddddddd} format.

Beyond this parameter, there is an additional query string parameter called PageView that has the constant value of Personal.

The following code snippet illustrates how to redirect the page to a specific personal list assuming you created one in the Tasks list and it is called My View.

  1. SPView myView = web.Lists["Tasks"].Views["My View"];
  2. if ((myView != null) && (myView.PersonalView))
  3. {
  4.     String urlToRedirect = String.Format("{0}?PageView=Personal&ShowWebPart={{{1}}}", myView.Url, myView.ID);
  5.     Response.Redirect(urlToRedirect);
  6.     // or you can use SPUtility.Redirect as well, like
  7.     // SPUtility.Redirect(urlToRedirect, SPRedirectFlags.Default, HttpContext.Current);
  8. }

Although we could use the constant PersonalViews.aspx view page name, I think it is a better practice to get the name (and the whole address) from the Url property of the view.

Create a free website or blog at