Power View and Marine Rescue on SP24

I will be presenting a session at SP24 on Power View using Marine Rescue Data.

https://www.sp24conf.com/2014-1/Conf/SP24S026/ConfPages/SessionRoom.aspx

The presentation is available here

Hope to see you online.

Posted in SharePoint | Tagged | Leave a comment

Office 365 at Marine Rescue Queensland

Read more about how Marine Rescue are using Office 365 to share documents and training materials to improve safety across Queensland – http://www.obs.com.au/AboutUs/Hot-Topics/Pages/Can-Office-365-Assist-Marine-Rescue-Queensland.aspx

Posted in SharePoint | Tagged , , | Leave a comment

Content Types and Hidden Columns

When working with list or libraries, it is often a requirement to have a column that can be set automatically, perhaps by a workflow, but which you do not want users to be able to manually change. When creating a content type, each site column can be set to Required, Optional or Hidden. This can be set at the content type level or at the library level.

If the site column is left as Optional and then the content type is added to the library, the column is correctly added to the library and can be used as required and set to Hidden through the content type within the library settings. This ensures it does not appear in forms so applies a degree of protection against users changing it manually.

If this content type is added to one or more lists or libraries and then the site column is set to hidden at the content type level then this setting will be inherited by all the lists or libraries using that content type.

However, if the site column is set to hidden at the content type level before the content type is added to the library then this is where a problem arises. The column is not visible from the list settings, nor in the content types in the list settings. It is not available in views nor in filters nor sorts. However, if you try to add the site column manually then you will find it is not available to be added – because it has actually been added already as part of the content type.

A logical thought at this point would be to go back to the content type level and change the site column to Optional or Required. Sadly changing this does not seem to be inherited by the list.

My response at this stage was to resort to PowerShell – because that can do anything right?

$web = Get-SPWeb
$list = $web.Lists["List Name"]
$col = $list.Fields["Column Name"]
$col.Hidden

This will show True – so our problem is that the column has been added as hidden. You may think this is what we wanted but in fact the problem is that the column is hidden at the list level rather than at the content type level.

The easy option seemed to be:
$col.Hidden = $false

Sadly this fails as it is not allowed.

Another thought was to try changing the content type within the list through PowerShell:

$ct = $list.ContentTypes["Content Type Name"]
$field = $ct.FieldLinks["Column Name"]
$field.Hidden = $false

This succeeds – but sadly makes no difference.

So what about deleting the offending column?

$col.Delete()

Nope – this fails too as you cannot delete a column which is hidden – but you cannot unhide it . . .

So we now have a column in our list that we cannot use as we want to and we cannot even delete using PowerShell – or can we?

After a bit of fiddling and some suggestions from Microsoft we finally found a way to get rid of the column:

$col.AllowDeletion = $true
$col.Sealed = $false
$col.Delete()
$list.Update()

Phew! Note that even though the column will say Sealed is False, you must set it to false as above – no idea why so if you have suggestions please let me know.

Microsoft has managed to reproduce this and is deciding whether or not it is a bug – I will keep you informed.

Posted in SharePoint | Tagged , , , , , , , , , | 1 Comment

Granular Permissions on SharePoint Libraries

In most circumstances the standard “Contribute” permission level hits the spot. It allows users to add new documents, edit existing ones and delete any no longer needed.

However, there are times when you may need something else. Perhaps you do not want users to be able to delete documents?

In SharePoint it is easy enough to create new permissions levels – I usually copy the Contribute and then delete the options I no longer want. CRUD is the acronym often associated with permission levels:

C = Create
R = Read
U = Update
D = Delete

Read will always be granted for one of the other levels so I generally just go for Create, Delete and Edit – edit being a more SharePoint consistent term than update.

All good so far, but what happens when you apply these different levels on a library?

You will hopefully already be familiar with the process of uploading a document into SharePoint; the way you select or drag the document, watch it whir away for a moment then get the form prompting you for any metadata, such as the Title column. Usually you fill this in and click save and hey presto there is your document in the library. Great!

