.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

how can i use tow sum column in Pivot Table

Posted By:      Posted Date: April 10, 2011    Points: 0   Category :

Hi All 

I Want to use Tow Column for Sum in Pivot Table it is possible ?

hear is my pivot stirng

PIVOT ( SUM(USEDKG), SUM(USEDBAG)   FOR RMaterial IN (      ' + @PivotColumnHeaders + '    )  ) AS PivotTable

but it showing error


View Complete Post

More Related Resource Links

Pivot table query dynamic column.


Hello All,


I want to export my data to excel sheet. The logic to export data in excel is clear.

Now my query is I have 3 tables and I want to generate the data jst like below.



Company Name

Company Email


Create Date


Fax No














And so on.....


The data will come from 3 tables I.e


  1. Master Table for Company

  2. How to Add Multiple Column's Sum in Pivot Table...???


    hi Fredz..


    My Final Table is #Example and in that table m adding Addition of 5 Columns but its not working Properly..???


    declare @columnheader varchar(max)

    Select @columnheader = COALESCE(@columnheader + ',isnull([' + cast(@FieldName as varchar) + '],0)','isnull([' + cast(@FieldName as varchar)+ '],0)') 

    FROM #Example


    DECLARE @query VARCHAR(max)

    SET @query = '

    SELECT * into fianltable

    FROM #Example



     SUM(Col1+ Col2+ Col3Col4 + Col5)     ----In that line error will occur

    ----If m can change this line n taking only SUM(Col1then its Works, but i want addition of

    ---- FIVE columns

     FOR [MergeValue]

     IN (' + @columnheader + ')


     AS p order by ' + @columnheader + ''


    Analysis Services 2008 e Excel pivot table: different filters for each column


    I have to create a report in excel 2010 based on a  analysis services 2008 cube.

    I created a pivot table that contains four columns containing the elements of a hierarchy. My problem is that each column should be filtered with different values of the same field.

    For example: given a hierarchy that describes me a set of products, I have to filter the product for customer  1 and 3, Product B for  customer 2 and the product C for the customer 3.

    I think this thing is not possible in Excel (right?)

    So I decided to use a mdx script to filter each product 

    I tried so (for example, product A):
    scope (descendants([Products].[products].[liv0].&[A], 1, leaves));
    [Measures].Quantity = sum({[Customer].[Code].&[01], [Customer].[Code].&[03]}, [Measures].quantity);
    end scope;

    I still get an error of infinite recursion. How can I solve my problem? Is there a better system than what I am following?

    Display column from child table. Possible ?



    I'am using dnamic entity with EF4. On a list page of a table, I would like to display a column containing information from a child table of the current element. Sample :

    Order List :

    Order Data | Required Date | Shipped Date | etc... | ... | Customer Name (foreign key with tostring() method override) | Customer Postal Code (Column that I want to add) |

    I don't known how to do that. Is it possible ? Maybe I have to to create my own Metadata proxy that will add dynamicly a column on the MetaColumn list of the table.

    Does someone have an easier or better idea ?

    Thank you for any help.

    DataGrid: Tailor Your DataGrid Apps Using Table Style and Custom Column Style Objects


    One of the most enduring challenges in writing user interfaces is figuring out how to display large amounts of data efficiently and intuitively without bewildering the user. The problem becomes particularly thorny when the interface must reflect hierarchical relationships within the data that the user needs to modify. The Windows Forms DataGrid control gives developers a powerful and flexible tool to meet this challenge. This article explains its basic operations and shows how to extend the DataGrid to display columns of data in an application-appropriate manner.

    Kristy Saunders

    MSDN Magazine August 2003

    SQLCE table column DefaultValues don't show up in XSD Dataset designer

    Using Visual Studio 2008 with SQL CE 3.5, I notice that default values in the creation scripts for the database tables are not reflected in the dataset designer XSD file.  For example, the following SQL script creates the non-nullable table column names "Content" with a default value of 'Image':    "Content" nchar(20) NOT NULL DEFAULT 'Image',But in the column properties of the dataset designer (XSD) panel, this column correctly shows up as non-nullable, but with a DBnull default value as follows:     Name: Content    Allow DBnull: False    DefaultValue: <DBnull>Am I missing something somewhere, or is this a VS bug?  Also, how do I get the XSD file to regenerate after schema changes in the database?  Sqlmetal doesn't do it.Thanks,    -BGood

    Fetch Identity column just after inserting a row in table

    Hi, Please help me with this question. I have a table and I insert a row into it. How can i select the latest inserted row? Can the 'inserted table' keyword be used outside trigger? ( I mean can we use it in above scenario?? If yes how)   Thanks in advance Tiya

    can alias in select be used for selecting other column in that table?

    Hi All,I want to use an alias name in a select clause to select other column in that table? select   top 1 (   case when CreatedByName <> '' then 'yy'         else 'xx' end) as filName, (filName + 'xx')from Order       But it throws error like " Invalid column name 'fileName'."Could you please help me out?

    How to get a Table name from the column Value in Sql Server?

    Hi All,I have a number of tables in the database and i have a column value as "abc" coming from one of the tables in the database,Now i need to find the table name from where this column value is coming?

    PIvot Table in SSRS 2008

    Hi All, Is there any data Region (Pivot) in SSRS 2008 or higher version? Our requirement is to show the report like excel pivot gererated from a OLAP Cube where we can select or unselect any dimesion attribute or measures. Thanks, IndiaSQl  

    SSAS Cube & Pivot table

    Hello, I am connecting to my SSAS cube through excel Pivot table. I have the following questions: 1) I have a filter for Company. However, instead of selecting multiple companies from a drop down list, I would like to have a range e.g. Company between 100 and 200. We have this "Filter expression and operator" option in SSAS but I dont see in excel. 2) I have 90 users who will be using my cube via pivot tables. How do I automatically have the data in excel refresh automatically such that when the user comes in, in the morning and open the pivot table, they have the refreshed data. Instead of them manually refresh it as it runs the query for a long time. Thanks in advance for all your help and suggestions! KJ

    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

    Is it suggested to have extra column on the SSISConfigurations SQL Configuration table

    Hello, I have all my configurations are saved in SQL Server table. SO I just wanted to know if it is advisible to have extra column on that table? Thanks, Prabhat

    How to recover from Derived Column Transformation Editor corrupting table metadata?

    Hi there, In attempting to use the DCTE to replace the value in a field with a trimmed version (SSN = trim(SSN)), it seems that my meta-data has become corrupted by the Derived Column drop-down list. As a result, #1: I no longer see my incoming SSN field in the "columns" tree, and any reference to it is deemed "invalid", even though it's value does make it out of the Data Transformation process. How can I get back the reference to this field without redoing this entire task? And, #2: in the process, DCTE created new columns with "SSN" prefixed by the task name, such as "trim character fields.SSN". How can I delete these? It seems that one slip of the mouse in this form can lead to irreversible corruption of the meta-data, which the "debugger" then references and uses to "invalidate" subsequent work. I have tried everything I can think of to refresh this, including using the "Advanced Editor" and reloading the entire package. Any ideas? Thanks, Karl Kaiser

    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.

    "Non-additive" target value in KPI. How to present it in a scorecard or Pivot Table.

    Hi.. I would like to have some help in designing the following solution. It musn't be to hard, but i can't figure it out yet.. I have 2 tables: Group and Worker. Table Group has GroupID and GroupTarget. Table Worker has WorkerName, GroupID, WorkerTarget and Amount. The relation between tables is GroupID, as each worker belongs to a group. Now I want to take this to a pivotTable, to present KPI for each worker, and the value of the indicator is calculated comparing the Amount vs the WorkerTarget. Also I setup a single hierarchy in the PivotTable, where the parent is the GroupID, and when displayed, you can see all the Workers belonging to that group. Now the problem is, Everything is fine when I want to see the actual group total amount value, as I just have to SUM all the Amounts of each Worker, and I can see the GroupAmount. But.. ¿How can I display the GroupTarget in the PivotTable in the same column as the WorkerTarget? I don't mind a solution using SSAS or PerformancePoint, but my idea is in SSAS to put some kind of formula in the KPI target, so when I'm in a level of the hierarchy, the target is WorkerTarger, and when I'm in the parent, the target is GroupTarget. Is there some kind of formula like this??   If not, any other idea will be much apreciated.. Thanks a lot.. regards.. Canario O.

    wrapping a table into a multi column page

    I have a table populated with some data fields that spans 5 pages.  I am trying to set up the layout to be a multi column report (just two columns) in which the table wraps at the end to populate the right section of the page How can this be done?Javier Guillen
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