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


Top 5 Contributors of the Month
MarieAdela
Imran Ghani
Post New Web Links

PivotTable Excel 2007 missing ALL levels

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

The Customer dimension has a hierarchy called Customers, with an All Customers level, and then the customerID. When I use this dimension in ProClarity, I have the All level, and then can choose if I want to drill down into the members.

Excel 2007 PivotTable skips the ALL levels and just goes to the first "real" level. How can I enable the ALL level in Excel, so that users can choose to see the details or not?

Thanks,

Lori




View Complete Post


More Related Resource Links

While Importing Excel 2007 file to Datatable - headerrow problem

  

Hi there,

 

I am trying to simply extract an excel data from an uploaded file an put it into a datatable. In this case the excel file has 3 rows but when I fill the datatable I only see row count of 2.

I tried changing HDR:NO; to HDR:YES and vice versa, but no luck. 

What am I doing wrong? (Note: the excel file cannot have a  headerrow)

 

string connstr = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + pFilePath + ";Extended Properties=\"Excel 12.0;IMEX=1;HDR:NO;\"";
            OleDbConnection conn = new OleDbConnection(connstr);
            conn.Open();
            DataTable dtTables = conn.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Tables, null);
            string strTablename = dtTables.Rows[0]["TABLE_NAME"].ToString();
            string strSQL = "SELECT * FROM [" + strTablename + "]";

            OleDbCommand cmd = new OleDbCommand(strSQL, conn);

            
            DataTable dt = new DataTable();
            OleDbDataAdapter da = new OleDbDataAdapter(cmd);
            da.Fill(dt);
            //At this point row count=2 which doesn't make sense


 

 

 


Excel 2007

  

Hi,

 

I want to develop an application which supports Server Side Excel Automation using a template(xltx). I am able to acheive most of the automation except(using excelpackage.dll - OfficeOpenXml), i am stuck up identifying the checkbox controls in my excel work sheet.

Any help on this is really appreciated.

 

My sample code.

 

Thanks,

Ram.


display data in excel 2007

  

 I am using .net  version 1.1  and  excel 2003 to display data.I need to display data in 2007 .Can  anyone suggest the reference to be added ,connection string change and what should be imported.  


Importing Excel 2007 spreadsheet into WSS 3.0 -- Error Message

  

Hi,

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!


Thanks! Patti N.

In sharepoint excel working fine in office 2007 but not in office 2010

  

Hi All,

we receive error  while trying to create excel sheet using the template we have uploaded on the SharePoint 2007.

 

This occurs on a machine that has Office 2010 installed. Earlier the machine had Office 2007 and everything was working fine.

 

Can you let me know why is problem is coming and how to resolve this.

 

Thanks

Sanjeev Tiwari

 


Sharepoint 2007 and Excel 2010 features

  

Does Sharepoint 2007 Server support the new Excel 2010 feaures like slicers? Or do i have to have Sharepoint 2010 to get that working (considering all other components besides Sharepoint 2010 are available in my Sharepoint 2007 environment) ?

- Thanks

RK

 


MOSS 2007 - "next 100" link missing from permissions pages

  

We're running two MOSS 2007 environments that are (should be!) identical for Development and Validation.  We don't have Production set up yet to compare...

In DEV, when you go to the Site Settings > View All Site Settings > People and Groups > Groups, you see a "1 - 100 >" link at the top right to page to the next 100 groups.  In VAL, we do not see that link even though we have more than 100 Groups (the list ends on the first page with the letter "l").

Everything we have looked at between the two systems looks identical, has anyone seen something similar and has a fix?  At this point we're waiting until we get the PRD environment set up to compare because we're stumped.  Any help would be appreciated, thanks!

-Richard.


Video: Connecting PivotTables in Excel 2007 to Data Stored in SharePoint Lists

  
Learn how to use the Open XML SDK 2.0 to create Excel 2007 PivotTables that are connected to data stored in SharePoint lists.

Interacting with the Excel Web Services API for SharePoint Server 2007

  
Get a quick start with the Excel Web Services API, which enables interaction with published Excel 2007 workbooks in SharePoint Server 2007 from a remote application. Learn considerations around session state, security, and performance.

Publishing Excel 2007 Workbooks to SharePoint Server 2007 (Visual How To)

  
Watch the video and explore code as you learn how to publish Excel 2007 Workbooks to SharePoint Server 2007 programmatically.

Sample: Publishing Excel 2007 Workbooks to SharePoint Server 2007

  
Explore the code in this visual how-to article as you learn how to publish Excel 2007 Workbooks to SharePoint Server 2007 programmatically.

