Category Archives: General SharePoint Development

Adding an O365 Security group to the Site Collection Administrators group

Here is the scenario… you wish provision every site collection in your SharePoint Online environment so that a key group of staff, say for example your support staff, have Site Collection Admin access.  To provide centralised control of this group you want to define it and its membership outside the boundaries of your SharePoint Online environment. In this case you have two options, a Security Group or an Office 365 Group. In the case of a Security group there is no associated email address so people can’t mail to it and it doesn’t appear in the address book. This is ideal for the type of group we are creating as communications with the support staff should be via a central help desk.

Here is where your problem arises because Microsoft publicly state that you cannot use a Security Group provide Site Collection Administrator  access. The reality is that it cane be done both manually and in code.


To add the security group as the primary SCA you will need to have O365 tenant admin permission. Go to the SharePoint Admin Center, locate the site collection in question , select the check box next to it and then click on Owners in the ribbon. In the Manage Administrators window you can add the security group just like you would any other user, both as a Primary or Secondary SCA.

If you don’t have tenant admin permission you will need to have SCA rights on the site collection in question. In this case you will be able to add the security group as a Secondary SCA by going to Site Settings and then selecting Site collection administrators under Users and Permissions. you can then add the security group as a Secondary SCA just like you would any other user .


You may have a requirement to retrospectively apply a change like this across your tenant using PowerShell or to set this up when you are provisioning the site collection as part of a provisioning app similar to the PnP provisioning samples.

The usual way to add someone as a SCA to a Site Collection using PowerShell is to use the Set-SPOUser cmdlet for example like this…

Set-SPOUser -Site $siteUrl-LoginName $userEmail -IsSiteCollectionAdmin $true 

You will notice that they way to identify the user being added as an SCA is via the -LoginName parameter which requires a valid email address in the tenant. The issue is that the  Security group doesn’t have an email address so it can’t be used it here. I’ve tried a number of approaches to including using the Object ID returned from Get-MSOLGroup cmdlet to no avail. I was able to resolve it and the simplest way to avcheive uses the claims encoded identity for the security group.

First you need to determine the claims encoded identity for the security group. One simple manual way of doing this is to go to a site and use the Check Permissions feature Under Site Permissions in Site Settings. Check the permissions for the security group in question and part of the report provides you with the claim encoded identity. It should look something like ‘c:0-.f|rolemanager|s-1-1-11-111111111-1111111111-1111111111-11111111’. In my case this manual approach is fine but it is probably possible to retrieve this using PowerShell as well.

Once you know this information you can substitute it where you would normally use the users email address in the SetSPOUser call and it will recognise the security group and set it in the Secondary Site Collection Admins group e.g.

Set-SPOUser -Site $siteUrl -LoginName "c:0-.f|rolemanager|s-1-1-11-111111111-1111111111-1111111111-11111111" -IsSiteCollectionAdmin $true

To achieve the same in code as part of a remote hosted app you will need to use CSOM. Something like the following C# ,NET code should allow you to do this. Obviously you will need to determine the claims ID for each of the security groups you want to add and will have already created you context object ctx

Dictionary<string, string> groupsForAdminAccess = new Dictionary<string, string>()
    {"Global Support Staff", "c:0-.f|rolemanager|s-1-1-11-111111111-1111111111-1111111111-11111111"},
    {"Legal eDiscovery","c:0-.f|rolemanager|s-1-1-11-111111111-1111111111-1111111111-11111111"}
 foreach (KeyValuePair<string, string> groupToAdd in groupsForAdminAccess)
    User claimsGroupUser = ctx.Web.EnsureUser(groupToAdd.Value.ToString());
    claimsGroupUser.IsSiteAdmin = true;


Leave a comment

Filed under General SharePoint Development, Office 365, SharePoint, SharePoint Online

Customising Overlay Colours in SharePoint Calendars

It was recently noted by one of our technical team that when setting up Calendar overlays the list of colour​s available seemed a bit screwed up with the same colour name appearing next to different colours. This issue is a result of the colour palette used to generate the custom Look for all sites in our SharePoint environment and is actually not a currently defined by Microsoft as a bug even though it would be nice to have a bit more clarity and control of how these are determined. So the question was asked ‘can we change these colours?’

I have done some additional research and confirmed that the colours presented to the user for calendars are generated by SharePoint based on the theme selected for the web site (in our case the custom Look) and not immediately customisable for calendars out of the box. Specifically each one of the 9 colours defined comes from the definition of Accent 1-6, Hyperlink, and 2 of the text/background theme colours in the custom Look. This is why the first 6 colours map directly to a colour defined by the accent shown in the SharePoint Color Palette Tool. I think the Hyperlink text/background colours are generated based on the definitions within the custom Look.