If you grant a user Create permission only, you would probably expect that process to remain the same. Wrong!

The first bit all goes well and the form pops up. If the user then types in a Title for example, when they click save they get a permission error – but the document is actually in the library . . .

It seems this is actually a two stage process. The Create permission allows you to load the document into the library and it is only after this point that the form pops up – so at this stage you now need Edit permissions. This means that if you have required metadata fields, users who only have Create permissions will never be able to check them in, and users who only have Edit permissions will never be able to upload them.

There is another oddity that you should also be aware of related to the Delete permission level.

When you upload a document through the standard browser interface, if it has a trailing space in the file name eg ‘FILE .DOCX’ then SharePoint will automatically trim out that space and all will be fine. If you use the Save As method to get the above document into the library then it does not trim it. Now when a user who does not have Delete permissions tries to edit the metadata, SharePoint tries to automatically remove that offending space character – but for some reason it seems to try to delete the old document and replace it with the new one – so again that permission error comes up – though somewhat confusingly for the user, assuming they have edit permissions any metadata changes have been saved. Note that in this circumstance, SharePoint does not let you delete the trailing space through the standard form interface – essentially renaming the document. In fact you will not even be able to tell it is there. The easiest way around this seems to be to open the library in Explorer mode and from there you can see the trailing space and rename the document – always assuming you have permissions of course.

Have fun.

Posted in SharePoint | Tagged , , , , , , | 2 Comments

CQWP and XSLT – Part 2

So after a little prodding I finally decided to move this post up my priority list.

In Part 1 we looked at creating a custom version of the standard CQWP which uses our own XSLT files. Just as an aside, rather than export the CQWP and then open it to make changes, you can actually tell it to use your custom XSLT files through the properties settings in SPD – much easier.

So now we have our CQWP linked to the copies of the standard XSLT files and it is time to open them up to see what is inside. Start with the easy one and open up the copy of ItemStyle.xsl. I use SPD but it is just a text file so in theory could be done with any text editor. What you find is 600+ lines of stylesheet. Skip over the first few lines which is just defining namespaces and a few parameters and variables and you will get to a template.

<xsl:template name="Default" match="*" mode="itemstyle">

In total there are 14 templates in the standard ItemStyle file – if you exclude the one called HiddenSlots then they match the 13 options you get in the CQWP settings under Presentation->Styles->Item style
CQWPitemStyles

My first suggestion is to clear some space so you can focus on what is important. Delete all the existing templates and we will create our own. You can also delete the parameters and variables prior to the first template as we are not going to use those in this example. So all we are left with is the opening and closing stylesheet tags then add in the example template as below:

<xsl:stylesheet 
  version="1.0" 
  exclude-result-prefixes="x d xsl msxsl cmswrt"
  xmlns:x="http://www.w3.org/2001/XMLSchema" 
  xmlns:d="http://schemas.microsoft.com/sharepoint/dsp" 
  xmlns:cmswrt="http://schemas.microsoft.com/WebParts/v3/Publishing/runtime"
  xmlns:xsl="http://www.w3.org/1999/XSL/Transform" xmlns:msxsl="urn:schemas-microsoft-com:xslt">
 <xsl:template name="MyStyle" match="Row[@Style='MyStyle']" mode="itemstyle">
  <div class="item">
   <xsl:value-of select="@Title" />
  </div>
 </xsl:template>
</xsl:stylesheet>

You should hopefully be able to copy and paste this as required.

The name and the @Style must be identical – including case. You also cannot have spaces in these. This name/@Style will be what you see in the CQWP dropdown as above to choose the style you want your users to see. If you have worked through my other XSL posts then the rest will look very familiar to you. Save this file and go to the page with your custom CQWP on it. Edit the web part and check out the Item style dropdown as in the image above. You should not be able to actually dropdown as there will only be the one choice – which should be the style you have just created.

