SharePoint 2013 SSRS: Report Errors When Attempting to Pass Parameters via URL

I was assisting one of my co-authors and colleagues, Jim Pletscher, with Reporting Services 2012 running on our new SharePoint 2013 Farm (so integrated mode of course using the Reporting Services Service Application). We have two (2) dedicated servers that only run Reporting Services.

One issue was that when running a report we would get a connection issue but after hitting F5 (once or twice) the report would eventually render as if nothing was wrong. Therefore I thought maybe one of the Reporting Services servers was acting funny as when one server was accessed it was fine but when the other server was accessed, things weren't right.

We left that one alone as we were getting a similiar issue consistently when attempting to pass parameters into the report rendering via the URL. Investigating the logs, I found the following:

Throwing Microsoft.ReportingServices.ReportProcessing.ReportProcessingException: , Microsoft.ReportingServices.ReportProcessing.ReportProcessingException: Cannot create a connection to data source 'REPORTS_DW'. ---> System.IO.FileLoadException: Loading this assembly would produce a different grant set from other instances. (Exception from HRESULT: 0x80131401)

There are tons of posts out there with "System.IO.FileLoadException: Loading this assembly would produce a different grant set from other instances" mostly involving .NET Framework 2.0 and Hotfixes. I was sure it couldn't be that.

I stumbled upon a related post but dealing with Excel Services 2010 here where the guy was saying to restart the services. After goofing my Search Service Application previously by trying to restart services, I only totally reboot servers now. So I was going to do that, however, I noticed in the comments of that post that some other guy said restarting the services did not work and that IISReset did the trick.

I was doubtful but decided to try IISReset first on both servers. I waited for the service to come back and I ran the original report without parameters: that worked fine. Next I clicked on the link in the report that contained the report URL with the parameters - it now worked without any issues!

I am glad it was a simple fix but I was hoping for something a little more complex as there is no explanation of why this would happen. If it happens again I am going to suspect an SSRS app pool issue.


 

SharePoint 2013 Search: Adding Search Result Counts to Your Refinements

Overview
The Refinement web part has the ability to display result counts along side the refinement values for a particular managed property out-of-the-box. However, this option is not available within the web part properties. Enabling search result counts in your refinements requires a tweak within the refinement display templates.

Displaying Counts for All Refinements
The easiest configuration is to simply turn counts on for all refinements. This is achieved by modifing the Filter_Default.html and Filter_MultiValue.html display templates. Using SharePoint Designer 2013, you may access the display templates easily by following the similiar steps as outlined here.

This time instead of opening the Search sub-folder under Display Templates, open the Filters folder:

 

Locate Filter_Default.html. Right-click the file and select Edit File in Advanced Mode:

Inside the code locate the ShowCounts variable and change from false to true:

 

Save the changes. Your single value refinements now display the counts. Repeat the same process on the Filter_MultiValue.html to display counts on your multi-value refinements.

 

Displaying Counts for Selective Refinements

There may be situations or search pages where you do not wish to display the refinement counts. For this case, and for best practices sake, it is recommended to make a copy of the Filter templates and create a custom refinement display template accordingly.

 

Simply right-click the Filter_Default.html file and select Copy:

Right-click again and select Paste:

 

Rename the file accordingly:

 



Edit the new copied file and change both the Title and ShowCounts values:

 

Save the changes. Repeat the same process for the Filter_MultiValue.html if desired.

 

Now when configuring the Refinement web part, when you choose the refiners, your new display template is available in the drop-down:

 



 

You may now choose to show refinement counts on single and/or multi-value refinement properties:

 



Search On!

 

SharePoint 2013/2016: Listing out Existing SharePoint App Pools

The SharePoint App Pools that run your web applications exist in SharePoint whether or not they physically exist in IIS. I previously looked at consolidating some of these app pools to reduce the worker processes and memory consumption. I used this post as a guide.

However, my manager decided that he wanted all of the web apps to go back to using their own app pool. You can't just go and create a new app pool in IIS and assign it to your web app (technically you can but that's wrong).

