Archive

Posts Tagged ‘SharePoint’

#Office365 Single Sign On Smart Links

November 6, 2014 2 comments

Consider the following scenario… You have migrated your company intranet to SharePoint online and wish to deploy the site as the IE homepage for your staff.

You have ADFS to provide a rich single sign on experience but you still get the Microsoft organizational sign in page when you first login to Office 365.

Using ADFS 2.0 or above, on-prem staff can bypass this login and navigate directly to the SharePoint site by using Smart Links. External Staff will bypass the Microsoft Sign In screen and go straight to your ADFS Proxy landing page.

Interested? Let’s begin.

image

Prerequisites:

Or

  • ADFS 3.0: Disable Extended protection token on ADFS Server to allow Fiddler HTTPS decryption
    Set-ADFSProperties -ExtendedProtectionTokenCheck None
  • IIS7 or above.

 

Step 1

Open Fiddler and enable HTTPS traffic decryption.
(It is not necessary to enable Windows 8 Mode even on a Windows 8 client for this procedure)

image

Step 2

Ensure you are logged out of Office 365 and sign into https://portal.micosoftonline.com where you should meet the Office 365 login page. (This must be done internally)

Step 3

Open fiddler and login to Office 365.
You should be taken to your destination without having to enter in any credentials as normal.

(If you get an authentication prompt that will not resolve you need to disable extended protection in ADFS as per the prerequisites).

Step 4

Look for a HTTPS 302 redirection from your ADFS server and copy the URL to notepad.

image

Step 5

In notepad, remove the text between ls/? and wa=wsignin
Remove all text after wreply%3D

This is your Smart URL and this will form the base part of any links you wish to give out to Office 365.

Step 6

Append the URL after wreply%3D with the target Office365/SharePoint site you wish to direct your users to using base encoding.

For example, https://yourcompany.sharepoint.com becomes https%253A%252F%252Fyourcompany%252Esharepoint%252Ecom

Or

https://yourcompany.sharepoint.com/sales becomes https%253A%252F%252Fyourcompany%252Esharepoint%252Ecom%252Fsales

Substitute with your site URL as appropriate. The final link should look like this:

https:/adfs.yourcompany.com/adfs/ls/?wa=wsignin1.0&wtrealm=urn:federation:MicrosoftOnline&wctx=wa%3Fwsilnin3.0%26aeiou%3D9%26fa%4D5

275200813%26rves%3D3.2.2202.0%26wi%3DJUI%16wreply%3Dhttps%253A%252F%252Fyourcompany%2

52Esharepoint%252Ecom

*Verify this link works by copying it into a IE Private Session that has not been logged into Office 365.

You should be taken straight into 365 and should not have to enter in your credentials.

Step 7

Create internal and external DNS records for your vanity URL to point to your IIS Server.

For example we want to use http://intranet.yourcompany.com as an easy to remember link that passes single sign on and not the full URL we have created in Step 6.

As this is going to be our homepage set via group policy we need to ensure we can resolve this URL Internally and Externally.

Step 8

Create a new site in IIS for your vanity URL by right clicking the sites folder.

Create a new physical path (This will not be used) and add the DNS name you created in Step 7 to the host name.

image

Step 9

Select HTTP Redirect and paste your URL from Step 6 into the destination box.

image

Once these steps have been completed your users should be able to use easy to remember links to connect to Office 365 without the need to enter credentials.

This works for the Office 365 Suite with the exception of CRM.

To get CRM to work you will need to perform the same actions from Step 3 changing the Office 365 link you were going to, (youcompany.sharepoint.com for example) to your CRM login.

Capture the URL and remove the text between ls/? and wa=wsignin

DO NOT append after wreply%3D otherwise the link will fail.

Continue the steps as described for the Office 365 smart link.

References

Excellent blog post from David Ross which forms the foundation of this blog post.
http://blog.kloud.com.au/2012/10/12/office-365-smart-links/

Official Microsoft Post
http://community.office365.com/en-us/w/sso/358.using-smart-links-or-idp-initiated-authentication-with-office-365.aspx

Changing the image used in EPTs

A question that arises quite regularly on the Project Server configuration courses, is:

Is it possible to change the icon used for the Enterprise Project Types?

These icons are the ones that appear next to the name of each EPT on the New button in Project Center.

image

The out of the box EPTs have the following image URL:

image

The folder that stores these images may be accessed by connecting to the server itself, by remote desktop connection, and then navigating to the following address:

C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\15\TEMPLATE\LAYOUTS\INC\PWA\IMAGES

This folder contains a number of images, three of which begin with the word “Center”:

image

It is therefore possible to copy one of these images, rename it and edit the image, using something like Paint.

image

The new image can then be used for existing and new EPTs.

Filed under: Project Server Tagged: 2010, 2013, CPS, EPT

via Carl Sprake MA(Oxon), ICTP, MCT http://ift.tt/1mNxrCu

Carl Sprake
After beginning my career in pharmaceutical sales I moved into IT training, initially in the pharmaceutical sector but then moved across to the NHS to teach basic computer skills and the ECDL. I then changed companies and moved into a primarily Microsoft Office training and support role. I joined CPS in 2008 as a training consultant and now provide configuration as well as training services.

This article has been cross posted from carlsprake.wordpress.com/ (original article)

“What happened in Vegas” – #SharePoint Conference 2014: Day 3 – Microsoft’s shock announcement & SharePoint storage architecture session #SPC14 #SharePointOnline

April 17, 2014 Leave a comment

Sacha-copy

spc14logo-DAY3

This is my personal account of activities from the SharePoint Conference 2014.

Day 3 – Session 1 – Microsoft’s shock announcement

So we all have heard that InfoPath forms are dead, the conference and I was buzzing to see what Microsoft were recommending to replace InfoPath. I felt sorry for the presenters with the pressure to excite the audience with such a socially hyped (SharePoint community) announcement and a great job they did, however the upcoming replacement is a spring release with basic functionality. Microsoft themselves have identified this and amongst others have recommended #Nintex forms, which is simple as we have already been implementing here at CPS, for mobile clients. Update on InfoPath and SharePoint Forms Session

Embedded image permalink

Back in the UK no one had time for me, out here in Vegas I struggled for time to network and meet all the new people, surrounded by interesting individuals!

Session 2 –  SharePoint storage architecture, 2001-present inside and out

Great last session of the day! Lee Mather wish you could have been here! Brilliant explanation of Shredded Storage and how to tune it? Went from no understanding to an Infrastructure Architecture in 60 minutes! If you want to get the best storage performance out of your SP farm watch this session..

SharePoint storage architecture, 2001-present inside and out

 

STAY TUNED AND FOLLOW THIS BLOG FOR THE NEXT RELEASE OF MY SHAREPOINT CONFERENCE 2014: DAY 4 – Microsoft want to team up with SAP/Meeting new partners & ‘The conference Streaker!’….

If you would like to talk to me regarding the SharePoint conference in more detail or even just SharePoint email me at sacha.cohn@cps.co.uk or even just SharePoint.

Also don’t forget to check our website out for more information at: www.cps.co.uk

Alternatively follow us on our LinkedIn, Twitter & YouTube channels for up to date industry news and information, along with product demos and guide to’s.

“What happened in Vegas” – #SharePoint Conference 2014: Day 2 – Session with @cfiessinger #SPC14 #SharePointOnline

April 9, 2014 Leave a comment

Sacha-copy

spc14logo-DAY2

This is my personal account of activities from the SharePoint Conference 2014.

Day 2 – Key Session 1: Queued for Clinton

So after a long queue finally got to see a president, well ex president. A great man, but clearly not the same as Microsoft Bill. Keynote session was good though, felt a bit like an ant in headlights in the vast room with more screens than a Television store. Never seen a TV screen so big, but it did make it easy to see some great evolution’s coming and staggering stats regarding Office 365.