Now, connect set up the Query part of the CQWP to select from a list – ideally the same one we created for one of the earlier XSL posts to keep it simple. Do not bother filtering or sorting at this stage. Scroll back down the web part settings and just underneath the Item style option you should now see Fields to display – these are also referred to as Slots. In this example there will only be the one as we only used one field in our XSLT – @Title. Here you can choose which field you use to populate @Title in the XSLT. Again in the interests of simplicity, enter Title – which may be there already.

Save your page and preview it – you should now see the Titles of the items in your list – and if you check the underlying HTML you will see that they are in DIVs where the class=”item”.

Congratulations! You now have control of your CQWP and what it presents. You can add more templates which will offer more choices of styles and you can get more adventurous with the content you present within the templates.

I will plan to add some further parts to this series – and hopefully it will not take me so long to get around to it.

Aside | Posted on by | Tagged , , , , | Leave a comment

Excel REST meets XSL

Firstly, I would like to thank all those that came along to my sessions at the Australian and New Zealand SharePoint Conferences in Sydney and Auckland. I am a big fan of empowering end-users and wanted to show a realistic example of what was possible so the level of interest and number of relevant questions made it all worthwhile.

So, my scenario is that I have a country manager who wants to see a dashboard of his team performance against controlled targets. Given that I am in Australia I will stick with a local example so the team are state managers. The country manager is a competent Excel user and wants to keep as much control over the chart types etc as possible. The state managers should be able to input their results but not change their targets.

To start with we will create our Excel spread sheet – this will work fine with either 2010 or 2013, Excel or SharePoint in any combination.

Create a worksheet called Targets with headings for State, Q1, Q2, Q3 and Q4. Enter your data for each state. Eg QLD, 24, 28, 32, 26. I often use the randbetween() function to create dummy data quickly. Format as a table using your preferred style and name the table Targets.

Create another worksheet called Chart, in A1 type State and in B1 type one of your states eg QLD. Under the Formulas ribbon define a name for B1 called State.
From B2:E2 type Q1 to Q4. In A3 type Target and A4 type Actual. Now we want to get our target values from the Targets table depending on which state is entered into B1. Use a vlookup function in cell B3 as follows =vlookup(State, Targets, 2, FALSE). This will use the defined name State as the lookup value, search for it in the Targets table and return the second column (which is the target for Q1). FALSE means it needs an exact match. Copy this formula for Q2 to Q4 changing the column returned to be 3 to 5 accordingly.
Now enter some dummy data for your Actual values in row 4.

Last step is to create our chart. Select the data in cells A2:E4 and insert your preferred chart. Name the chart as Chart.

All done – you should now have something that looks like this:

Chart

If you change the state typed into B1 then the appropriate targets should automatically populate in row 3 and the chart redraw accordingly. If this is not the case, recap the previous steps.

Save your Excel file as States.xlsx and upload to your Docs library in SharePoint. Note that you will need Excel services to be running which also means you will need an Enterprise licence of SharePoint 2010 or 2013. If you are using Office 365 then all should be fine.
Now you have the file in SharePoint we can try out some Excel REST fun. Excel REST allows me to get data or objects from the Excel file using a URL. The URL is constructed as follows:
 http:// – the server name
 /_vti_bin/ExcelRest.aspx – the Excel REST page
 /Docs/States.xlsx – the path to your spread sheet from the site you make the call
 /model/Charts(‘Chart’) – the chart (or other object/model) we want to retrieve
The models available are:
 Charts
 Ranges
 Tables
 PivotTables
Try this out in your browser window. Type the URL using the elements as defined above:
Eg //server/site/_vti_bin/ExcelRest.aspx/Docs/States.xlsx/model/Charts('Chart')

You should now see the chart from your Excel file in your browser window. If not, then make sure you check you have the correct library name and file name and also the name of your chart in the Excel file. I have used everything from my example spread sheet above so if you have done anything differently then you will need to change the path or names to match.

