SpotFire SharePoint Connector – error web player

We are having an issue with authentication when using the Spotfire SharePoint Connector.  This feature has been added to our Spotfire 6.5 environment and allows direct access between SharePoint and Spotfire. Firstly, to create Spotfire reports using SharePoint lists and also allows for reports built on Excel spreadsheets stored in SharePoint to be viewable using the Spotfire Web player (provided the Spotfire Admin  account has Power User access to the SharePoint site/library where the content is located).  By default reports built using .xlsx in SharePoint do not display in the Web Player due to an authentication issue.

We have solved the problem with the web player not working for some Spotfire reports based on .xlsx documents stored in SharePoint (using the SharePoint Connector in 6.5).  The Spotfire Admin account needs to have access to the site that stores the .xlsx and for some reason this wasn’t working when the access was granted via a SharePoint permission group – access needed to be granted directly to the site.  See below:

spotfire-error

When the account was in the BI Power User group reports based on content in this site were not working in the web player.  Changing to the above the reports are working in the web player.

Note in these two cases I have granted the account Read access.  If Spotfire analyses are configured to connect directly to a list and include update functionality Spotfire Admin will require Contribute access.

tags: SpotFire SharePoint Connector – error web player,

The excel workbook shows error

The excel workbook shows error on my clients intranet homepage which is a excel web access webpart. Which checking the backend file and opening it with excel services has same error but when opened in excel it open fine.

Error: ” An Error has occurred. Please try again.

Investigations: The previous night there has been a power outage to the data center and next morning this is not working.

When the same file is renamed and saved again it works fine with excel web app so it could be something related to cache on the sever.

Then the eventlog on one of the frontend shows this error in application

5226

 Error: Unable to create or access workbook cache at C:\WINDOWS\TEMP\Excel Server\FileCache\SharedServices1-1078443246\Ranges. Excel Services is unable to function without a workbook cache

excelwebparterror

Bang on !!! when verified the “ranges” folder is missing on one of the WFE and the same excel url is tested on other WFE’s  which also displays error. I suppose the cache for each excel file using excel service is stored only on one server irrespective of which WFE this file is served.

Restore the folders in the path of the cache resolved the issue:

C:\WINDOWS\TEMP\Excel Server\FileCache\SharedServices1-1078443246\

This path has the following folders “Images”, “Ranges”, “SavedWorkbooks”, “Workbooks”

In case if that is also not working for you, you could remove the whole directory “Excel Server” in windows\temp folder and make sure the excel service application pool account has access to recreated folder at that path and restart the IIS should create the folder back again and rebuilt the cache as necessary.

More Detailed description and resoulution at: http://technet.microsoft.com/en-us/library/ee513091(v=office.14).aspx

Removing a Corrupted Site Column in SharePoint 2010

Errors like correlation Id:

System.ArgumentException: Invalid field name: {GUID} [URL of list]  (ULS log)

 “Field type xxx is not installed properly. Go to the list settings page to delete this field.”

To determine what field is causing the error run this PowerShell script.  The PowerShell should terminate with an error, along with the name of the erroneous field.

$web = Get-SPWeb http://portal/site

$fields = $web.Fields

foreach ($field in $fields} {write-host $field.id}

First you will need to determine the GUID ID for the corrupted column.  To do this you will run a PS script against the Site Collection to generate a report that lists all the fields, like so.

$web = Get-SPWeb http://portal/site

$web.AvailableFields.SchemaXml | Out-File c:\temp\fields.txt

Once done open up the fields.txt file and do a search for the corrupted column.

After determining the field name for the {GUID} I was then able to identify the problem column or content type. In my case the issue was with a column name “TypeTaxHTField0″ or simply Type. SharePoint by default appends the TaxHTField0 suffix to the end of all the static names.

Now that I have the Field Name Column, I needed a way to get into the lists settings to either delete or remove this column from the list. Since I was unable to access the list because of the correlation id, I had to determine the {GUID} of the list.

The way to get the {GUID} of the list I turned to PowerShell.

Below is the script I used to get the {GUID} of the list.

$site = Get-SPSite {URL OF YOUR SITE}

$web = $site.OpenWeb(“SUBSITE”)

$web.lists | Format-Table title,id

$web.Dispose()

$site.Dispose()

Once I got the {GUID} of the list I was then able to browse to the library settings by appending the {GUID} to the end of the list settings edit URL

i.e.

http://URL OF SITE/SUBSITE/_layouts/listedit.aspx?List={GUID}

Once inside the list settings I was then able to make the necessary changes. For me all I needed to do was remove the “Type” column from the Metadata Navigation Settings by removing it from both “Configure Navigation Hierarchies” and “Configure Key Filters”

Once I removed the “Type” column from the Metadata Navigation I then deleted the “Type” column from my list and then re-added it. Once I re-added it back into the list, I then re-added it back into the Metadata Navigation. Ater doing so I was able to access the list without error. If you find you do not need that column at all, simply delete it from your list.

Another way to get the field name from “SharePoint Manager” (a tool can be found in codeplex.com ) Run this tool on a SP server and navigate to subsite and fields to get more richer information using object model. http://spm.codeplex.com/

Hopefully this helps others who encounter the same issues.

list view filters displaying few records

If you have a list with more than 5000 items and if the item threshold limit is 5000, even after using filter you may see it displays:

“This view cannot be displayed because it exceeds the list view threshold (5000 items) enforced by the administrator.”

“Displaying only the newest results below. To view all results, narrow your query by adding a filter.”

For a view to quickly filter through a large number of items, the first column that you specify in the filter must be indexed. Other columns you specify in the view filter may or may not be indexed, but the view does not use those indexes. You also need to make sure that the first column of the filter does not return more items than the List View Threshold, even if the final result of the filtered view returns less than the List View Threshold.

If the first column of the filter returns more items than the List View Threshold, you can use a filter with two or more columns. When you define a filtered view that uses two or more columns, using an AND operator will usually limit the total number of items returned. But even in this case, you still need to specify as the first column in the filter the column that most likely returns the lesser amount of data. Note that using an OR filter almost always increases the number of items returned and won’t be effective under these circumstances.

So sometimes if easy to switch the filters like if you would like to filter location and them by date range try switching the filters use date range first so that the filter results are less than threshold value and then use your other filters.

As we spoke how to create a index on a column:

1.Navigate to the site that contains the list or library.
2.Click its name on the Quick Launch, or click on the Settings menu , click View All Site Content, and then click the name.
3.Click List or Library > List Settings or Library Settings.
4.Under the Columns section, click Indexed columns.
5.On the Indexed Columns page, click Create a new index.
6.Do one of the following: To create a simple index:
1.In the Primary Column section, under Primary column for this index, select the column.
2.Click Create. To create a compound index:
3.In the Primary Column section, under Primary column for this index, select the column.
4.In the Secondary Column section, under Secondary column for this index, select a different column.
5.Click Create.