WP_20140303_005 WP_20140303_021

A major output for me on this day was the sneak preview and exclusive glimpse of the Office Graph – A social media tool drawing information from SharePoint, Yammer and LinkedIn. (See below photo)

WP_20140303_010

Session 2: Anyone can build a SharePoint App with Microsoft Access

This session was fantastic if you’ve never played with Access in SharePoint so I highly recommend clicking and watching the below presentation on this, however, my personal view was that I was a little confused and underwhelmed, but see what you think? . You can find it at: Anyone can build a SharePoint App with Microsoft Access. Their were other Access sessions on the day, though not as good.

Leaving the session, one thing I can say was that this conference was not short of Coke fridges, anyone would have thought Coca-Cola were the Gold Sponsors of this conference! Cokes on tap everywhere to keep me going anyway…

WP_20140304_002

Session 3: Roadmap for Enterprise Social

Oops! popped through the wrong door….

SharePoint Conference 2014 Projectors

Wow they were big, and all just for Social… These even had to have their own rooms to be stored in! Still can’t get over the amount of these that were used, the electricity bill must have been astronomical.

Great session, wasn’t surprised, as Christophe Fiessinger is a great guest speaker, with some really insightful content on Enterprise Social. Another session I would strongly advise to have a watch: Microsoft’s roadmap for Enterprise Social

image

 

STAY TUNED AND FOLLOW THIS BLOG FOR THE NEXT RELEASE OF MY SHAREPOINT CONFERENCE 2014: DAY 3 – Good Session by legal & The Speedway in the evening….

If you would like to talk to me regarding the SharePoint conference in more detail or even just SharePoint email me at sacha.cohn@cps.co.uk

Also don’t forget to check our website out for more information at: www.cps.co.uk

Alternatively follow us on our LinkedIn, Twitter & YouTube channels for up to date industry news and information, along with product demos and guide to’s.

SQL Healthcheck

A common task when carrying out a health check on a client’s SharePoint farm is to ensure that all databases are being backed up and that the database files have been sized correctly.  Most of the time, a DBA will be tasked with carrying out backups and confirming that they are functional but in some cases, there is no DBA and there is no-one who can answer then questions around SQL Backups.

In this scenario it is useful to have a SQL script to capture and display this information.

Last Backups

The following code will present the dates of the backups, along with the collation, recovery model and whether auto shrink is on.

   1:  SELECT DISTINCT
   2:      d.Name AS [Database Name]
   3:  ,    d.recovery_model_desc AS [Recovery Model]
   4:  ,    COALESCE((
   5:              SELECT
   6:                  CONVERT(VARCHAR(12), MAX(bus_f.backup_finish_date), 101)
   7:              FROM msdb.dbo.backupset bus_f
   8:              WHERE
   9:                  bus_f.database_name = d.name
  10:              AND
  11:                  bus_f.type = 'd'
  12:              AND
  13:                  bus_f.is_copy_only = '0'
  14:      ), 'No Full') AS [Last Full Backup]
  15:  ,    COALESCE((
  16:              SELECT
  17:                  CONVERT(VARCHAR(12), MAX(bus_d.backup_finish_date), 101)
  18:              FROM msdb.dbo.backupset bus_d
  19:              WHERE
  20:                  bus_d.database_name = d.name
  21:              AND
  22:                  bus_d.type = 'i'
  23:              AND
  24:                  bus_d.is_copy_only = '0'
  25:      ), 'No Diff') AS [Last Differential Backup]
  26:  ,    COALESCE((
  27:              SELECT
  28:                  CONVERT(VARCHAR(20), MAX(bus_t.backup_finish_date), 120)
  29:              FROM msdb.dbo.backupset bus_t
  30:              WHERE
  31:                  bus_t.database_name = d.name
  32:              AND
  33:                  bus_t.type = 'l'
  34:      ), 'No Log') AS [Last Log Backup]
  35:  ,    d.collation_name AS [Collation]
  36:  ,    d.recovery_model_desc AS [Recovery Mode]
  37:  ,    CASE(d.is_auto_shrink_on)
  38:          WHEN 1 THEN 'Yes'
  39:          WHEN 0 THEN 'No'
  40:          ELSE '??'
  41:      END AS [AutoShrink Is On]
  42:  FROM sys.databases d
  43:  WHERE
  44:      d.name != 'tempdb'
  45:  AND
  46:      d.state_desc = 'online'
  47:  ORDER BY
  48:      d.name
  49:  ;