There are a number of solutions for implementing customisations but these are point solutions that apply to a specific calendar in a specific site and end users would need to deploy the solution themselves to customise the colours in their calendars. We could possibly look at updating our custom site provisioning app to deploy this customisation with a calendar when the site is first created.

For future reference here a few of possible point solutions (all use a similar approach)…

Leave a comment

Filed under General SharePoint Development, SharePoint, SharePoint 2010, SharePoint 2013, SharePoint Online

Free ‘second shot’ on Microsoft Cert Exams is back!

From today (5th January 2015) until the end of May this year Microsoft is once again offering those wishing to sit a Microsoft Certification Exam a second shot at passing the exam if they fail the first time around.

There are of course conditions associated with the offer but these are clear and reasonable such as booking the retake within 30 days of sitting the first attempt.

Full details can be found at the Microsoft Born To Learn blog

Leave a comment

Filed under Computers and Internet, General SharePoint Development, Office 365, SharePoint, SharePoint 2010, SharePoint 2013, SharePoint Online, Wollongong .Net Users Group

How to avoid cell merging when exporting to Excel in SSRS 2008

Have you had problems with merged cells in excel exports from Reporting Services reports? I have so I did some hunting around and found some information that looks like its solved my problem.

Add this post and the information at the following MSDN blog and you should have your problems with merged cells in excel exports from Reporting Services reports nailed.

Ed Spencer's Blog

Lets be completely direct here. Cell merging in Sql Server Reporting services after exporting to Excel, is a common nightmare.

It happens because the engine that transforms the report tries to do so on a presentation basis.

I have been developing reports in SSRS for a few years now, and here are the best ways around the issue that I have found:

1. Don’t use standalone textboxes for titles, or any non-data elements.
Rather than fiddle with these for hours trying to get them to line up, just insert another row or two as headers above your data driven report element (e.g. table). You can then play with the presentation of the cells to make it look like it isn’t part of the same table. This can be done by colouring certain borders white to give the impression that there is nothing there.

2. Use points and not centimetres when…

View original post 96 more words

Leave a comment

Filed under Business Intelligence, General SharePoint Development, SharePoint 2010, SharePoint 2013

Whitespace in Excel and other issues with exports from Reporting Services

I discovered some interesting behaviour in the Excel export files from Reporting Services today. Some users of the system I’ve been working on have a requirement to periodically export the data in MS Excel format so they can do some additional analysis. They recently began testing the export function on the report in question and have noted theat they are unable to generate a pivot table because there are additional empty rows and columns before at the top and on the left of the table in the spreadsheet.

My assumption was that when you export to Excel the export rendering engine would only attempt to output the tables on the page. Obviously my assumption was invalid and the rendering engine attempts to render into Excel as closely as possible to the web version including any whitespace around the report. The more whitespace the more blank cells, rows and columns will appear in the Excel export. The quick solution to the issue is to simply move the various object on the report so the edges butt up against each other and the sides of the page. Removing the whitespace in this way means the rendering engine wont include additional empty rows or columns in the spreadsheet.

Note also that I’m note using a head or footer for the exportable version of the report. These will also cause issues if the purpose of the export is to apply a pivot table to the data for analysis.

In-line images also cause issues in the exported Excel spreadsheet. I have small icon in cells in the table with actions defined to open sub-reports. The images were embedded in rectangle objects to enable control of positioning and size. The behaviour I saw in the Excel export spreadsheet was that I had merged columns around the images and this again interfered with apply a pivot table to the data. My only solution here was to remove the images from the exportable version of the report.

Finally, the report had two tables on the page. The first was a summary table and the second was a table containing the detail row for every item meeting the users selection parameters. When the user exports to Excel both tables appear on the one spreadsheet and the user is unable to simply create a table or pivot table on either of the data tables. Ideally the user wants each data table on its own spreadsheet. The resolution to this is again simple if you think in terms of what the rendering engine is trying to do i.e. render what it sees on the page into a similar looking spreadsheet. First we want to force the second table to appear on a new page. This is achieved by configuring the tablix for the first table to put a section break after it (see image below). Once this is configured the second table will appear on a separate page and the rendering engine will interpret the section break as needing to put the second table onto a second sheet in the Excel file.


Leave a comment

Filed under Business Intelligence, General SharePoint Development, SharePoint 2010, SharePoint 2013

