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

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

Named Set Subtotal for Excel

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

I am trying to create a named set that will exclude certain members from a dimension. Unfortunately, when that set is used in excel, it does not return a subtotal which is expected behavior. Teo Latchev has blogged about this  as well as posted on this topic with a workaround however it is not working for me...when I include the [All] member, the total it provides is for all members, while I need it to be the sum of the listed members only ...I have tried a couple of tacks with the same result:

select {Measures.[Units]} on 0,
-{[Department].[Department].&[-1], [Department].[Department].&[-2],
[Department].[Department].&[19], [Department].[Department].&[20],
[Department].[Department].&[23], [Department].[Department].&[24],
[Department].[Department].&[25], [Department].[Department].&[27]}}
on 1
FROM [cube]

OR explicitly listing them...

select {Measures.[Units]} on 0,
{      [Department].[Department].&[1]

View Complete Post

More Related Resource Links

Sharepoint export to spreadsheet gives two extra columns in excel 2007 named as type and path, is th

When i export a list in excel 2007, it creates two extra columns named as type and path. At the same time this behaviour is not exhibited in office 2000. Is there any way to remove these two columns without using code ? Is there any configuration settings required for this, to remove it on the fly ? Thanks Ravish Verma

Named Set working in excel but not in Browser....



I have a problem in the named sets.

I have a dimension with 6 levels and I have to calculate the standard deviation for each level, only for the siblings. I used Siblings to calculate the stdevp but it was not working with the filter, So i had to use named sets.

The problem is, this calculation is working fine in the Excel, even with the filter, but in the SSAS browser, filter is not working. Could anyone please suggest me the solution for this, or to calculate stdevp for siblings with some other way, that should work for the filters also(Filter here should be on the same dimension for which the stdevp is being calculated)?.

here are my calculations:

Named Sets:


[LEVEL2] : [DIM_A].[A].[LEVEL2].

Sum of Specific Named Columns in Excel



i want to create aformula in excell  . i have a scenario like that i have one row in which i am giving names to columns like A,B C, etc

I want to sum those columns in which i have given the name 'A' only.

Help Required..





How do I read an Excel Named Table?


First I am using Excel 2007 and a .xlsm file. I am able to read in data from Excel that is on a Sheet or in a Range, but how do I read from a named table. 

My sampe file as data in B10:G20 and that area is a table named Table1.  Since the location and size changed between my actual files, Table1 will not always be in that location.

Here is the SQL command I am currently using

SELECT * FROM OPENROWSET ('Microsoft.ACE.OLEDB.12.0','Excel 12.0; Database=C:\Test.xlsm', 'SELECT * FROM [table1$]') 

I have also set this file up as a Linked Server and have gotten the same result.  And I have tried Table1 with and without out square brackets, and with and without the dollar sign.  I'm sure I'm missing something small.


Thanks for any help you can give!.

A better way to reference your wizard steps using named steps

Note: this article uses the plain vanilla but the concepts apply equally well to its popular counterpart .

By far the most common way that I see wizard steps reference in code snippets is by their index.

Export Visio Database Table Names to Excel

If you use the Enterprise Architect edition of Microsoft Visio for data modeling regularly, then there is a good chance that at some point you've wanted to export just the table names into Excel. You might want to do this to map logical ERD entities to physical data model tables, track project status by entity, or track overlap between database versions.

Excel Automation in .Net

Excel Automation is a buzz word in both webapps and winapps. In the programming life (like Mr.Anderson neo) I guess almost every one could have come across this word. For others let me go further in detail. Excel Automation is automating some or all of the process involved in creating or updating excel worsheets.

.NET 3.5 Adds Named Pipes Support

you want a named pipe server, all you have to do is create some instances of NamedPipeServerStream to handle each client connection. I stole the following code straight from the MSDN documentation.

display data into GridView from excel sheet

I have to display data from excel sheet into my gridview and also I need to figure out a way to store data into excel files from my web page. I am using C# 3.5, VS 2008.

Export SharePoint List to Excel Spreadsheet Programmatically using C#

In SharePoint applications, Custom Lists are used to store business data and Document Libraries to store the documents. But for data manupulation and analysis, Microsoft Excel provides very rich features as compared to SharePoint Lists. That's why people still loves to work on Microsoft Excel Sheets.

Creating an Excel Spreadsheet programmatically using VB.NET

The Interoperability services make it very easy to work with COM Capable Applications such as Word and Excel. This article reveals using Excel from a managed application. Excel is the spreadsheet component of Microsoft Office 2000. The majority of Excel programmatic functionality is exposed through Automation via the type library Excel9.olb. The intention of this article is to express that a managed application can interrelate with Excel as a COM server.

Create And Run Excel Macro At Runtime in C#

You've been tasked with writing a Windows Form application using the .NET Framework that interacts with Microsoft Excel. If you are new to working with Excel in .NET and are having trouble learning how to format certain cells or cell ranges, then this little tip will come in handy. Rather than scour the internet hoping to find the correct set of constants or method syntax for formatting your spreadsheet, use the built in tools in Excel to write the familiar VBA code for you. Then, you can paste the VBA code generated by Excel into the sample C# code below and run it dynamically at runtime.

Creating an Excel Spreadsheet and Adding Data to It Programmatically

The Interoperability services make it veryeasy to work with COM Capable Applications such as Word and Excel. Thisarticle reveals using Excel from a managed application. Excel is thespreadsheet component of Microsoft Office 2000. The majority of Excelprogrammatic functionality is exposed through Automation via the typelibrary Excel9.olb. The intention of this article is to express that amanaged application can interrelate with Excel as a COM server.

Creating Excel file from C#

I used below coding for create a excel sheet.I got from on web site.
In below coding there is no problem.Its create the excel file.If i run second time,its ask to overwrite the current excel file.If I said "Yes" then it ok ,but If I select cancel button,its generate the Error message.The exception is

Gridview that exports selected rows to Excel

his seems like a messy workaround, but I ended up using two Gridviews - one to display my data and show my checkboxes, and the other which was invisible amd would eventually end up as my Excel spreadsheet.

PrintPocketCE Print Pocket Excel, Pocket Word and email files

Version 3.560 (May 15, 2009):

Important bug fix: a slight difference in how some devices create fonts was causing a few devices to have significantly longer print times for large print jobs.

Adjusted PocketJet printer paper feed commands

Other minor fixes and enhancements.

Version 3.559 (Mar 5, 2009):

Added support for Martel MCP78xx printers

Version 3.558 (Aug 20, 2008):

Added support for Martel MCP78xx printers

Version 3.557 (June 27, 2008):

Fixed error in Canon printer support

Fixed COM0 port selection

Version 3.556 (June 15, 2008):

Added support for Brother MW-260

Added support for Sato MB400

Version 3.555 (Aug 8, 2007):

Added support for Pentax RuggedJet 3 and RuggedJet 4 printers

Changed Epson TM-P60 support to maximum page width of 1200 dots

Version 3.551 (Aug 1, 2006):

Added Peripheral Nomad printer support

Version 3.550 (Mar 23, 2006):

Added Panasonic JT-H200PR printer support

Added Pocket Spectrum printer support

Fixed problem with WM5.0 "inverted image" problem

Convert excel file into xml


Hai, I have two columns with id,name in the excel sheet.I want to convert into xml file in the below format

<?xml version="1.0" encoding="utf-8"?>

<guide xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema"> <Boat id="1"> <name> Copper Kettle </name> </Boat> <Boat id="2"> <name> Copper Penny </name> </Boat> <Boat id="3"> <name> Copperhead </name> </Boat> <Boat id="4"> <name> Coppertone </name> </Boat> <Boat id="5"> <name> Copy Boy </name> </Boat> </guide>

suggest me with sample code in ASP.NET. Thanks

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