I found a post here that creates a function for setting the app pool. You must know the name of the SharePoint App Pool. Since I previously deleted mine I used a reference in that function to list out the available SharePoint app pools:

[Microsoft.SharePoint.Administration.SPWebService]::ContentService.ApplicationPools | ft Name

You may use any of the existing names when calling the function in Ivan Josipovic's post.

Notice, however, the Central Admin app pool is not listed. The app pools listed are all for the content-based web applications. To get the administrative app pools, use this command:

[Microsoft.SharePoint.Administration.SPWebService]::AdministrationService.ApplicationPools | ft Name

I therefore created a new function to handle my Central Administration app pool change:

Function Set-WebCAApplicationPool($WebAppURL,$ApplicationPoolName){
    $apppool = [Microsoft.SharePoint.Administration.SPWebService]::AdministrationService.ApplicationPools | where {$_.Name -eq $ApplicationPoolName}
    if ($apppool -eq $null){
        write-host -foreground red "The Application Pool $ApplicationPoolName does not exist!"
        return 1
    }
    $webapp = get-spwebapplication -Identity $WebAppUrl
    if ($webapp -eq $null){
        write-host -foreground red "The Web Application $WebAppUrl does not exist!"
        return 1
    }
    $webapp.Applicationpool = $apppool
    $webApp.Update()
    $webApp.ProvisionGlobally()
    write-host -foreground green "$WebappURL Application Pool has been changed to $ApplicationPoolName"
    return 0
}
Set-WebCAApplicationPool -WebAppURL "http://SP2013SVR:12345" -ApplicationPoolName "SharePoint Central Administration v4"

Obtaining Service Application App Pools is much easier as there is a SharePoint PowerShell cmdlet for this:

Get-SPServiceApplicationPool | ft name

These are the Service Application Application Pools that are running on the entire farm - regardless of the server. I consolidated several Service Applications to use the SharePoint Web Services System pool if the services run on the same application server.

SharePoint 2013 Search: Getting a Value From the Previous Search Result

If you have been playing around with the new Search Display Templates, you have probably seen lots of references to ctx.CurrentItem. This is the current search result item from the result set. However, what about the previous item? Maybe you want to group results or only display certain values if the previous value is different than the current value. The value may be of any managed property or regular property of the CurrentItem object.

There isn't a PreviousItem object so I needed to figure out a different way to get to the previous item. The CurrentItem has a ParentTableReference which contains a collection of ResultRows. Perfect!

The ctx object stores the CurrentItemIdx which can be used to get a particular ResultRow and any property of the search result object (treat it like the CurrentItem).

So now I can get any result value relative to the current result item by using the index as a pointer. The previous item would be ctx.CurrentItemIdx - 1 and the next item would be ctx.CurrentItemIdx + 1.

Here is the logic in a nutshell:

<!--#_ 
        if (!Srch.U.n(ctx.CurrentItem.ParentTableReference) && ctx.CurrentItem.ParentTableReference.TotalRows > 1) {
           var previousIndex = ctx.CurrentItemIdx - 1;
           if (previousIndex >= 0)
           {
                var previousValue = ctx.CurrentItem.ParentTableReference.ResultRows[previousIndex].<<property>>;
               <<<add other conditions and set flags here>>
           }
        }
_#-->


You can now check the previous value of a property against the ctx.CurrentItem value of the property and alter the display or perform other functions accordingly.

Let me know if you find a better way!


 

SharePoint 2013 Search: Adding a New Admin Component to a Search Server

This blog is part of my Scaling Out Search Series and describes the processes for adding a new admin component to a new search server:

Get Search Service Instance and Start on New Index Server$ssi = Get-SPEnterpriseSearchServiceInstance -Identity "<<new index server>>"
Start-SPEnterpriseSearchServiceInstance -Identity $ssi

Wait for Search Service Instance to come online
Get-SPEnterpriseSearchServiceInstance -Identity $ssi

