.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Win Surprise Gifts!!!

Top 5 Contributors of the Month
david stephan
Gaurav Pal
Post New Web Links

Pivot drillthrough error in Excel

Posted By:      Posted Date: October 14, 2010    Points: 0   Category :Sql Server

Mixed behaviour on one of our customer installation. I get the error "XML for Analysis parser: The LocaleIdentifier property is not overwritable and cannot be assigned a value." when i try to drillthrough in a Excel pivot table that connects to a SSAS cube.
But this error dont occurs on every workstation/server, it depends of the settings if this error occurs or if the drillthrough in the excel pivot table works fine.

The drillthrough works fine on a client pc installation with:
- Windows XP Professionel 5.1.2600 Service Pack 2 NU 2600 French language version
- Excel (12.0.6504.5001) with SP2 (12.0.6425.1000) French version
- Regional Settings: Swiss(French)
- Setting Language for non Unicode support: Swiss(French)

The SSAS Instance is installed on a server with:
- Windows Server 2003 R2 x86 English Version
- SQL Server Standard 2005 SP3 English (9.0.4035), SSAS SP3 (9.0.4035.00) English
- Excel 2007 (12.0.4518.1014) French Version
- Regional Settings: Swiss(French)
- Setting Language for non Unicode support: Swiss(French)

If i start Excel on the server and try to drillthrough the pivot table, i get the Error: ".. The LocaleIdentifier property is not overwritable .."

If i change on the Server the settings Regional Language and Language support for non Unicode to US(English), th

View Complete Post

More Related Resource Links

Excel 2007 Pivot table OLAP- SSAS 2008 Error

Hi, I do have a strange issue with Excel 2007 pivot table report; the data source is SSAS 2008 cube. When refresh the excel 2007 report data using the 'Refresh All' or 'Refresh' button available under the Data menu, the below error is thrown. "The expression contains a function that cannot operate on a set with more than 4,294,967,296 tuples." After some analysis, the report works well on the below workaround, 1. Remove all the calculated measures from report (3 measures in this scenario) 2. Remove one row label (dimension attribute) from report But the measures, attributes are valid one from cube and it is required fields in the report. So, understand that removing few items is not at all a solution. The below workaround also works. 1. Check the 'Defer Layout Update' option from the Pivot Table Field List window. 2. Click on the Update button After that Unchecked the 'Defer Layout Update' option and apply the filter, etc in the report. The report works very well. But every time, we need to use the above work around after open the excel report. I have NO clue on this issue. Please help to resolve this issue. Thanks, Jey

Importing Excel 2007 spreadsheet into WSS 3.0 -- Error Message



I'm trying to import (Custom Lists >> Import Spreadsheet) into WSS 3.0 and I'm getting the following message: 

Refers to the _layouts

You are not authorized to view this page.  You might not have permissions to view this direcotyr or page using the credentials you supplied. [More stuff here.]

Http ERror 403 - Forbidden

Is this just a permissions problem or is there some other underlying issue?  Should you be able to upload an Excel spreadsheet (with links) into a Custom List?


Thanks! Patti N.

Excel file getting error


when i am trying to save a excel document in library it getting error like "The file cannot be saved because some properties are missing or invalid"

If anyone know the solution for this pls tell me.



can't to insert value to EXCEL folder get error

Hi Can't to insert value in EXCEL folder get error   The Microsoft Jet database engine could not find the object 'Sheet1$'.  Make sure the object exists and that you spell its name and the path name correctly.   there is a code      The Microsoft Jet database engine could not find the object 'Sheet1$'.  Make sure the object exists and that you spell its name and the path name correctly. {        string sConnectionString = null;     sConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source='C:\\Users\\12.xls';Extended Properties=\"Excel 8.0;\"";        System.Data.OleDb.OleDbConnection objConn = new System.Data.OleDb.OleDbConnection(sConnectionString);        objConn.Open();          //Add two records to the table.        System.Data.OleDb.OleDbCommand objCmd = new System.Data.OleDb.OleDbCommand();        objCmd.Connection = objConn;        objCmd.CommandText = "Insert into [Sheet1$] (FirstName, LastName)" + " values ('Bill', 'Brown')";        objCmd.ExecuteNonQuery();        objCmd.CommandText =

Excel 2003 pivot table - parent showing grand total than the sum of its children

All, In Excel 2003, I have a product hierarchy, for simplistic sake lets say it goes A > B > C > model nbr.  I have filtered on model nbr 1.  I have the product hierarchy in my pivot table and unit cnt as the measure. Starting at level A in the hierarchy I am showing a total unit of 12000, if I drill down to B it still shows 12000, if I drill down to C it shows 100 and if I drill down to model nbr it shows me model nbr 1 with a unit cnt of 100. So my question is why are my parents not filtering the model nbr, it is taking the total for ALL records that have level A as their parent regardless of the model nbr? This does NOT happen in Excel 2007 or when I browse my cube in SSAS.

