Top 10 posts of 2018 on SharePointLibrarian.com

Thank you, friends, for making 2018 my best blogging year yet. I enjoy sharing what I learn along the way, and am glad to know it’s being found useful.

The following includes the ‘Top 10’ most popular posts by views published in 2018:

Solution: “We’re sorry. We can’t open {file} because we found a problem with its contents.”
When using SharePoint Designer workflows to automatically create documents from a template you might run into an error message: “We’re
Read more.
Customize styles/formatting of SharePoint list column header rows
Note: This post applies to on-premise/server lists or O365 lists set to “classic” view. This will not work on modern
Read more.
How to resolve SharePoint list view threshold error: “The view cannot be displayed because it exceeds the list view threshold…”
SharePoint lists have a default limit of 5,000 items per view. But lists can contain 30 million items (just not
Read more.
How to create a SharePoint Governance Plan (includes template)
What is governance? The Burton Group says “Governance uses people, process, technology, and policies to define a service, resolve ambiguity,
Read more.
Using today’s date and/or current time in calculated columns and list view filters
I previously shared how to create a “Today” column in SharePoint that would always be up-to-date even if list items
Read more.
Embedding Power BI Report Server (PBIRS) reports in SharePoint
If you’re on-premises and looking to embed a Power BI Report Server report in SharePoint, you’ll find it’s not as
Read more.
How to create a powerful date table or DateKey in Power BI
DateKeys are essential for relative time measures. In “manage relationships” you tie the ‘DateKey' to a date field in each
Read more.
View and share Power BI Report Server (PBIRS) reports full screen
The O365 Power BI Service has an easy “expand” icon on reports to make them full screen whereas Power BI
Read more.
Intro to conditional formatting & rules/validation when customizing SharePoint new item forms with PowerApps in Office 365
This is my intro to conditional formatting & rules/validation when customizing SharePoint new item forms with PowerApps in Office365. The
Read more.
Show last modified date for a SharePoint list without using code
How to display last modified date for a bulk-updated list in SharePoint without using code.
Read more.

Want more? Check out the archives for all previous posts:


Solution: “We’re sorry. We can’t open {file} because we found a problem with its contents.”
When using SharePoint Designer workflows to automatically create documents from a template you might run into an error message: “We’re
Read more.
Customize styles/formatting of SharePoint list column header rows
Note: This post applies to on-premise/server lists or O365 lists set to “classic” view. This will not work on modern
Read more.
How to resolve SharePoint list view threshold error: “The view cannot be displayed because it exceeds the list view threshold…”
SharePoint lists have a default limit of 5,000 items per view. But lists can contain 30 million items (just not
Read more.
How to create a SharePoint Governance Plan (includes template)
What is governance? The Burton Group says “Governance uses people, process, technology, and policies to define a service, resolve ambiguity,
Read more.
Using today’s date and/or current time in calculated columns and list view filters
I previously shared how to create a “Today” column in SharePoint that would always be up-to-date even if list items
Read more.
Embedding Power BI Report Server (PBIRS) reports in SharePoint
If you’re on-premises and looking to embed a Power BI Report Server report in SharePoint, you’ll find it’s not as
Read more.
How to create a powerful date table or DateKey in Power BI
DateKeys are essential for relative time measures. In “manage relationships” you tie the ‘DateKey' to a date field in each
Read more.
View and share Power BI Report Server (PBIRS) reports full screen
The O365 Power BI Service has an easy “expand” icon on reports to make them full screen whereas Power BI
Read more.
Intro to conditional formatting & rules/validation when customizing SharePoint new item forms with PowerApps in Office 365
This is my intro to conditional formatting & rules/validation when customizing SharePoint new item forms with PowerApps in Office365. The
Read more.
Show last modified date for a SharePoint list without using code
How to display last modified date for a bulk-updated list in SharePoint without using code.
Read more.

MCSA: O365 certification & 70-346/70-347 exams retired; replaced by role-based M365: Enterprise Administrator Expert certification & MS-100/MS-101 exams

If you’ve been studying for 70-346 and 70-347 to get your MCSA, there’s no better time than right now to schedule those exams. As of March 31, 2019, they will both be retired. The exam page for each currently features the following notice.

“This exam retires on March 31, 2019. A replacement exam may be available. To learn more, visit our blog: https://www.microsoft.com/en-us/learning/community.aspx and review our Exam Retirement posts.”

New Exams

The retirement post last updated Dec 21, 2018, lists the two exams as follows, being replaced with role-based MS-100 and MS-101.

From https://www.microsoft.com/en-us/learning/microsoft-365-exams.aspx

Retired Certifications

Along with the 32 exams slated for retirement, they’re retiring a number of certifications as well, replaced with role-based certifications.

If you’ve already gotten your MCSA: O365, don’t worry – it’s still active after retired.

“This certification retires on March 31, 2019. You will no longer be able to earn this certification after this date. If you earn it prior to that date, it will remain in the Active Certifications section of your transcript after that date.”

From https://www.microsoft.com/en-us/learning/mcsa-office365-certification.aspx

What should I do now?

This info-graphic illustrates the various paths available:

What if I already have my MCSA?

  • Unfortunately, if you’ve already gotten your MCSA: Office 365, there is no transition exam. If you want to new certification you’ll need to start anew and take MS-100/MS-101 since the new certification is “expert” level as opposed to your current “associate” level.
  • You could also consider upgrading your MCSA to the existing MCSE: Productivity certification instead of pursuing the new M365 Expert certification

SharePoint calendar all-day events showing as previous day in content search web parts

Despite your regional settings being correct, all-day events for some reason are using UTC time when they’re stored and are likely showing as the wrong day in content search web parts and similar web parts.

Though they look correct as an individual item or on a calendar, the way they’re stored doesn’t acknowledge your regional settings and, when pulled through a search web part, render in UTC as beginning 6 (or other) hours earlier than they actually do.

For example, I have a content search web part that pulls all events in our organization and show’s “today’s events.” If there’s an all day event, it shows as starting 6:00 PM the day prior to its actual day.

I could not find a straightforward solution to fix all affected events. And my solution is not ideal, but it accomplishes a need. You could instead explore the possibility of creating a calculated column that adds hours to fix the alignment. But please share if you’ve encountered the same issue and have resolved it a better way.

To get around this issue, I created a SharePoint Designer 2010 Workflow on calendars that:

  • Triggers on creation or modification
  • “Unchecks” the All Day Event box
  • Sets a specific start time on the start date (I chose 7:00AM)
  • Sets End Time to the original end time (if you don’t do this, it sets end time to blank).

Solution: “This web site has been configured to disallow page editing with SharePoint Designer.”

There are three possible causes I’m aware of that you should check if you receive this message:

  • Central admin settings not configured properly
  • Site collection settings not configured properly
  • You’re using a Project Web App (PWA) site template and can only fix this on SharePoint Server

I’ll cover the solutions for each in the same order:

Central admin

Go to central admin –> manage web applications

Select the web app on which you received the error and select “SharePoint Designer” from the general settings drop-down. Make sure the first box is checked and click “OK.”

Site collection settings

Go to site settings –> SharePoint Designer Settings (under Site Collection Administration)

Make sure “Enable SharePoint Designer” is checked and click OK

Project web app template issue

Log in to a SharePoint server and go to C:\Program Files\Common Files\microsoft shared\Web Server Extensions\16\TEMPLATE\SiteTemplates\PWA\XML

Open the ONET XML file in a text editor like NotePad

Search for “webdesign” (Ctrl+F to open search) and delete the following property:

DisableWebDesignFeatures="wdfeditpages"

Perform an IIS reset (run SharePoint management shell as administrator)

You may need to repeat these steps on multiple servers if you have multiple web front end servers. You can just copy the ONET file and overwrite the same file on the other servers in the same location. Don’t forget to do an IIS reset afterward on each.

Close and re-open your Project Web App site in SharePoint Designer and you should now be able to edit as you do with other sites.

Add Bing search results to SharePoint results

Want to make your “search everything” really search everything? Add bing results to your SharePoint search results. You can do this as a result block (added to your results column) or as a separate results column as seen in the example at the bottom of this post.

Bing supports OpenSearch protocol while Google does not. This allows us to utilize Bing’s search engine from within our SharePoint environments (server or online). If you’re using SharePoint Server (on-prem), you’ll need to provide a proxy server for crawling and federation in central admin –> search administration.

The rest of the steps are the same for either online or on-prem:

  1. Create result source
  2. Create query rule

And you can do a similar process for a single site collection (through site collection settings) but I’ll be demonstrating this through central admin (for all site collections).

Create result source

  1. From central admin –> search administration, click on “Result sources” under “Queries and Results”
  2. Click “New Result Source”
  3. Set the settings as follows, pasting https://www.bing.com/search?q={searchTerms}&format=rss&Market=en-us for the Source URL.
  4. Leave credentials as “anonymous” and click “Save”

Create query rule

  1. Click on “Query Rules” from the left nav in Search Administration
  2. Set context to “Local SharePoint Results” and click “New Query Rule”
  3. Name it “Bing Query Rule” and click “Remove Condition” since we want Bing to be included in every query
  4. Click “Add Result Block” and set as follows:

Optional settings:

  • You can set the “context” settings to apply this rule to ALL result sources, or just to the Local SharePoint Results (default)
  • You can edit the result block so that a “more” link appears. Link it to https://www.bing.com/search?q={subjectTerms} to allow users to leave SharePoint and see even more results from Bing
  • Choose whether you want the “Bing results” block to appear at the top of SharePoint results or ranked. I recommend ranked if you’re only wishing to supplement your search for the occasional user who thinks SharePoint search is internet search.

Implementation

There are too many ways to implement this new result source to share here, but use your imagination. Each “search results” web part can be configured to use or ignore query rules.

In this example, we’re using query rules and promoting the top two bing results to the top of our SharePoint results with a “more” link that would take us out to Bing. This method requires the result source and query rule.

In this example, we’ve created a third column on our search results page that shows the top Bing results alongside our top SharePoint results. You don’t need a query rule for this, just the added result source and a separate web part.

So finally “Search everything” can really “search everything.”

Import a synonym thesaurus to improve search in SharePoint server

Health care or healthcare? Doctor or physician? Emergency room or ER?

No matter which terms, spellings or abbreviations a SharePoint search user uses, they’re searching for something specific. By utilizing a simple thesaurus, we can make sure that a user searching for “ER” also gets results that include “emergency room.”

How to create a thesaurus

Note: Not currently available in SharePoint online/O365. But should be supported in 2013/2016/2019. 

What to include

If your industry is like most others, you have many acronyms – that’s a good starting place for building a thesaurus. Your thesaurus can contain considerations such as:

  • Acronyms (ER/ED for emergency room/department)
  • Abbreviations (Dr. and doctor)
  • Common misspellings (absess and abscess)
  • Formal and casual pairs (gastric and stomach)
  • Synonyms (purchasing, requisition, ordering, supplies, etc.)
  • Variations of a word (radiology, radiologist, radiologic, radiological)

Once you’ve compiled your basic thesaurus from going through the above suggestions, you should analyze the search reports provided by SharePoint to see what people have been actually searching but not finding. Then use those search terms to further enhance your thesaurus.

For example, when my governance committee analyzed search reports, we came up with people searching for “KU” (our local University and neighboring health system) and added “University of Kansas” to our thesaurus.

Import your completed thesaurus

Note: Each time you import, it overwrites the existing thesaurus. I recommend updating the same file you’ve created with new lines rather than recreating it multiple times. Also be sure to save the file in a place others can access and update in your absence. You won’t be able to export it later.

Repeat these steps each time you want to update the thesaurus:

Note: You must be a search service application administrator to import the thesaurus.

Log in to a SharePoint server and run PowerShell with the SharePoint snap-in or just use the SharePoint management shell (recommended). Either way, run as administrator.

Run the following cmdlets

$searchApp = Get-SPEnterpriseSearchServiceApplication 
Import-SPEnterpriseSearchThesaurus -SearchApplication $searchApp -Filename <Full UNC Path>

If successful, you’ll see the following confirmation:

Test your work!

The changes should be almost instant, with no need to perform any crawls. Here’s an example of how in our search “med staff” now also searches for “medical staff”.

Governance activity: analyze SharePoint search reports and provide synonyms

I have an outstanding group of forward-thinking, hard-working volunteers who gather monthly to discuss ways in which we can improve our implementation and utilization of SharePoint at LMH Health. Tomorrow is our last meeting of the year, and we’ll be spending the majority of our meeting poring over abandoned search queries and “no result” queries provided to us by the out-of-the-box SharePoint usage and popularity reports.

Is it worth the time?

Absolutely. No matter how big your company is, investing time in improving search – even if it will only save a searcher 5 seconds – is the logical action from both a financial and efficiency standpoint. Here are some real-world stats from LMH Health (a relatively small health system) to further convince you:

  • 19% of our annual SharePoint site visits are from someone performing a search query
  • That’s 18,972 searches per year, or about 75 each day
  • If each search takes thirty seconds, we spend 158 hours searching annually
  • If we play with a $20/hr wage, we would be spending $3160/year searching or $0.17 per search
  • If we can improve search so that we reduce that thirty seconds to twenty seconds, we’ll save 52.7 hours and $1054/year
Annual costs and savings scenario of SharePoint search times

So if saving money and time is important to you, this is worth exploring with your governance committee or SharePoint team.

How to get your search analytics/reports

Note: You must be at least a site collection admin to access the search reports.

1. Go to Site Settings –> “Popularity and Search Reports” under Site Collection Administration

2. I recommend the three reports below (all queries by month except “Query rule”) for a high-level overview with which your committee will be able to assist.

Note: You could individually use the Query Rule report to analyze the success (or lack thereof) of any query rules you’ve implemented, but I wouldn’t involve your committee in its analysis.

Available search reports as a site collection admin

3. Once those files are downloaded, I’d combine each of the months (each tab of each report is a different month) using Power BI or some excel magic to determine the queries most frequently searched on your site collection (by summing query text counts across tabs)

Engage your governance committee

Now it’s time to present your findings to your committee members. At our meeting we’ll be splitting into groups and each taking a portion of the top abandoned and “no results” queries to determine:

  1. What the searchers were attempting to discover on our intranet using that query text
  2. What synonyms we can come up with to add to a synonym dictionary or query rules to improve search.
    1. Synonym dictionary is installed via PowerShell for on-prem only
    2. Query rules can be on-prem OR hybrid and are a way of promoting certain results based on rules you establish

For my group, I’m using two files: a search thesaurus worksheet and a search analysis worksheet. 

Search thesaurus

This is the “blank slate” I’ll be sharing with each group, challenging them to fill the rest of the rows with words they use that often don’t bring up the correct result because the actual result is using a variation of the query

Search analysis

The second worksheet is focused on analyzing actual, real search queries you collected from the SharePoint search reports. Pick the highest occurring results from the abandoned and “no result” reports and include them in the tables. Have your committee speculate as to what the desired resource actually was and provide alternative names/words so that you can build rules and/or synonyms around it.

Conclusion and follow-up

Always follow up on governance tasks and finish your part of the work before your next meeting. Then you can share the results/impact with your members, celebrating your collaboration.

Once we’re finished with this activity tomorrow, I’ll collect the results and add appropriate suggestions to our synonym dictionary we deploy via PowerShell. There may also be suggestions made that would make great query rules.

For example, if one of your “top queries” is consistently “Dress code,” maybe you’ll create a query rule that provides the actual (and hopefully only) dress code policy at the top of results in its own promote block.

An effective governance committee is one that is put to use. Leverage your organization’s knowledge and inspire your members to be daily advocates for your digital workplace. It’s as much theirs as yours, and we make it better by building it for and with the users.

SharePoint’s Content Query Web Part (CQWP) is missing!

Applies to: SharePoint Server/on-prem

I love SharePoint’s Content Query Web Part (CQWP) – but if you’re missing a feature in your site collection(s), you may not have it! Luckily you can get it back in just four clicks.

  • Go to Site Settings
  • Click “Site Collection Features” under “Site Collection Administration” (Note: Must have full control or site collection administrator permissions to see this option)
  • Activate “SharePoint Server Publishing Infrastructure
  • Now your CQWP should be available as a web part under Content Rollup.

If you need to activate the SharePoint Server Publishing Infrastructure across all site collections in a web app, I recommend checking out 
Salaudeen Rajack’s site, SharePointDiary.com, for instructions on doing this via PowerShell.

Increasing MySite OneDrive for Business Storage

I recently ran into the following error message when using OneDrive for Business via MySites (on-prem 2016).

“No free space. Your site is out of storage space and changes can’t be saved…”

The recommendation is to empty your recycle bin, but the recycle bin is probably empty.

The default storage limit for MySites is set to 100 MB (which doesn’t go so far these days).

If you already have a bunch of personal sites created, it’s not as easy as tweaking the quota template. You will need to update the quota template for future sites, then reset all existing sites to use the updated template as well.

Update the Quota Template

Learn more on quota templates: Create, edit, and delete quota templates in SharePoint Server

Set existing sites to use updated template

Now that you’ve set the storage quota template for future sites, we need to update pre-existing sites to adopt this new storage limit. We’ll need to do this via PowerShell.

  1. Remote connect to a SharePoint server
  2. Open SharePoint 2013/2016 Management Shell as administrator (right-click, Run as Administrator)
  3. Run the following script, replacing the web app address with your own, and the template name if different from Personal Site
    $SPWebApp = Get-SPWebApplication https://mysites.MYORG.org
    
    foreach ($SPSite in $SPWebApp.Sites)
    {
        if ($SPSite -ne $null)
        {
            Set-SPSite -Identity $SPSite.url -QuotaTemplate "Personal Site"
            $SPSite.Dispose()
        }
    }

And that’s it! Once that has run successfully, all of your MySites should now have the new limit in place.

Using current date and/or time as default column value in SharePoint

Date and time fields can have a default value of “Today” or “Now” that populates if you don’t enter another value in the field before saving an item. In your date/time column settings: 

  • If you choose type “Date only” you’ll get the current date
  • If you choose “Date and time” you’ll get the current date and time

You can instead check the box for “Use calculated value” and use “=Now()” as the formula to return the exact same result. However, the benefit/difference of using the calculated option with =Now() instead of the “Today’s date” option is that if your users change from “Date only” to “Date and time” type down the road, “=Now()” will include both date and time values already. It doesn’t hurt to have time included and not displayed.

Finally, you can use the calculated value option to do true calculations for things like “Due date” or “Reminder” where you use a formula like:

  • =Now()+7 is exactly a week from the current date and time
  • =Now()+(1/24*n) where you’ll replace “n” with a number of hours from the current time

Note that these values will be static, meaning once the date/time populates, it doesn’t update when you edit the item. It’s merely a default value inserted upon creation if you don’t manually enter a different value.

Also, when using Now() with hourly calculations you’ll want to double-check your time zone settings (Site settings –> regional settings) and adjust your formulas accordingly if you’re unable to identify a fix.

If just using “Date only” type, you can instead use =Today() and not worry about time zone so much unless your calculations will involve units less than one day.

Related topics:

If you need a “live” always-updated value regardless of items being modified, you’ll need to create a separate “Today” column using Microsoft Flow or SharePoint Designer:

This post also includes ideas for calculations using today’s date.