(The full script contains a loop that waits for the service to come online)

Clone Active Search Topology$ssa = Get-SPEnterpriseSearchServiceApplication
$active = Get-SPEnterpriseSearchTopology -SearchApplication $ssa -Active
$clone = New-SPEnterpriseSearchTopology -SearchApplication $ssa -Clone –SearchTopology $active

Add New Admin ComponentNew-SPEnterpriseSearchAdminComponent -SearchTopology $clone -SearchServiceInstance $ssi

Activate the Cloned Search Topology
Set-SPEnterpriseSearchTopology -Identity $clone


Review new topology
Get-SPEnterpriseSearchTopology -Active -SearchApplication $ssa


Monitor Distribution of Index
Get-SPEnterpriseSearchStatus -SearchApplication $ssa

(The full script contains a loop that waits for the index component to be Active)

The new index component will be "Degraded" until the index is fully synced. Use the -text parameter to retrieve more detailed information.

Once all components are Active, reviewing the Search Application Topology in Central Admin shows the following:

My second search server was now complete!


FULL SCRIPT

Add-PSSnapin Microsoft.SharePoint.PowerShell -ErrorAction SilentlyContinue

#Get Search Service Instance and Start on New Index Server
$ssi = Get-SPEnterpriseSearchServiceInstance -Identity "<<new index server>>"
Start-SPEnterpriseSearchServiceInstance -Identity $ssi

#Wait for Search Service Instance to come online
do {$online = Get-SPEnterpriseSearchServiceInstance -Identity $ssi; Write-Host "Waiting for service: " $online.Status}
until ($online.Status -eq "Online")

#Clone Active Search Topology
$ssa = Get-SPEnterpriseSearchServiceApplication
$active = Get-SPEnterpriseSearchTopology -SearchApplication $ssa -Active
$clone = New-SPEnterpriseSearchTopology -SearchApplication $ssa -Clone –SearchTopology $active

# Add New Admin Component
New-SPEnterpriseSearchAdminComponent -SearchTopology $clone -SearchServiceInstance $ssi
#Activate the Cloned Search Topology
Set-SPEnterpriseSearchTopology -Identity $clone

#Review new topology
Get-SPEnterpriseSearchTopology -Active -SearchApplication $ssa

#Monitor Distribution of Index
do {$activeState = Get-SPEnterpriseSearchStatus -SearchApplication $ssa | Where-Object {$_.Name -eq "IndexComponent2"}; Write-Host "Waiting for active distribution: " $activeState.State}
until ($activeState.State -eq "Active")

SharePoint 2013 Search: Adding a New Analytics Component to a Search Server

This blog is part of my Scaling Out Search Series and describes the processes for adding a new analytics component to a new search server:

Get Search Service Instance and Start on New Index Server$ssi = Get-SPEnterpriseSearchServiceInstance -Identity "<<new index server>>"
Start-SPEnterpriseSearchServiceInstance -Identity $ssi

Wait for Search Service Instance to come online
Get-SPEnterpriseSearchServiceInstance -Identity $ssi

(The full script contains a loop that waits for the service to come online)

Clone Active Search Topology$ssa = Get-SPEnterpriseSearchServiceApplication
$active = Get-SPEnterpriseSearchTopology -SearchApplication $ssa -Active
$clone = New-SPEnterpriseSearchTopology -SearchApplication $ssa -Clone –SearchTopology $active

Add New Analytics Processing Component
New-SPEnterpriseSearchAnalyticsProcessingComponent -SearchTopology $clone -SearchServiceInstance $ssi

Activate the Cloned Search Topology
Set-SPEnterpriseSearchTopology -Identity $clone


Review new topology
Get-SPEnterpriseSearchTopology -Active -SearchApplication $ssa


Monitor Distribution of Index
Get-SPEnterpriseSearchStatus -SearchApplication $ssa

(The full script contains a loop that waits for the index component to be Active)

The new index component will be "Degraded" until the index is fully synced. Use the -text parameter to retrieve more detailed information.

