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


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

Excel 2007 Cubeset function - Retrieve subset of customers based on state

Posted By:      Posted Date: September 20, 2010    Points: 0   Category :Sql Server
 

I am working on making a report dynamic by allowing a user to choose a State and have the cubeset function return that State's customers.

I have the following

Cell $P$5 =CUBESET("Connection", "[Order Ship to Code].[Order Ship To State].[All Order Ship to Geography].children", "Ship to State")

Cell P6-P32 = CUBERANKEDMEMBER("Connection",$P$5, ROW(A1*))           *A2, A3, etc.

Cell $A$2 = "CA" ("CA" is choosen in the list of states based on a validation list pointing to the CubeRankedMembers in P6-P32)

=CUBESET("Connection", "[Customer Dimension].[Customer Name].[All Customer Dimension].children", "Customers") returns all customers

I have not been able to write a CubeSet function that combines the two sets to retrieve, for example, California Customers

This two functions I have written that are not working correctly...

=CUBESET("Connection", "([Order Ship to Code].[Order Ship To State].[All Order Ship to Geography].["&$A$2&"],[Customer Dimension].[Customer Name].[All Customer Dimension].children)", A$2&" Customers")

=CUBESET("Connection","[Order Ship to Code].[Order Ship To State].[All Order Ship to Geogra


View Complete Post


More Related Resource Links

Excel 2007 Cubeset function needs to pull list based on part of field value (ie contains, wildcardin

  

I have a Cube with a field called 'Item Code'.  This field is six characters in length.  The first two characters differentiate what kind of product it is.  I am building my reports with cube functions.  I do not want to use a pivot table to retrieve the list by using the 'contains' or 'begins with' label filters.  Is there a way to use the cubeset function with left, mid, right functions?  I realize that it can be set up in the cube as a separate field, but I am not able to update the cube.  Below are my examples - 'All Products' and 'Product R3189S' work fine, but I need to retrieve 'Products starting with R3'.

=CUBESET("Financials Sales Cube", "[Product Dimension].[Item Code].[All Product Dimension].children", "All Products")

=CUBESET("Financials Sales Cube", "[Product Dimension].[Item Code].[All Product Dimension].[R3189S]", "Product R3189S")

= CUBESET("Financials Sales Cube", "[Product Dimension].[Item Code].[All Product Dimension].[R3****]", "Products Starting with R3")

Thank you in advance.

Karen


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.  


Testing: Get Your Customers Involved in the Testing Process with Functional Tests in Excel

  

For specification documents to be truly valuable, they need to give an accurate picture of all the requirements of a project. This article describes how the communication value of specification documents can be improved by permitting users to test the code under construction using the Framework for Integrated Test (FIT), an open-source tool. It also explains how you can build a Windows Forms application in C# (WinFITRunnerLite) that converts functional tests, as written by your customers using Excel, into a form that allows you to run them with FIT against the code you're developing.

Will Stott

MSDN Magazine February 2005


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

 


Best practice? Accessing subset of data based on user

  

I have a DB and associated Asp.net 3.5 web app. The database holds information on our clients. We're adding client access functionality and I need to know how best to restrict user access to data only pertaining to their client.

The clients are stored in a hierarchy and each user can be associated with multiple clients. The user should only be able to access the data for their associated clients and any sub clients thereof.

My initial thought is to add a collection of clients to their profile, then use that to filter all queries; however, my gut instinct tells me that using the profile for such a security function is inadvisable.

Could anyone provide any insight on a best practice for this? I'm sure it must be a pretty common need.

Thanks!
Jay 


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???
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