The chart is returned as an image so this can be used in a variety of ways such as adding an image to a page and entering the URL as above.
So far so good, but we are only using the data within the spread sheet at the moment. To tell the spreadsheet to use different data we add parameters to our URL.
Eg //server/site/_vti_bin/ExcelRest.aspx/Docs/States.xlsx/model/Charts('Chart')?Ranges('Chart!B4')=40

Following on from the example above, this will set the value of the cell B4 in the spread sheet to 40 (the actual for Q1) and redraw the chart before returning the image.

Now if all you wanted was a chart from your list data then you could edit your page and insert a picture from address and use this as the URL. Easy. However, I did promise to mix this with some XSL to give you a dashboard so here goes.

I have created some custom XSL below which as you can see is pretty simple. It creates a table and then for each row put the Title in the first column and then an image in the second. The URL for the image is our REST call but with the values coming from our list columns.


<xsl:stylesheet version="1.0" exclude-result-prefixes="xsl" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
<xsl:template match="/">
<xsl:variable name="Rows" select="/dsQueryResponse/Rows/Row"/>
<table style="width:100%">
<xsl:for-each select="$Rows">
<xsl:call-template name="RowView" />
</xsl:for-each>
</table>
</xsl:template>
<xsl:template name="RowView">
<tr>
<td>
<xsl:value-of select="@Title" />
</td>
<td>
<img src="/sites/SPC2013/IV/_vti_bin/ExcelRest.aspx/Documents/States.xlsx/Model/Charts('Chart')?Ranges('Chart!B1')={@Title}&amp;Ranges('Chart!B4')={@Quarter1}&amp;Ranges('Chart!C4')={@Quarter2}&amp;Ranges('Chart!D4')={@Quarter3}&amp;Ranges('Chart!E4')={@Quarter4}" />
</td>
</tr>
</xsl:template>
</xsl:stylesheet>

Notice that the ampersands separating the URL parameters have to be escaped – ie &amp;
In my example, I have saved this stylesheet to /Style Library/custom/Charts.xsl.

So far so good – now we need a SharePoint list to apply this to. Create a list called States. Rename the Title column to State and create number columns for Quarter1, Quarter2, Quarter3 and Quarter4.

Interesting thing to note here is that if you try to abbreviate your column names to Q1 or Qtr1 you will have trouble with your XSL. For some reason SharePoint does not like column names which are less than 5 characters and so will encode the first character. This makes it pretty horrible in XSL so I suggest you stick with the column names as above.

Add some data to your list and make sure you use the same names for the States.

Now for the fun bit. Create a new view on your States list and call it Dashboard. No need to apply filters but make sure all the input columns are showing. Edit the page in the view and now edit the properties of the web part. In the Miscellaneous section add the URL to your XSL file in the XSL Link box.

ChartXSLlink

Finally the magic bit. Click OK and stop editing the page. You will now see the name of each state followed by a chart redrawn for the data from each item in your list.

RESTdashboard

Excel REST and XSL – what a great combination!
Download the blog post as PDF – Excel REST Meets XSLT
Download the spreadsheet example – States
Download the XSL stylesheet – Chart.xsl

Good luck and have fun.

Posted in SharePoint | Tagged , , , , , , , , , , | 3 Comments

SharePoint Conferences

Sorry if I have been a bit slack lately but hope to get back to some more posts soon.

In the meantime I have just been confirmed as speaking at the SharePoint Conferences in:

Sydney on 10 & 11 April 2013
Auckland on 16 & 17 April 2013

I will be presenting a couple of sessions on Information Visualisation so hope to see you there.

Dave

See below links to the presentations. Blog on Excel REST to follow soon.

SPC 2013 – Power View – Dave Paylor

SPC 2013 – Information Visualisation – Dave Paylor

Dave

Posted in SharePoint | Tagged , , , , , , | 6 Comments