Once all components are Active, reviewing the Search Application Topology in Central Admin shows the following:

NEXT, I created a New Admin Component.



FULL SCRIPT

Add-PSSnapin Microsoft.SharePoint.PowerShell -ErrorAction SilentlyContinue

#Get Search Service Instance and Start on New Index Server
$ssi = Get-SPEnterpriseSearchServiceInstance -Identity "<<new index server>>"
Start-SPEnterpriseSearchServiceInstance -Identity $ssi

#Wait for Search Service Instance to come online
do {$online = Get-SPEnterpriseSearchServiceInstance -Identity $ssi; Write-Host "Waiting for service: " $online.Status}
until ($online.Status -eq "Online")

#Clone Active Search Topology
$ssa = Get-SPEnterpriseSearchServiceApplication
$active = Get-SPEnterpriseSearchTopology -SearchApplication $ssa -Active
$clone = New-SPEnterpriseSearchTopology -SearchApplication $ssa -Clone –SearchTopology $active

# Add New Analytics Processing Component
New-SPEnterpriseSearchAnalyticsProcessingComponent -SearchTopology $clone -SearchServiceInstance $ssi
#Activate the Cloned Search Topology
Set-SPEnterpriseSearchTopology -Identity $clone

#Review new topology
Get-SPEnterpriseSearchTopology -Active -SearchApplication $ssa

#Monitor Distribution of Index
do {$activeState = Get-SPEnterpriseSearchStatus -SearchApplication $ssa | Where-Object {$_.Name -eq "IndexComponent2"}; Write-Host "Waiting for active distribution: " $activeState.State}
until ($activeState.State -eq "Active")

SharePoint 2013 Search: Adding a New Content Processing Component to a Search Server

This blog is part of my Scaling Out Search Series and describes the processes for adding a new content processing component to a new search server:

Get Search Service Instance and Start on New Index Server$ssi = Get-SPEnterpriseSearchServiceInstance -Identity "<<new index server>>"
Start-SPEnterpriseSearchServiceInstance -Identity $ssi

Wait for Search Service Instance to come online
Get-SPEnterpriseSearchServiceInstance -Identity $ssi

(The full script contains a loop that waits for the service to come online)

Clone Active Search Topology$ssa = Get-SPEnterpriseSearchServiceApplication
$active = Get-SPEnterpriseSearchTopology -SearchApplication $ssa -Active
$clone = New-SPEnterpriseSearchTopology -SearchApplication $ssa -Clone –SearchTopology $active

Add New Content Processing Component
New-SPEnterpriseSearchContentProcessingComponent -SearchTopology $clone -SearchServiceInstance $ssi

Activate the Cloned Search Topology
Set-SPEnterpriseSearchTopology -Identity $clone


Review new topology
Get-SPEnterpriseSearchTopology -Active -SearchApplication $ssa


Monitor Distribution of Index
Get-SPEnterpriseSearchStatus -SearchApplication $ssa

(The full script contains a loop that waits for the index component to be Active)

The new index component will be "Degraded" until the index is fully synced. Use the -text parameter to retrieve more detailed information.

Once all components are Active, reviewing the Search Application Topology in Central Admin shows the following:

NEXT, I created a New Analytics Component.


FULL SCRIPT

Add-PSSnapin Microsoft.SharePoint.PowerShell -ErrorAction SilentlyContinue

#Get Search Service Instance and Start on New Index Server
$ssi = Get-SPEnterpriseSearchServiceInstance -Identity "<<new index server>>"
Start-SPEnterpriseSearchServiceInstance -Identity $ssi

#Wait for Search Service Instance to come online
do {$online = Get-SPEnterpriseSearchServiceInstance -Identity $ssi; Write-Host "Waiting for service: " $online.Status}
until ($online.Status -eq "Online")

#Clone Active Search Topology
$ssa = Get-SPEnterpriseSearchServiceApplication
$active = Get-SPEnterpriseSearchTopology -SearchApplication $ssa -Active
$clone = New-SPEnterpriseSearchTopology -SearchApplication $ssa -Clone –SearchTopology $active