Exporting MOSS 2007 List to Excel 2007

  
When doing the export, the columns "Item Type" and "Path" are automatically displayed in the sheet.  Is there anyway to suppress these fields from exporting automatically?  I'm sure this has already been asked before, but I couldn't find anything after much searching (perhaps I'm using the wrong keywords).  Any help would be greatly appreciated.

Linked Server to access Excel 2007

  

Hi

I'm tried SELECT * INTO XXX FROM OPENROWSET alongwith Microsoft.ACE.OLEDB.12.0.

Apparently the query requires the sql account to have SYSADMIN privileges.

Considering that SYSADMIN should not be provided to a database account on a Production Server, I tried using the Linked Server method.

Following is my code.


Exec sp_addlinkedserver 'AB2','Ace 12.0','Microsoft.ACE.OLEDB.12.0','\\202.46.215.35\sagarr\Test1\cpc\c2\AB2.xlsx',NULL,'Excel 12.0;IMEX=1'
Exec sp_addlinkedsrvlogin 'AB2','false',NULL,NULL,NULL
go
SELECT * INTO [CPCAB2.xlsx] FROM OPENQUERY([AB2] ,'SELECT * FROM [Sheet1$]')
Exec sp_dropserver 'AB2','droplogins'


Now i get the following error

Error.15247-User does not have permission to perform this action

My Excel file, Database and Windows Application run on separate machines.

i have provided the following privileges

GRANT ALTER ANY LOGIN TO sqlaccount
GRANT ALTER ANY LINKED SERVER TO sqlaccount


EXEC sp_configure 'show advanced options', 1
RECONFIGURE
EXEC sp_configure 'ad hoc distributed queries', 1
RECONFIGURE

The DisAllowAdHocProcess in

Excel 2007 Filter on multiple values

  
Hi Everyone,How do I use Excel 2007 Pivot Table's label filters/value filters to filter with multiple conditions? for example, I want to get all customers with customer name start with "G" and doesn't include "E" and with measure>100 and measure between 50-60.Regards,George

Linked Server to access Excel 2007

  
Hi I'm tried SELECT * INTO XXX FROM OPENROWSET alongwith Microsoft.ACE.OLEDB.12.0. Apparently the query requires the sql account to have SYSADMIN privileges. Considering that SYSADMIN should not be provided to a database account on a Production Server, I tried using the Linked Server method. Following is my code. Exec sp_addlinkedserver 'AB2','Ace 12.0','Microsoft.ACE.OLEDB.12.0','\\202.46.215.35\sagarr\Test1\cpc\c2\AB2.xlsx',NULL,'Excel 12.0;IMEX=1' Exec sp_addlinkedsrvlogin 'AB2','false',NULL,NULL,NULL go SELECT * INTO [CPCAB2.xlsx] FROM OPENQUERY([AB2] ,'SELECT * FROM [Sheet1$]') Exec sp_dropserver 'AB2','droplogins' Now i get the following error Error.15247-User does not have permission to perform this action If I execute the query from Query Analyzer it works fine, but fails when I execute it using Windows App and encapsulate code in Stored Proc. My Excel file, Database and Windows Application run on separate machines. i have provided the following privileges GRANT ALTER ANY LOGIN TO sqlaccount GRANT ALTER ANY LINKED SERVER TO sqlaccount EXEC sp_configure 'show advanced options', 1 RECONFIGURE EXEC sp_configure 'ad hoc distributed queries', 1 RECONFIGURE The DisAllowAdHocProcess in Registry has value 0 Please let me know what additional permissions should i set to get it working???

String Data missing start from "+" When Export Data To Excel Using Gridview

  
Hi,I am exporting data from gridview to excel file.My problem is that for all the string which contain "+", the rest of data begining from it is missing in excel file.For example: PWE-WER+78, when exported it will become PWE-WER.Below is my code.                Response.ClearContent() Response.Buffer = True Response.AddHeader("Content-Disposition", "attachment;filename=" & "ExportedExcel.xls") Response.ContentType = "application/vnd.ms-excel" Response.Charset = "" lobjStringWriter = New StringWriter lobjHtmlWriter = New HtmlTextWriter(lobjStringWriter) dgdResult.RenderControl(lobjHtmlWriter) Response.Write(lobjStringWriter.ToString()) Response.End() I  have search through online but no solution.Does anyone has idea on this?

Excel 2007/ASP.NET Exception using .Workbooks.Add()

  
Hi,I am receiving the following exception when using the xlApp.Workbooks.Add() method with ASP.NET and Office 2007.  Server Error in '/ExcelTest' Application. Microsoft Office Excel cannot open or save any more documents because there is not enough available memory or disk space. . To make more memory available, close workbooks or programs you no longer need. . To free disk space, delete files you no longer need from the disk you are saving to. Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. Exception Details: System.Runtime.InteropServices.COMException: Microsoft Office Excel cannot open or save any more documents because there is not enough available memory or disk space.   There is plenty of disk space and memory on the servers. What's really strange is that it works OK when I specify a template with the Add method: .Workbooks.Add(Server.MapPath("~") & "\ReportTemplate.xls")So that's a workaround anyway...Below is a small sample I'm using to try and debug this problem.In a new web project, add a reference to Microsoft Excel 12.0 Object Library, add a button named btnGenerateReport, a label named lblStatus, and a Hyperlink named lnkExcelReport to Default.aspx and paste the
Categories: 
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