.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

Duplicate Product() function from excel

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

I need to duplicate the excel function PRODUCT(1+D76:K76)-1

I need to replace the line Sum(Rtn) as Year with PRODUCT(1+D76:K76)-1   where rtn would be  the columns my current sql is :

Sum(Rtn) as Year,
Sum(case when Month([Date])=1 then Rtn else null end) as [1], 
Sum(case when Month([Date])=2 then Rtn else null end) as [2], 
Sum(case when Month([Date])=3 then Rtn else null end) as [3], 
Sum(case when Month([Date])=4 then Rtn else null end) as [4], 
Sum(case when Month([Date])=5 then Rtn else null end) as [5], 
Sum(case when Month([Date])=6 then Rtn else null end) as [6], 
Sum(case when Month([Date])=7 then Rtn else null end) as [7], 
Sum(case when Month([Date])=8 then Rtn else null end) as [8], 
Sum(case when Month([Date])=9 then Rtn else null end) as [9], 
Sum(case when Month([Date])=10 then Rtn else null end) as [10], 
Sum(case when Month([Date])=11 then Rtn el

View Complete Post

More Related Resource Links

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


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

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.


Searching Duplicate values in Excel - MVC


Hi everyone,

I am working in an MVC .net project. I use the below method to search for duplicate values in an excel sheet before uploading.

The dataset contains all the rows in the excel sheet. the colName is one of the columns in the excel sheet where the duplication is checked. the values in the particular are checked , iterated and if unique a copy stored in hashtable and the duplicate is stored in array list and the entire row containing the duplicate cell value is deleted.

"Here i only compare all the values in a single column of an excel sheet(colName).  I would like to send two columns and compare a cell value in both the columns".

Can anybody help me.

public DataSet RemoveDuplicateRows(DataSet dSet, string colName)
            Hashtable hTable = new Hashtable();
            ArrayList duplicateList = new ArrayList();

            foreach (DataRow drow in dSet.Tables[0].Rows)
                if (!string.IsNullOrEmpty(drow[colName].ToStr

Excel Export - Toggled details duplicate


I have a report that is duplicating rows when toggeled within the excel export.  I do not have this problem in the report viewer, it only happens when exported to excel.  Also, it does this in SQL 2008 and not SQL 2005. 

Any ideas on what could cause the duplication in excel and not the report viewer?



MDX Root()-function: different results in Cube Browser and Excel


I try to write a calculated member that returns the percentage of total (but independent from the chosen dimensions). So i started with the included template "Percentage of total".

When i use the measure in my cube browser, it works so far, but not when i use the measure in excel client.

The problem is the root-function. When i place my dimension in the rows-section and deselect some members in the filter-window, the root-function isn't influenced by the filter in excel. When i browse the cube in SSMS it gives the correct result, because the filter is applied to it.

You can reconstruct the problem very easily by adding this measure: "Root([Dimension Name]),[Measures].[Measure Name]"

This is a big problem in my case, because i absolutely need a grand total, that uses the filter-selections.

I already spent days to find a way, how to write e calculated measure, that just gives me the grand total according to the filter and which i can use with different dimensions. Or is there any way to access the calculated grand total cell in MDX? I can't believe that my problem is so difficult to solve.

Please help me out!


AllowPage and Excel function in Gridview


I have a grdiview that has about 50 columns and the users usually want to see more then 3000 rows of data. The process is kind of slow but the bigger problem is that the application crashes when the user tries to see a large amount of data. Right now, on the gridview, allowpage is set to false and have an excel button.

My question is, when I click the excel button, does it just grab it from the gridview and export to excel sheet when allowpage is set to false? on the other hand, if I set allowpage to true, (i want to show 100 rows per page) does it go back to server again to grab the whole data if there are more then 1 pages of data?

how can i make this webpage more efficiently? please let me know if you need more information. I do not really know how to explain this problem well. advise me, thanks.

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.

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

Content page control passes as argument to javascript function


Here is the content page:


<%@ Page Language="C#" MasterPageFile="~/MasterPage.master" AutoEventWireup="true" CodeFile="JavaDynamicCopyValue.aspx.cs" Inherits="JavaDynamicCopyValue" Title="Untitled Page" %>
<asp:Content ID="Content1" ContentPlaceHolderID="ContentPlaceHolder1" Runat="Server">
 <script type="text/javascript" >
 function copyValue(input, output )
   ouput.value = input.value;
   return true;
 <asp:TextBox ID="txt1" runat="server" onchange="copyValue(this,document.getElementById('<%=txt2.ClientID%>'))"></asp:TextBox>
    <br />
    <br />
    <asp:TextBox ID="txt2" runat="server"></asp:TextBox>


I got a javascript run time error says output is undefined. if I view source of the page, the txt1 markup became:

<input name="ctl00$ContentPlaceHol

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