# Add New Content Processing Component
New-SPEnterpriseSearchContentProcessingComponent -SearchTopology $clone -SearchServiceInstance $ssi
#Activate the Cloned Search Topology
Set-SPEnterpriseSearchTopology -Identity $clone

#Review new topology
Get-SPEnterpriseSearchTopology -Active -SearchApplication $ssa

#Monitor Distribution of Index
do {$activeState = Get-SPEnterpriseSearchStatus -SearchApplication $ssa | Where-Object {$_.Name -eq "IndexComponent2"}; Write-Host "Waiting for active distribution: " $activeState.State}
until ($activeState.State -eq "Active")

SharePoint 2013 Search: Adding a New Crawl Component to a Search Server

This blog is part of my Scaling Out Search Series and describes the processes for adding a new crawl component to a new search server:

Get Search Service Instance and Start on New Index Server$ssi = Get-SPEnterpriseSearchServiceInstance -Identity "<<new index server>>"
Start-SPEnterpriseSearchServiceInstance -Identity $ssi

Wait for Search Service Instance to come online
Get-SPEnterpriseSearchServiceInstance -Identity $ssi

(The full script contains a loop that waits for the service to come online)

Clone Active Search Topology$ssa = Get-SPEnterpriseSearchServiceApplication
$active = Get-SPEnterpriseSearchTopology -SearchApplication $ssa -Active
$clone = New-SPEnterpriseSearchTopology -SearchApplication $ssa -Clone –SearchTopology $active


Add New Crawl Component
New-SPEnterpriseSearchCrawlComponent -SearchTopology $clone -SearchServiceInstance $ssi

Activate the Cloned Search Topology
Set-SPEnterpriseSearchTopology -Identity $clone


Review new topology
Get-SPEnterpriseSearchTopology -Active -SearchApplication $ssa


Monitor Distribution of Index
Get-SPEnterpriseSearchStatus -SearchApplication $ssa

(The full script contains a loop that waits for the index component to be Active)

The new index component will be "Degraded" until the index is fully synced. Use the -text parameter to retrieve more detailed information:

Once all components are Active, reviewing the Search Application Topology in Central Admin shows the following:

 



NEXT, I created a New Content Processing Component.


FULL SCRIPT

Add-PSSnapin Microsoft.SharePoint.PowerShell -ErrorAction SilentlyContinue

#Get Search Service Instance and Start on New Index Server
$ssi = Get-SPEnterpriseSearchServiceInstance -Identity "<<new index server>>"
Start-SPEnterpriseSearchServiceInstance -Identity $ssi

#Wait for Search Service Instance to come online
do {$online = Get-SPEnterpriseSearchServiceInstance -Identity $ssi; Write-Host "Waiting for service: " $online.Status}
until ($online.Status -eq "Online")

#Clone Active Search Topology
$ssa = Get-SPEnterpriseSearchServiceApplication
$active = Get-SPEnterpriseSearchTopology -SearchApplication $ssa -Active
$clone = New-SPEnterpriseSearchTopology -SearchApplication $ssa -Clone –SearchTopology $active

# Add New Crawl Component
New-SPEnterpriseSearchCrawlComponent -SearchTopology $clone -SearchServiceInstance $ssi

#Activate the Cloned Search Topology
Set-SPEnterpriseSearchTopology -Identity $clone

#Review new topology
Get-SPEnterpriseSearchTopology -Active -SearchApplication $ssa

#Monitor Distribution of Index
do {$activeState = Get-SPEnterpriseSearchStatus -SearchApplication $ssa | Where-Object {$_.Name -eq "IndexComponent2"}; Write-Host "Waiting for active distribution: " $activeState.State}
until ($activeState.State -eq "Active")

SharePoint 2013 Search: Adding a New Index Component to a Search Server

This blog is part of my Scaling Out Search Series and describes the processes for adding a new index component to a new search server:

