Digital Workplace Conference 2018

I am honoured to have been selected to present at the Digital Workplace Conference, New Zealand in May 2018 and even more lucky to be co-presenting with Keith Williams, General Manager of Marine Rescue Queensland.

18612 STP Badges I'm speaking

We released a new Azure-based app in January and finally retired a rather dated ASP application (no not .NET) which was hosted on Windows Server 2003 and using SQL 2005.  Having served well for many years it was definitely starting to show it’s age.

Every time one of our rescue vessels goes in the water we capture details in our new app such as how much fuel was used, how many people we assisted and how long the vessel was at sea.  We also use it to keep track of around 1500 volunteers along with their training records.

Data quality has long been a challenge for us with most of our crew members much more interested in being at the helm of a vessel than behind a keyboard.  We intentionally took this into consideration when designing the new app and have employed a few different techniques to improve the data which is input.

A key example of this is giving something back.  I will try not to get on my soapbox but it has long been a personal crusade to provide something which shows where the data is going or how it is used.  Too often I have seen organisations demand their staff provide all sorts of data but it just disappears into a black hole.  There is very little incentive in that scenario to put any effort into ensuring the data is useful or accurate.

Almost every page in our new Portal (or is it porthole) contains some statistics from the database relevant to the that page.  I mentioned persons assisted above.  Showing the total number of persons assisted for the current month means that as soon as somebody enters a new activation they can see those statistics change so they know they are making a difference.

Encouraging this level of interest in the statistics means that the quality of the data is improving – which in turn means that the quality of the statistics is improving.  Now that is a great improvement cycle where everybody benefits.  If you come along to watch our session and learn more about this then do say hello!  Look forward to seeing you there.

Advertisements
Posted in Power BI, SharePoint | Tagged | Leave a comment

Pausing Long-Running PowerShell

I recently developed a PowerShell script that was going to run for a few days.

Given it was going to run for so long, I really wanted to be able to pause it if necessary to prevent having to start all over again. Not only that, but ideally I wanted others to be able to pause it too.

The script was running in bite-size batches so my initial thinking was to add a Start-Sleep at the start of each batch and look for keyboard input. However, this only works if it is running as a service account so others can log in to access the script window.

I decided to combine this concept with checking the content of a text file. If it contains YES then just carry on as usual, otherwise, sleep and then check again.

This means that anybody can edit the text file, change the contents and save it – and the script will gracefully pause when it gets to the next convenient point.

To start it up again then anybody with access can just edit the text file, set the value to YES, and the script will start up again as if nothing happened when it gets to the end of the current sleep.

$batchNum = 1;
$continueFile = 'continue.txt';
Write-Host "Starting . . .";
do {
  Write-Host "Batch $batchNum";
  do {
    $continue = Get-Content $continueFile;
    if ($continue -ne 'YES') {
      Write-Host "Pausing for 10 seconds - to restart ensure continue.txt = YES";
      Start-Sleep 10;
    } else {
      Write-Host "Continuing . . .";
    }
  } while ($continue -ne 'YES')
  Start-Sleep 10;
  $batchNum++;
} while ($batchNum -lt 1000)
Write-Host "Finished . . .";

Of course, if you really need to then you can always CTRL+C to cancel the script at any stage too, but this is a simple way to not only allow the script owner to pause but allow others to pause too.

Posted in PowerShell | Tagged , , , , , , | Leave a comment

Power BI – Maps

OK, so it has been a while since my last post.  Too busy?  Lack of inspiration? Tired fingers?  Lots of excuses but I finally got around to dusting off the keyboard so brace yourself for some Power BI.

If any of you have not yet taken a look at Power BI then you are missing a treat.  It has come a long way since the early days of the Excel plug-in with a great desktop app which is free to download – so what are you waiting for?  I will be presenting a workshop and session at the Digital Workplace Conference in Sydney coming up in August so you have no excuses.  Well maybe just finish reading this first?

Maps can really give your data some visual context and they are really easy to do in Power BI but there are a few little tips to prepare your data so that is what I am going to focus on today.

The first thing I am going to do is grab the Australian States and Territories from a web page.

Select Get Data and choose Web as the source.  Then paste in a good URL for your data:

PowerBIWeb

Clicking OK will take you to the Navigator page which lets you choose which data on the page you would like to include.  In this case I want the Statistics table so I tick that and click Load:

PowerBIWebNavigator

So now we have some geographic data we are ready to create a map right?  Well not quite.  The problem is that there seem to be other geographic places around the world with the same names, so just creating a map as it is will likely give you some unexpected results – and using abbreviations is even worse.

The first thing I want to do is add a column to our data.  I could do this through the Data view but I want to try out a cool new feature so use Edit Queries and select Add Column from the tabs at the top of the page.  Select the State / Territory column and then choose Column From Examples -> From Selection:

PowerBIFromSelection

Type into the first row of the column created:

Australian Capital Territory, Australia

You will notice that Power BI automatically assumes you want the same pattern applied to all the other rows too:

PowerBICombined

Click, OK then Close and Apply and you now have a column which also references the country.  Even I could not confuse that with Victoria in South Africa.

One last step.

Select the new column – I have left it with the default name Combined – and from the Modelling tab choose Data Category -> State or Province:

PowerBICategory

We are now ready to create our map.  In the Report view click on the Combined column and Land Area km2 and then choose the Map or the Filled Map:

PowerBIFilledMap

Nice work!  We got to load data from a web site, add a column by example, correctly categorise our data and create a chart.  Not bad for a few minutes work!

Have fun – and do come say hello at the conference if you are attending.

Posted in Office 365, Power BI | Tagged , , , | Leave a comment

Missing out on Intellectual Property

If your organisation is using shared folders with restricted access then you will almost certainly not be realising the potential value of your intellectual property.

Take a look at my post on the Cost of Information Silos and then try calculating with figures from your own organisation.

You will probably be surprised at how much you are missing out on!

Posted in Office 365, SharePoint | Tagged , , , , , , | Leave a comment

Set JSLink on a Site Column With JSOM

I remain very impressed with JSLink and what it can do, particularly related to individual site columns.  While preparing a presentation for the local Office 365 Saturday event here in Brisbane, Queensland I realised that while it is easy to connect a JSLink script to a site column using PowerShell, there have been many occasions on client sites where I have been limited to Site Collection administrator access permissions with no access to a server.

While I live in hope that one day Microsoft might add that simple text field to the properties of a site column in the browser, I figured it must be possible using JavaScript with JSOM.

And of course it is . . .


var context = new SP.ClientContext.get_current();
var web = context.get_web();
var field = web.get_availableFields().getByTitle("My Site Column");
context.load(field);
context.executeQueryAsync(function(){
field.set_jsLink("~sitecollection/Style Library/custom/MyJSLink.js");
field.updateAndPushChanges(true);
context.executeQueryAsync(function(){
var jsl = field.get_jsLink();
console.log(jsl);
});
});

This can be run from the console in a browser – just make sure you use your site column name and set the path to your script. You do also need to be logged in as a Site Collection administrator. Oh, and it works for Office 365 too.

There is also a copy on GitHub if you prefer.

Happy JSLinking!

Posted in JSLink, Office 365, SharePoint | Tagged , , , , , , | 2 Comments

Site Column Display Templates – KPIs

Furthering my theme of Site Column Display Templates, this is really simple but one that I will definitely reuse over and over.

Site Column
Title: kpiIcon
Type: Choice [Red, Yellow, Green]

This site column can now be added to any list or library as usual.

Display Template
You can copy or download the complete display template from GitHub.

var paylord = paylord || {};
paylord.kpi = paylord.kpi || {};

paylord.kpi.viewFieldTemplate = function(ctx) {
var kpi = ctx.CurrentItem[ctx.CurrentFieldSchema.Name];
var suffix = "";
if (kpi == "Green") {
suffix = "0.gif";
} else if (kpi == "Red") {
suffix = "2.gif";
} else {
suffix = "1.gif";
}
var ret = "<img title="&quot; + kpi + &quot;" src="/_layouts/images/kpidefault-&quot; + suffix + &quot;" alt="" />";
return ret;
};

(function () {
var fieldCtx = {};
fieldCtx.Templates = {};
fieldCtx.Templates.Fields = {
"kpiIcon": {
"View": paylord.kpi.viewFieldTemplate,
"DisplayForm": paylord.kpi.viewFieldTemplate
}
};

SPClientTemplates.TemplateManager.RegisterTemplateOverrides(fieldCtx);

})();

Told you it was simple. The scoping object is set up to keep it behaving nicely then my function checks the selected choice value (Red, Yellow or Green) and returns an image using the standard SharePoint icons.

In this case I have not made any change to the edit mode but applied my function to both the View and Display modes.

PowerShell
The ability to add the JSLink property through the UI to a Site Column seems to have been forgotten by Microsoft so I am hoping we can encourage them to make this simple enhancement – add your vote through User Voice.

In the meantime we are stuck with using a few lines of PowerShell:

$web = Get-SPWeb "http://sitename"
$field = $web.Fields["kpiIcon"]
$field.JSLink = "~sitecollection/Style Library/custom/KPI.js"
$field.Update($true)
$web.Dispose()

Summary
Choosing the KPI value is not changed at all so is a standard radio button:
Choosing KPI

When in display or view mode the KPI is displayed as an icon:
viewKPI

Completely reusable by anybody with the ability to add columns to list or libraries – a great and simple addition to any SharePoint environment.

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