Tag Archives: SharePoint Office Web Apps

How to show SharePoint list data in a pivot table

List data inside SharePoint can be pulled into Excel as a data source which can in turn be embedded on a SharePoint page as a graphical representation of the SharePoint list data. Great way to spice up your SharePoint page with graphical content and the functionality is built into SharePoint online. It’s also available in SharePoint on-premises 2010, 2013 and 2016 if you have Office Web Apps configured or excel services enabled.

Excel graph on SharePoint page

First open excel and go to “Data” in the ribbon and “From other sources”, select “From OData Data Feed”.

import sharepoint list data to excel

Copy and paste the URL of the site adding “_vti_bin/listdata.svc” at the end.

connecting to sharepoint

Select your list when prompted and a pivot table will appear. Select a few columns to populate it with data.

Pivot chart in excel

Save the excel file and upload it into a SharePoint document library on your site.

Edit the page and add the “Excel Web Access” web part under the business data category.

adding excel web access web part to sharepoint page

In the web part properties select the excel document and optionally enter a named item such as a pivot chart or table. Your chart is now on the SharePoint page and can be configured to refresh at intervals. It’s a great way to visualise SharePoint list data easily without any third party products.

web part on page

Watch the video guide below for a more detailed explanation and guide.



Class site templates in SharePoint

As part of my work for Cloud Design Box, I have been working on class templates for student and teacher collaboration in SharePoint. My templates are using lots of display templates and are provisioned automatically from MIS data using the Salamander provisioning tools. Each template combines, out-of-the-box lists and libraries with custom content types, search web parts and JavaScript display templates. It’s available in both SharePoint on-premises and SharePoint Online. The video below goes through some of the features available for the class sites. Each class template is customised on a per school basis with a responsive design so that it will work on all devices.

For more information or an online demo, contact me by clicking here.



Create a simple helpdesk in SharePoint

Below is a quick tutorial on how to create a simple helpdesk/support log in SharePoint online (Office 365) or using the on-premises SharePoint 2013 release. It uses out-of-the-box functionality to create a simple system for users to log and track support issues.

The steps required to create the help desk are:

  • Create a custom list
  • Add additional columns
  • Create list views
  • Create a new item form
  • Set list permissions
  • Configure Alerts
  • Create a notify workflow

Create a custom list
Select “Add an App” from the SharePoint menu

Office 365 menu

Select “Custom List”

SharePoint 2013 App

Give the App a Name

SharePoint 2013 App Custom List
Create additional columns
In the list settings, create some additional columns:

Description (multiple lines of text)*

Status (choice with the options: New, In Progress, Resolution Offered, Closed)*+

Resolution (multiple lines of text)

* Set column as required – force users to enter a value

+ Set default value to “New”

SharePoint App Columns
Create list views
On the list page, select “Create View”

SharePoint 2013 list view

Create a new view based on All Items called “My Open Calls”. This is the default end user view to review their open calls.

Select the following columns:

Title (linked to item)

Status

Created

Uncheck any other columns. Add a filter to show items created by the user and items that do not have the status “Closed”.

Filter 365 list view

Create another view called “My Closed Calls”. Display the following columns:

Title

Status

Filter by the user and items that have the status “Closed”.

Filter SharePoint 2013 View

You may also want to sort these items by newest first to make it easier for the end user to see recently closed items.

Create another view called “All Resolved Calls”. Display the following columns:

Title

Description

Resolution

Created By

Created

This time only add a filter to show items with the status “Closed”.

Filter View

Create another view called “All Open Calls”. Display the following columns:

Title

Description

Status

Resolution

Created

Created By

This time only add a filter to show items without the status “Closed”.

Filter 365

The views “All Open Calls” and “All Resolved Calls” will be used by the resolution team to manage logged issues. The other views will be used by the end user to track their own open issues.

Permission List
At this stage, users with contribute access can add and edit all the logged incidents. This needs to be locked down so users can only edit their own items. To do this, select “Advanced Settings” from the List Settings page.

List Settings

Lock down the Item-level Permissions so that users can only read and edit their own items.

Item Level Permission

Users will Full Control on the list will have access to see and edit all of the items despite the settings above.

Create a new form
Open the site in SharePoint Designer 2013. Using the Site Objects Navigation, find the “Lists and Library” category and select the list.

SharePoint Designer 2013

Create a new Form for the list by selecting “New” in the Forms section.

New Form Advanced Mode

Give the form a new name and select the “New item form” type. Set this as the default form for the selected type.

Form Settings

Right click on the new form and select “Edit in Advanced Mode”

Edit in advanced mode

Locate the table rows in the HTML for the Status and Resolution. Remove these including the start and end tr tag. This will remove the columns from the new form. Save the form, this should now apply to any new items added to the list.

New Form

Set up alerts and workflows
The support team should sign up for alerts on the list so that they are notified when a new issue is added or updated. A simple workflow can be added to the list to notify the end user when their issue has been updated. I will write a blog post on how to do this soon.

Design
Adding icons to a SharePoint page to point directly to the views will make it easier to navigate for the end user and much more attractive to use

Troubleshooting Office Web App Farm Installations

New functionality in Microsoft Office Web Apps is the preview feature in search (see below). Setting up a separate farm for Office Web Apps is a great way to improve performance and make a scalable solution but it can bring up some new technical challenges. A couple of the most common issues are listed below.

Web Apps Search Preview

Do the internal and external URLs of the Web Apps Server resolve from the SharePoint server?

Test: https://webapps.contoso.com/hosting/discovery
Should return XML without certificate error

Test: https://WebAppServer01.contoso.com/hosting/discovery
Should return XML without certificate error

Certificate Error?

When setting up the web apps farm, a SAN certificate needs to be specified with both the internal URL and the external URL (see below). Wildcard or single host certificates will not work.

SAN Certificate
404 not found?

Both the SharePoint and Web App server need to see each other. Make sure that internal DNS is setup for the external web app address. Run ipconfig /flushdns on the SharePoint server. The SharePoint server needs to resolve both addresses internally.

The Web Apps server also needs to have an entry for the SharePoint site URL. Ensure that the SharePoint site has an internal DNS entry and flush the DNS on the web apps server. Browse to the SharePoint site on the web apps server to ensure that it can see the site without any certificate errors.