Get Search Service Instance and Start on New Index Server$ssi = Get-SPEnterpriseSearchServiceInstance -Identity "<<new index server>>"
Start-SPEnterpriseSearchServiceInstance -Identity $ssi

Wait for Search Service Instance to come online
Get-SPEnterpriseSearchServiceInstance -Identity $ssi

(The full script contains a loop that waits for the service to come online)

Clone Active Search Topology$ssa = Get-SPEnterpriseSearchServiceApplication
$active = Get-SPEnterpriseSearchTopology -SearchApplication $ssa -Active
$clone = New-SPEnterpriseSearchTopology -SearchApplication $ssa -Clone –SearchTopology $active

Create New Index Component for Index Partition 0
New-SPEnterpriseSearchIndexComponent –SearchTopology $clone -SearchServiceInstance $ssi -IndexPartition 0 -RootDirectory "<<server index location>>"

Activate the Cloned Search Topology
Set-SPEnterpriseSearchTopology -Identity $clone


Review new topology
Get-SPEnterpriseSearchTopology -Active -SearchApplication $ssa

Monitor Distribution of Index
Get-SPEnterpriseSearchStatus -SearchApplication $ssa

The new index component will be "Degraded" until the index is fully synced. Use the -text parameter to retrieve more detailed information:

 

 

(The full script contains a loop that waits for the index component to be Active)

 

 

 

Once all components are activated, reviewing the Search Application Topology in Central Admin shows the following:

 

NEXT, I created a New Crawl Component.
 
FULL SCRIPT

Add-PSSnapin Microsoft.SharePoint.PowerShell -ErrorAction SilentlyContinue

#Get Search Service Instance and Start on New Index Server
$ssi = Get-SPEnterpriseSearchServiceInstance -Identity "<<new index server>>"
Start-SPEnterpriseSearchServiceInstance -Identity $ssi

#Wait for Search Service Instance to come online
do {$online = Get-SPEnterpriseSearchServiceInstance -Identity $ssi; Write-Host "Waiting for service: " $online.Status}
until ($online.Status -eq "Online")

#Clone Active Search Topology
$ssa = Get-SPEnterpriseSearchServiceApplication
$active = Get-SPEnterpriseSearchTopology -SearchApplication $ssa -Active
$clone = New-SPEnterpriseSearchTopology -SearchApplication $ssa -Clone –SearchTopology $active

#Create New Index Component for Index Partition 0
New-SPEnterpriseSearchIndexComponent –SearchTopology $clone -SearchServiceInstance $ssi -IndexPartition 0 -RootDirectory "<<server index location>>"

#Activate the Cloned Search Topology
Set-SPEnterpriseSearchTopology -Identity $clone

#Review new topology
Get-SPEnterpriseSearchTopology -Active -SearchApplication $ssa

#Monitor Distribution of Index
do {$activeState = Get-SPEnterpriseSearchStatus -SearchApplication $ssa | Where-Object {$_.Name -eq "IndexComponent2"}; Write-Host "Waiting for active distribution: " $activeState.State}
until ($activeState.State -eq "Active")
 

SharePoint 2013: Scaling Out Enterprise Search Series

Before I moved my index partition to my WFEs, I originally had 1 WFE and 1 Search Server (which we like to call the Index Server although now it is becoming the Search Crawl/Processing server). I previously moved the Query Component to the WFE. Therefore I had the following:

A new "Index Server" was provisioned and I added the server to the farm. Now I needed to scale out the search components a bit:

Technically, scaling out the Index Partition involves creating a new Index Partition for every 10 million items. Creating a new Index Compoent is considered as a fault tolerance procedure. Nonetheless, adding more servers and creating more search components can be considered scaling out overall.

Once the new Index Server was ready I needed to perform the folowing:

1. Add a New Index Component
2. Add a New Crawl Component
3. Add a New Content Processing Component
4. Add a New Analytics Component
5. Add a New Admin Component

Add the end of these processes, my Search Topology resembled the following:

Search On!