Excel Data Source SSIS AcquireConnection failed 0x80004005 unspecified error

Nuances of using Excel data sources with SSIS Your Development Machine Setup Needs Jet Drivers, available along with the default Office install so if you have Excel working fine locally, this is enough. Installing the "Office Data Connectivity Components" (download file name AccessDatabaseEngine.exe) as well as having Office installed corrupts the Jet Driver stack so that SSIS in VS 2008 cannot create the object to read the XLS file. You will see the title error "Unspecified Error" and "80004005" code. Resolution: Uninstall the Jet Engine pack on your development XP machine if you have Office. It will be listed as something like "Microsoft Access Database Engine (2010)". Run Office 200x setup with the Repair option and reboot. Try configuring the task in SSIS and it should be able to read the Sheet names again and columns.  Live Server (32-bit and 64-bit) Deploying your SSIS package to a server without Office 200x means it will not have the Jet Engine drivers to read XLS, XLSX files. You must install the 32-bit Office Data Connectivity Components - Jet Drivers mentioned above found here  (http://www.microsoft.com/downloads/details.aspx?familyid=7554f536-8c28-4598-9b72-ef94e038c891&displaylang=en). There are no 64-bit drivers; therefore regardless of 32 or 64-bit live environments, this step is the same. You shoul

How to change connection string of a pivot table pointing to SSAS 2005 cube using excel 2003?

Hi All,I am not sure if I should have posted this query to Excel 2003 forum. But posting it here as it applies to SSAS 2005 as well.Ok, let me give the background before I tell the actual problem.We have users on ABC domain and the SSAS server is also on ABC domain. Users on this domain can acess the excel pivots by connecting to cube to browse the data. They leave the Userid & password field blank while they setup the connection string and it works fine. Thanks to windows authentication that takes the credentials of user logged in. Let's say I have two users A and B, they login to ABC domain with their own windows ids.  Now when user A creates a excel file having a cube pivot and then sends this file to user B, user B can refresh and modify the same excel file (he can select new measures to pivot, new hierarchies in filters and so on).Now, let's say I have another user, user C. He has excel 2003 installed on his PC and cannot migrate to excel 2007. He is on different domain XYZ but have a valid windows userid on domain ABC. The domain ABC & XYZ can not be setup to have trusted relationship. Now, when user A sends the same excel file to user C. When user C opens the file and try to refresh it or try to modify the pivot by selecting/deselecting any elements, he gets below error prompt:" An error was encountered in the transport layer." and "Errors in the

Need an example of error in OLE DB Source component when source is Excel file.

I want an example of error (not truncation) in individual row or individual cell that can occur in OLE DB Source component when source is Excel file. I'm trying out Error Output of data task component.  I have already tried out Error Output of OLE DB Destination component.

Excel Services 2010 Error - "The workbook cannot be opened."

I enabled Excel Services and added a Excel Web Access viewer to my page. It's just a simple spreadsheet (which incidentally works fine on my standalone development server) that displays the error "The workbook cannot be opened." when the page loads. The trusted file locations are set up appropriately. We are using Kerberos authentication. My event viewer showed this "Critical" event (ID: 3760): SQL Database 'Prod_WSS_Content' on SQL Server instance 'servername' not found. Additional error information from SQL Server is included below. Cannot open database "Prod_WSS_Content" requested by the login. The login failed. Login failed for user 'domain\user'. In other words, the service account that ECS was running under did not have permission to the content database. So I granted the user read permission on the database. Then I got the same error and this "Error" level event in the event viewer (ID: 5617): There is a compatibility range mismatch between the Web server and database "Prod_WSS_Content", and connections to the data have been blocked to due to this incompatibility. This can happen when a content database has not been upgraded to be within the compatibility range of the Web server, or if the database has been upgraded to a higher level than the web server. The Web server and the database must be upgraded to

Create an Excel contraining PIVOT feature in ASP.NET

Hi, We have to generate an excel sheet with pivot table from ASP.NET Web page. Please advice me with some approaches. Thanks in advance. Ratheesh 

Error when programmatically connect filter webpart with excel webpart

Hello, On MOSS 2007 SP2 64bit I am trying to programmatically connect a SPSlicerTextWebPart with an ExcelWebRenderer but I am getting the error: "The connection point "IFilterValues" on "g_046273fe_af27_4064_86b1_70f2c57c326c"(the excel webpart)   is disabled" and I can't find a way to enable it... I'm using the folowing code: using(SPLimitedWebPartManager webPartMgr = web.GetLimitedWebPartManager("Stats.aspx", PersonalizationScope.Shared)) { Microsoft.SharePoint.Portal.WebControls.SPSlicerTextWebPart filterWebPart = (Microsoft.SharePoint.Portal.WebControls.SPSlicerTextWebPart)webPartMgr.WebParts[0]; if (filterWebPart.ConnectionID == Guid.Empty) { filterWebPart.ConnectionID = Guid.NewGuid(); } webPartMgr.SaveChanges(filterWebPart); ProviderConnectionPoint providerCP = webPartMgr.GetProviderConnectionPoints(filterWebPart)[0]; Microsoft.Office.Excel.WebUI.ExcelWebRenderer excelWebPart = (Microsoft.Office.Excel.WebUI.ExcelWebRenderer)webPartMgr.WebParts[1]; if (excelWebPart.ConnectionID == Guid.Empty) { excelWebPart.ConnectionID = Guid.NewGuid(); } excelWebPart.AllowConnect = true; webPartMgr.SaveChanges(excelWebPart); ConsumerConnectionPoint consumerCP = webPartMgr.GetConsumerConnectionPoints(excelWebPart)[2]; TransformableFilterValuesToFilterValuesTransformer t = new TransformableFilterValue

Use Report Action as default drillthrough on a calculated cell in Excel

Hi, I am using SSAS 2008 R2 and Excel 2010. Is there a way to override the default behavior from the cube to force an excel pivot table to perform a report action rather than a drillthrough when I double click a cell that is a calculated member? Currently, I get an error message. Truth be told, I wouldn't mind making all the double clicking in my pivot table cells pop a report for the user since the drillthrough formatting is so primitive, but I really need to know how to do it for the calculated cells. Thanks, James

Excel Services no refresh data & no error logs

Hi! I'm trying to publish an Excel workbook which loads data from SQL Server. The result is: no data loaded in the workbook and no error logged on Sharepoint logs. Notice that if I try to refresh the workbook in Office Excel all works fine! I try to set up Windows Authentication, None (to use unattended account) and with .odc file but with no result! The procedure I follow is: 1) create a new Excel file and create new data connection to the database (SQL Server 2008) 2) upload the file in the trusted file location 3) open the file in a web part and click on the "refresh all" button I used the same unattended account which I setup for this purpose with BCS and it works fine! I also try to set with PowerShell the Application ID created ("ES"): "set-spexcelserviceapplication -identity 6d871d92-19dd-4421-b33b-0012f9a5ea18 -unattendedaccountapplicationid ES" with no result. I noticed that the identity of the Excel Service application shown in the Central Administration isn't in the IIS Application Pool list: could it be the problem? I also tried to delete / re-create the application but the two IDs remains disequal! Have someone of you already worked with Excel Services and could give me any help? Thanx in advance, Elena

Excel pivot table report filter selections from a cube

There are two filters added to the report filter, Date (8/31/2010) and Product (Product A), from a cube I have.  Everything works just fine.  Once the cube is refreshed each day to include the new data, for some reason, only the selection of Date, 8/31/2010, stays but the selection of Product changes to All Products.  Both dimensions are fully processed but the surrogate keys for existing records do not change.  I checked the MDX captured in Profiler and in the where clause, the date selection is passed from the pivot table but the product selection is lost and all products is passed in.  Any thoughts? TIA. 

SQL Server 2005 Analysis Services - Performance of Excel Pivot Tables

Hi, I've several cubes available for several users within my organization. I've the SQL Server 2005 and the Analysis Services running on the same machine but it has 30Gb of RAM, 8 processors and a quick enouth disk. The problems is that i've some automatic processes that "build" some excel reports and the users use pivot tables with olap connections to the cubes regularly. I've noticed that the excel "locks" when the user is drilling down the data and the server has only one processor (there are 8...) at 100%... Is there a way to make the analysis services use the maximum processing capacity that is available? The users are complaining about the slowness of the cubes... Thanks in advance Hugo

SQL 2005 Error - Connecting Excel workbook to SQL 2005 server

Question: I am trying to connect an Excel workbook to a remote SQL server database and getting the following error below (see code snippet). Any insight would be appreciated. Wondering if there is an easier method to connecting then what I have. I have VBA coding in the Excel workbook (if needed, I can provide it). Connection failed: SQLState: '01000' SQL Server Error: 10054 [Microsoft][ODBC SQL Server Driver][DBNETLIB]ConnectionRead(recv()). Connection failed: SQLState: '08501' SQL Server Error: 11 [Microsoft][ODBC SQL Server Driver][DBNETLIB]General network error. Check your network documentation.

Error occuring while exporting larger reports to Excel from SharePoint



I have SSRS configured in SharePoint integration mode. I have reports deployed. if a user wants to export the report to an excel file works fine for smaller reports. But, in the case of larger reports it is throwing me an error while doing export to Excel. I am getting message like

An unexpected error has occured.

Example: Monthly reports are exporting to excel file fine. Where as, Yearly report has more data than the monthly reports. If I try to export these reports, I am getting above error. I must sure, this is something to do with the IIS time out or application time out(setting in web.config file of web application). Please some one guide me where is best place to do this.

Viewing works fine for both the reports.

I appreciate your help.



ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  Silverlight  Others  All   

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend