Unable to create DataSource with using Excel Service in DashBoard Designer

The symptoms are when you lunch PerformancePoint DashBoard Designer and try to create a new Data Connection like
Right Click Data Connections and select “New DataSource” -> Excel Services and Click Ok. you should have get an error like
“An unexpected system error has occured.Additional details have been logged for your administrator.”

In ULS Logs you can see the fallowing error:

An unexpected error occurred.  Error 8205.  Exception details: System.Web.Services.Protocols.SoapException: You do not have permissions to open this file.
at System.Web.Services.Protocols.SoapHttpClientProtocol.ReadResponse(SoapClientMessage message, WebResponse response, Stream responseStream, Boolean asyncCall)
at System.Web.Services.Protocols.SoapHttpClientProtocol.Invoke(String methodName, Object[] parameters)
at Microsoft.PerformancePoint.Scorecards.Client.ExcelService.OpenWorkbook(String workbookPath, String uiCultureName, String dataCultureName, Status[]& status)
at Microsoft.PerformancePoint.Scorecards.DataSourceProviders.ExcelServicesDataSourceProvider.GetCubeMetaData(Boolean extendedMetadata)
at Microsoft.PerformancePoint.Scorecards.Server.PmServer.GetCubeMetaDataForDataSourceHelper(DataSource dataSource, Boolean extendedMetadata)
at Microsoft.PerformancePoint.Scorecards.Server.PmServer.GetCubeMetaDataForDataSource(DataSource dataSource)

The cause is Anonymous Authentication is not supported if you need to connect Excel Services by DashBoard Designer.

“You cannot connect to an Excel Services data source when the site or library containing the workbook you are trying to connect to is set to Anonymous Access.” http://technet.microsoft.com/en-us/library/ff191193.aspx

For solution
1) Disable “Anonymous Authentication” from IIS Management Console -> Authentication -> Anonymous Authentication

2) If you still need anonymous authentication than extend the site without Anonymous Authentication.

Excel Service can not display workbooks in Sharepoint 2010

Hi Everybody,

I have recently faced for a strange issue about displaying excel workbooks on Sharepoint 2010 .
When i checked the uls logs we have multiple exceptions like.

Stack trace: onetutil.dll: (unresolved symbol, module offset=00000000000A1A7D)
at 0x000007FEEFD71A7D onetutil.dll: (unresolved symbol, module offset=00000000000A2C3D)
at 0x000007FEEFD72C3D owssvr.dll: (unresolved symbol, module offset=0000000000008FA2)
at 0x000007FEE9A58FA2 owssvr.dll: (unresolved symbol, module offset=0000000000048C74)
at 0x000007FEE9A98C74 mscorwks.dll: (unresolved symbol, module offset=00000000002BF6D7)
at 0x000007FEF365F6D7 Microsoft.SharePoint.Library.ni.dll: (unresolved symbol, module offset=00000000000EC86B)
at 0x000007FEEB45C86B Microsoft.SharePoint.ni.dll: (unresolved symbol, module offset=0000000001AB6942)
at 0x000007FEED476942 Microsoft.SharePoint.ni.dll: (unresolved symbol, module offset=0000000001A7ADED)
at 0x000007FEED43ADED Microsoft.SharePoint.ni.dll: (unresolved symbol, module offset=0000000001A7CF66)
at 0x000007FEED43CF66 Microsoft.SharePoint.ni.dll: (unresolved symbol, module offset=0000000001A7FA31)
at 0x000007FEED43FA31

An internal error occurred.
at Microsoft.Office.Excel.WebUI.ExcelWebRendererControl.InternalPreRender()
at Microsoft.Office.Excel.WebUI.Utils.ExecuteWebEventHandler(CoreWebEventHandler coreWebEventHandler, CoreWebEventHandler baseClassHandler, Object param, IMainControl mainControl)

Utils.ExecuteWebEventHandler: unexpected exception System.NullReferenceException: Object reference not set to an instance of an object.
at Microsoft.Office.Excel.WebUI.ExcelWebRendererControl.InternalPreRender()
at Microsoft.Office.Excel.WebUI.Utils.ExecuteWebEventHandler(CoreWebEventHandler coreWebEventHandler, CoreWebEventHandler baseClassHandler, Object param, IMainControl mainControl)