Filtering datasets using a boolean report parameter to determine IN value set

Have you every come across a reporting requirement where the user of the report needs to be able to choose between two sets of criteria to filter on a value in the main report. I recently had a requirement where the report I was developing listed tasks by default that had a status of Active or On Hold but I needed to be able provide the user with the ability include tasks with a status of  Initiation, Closed or Cancelled.

The standard report parameter configured for multivalue could meet the underlying requirement by setting the default selections to Active and On Hold  and allowing the user to add in the remaining 3 status’ if they required for example:

Status Multivalue Parameter

but for this report the requirement specified that the user should only have to make a yes/no decision regarding whether to include the additional status tasks in the report i.e.

Boolean Status Parameter

I looked closely at the configuration options for Boolean report parameters and tried a few different things try to configure it so that the two possible values it could be set to were the two status value sets but given it is a boolean the only values it can set the parameter to are 1 or 0. A logical restriction but not helpful for meeting my requirement.

I looked at the options for configuring other types of parameters, such as the Multivalue type, to display like a boolean but again  found nothing helpful for meeting my requirement.

I eventually  solved my problem using the Boolean type parameter and expressions within the filter criteria on the main report dataset.
Here is how I set it up…

1. First set up the boolean report parameter:


and configure the default value to False (for my requirement)


2. Now create two report parameters that will provide the two value sets for the filter. Set the Name of each parameter so it is evident which one includes and excludes the values. In my case I used StatusExcludeSet and StatusIncludeSet.  Set the Data Type to Text and check Allow multiple values. I also selected Internal for visibility as we don’t want either of these parameters to appear to the end-user or be available for setting within SharePoint.


To complete the configuration of the parameter select Default Values from the left panel, select Specify Parameters and then add the default values. In my case these are the actual strings Active and On Hold but check in your dataset to verify you don’t need to specify the actual Id values here rather than the display text.


Don’t forget to repeat this step for the second parameter that includes the additional values in the set that were excluded in the one you’ve just created.

3. Next open properties dialog for the primary dataset, select Filters on the left panel, click Add to create an empty filter and select the relevant field, in my case Status, for the Expression e.g.


4. Next we need to configure the value that will be used for filtering. We will be filtering on a set of values so change the filter Operator to In (as shown in the picture above) and then click on the function (ƒx) button to the right of the Value field. For the expression I used the IIF conditional function to test the value of the boolean parameter IncludeExtraStatusParameter and based on its value return either the value set contained in the Internal parameter StatusExcludeSet or StatusIncludeSet e.g.

 =IIF(Parameters!IncludeExtraStatusParameter.Value = CBool("False"), Parameters!StatusIncludeSet.Value, Parameters!StatusExcludeSet.Value)


Leave a comment

Filed under Business Intelligence, General SharePoint Development, SharePoint 2010, SharePoint 2013

Report vs Dataset Caching in SharePoint

In one of my previous posts I mentioned two techniques for improving the performance of reports hosted in SharePoint … Dataset caching and Report caching, but how do you decide you which one to apply?

Report Caching when enabled will stored a copy of the data required to run the report (this is not to be confused with snapshotting of reports where a complete rendered copy of the report stored). The cached data is only accessible to the report that cached it and can have specific refresh and expiration configuration settings that suit the requirements of the report. This is particularly appropriate for reports that contain information that is periodic in nature (e.g. daily/weekly/monthly status reports) but the information isn’t referenced in other reports. It should be noted that if data shown in a cached report changes this will not be updated in the report until the cache is and as a result you may find any other reports that reference that data directly will show different, more current, results

Shared Dataset Caching enables the storage of the data on a per dataset basis. The cached data is accessible to any report that depends on the Shared Dataset and all reports gain the performance advantage from the cached data. This is particularly appropriate where the rate of change of the various data sources is understood and can be taken into account when configuring the caching of the various shared datasets you have available. Configuring each Shared Dataset’s cache properties to take into account the churn rate on the source data will enable you ensure that the data stored is maintained at optimum freshness whilst still delivering the improved performance benefit to your reports of not having to query all the way back to the SQL Server for the data.

If you are combining both approaches, which is technically feasible, careful consideration should be given to when the cache at each point is expired/refreshed, how often the source data is expected to change and what the requirements of the consumers of the reports are regarding the currency of results displayed in reports, for example do they require the figures correct as of 12pm each day or the most up-to-date figures possible?

Leave a comment

Filed under Business Intelligence, General SharePoint Development, Project Server 2010, SharePoint 2010, SharePoint 2013