.csharpcode, .csharpcode pre
{
font-size: small;
color: black;
font-family: consolas, “Courier New”, courier, monospace;
background-color: #ffffff;
/*white-space: pre;*/
}
.csharpcode pre { margin: 0em; }
.csharpcode .rem { color: #008000; }
.csharpcode .kwrd { color: #0000ff; }
.csharpcode .str { color: #006080; }
.csharpcode .op { color: #0000c0; }
.csharpcode .preproc { color: #cc6633; }
.csharpcode .asp { background-color: #ffff00; }
.csharpcode .html { color: #800000; }
.csharpcode .attr { color: #ff0000; }
.csharpcode .alt
{
background-color: #f4f4f4;
width: 100%;
margin: 0em;
}
.csharpcode .lnum { color: #606060; }

File Information

The following script will present details of all of the files associated with the databases, including the autogrowth settings and the maximum size that the files will grow to.

   1:  SELECT
   2:      DB_NAME(mf.database_id) AS [Database Name]
   3:  ,    mf.name AS [Logical Name]
   4:  ,    CONVERT (DECIMAL (20,2) , (CONVERT(DECIMAL, size)/128)) [Current File Size (MB)]
   5:  ,    CASE mf.is_percent_growth
   6:          WHEN 1 THEN 'Yes'
   7:          ELSE 'No'
   8:      END AS [Is Percent Growth]
   9:  ,    CASE mf.is_percent_growth
  10:          WHEN 1 THEN CONVERT(VARCHAR, mf.growth) + '%'
  11:          WHEN 0 THEN CONVERT(VARCHAR, mf.growth/128) + ' MB'
  12:      END AS [Growth Increment]
  13:  ,    CASE mf.is_percent_growth
  14:          WHEN 1 THEN CONVERT(DECIMAL(20,2), (((CONVERT(DECIMAL, size)*growth)/100)*8)/1024)
  15:          WHEN 0 THEN CONVERT(DECIMAL(20,2), (CONVERT(DECIMAL, growth)/128))
  16:      END AS [Next Autogrowth Size (MB)]
  17:  ,    CASE mf.max_size
  18:          WHEN 0 THEN 'No growth is allowed'
  19:          WHEN -1 THEN 'File will grow until the disk is full'
  20:          ELSE CONVERT(VARCHAR, mf.max_size/128)
  21:      END AS [Max Size (MB)]
  22:  ,    mf.physical_name AS [Physical File Name]
  23:  FROM sys.master_files mf
  24:  ORDER BY
  25:      DB_NAME(mf.database_id)

.csharpcode, .csharpcode pre
{
font-size: small;
color: black;
font-family: consolas, “Courier New”, courier, monospace;
background-color: #ffffff;
/*white-space: pre;*/
}
.csharpcode pre { margin: 0em; }
.csharpcode .rem { color: #008000; }
.csharpcode .kwrd { color: #0000ff; }
.csharpcode .str { color: #006080; }
.csharpcode .op { color: #0000c0; }
.csharpcode .preproc { color: #cc6633; }
.csharpcode .asp { background-color: #ffff00; }
.csharpcode .html { color: #800000; }
.csharpcode .attr { color: #ff0000; }
.csharpcode .alt
{
background-color: #f4f4f4;
width: 100%;
margin: 0em;
}
.csharpcode .lnum { color: #606060; }

‘What happened in Vegas’ – #SharePoint Conference 2014: Day 1 #SPC14 #SharePointOnline

April 1, 2014 1 comment

spc14logo-DAY1 Sacha-copy

 

 

 

 

 

This is my personal account of activities from the SharePoint Conference 2014.

PRE-EVENT (On my #Surface) 

Excited for the event, I start getting my itinerary ready off the official SharePoint Conference guide from their website and I’m overwhelmed with the choices. 60 page prints later and still no idea on which sessions to attend. Several hours later… I finally manage to narrow down my selections and highlight the sessions out of my 60 pages that were of interest to me. Keen to find out who was going from Microsoft UK and was disappointed to find that no one I knew was going! 😦 However to cheer myself up and keen to get my diary sorted I call to find out when you get to access your own timetable and was notified that I would have access the next day via email. I log in the next day to find a great looking site, but to my disbelief the session names don’t align to the new timetable I had just accessed! Back to the start I went and followed the new timetable to create my hectic diary while in Vegas. See below.

WP_20140301_007

As you can see, a busy week on my hands!

DAY 1

9-10 hours later I arrive at ‘The Venetian hotel’ reception to book into my room where the receptionist tells me that I’m booked into only a standard room and would I like to be upgraded to an executive room? To which I reply what’s the difference, this is America surely the room is big enough?! So I decided to see what a Executive room consists of. (See below)

WP_20140301_004

Executive room? Is this not it?! And it didn’t stop there, just look at the menu. Apart from my room being more than adequate, the hotel restaurant choice was something else, all I wanted was a simple burger and chips and this is what I had at my disposal…

WP_20140301_006

After a good nights sleep I was faced with the long walk to breakfast  it taking me 20 minutes to get to the buffet table in the same hotel! With an extra 5 minutes to find a table, I had burnt off breakfast in calories. That being said it was nice to have food included at the SharePoint conference but unfortunately due to my simple requirements dieting was preferable. Anyway enough about my stay and the facilities, I will get to the important stuff – The Conference itself!

Day session on a Sunday! – Drumbeat  drumbeat logo Unfortunately there is not a lot I can say about this due to Microsoft Partnership confidentiality, however what I will say is that it was great to meet some partners that had the same issues as me – I’m not alone! 🙂

STAY TUNED AND FOLLOW THIS BLOG FOR THE NEXT RELEASE OF MY SHAREPOINT CONFERENCE 2014: DAY 2 – CUING FOR CLINTON….

 

If you would like to talk to me regarding the SharePoint conference in more detail or even just SharePoint email me at sacha.cohn@cps.co.uk

Also don’t forget to check our website out for more information at: www.cps.co.uk

Alternatively follow us on our LinkedIn, Twitter & YouTube channels for up to date industry news and information, along with product demos and guide to’s.

CRM Online UI Frustrations

March 31, 2014 Leave a comment

Alan EardleyI am new to customising CRM online and have come to it since the December 2012 Service Update that included the ‘Updated Forms’.  The interface that the ‘Updated Forms’ provide is much more in keeping with the 2013 generation of SharePoint and Office 365 products and would therefore be my first choice for a general CRM UI style.​

​​​With this in mind, I started some simple configuration of fields and forms without issue.  When I started implementing some slightly more sophisticated changes that require JavaScript a change took place in the interface.  At first the it was not apparent as changing between the ‘Updated Forms’ and the ‘Classic Forms’ is a regular occurrence in CRM Online.  The regular change in UI is not an experience that I find pleasureable and I consider it to be a sign of a rushed release of functionality that is probably putting more people off the idea of CRM Online than it is encouraging them to use it.

​As it turns out the ‘Updated Forms’ are in fact classified as ‘Read-Only’ forms with controls on the page to allow the in-place editing of specific data.  This type of form is not the same as an ‘Editing’ form which is what the ‘Classic Forms’ are.  There are significant restrictions imposed on the ‘Read-Only’ forms because of the use of the in-place controls for editing:

They do not support the use of JavaScript events OnLoad, OnChange and OnSave.

​As a result of this restriction, when any of the three JavaScript events are used in an ‘Updated Form’ CRM Online will revert to displaying the ‘Classic Form’.

​This adds to the confusion in the interface and in addition prevents the usage of one of the key features that the ‘Updated Forms’ enable, namely the ‘Sales Process’.

Where do I get templates?

March 24, 2014 Leave a comment

I downloaded another template for using when producing wireframes this morning.  It was for CRM, which until recently I had very little need to wireframe.  It made me think about all of the templates that I have created, used and not been able to find.  I use templates for Word, Excel, Visio (wireframe, architecture, processes), Balsamiq, etc. so I thought it might be useful to keep links to the templates in one place.

So this is my collection of links to templates.

Architecture

Microsoft Office (including SharePoint, Exchange & Lync) Visio 2010 http://www.microsoft.com/en-us/download/details.aspx?id=35772

SharePoint

SharePoint 2010 Balsamiq http://sharepoint.jsturges.com/2011/11/sp2010-wireframe-template/
SharePoint 2013 Balsamiq http://flucidity.com/2013/02/sharepoint-2013-balsamiq-mock-up-template/

CRM

CRM 2013 Visio 2010 http://crmdynamo.com/2014/01/free-to-use-crm-2013-wireframe-template/

SharePoint Tools: My Favourites

There are some great posts out there on sets of useful tools that are essential when working with SharePoint, chief among them is Tobias Zimmergren’s post Tools for your SharePoint 2013 development toolbox.

Tobias’s post inspired my to do this post as I have also found some additional tools to be invaluable:

Design Tools

Balsamiq

This is a great tool for rapidly producing wireframes.  It requires very little time to learn and produces wireframes that are sufficiently lifelike for clients to picture how their SharePoint solution will work.

http://balsamiq.com/

Metro Studio

When you need icons, this is the tool to use.  With a library of pre-installed icons that can be added to a project, re-sized, coloured and exported as individual png files or a sprite, Metro Studio provides a quick way of adding some polish to a solution.

http://www.syncfusion.com/downloads/metrostudio

SharePoint Colour Palette Tool

When working with composed looks in SharePoint 2013 this is the only tool for the job: It allows all of the colours to be changed and to be previewed offline and then the resultant file can be uploaded.

http://www.microsoft.com/en-gb/download/details.aspx?id=38182

SharePoint Tools

SharePoint Manager

Incredibly useful for investigating settings for all aspects of a farm.

http://spm.codeplex.com/

SharePoint 2013 Client Browser

Similar to the SharePoint Manager but it allows remote access and therefore works really well with Office 365.  It can also create a PowerShell session ready to use with CSOM so it really is ideal for Office 365 where there are not the PowerShell commandlets that you get with an on-premises farm.

http://spcb.codeplex.com/

CAML Designer

If you need to query a SharePoint, and let’s face it, everyone has to at some point, then this is the tool to use.  It allows you to build the CAML query and then to create code snippets for use with a load of other techologies.

http://biwug-web.sharepoint.com/SitePages/Caml_designer.aspx

SharePoint 2013 Search Query Tool

This is a great tool for testing search queries.  It allows you to confirm the search configuration and to test queries that you use in Content by Search Web Parts.

http://sp2013searchtool.codeplex.com/

Infrastructure & SQL

mRemoteNG

I no longer have SharePoint installed on my laptop, instead I use VMs in Azure, VMs on client sites or Office 365.  When using VMs, this tool allows me to keep all of the connections in one tool and to organise them and manage them more easily.

http://www.mremoteng.org/

PortQuery

I have used this tool for longer than I care to remember.  It is perfect for testing connectivity between different application and machines as it allows you to test particular ports on different machines.

http://blog.eardley.org.uk/2012/01/essential-tools-port-query/

http://www.microsoft.com/en-gb/download/details.aspx?id=24009

ULS Viewer

When you need to look at ULS logs – this is the tool to use.

http://archive.msdn.microsoft.com/ULSViewer

Log Parser

If you have multiple machines and types of log to analyse then this tool offers features such as writing all entries to a database to allow for centralised analysis.

http://technet.microsoft.com/en-us/scriptcenter/dd919274.aspx

SQL Management Studio

SharePoint requires SQL Server.  If you need to investigate issues related to SQL Server, this is the tool to use.

Other

OneNote

When you want to keep notes of things you learn.

Windows Live Writer

When you want to share what you have learned …

http://windows.microsoft.com/en-us/windows-live/essentials

Spotify

Life is dull without music

https://www.spotify.com/uk/

Excel Charts – Trends, targets and highlights

I’ve been reading up on excel charts recently, so I thought I would share a few tips on creating Excel charts. In this post I want to look at three main elements:

  • Trends
  • Targets
  • Highlights

Trends

A large amount of the data that we want to look at is time phased. It is therefore useful to be able to plot the data in ways that make it easy to spot trends over time.

Since we are used to thinking of time as linear, line charts are often the best way of plotting time phased data. For example if we have total work done per day:

image

Then we can plot this on a chart:

image

This is a line chart, that shows the full six months worth of data. However, if we apply a filter to the data, by selecting the Data tab and then clicking on the Filter button, we can specify that we only want to view data from, for example, a particular month.

image

image

The chart will update to show only the selected data. If your chart continues to show all the data then follow these steps:

  1. Select the chart
  2. Select the Chart Tools – Design tab
  3. In the Data group, click Select Data to display the Select Data Source dialogue box
  4. Click the Hidden and Empty cells button to display the Hidden and Empty cells settings dialogue box
  5. Ensure the Show data in hidden rows and columns check box is not selected
  6. Click OK
  7. Click OK

image

Targets

For a particular set of data we may want to set a target. For example if we look at Resource Utilisation, we may aim for 80% utilisation.

Our initial data and chart may look like this:

image

If we are using a pivot table as the source then the data may well already be in this nested format. If not then excel is clever enough to plot the chart based on our nested data.

To show the performance against the target of 80% we can add another data series to the chart.

image

In order to add this additional column to the chart:

  1. Select the range E1:E25
  2. Do Ctrl+C to copy this data
  3. Click on the Chart area to select the chart
  4. Do Ctrl+V to paste the data in to the chart

image

The series can now be formatted as desired.

Highlights

This time we want the chart to automatically pick out the month with the highest, or perhaps lowest, resource utilisation. We can do this by again adding another data series, but this time controlling the value of each data point using a formula.

image

Essentially the formula says that if the utilisation for the current month is the highest utilisation over the two year period, then show that value, other wise leave it at zero.

When this new series is added to the chart and formatted as a column then only one column is visible:

image

The lowest point could also be found using a similar formula.

=IF(D2=MIN($D$2:$D$25),D2,0)

This could be combined with the previous data to produce the following:

image

Filed under: 2013, CPS, Excel, Microsoft Office, Project Server Tagged: Chart, excel

via Carl Sprake MA(Oxon), ICTP, MCT http://carlsprake.wordpress.com/2013/09/13/excel-charts-trends-targets-and-highlights/

Carl Sprake
After beginning my career in pharmaceutical sales I moved into IT training, initially in the pharmaceutical sector but then moved across to the NHS to teach basic computer skills and the ECDL. I then changed companies and moved into a primarily Microsoft Office training and support role. I joined CPS in 2008 as a training consultant and now provide configuration as well as training services.

This article has been cross posted from carlsprake.wordpress.com/ (original article)