Watson bucket parameters: SharePoint Server 2010, ULSException14, 5f9be61a “excel services application”, 0e00178d “14.0.6029.0”, 13ed43ce “microsoft.office.excel.webui.internal”, 0e00178b “14.0.6027.0”, 4da5bd73 “wed apr 13 18:12:51 2011”, 000009ca “000009ca”, 000000e9 “000000e9”, 4a6d3421 “nullreferenceexception”, 62646b69 “bdki”
The file that i want to open have url like http://blog.bugrapostaci.com:80/_layouts/xlviewer.aspx?id=/Shared%20Documents/CaseTest.xsls
But  the important one is in logs   in this correlation that unrelated information log not the exceptions. This info is about :
“There is no Web named “/SiteDirectory/BlogContent/Lists/Categories/AllCategories.aspx”. in first i ignored it quickly. Than i realized that article:

Excel Calculation Service cannot display workbooks if no root site collection is created for a web application.
http://support.microsoft.com/kb/2596582

The sharepoint version which i was working on it is before Oct 2011 CU . The Solution is simple install at least Oct 2011 CU for fixing these errors.

Updating Excel Server Properties from content with Moss2007 and Office 2010 – Part 2

In first aticle i have told about how to update Excel Server Properties and integration of Moss 2007 for old Excel 97 and 2003 format. As explained before its not able to do that with new xlxs file formats without using some visual basic code.

Part 2 – Using VBA for Excel 2007 , 2010 format:

I have created a document library named TestExcel on my Sharepoint and select document file template for excel file. After do that i have created 3 column on list.

TotalCost as  Number 
Note: In my envoriment i create a column name is Total than i changed the column name as ”TotalCost” so keep in your mind. i will explain it later becuase when you change the column name on a document libarary actual name on db can not changes and this  causes very common misunderstanding. )
PaymentDate as  DateTime
Description as  Single Line of Text

So far so good Lets go to Document Library and create a new Excel Document form library by clicking New-> Document.

If everything is ok you will see familier Excel 2010 open your document. And notice that Document Properties panel is opened for you. If you are getting problem with to open Excel document by clicking Sharepoint Document Library ->New-> Document you have possible owssupp.dll registration problem.
see kb article for more information:
http://support.microsoft.com/kb/833714

!!!Dont forget to save this excel file as “Excel Macro-Enabled Workbook” for other users can able to use this file correctly.

I have created a sample excel content see bellowed screenshot. And notice that i have give cells names which i want to map with server properties .

!Important : Sharepoint uses  ISO8601 format (YYYY-MM-DDTHH:MM:SSZ) for saving dates so you should make changes on date format for sharepoint understanding.
i use for that another cell and formul like =TEXT(B3,”yyyy-mm-ddThh:mm:ssZ”)

Now we have to enable Developer Tab on office ribbon . For do that.

1)Click File Menu -> Options

And Check “Developer” check box. and click ok.

Select Developer Tab -> Click Visual Basic.

type  codes to VBA IDE

//This function is updates server properties.
Sub SynchServerPorperties()
Dim metaprop As MetaProperty
‘On Error Resume Next

For Each metaprop In ActiveWorkbook.ContentTypeProperties
If metaprop.Name = “TotalCost” Then
metaprop.Value = Cells(2, 2).Value
End If
If metaprop.Name = “PaymentDate” Then
metaprop.Value = Cells(3, 3).Value
End If
If metaprop.Name = “Description” Then
metaprop.Value = Cells(4, 2).Value
End If

Next

End Sub

‘We are updating Server Properties before save the file.
Private Sub workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
SynchServerPorperties
End Sub

*This code provide us when user save file , server properties would be updated.

Click “Save” on excel and test server properties updated.

Update xlms file to sharepoint.

When everytime you click “Save” If you are annoyed a “Privacy Warning” message like me you can get rid of it bellowed way.


