pivottable

How to add Average to the Pivot table in Excel?

I have columns with count of Customers and count of orders. how can i show the average count of orders? ...

Cannot display measure variance in Excel pivot table

I'm trying to display the difference in a measure from one day to the next in Excel (2007) by setting the value field setting to 'Difference From' with Base field = day and Base item = previous. If my underlying data is a SSAS cube, the value that displays for each cell is #N/A. If my underlying data is a set of records on another worksh...

Runtime error 1004: Application-defined or object-defined error

I've been having a major issue... well maybe not major, but I've been trying to figure this out since yesterday lunchtime. I have the following code: Application.CutCopyMode = False ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _ "Data!R7C1:R5000C40").CreatePivotTable TableDestination:= _ "'[Master-File.xls...

SQL Query - Along the lines of a pivot table

I have a mock up of a sql query that will represent a real sql query. Create table #tmp ( Atype varchar(10), Btype varchar(10) ) insert into #tmp values ('a','x') insert into #tmp values ('b','x') insert into #tmp values ('a','y') insert into #tmp values ('a','y') insert into #tmp values ('b','z') insert into #tmp values ('b','y') s...

ManualUpdate on Excel PivotTable

I'm attemping to update an Excel 2007 pivot table in VSTO (C#) and would like to ensure that the pivot table doesn't get updated until all of my edits are done. So there's a property on the PivotTable class, ManualUpdate, that apparently does exactly what I want. Unfortunately it appears that often times when I assign it the value "tru...

Pivot data using LINQ

I am trying to see if I can use LINQ to solve a problem I am having. I have a collection of items that contain an Enum (TypeCode) and a User object, and I need to flatten it out to show in a grid. It's hard to explain, so let me show a quick example. Collection has items like so: TypeCode | User 1 | Don Smith 1 | Mike Jones 1 | Jame...

Excel vba: error hiding calculated field in Pivot table

I have written several Subs to show/hide fields in a PivotTable. Now I am trying to do the same with a calculated field, but I get an error when hiding it. I took my code from the recorder and the recorder's code also halts on the last line. I googled the error message, without serious result. Sub PrRemove() 'remove PR Dim pt As...

Excel VBA: Recreate Source Data from Pivot Table Cache

I am trying to extract the source data from a pivot table that uses a pivot table cache and place it into a blank spreadsheet. I tried the following but it returns an application-defined or object defined error. ThisWorkbook.Sheets.Add.Cells(1,1).CopyFromRecordset ThisWorkbook.PivotCaches(1).Recordset Documentation indicates that Pivo...

OWC PivotTable toolbox item grayed out for WebForm

I'm trying to build a Pivottable on a webform in vb.net 2005 using OWC. I have added the Pivottable item to the toolbox, but it is grayed out and so I cant use it. If I develop a Winform instead of a Webform then the toolbox items are there and work correctly. ...

Using PivotTable's in Access 2007 with large databases (~200 MB)

Hi guys, I'm trying to use Access to help me summarize scientific data - temperature and humidity over the past 30 years or so. The databases are quite large - approximately 200 megabytes each. The ideal for me would be to use a pivot table to perform the summaries for me, but I'm encountering the problem that every time I try to modi...

How to create Pivot table using C#?

Hi! all I need to create a pivot like this BatchID-1 BatchID-2 BachName-1 BachName-2 Chemical-1 0.1 null Chemical-2 null 0.3 BatchID,BatchName are columns of the Sql Server Table. Here i need to display the value of specific ...

C# VSTO: Coloring pivottable cells

HI, I'm trying to make some code color a PivotTable. It works just fine coloring the cells it's supposed to, but if I refresh the table, all the colors disappear, as if the colors haven't been properly attached to the PivotTable. I have the following code (this is a snip from a larger code): myPivotTable.PivotSelect("'" + item["Name"]....

pivot table in userForm in excel vba

Dear All, Any idea about how to embed/insert pivotTable/pivotChart in a userForm? *code sample will be great to share. 1000,000 thanks in advance ...

Identify PivotTable child charts

Hi, I'm trying to delete all charts that a linked to a specific PivotTable. My code is really simple, but I can't figure out how to test if the found chart's datasource is the given PivotTable's data. public void DeleteCharts(Worksheet mySheet, PivotTable myPT) { // Delete all charts foreach (ChartObject item in (C...

SQL Pivot Problem - multiple aggregates?

I have the following data returned buy a simple SQL query. The Number of sites could change, but X,Y,Z are fixed (they are different types of accidents, and the data stored represents the number of occurances) | Site | X | Y | Z | -------------------- A 1 2 3 B 4 5 6 C 7 8 9 I need to get it to the ...