Privacy warning : “This document contains macros, ActiveX controls, XML expansion pack information, or web components. These may include personal information that cannot be removed by the Document Inspector.

Click Developer Tab-> Macro Security

Select “Privacy Options” and remove check on “Remove personel information from file properties on save”

Upload “xlsm” File to Sharepoint and notice that metadata form filled automatically.

If everything is ok. you have to see bellowed screen 🙂

End of Article…
See you next time.

Updating Excel Server Properties from content with Moss2007 and Office 2010 – Part 1

Hi everyone,

After a good vacation now the time to new article has came. In this article we talk about how to set Sharepoint Document Library metadata from Excel Content.
The problem is that if you are using Excel 2007 or Excel 2010 and you create a document library on Sharepoint with extra metadata columns and open an excel doc template from this Document Library -> new Excel Document. You can able to see server properties show us the needed metadata. After we change Document Properties and map it from excel content (I mean any mapped excel cell has changed we want mapped  server properties should to be changed) and save the document. and notice that the server properties is not updated. This actually is not a problem this issue is one of by design. Only way to do it  coding some visiual basic for Excel 2007 and Excel 2010(.xlsx file format). But if you use old format of Excel 97 or Excel 2003 (xls) you can still able to use custom properties like as i mentioned.

First Solution – Using old format xls files.

I have created a document library named TestExcel on my Sharepoint and select document file template for excel file. After do that i have created 3 column on list.

TotalCost as  Number 
Note: In my envoriment i create a column name is Total than i changed the column name as “TotalCost” so keep in your mind. i will explain it later becuase when you change the column name on a document libarary actual name on db can not changes and this  causes very common misunderstanding. )
PaymentDate as  DateTime
Description as  Single Line of Text

So far so good Lets go to Document Library and create a new Excel Document form library by clicking New-> Document.

If everything is ok you will see familier Excel 2010 open your document. And notice that Document Properties panel is opened for you. If you are getting problem with to open Excel document by clicking Sharepoint Document Library ->New-> Document you have possible owssupp.dll registration problem.
see kb article for more information:
http://support.microsoft.com/kb/833714

After opened this document i save as this document as Excel 97-2003 workbook type .

I have created a sample excel content see bellowed screenshot. And notice that i have give cells names which i want to map with server properties .

!Important : Sharepoint uses  ISO8601 format (YYYY-MM-DDTHH:MM:SSZ) for saving dates so you should make changes on date format for sharepoint understanding.
i use for that another cell and formul like =TEXT(B3,”yyyy-mm-ddThh:mm:ssZ”)

Notice that Document properties can not updated when you changes the cells in content. For automatically updating this properties we should make some bindings. So click Document Properties box and select Advanced Properties.

Select “Custom” tab.And Select Properties of Total .
Check “Link to Content” link this activates Source Combobox.
In combobox select named cell.
Note: Notice that in properities you see a line “Total” this is the actual sharepoint column name not “TotalCost” . Because i changed the column name from sharepoint list before i create a new document. So column name has changed on diplay but actual column name still is Total

After all binding done correctly. You have to see Document Properties has changed and updated.

Save the Excel Document to your computer. Get back to sharepoint list and upload this excel document to library. While updating if you do it correctly , metadata fields should be filled automatically.

Click ok. and see All Document View show us document content data in view.

In next artcile we do it this via using some simple VBA script for Excel 2007-Excel 2010 formats.
See you now.

c# Excel response with httphandler

An example of excel output used by HttpHandler

public class IExcelHandler : IHttpHandler
    {
 
        #region IHttpHandler Members
 
        public bool IsReusable
        {
            get { return false; }
        }
 
        public void ProcessRequest(HttpContext context)
        {
            // set Content type for Excel
            context.Response.ContentType = "application/vnd.ms-excel";
            // for download and give a file name
            context.Response.AppendHeader("content-disposition", "attachment; filename=MyExcelFile.xls");
            context.Response.Charset = "";
 
            //Write here your content
            // context.Response.Write(data);
 
            context.Response.End();
        }
 
        #endregion
    }
...