Solved

How do I add an extra summary line to Excel Pivot table report?

Posted on 2010-09-07
11
622 Views
Last Modified: 2012-05-10
I have created an Excel Pivot table in VB.Net that summarises the count of orders for each product by key groups of clients.Each group comprises a number of delivery address and I now need to add the following to my report.
1. A count of the number of delivery addresses per group
2. Show number of orders for each product as a percentage of count of delivery addresses per group. This needs to be shown as a summary row (Distribution%) and the count of orders per group must also be shown as a summary row (Count).
Question is how do I do all this in VB.Net?
Please see a sample report as done manually by some one else.

Thank you for your help.
Pivot-Report.png
0
Comment
Question by:ajege
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 3
  • 2
11 Comments
 

Author Comment

by:ajege
ID: 33624996
I need to add an extra summary line to a pivot tabel report.
0
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 33626361
From what you describe, I don't believe you can do that inside a pivot table. You would need to add formulas to cells outside the table.
0
 

Author Comment

by:ajege
ID: 33627932
Hi rorya,
Thanks for your input, but do you have any idea how to do this in VB.Net (I'm new to Pivot Tables).
0
Office 365 Training for Admins - 7 Day Trial

Learn how to provision tenants, synchronize on-premise Active Directory, implement Single Sign-On, customize Office deployment, and protect your organization with eDiscovery and DLP policies.  Only from Platform Scholar.

 
LVL 85

Expert Comment

by:Rory Archibald
ID: 33627976
Nope, I don't do .Net, but it's not really a pivot table question per se since what you need are formulas. (you can't insert rows in the middle of a pivot table)
You may wish to get the .Net zone added to this question.
0
 

Author Comment

by:ajege
ID: 33628865
Thanks for your help.
0
 
LVL 34

Expert Comment

by:Norie
ID: 33630214
As rorya has suggested it's not strictly a pivot table question or purely an Excel one.

Are you currently automating the process, eg creating an instance of Excel, creating the pivot etc, from .NET?

If you are what code do you have so far and what language are you using? VB? C++? C#?

One approach, and this is perhaps how I would do it myself, would be to do what you want manually in Excel while you have
the macro recorder on.

This will generate VBA which you should be able to adapt to use in .NET.

One thing you might want to try in the pivot table is adding the field you are interested in again and then set it to
display as a % of...

Not sure if that will work for you without seeing your data, but if if it doesn't you could also try a calculated field or rorya/s formula suggestion.

So basically - see if you can do what you want in Excel, record it and then adapt the recording for .NET.


PS I know it might sound complicated but it can be done.
0
 

Author Comment

by:ajege
ID: 33630951
Hi imnorie,
Thank you for your suggestion.
I am using VB.Net to read data from SQL server database and then creating a pivot table. See part of my code below:
   Dim xlWBook As Excel.Workbook = xlApp.Workbooks.Add(Excel.XlWBATemplate.xlWBATWorksheet)
            Dim xlWSheet As Excel.Worksheet = CType(xlWBook.Worksheets(1), Excel.Worksheet)
            Dim xlRange As Excel.Range = CType(xlWSheet, Excel.Worksheet).Range("A2")

            'Create the Pivotcache.
            Dim ptCache As Excel.PivotCache = xlWBook.PivotCaches.Add( _
            SourceType:=Excel.XlPivotTableSourceType.xlExternal)
            connstr = ConfigurationManager.AppSettings.Get("sqlConString")
            connstr = "OLEDB;Provider=SQLOLEDB.1;" & connstr
            ptCache.Connection = connstr
            ptCache.CommandText = sSql   'command()
            ptCache.CommandType = Excel.XlCmdType.xlCmdSql

            'Create the Pivottable.
            Dim ptTable As Excel.PivotTable = _
            xlWSheet.PivotTables.Add( _
            PivotCache:=ptCache, _
            TableDestination:=xlRange, _
            TableName:="PT_Summary")
           
            'Setup the Pivottable.
             With ptTable
                .ManualUpdate = True
                .SmallGrid = False
                .PivotFields("ih_Date").Orientation = Excel.XlPivotFieldOrientation.xlPageField
                .PivotFields("eanName").Orientation = Excel.XlPivotFieldOrientation.xlColumnField
                .PivotFields("ho_Name").Orientation = Excel.XlPivotFieldOrientation.xlRowField
                .PivotFields("Category").Orientation = Excel.XlPivotFieldOrientation.xlRowField
                .PivotFields("ih_accRef").Orientation = Excel.XlPivotFieldOrientation.xlDataField
                .Format(Excel.XlPivotFormatType.xlPTClassic)
                .ColumnGrand = False
                .RowGrand = False
                .ManualUpdate = False
                .ManualUpdate = True
            End With
The RowField is a grouping for which I want to create an extra summary line.

I will try your suggested approach of recording a macro, but any other suggestions will be appreciated.
0
 
LVL 34

Expert Comment

by:Norie
ID: 33631405
You really should try what I suggested.

Personally I wouldn't go near code for creating/manipulating a pivot table using the macro recorder.

If you could post an example of the raw data it would be helpful.
0
 

Author Comment

by:ajege
ID: 33670062
How can I copy a pivot table using VB.Net or VBA?
0
 
LVL 34

Accepted Solution

by:
Norie earned 500 total points
ID: 33674764
There is no specific command to copy a pivot table as such.

If you copy the range the pivot table is in then normally the pivot table functionality and formatting are carried over.

You can get the range of the pivot table using it's TableRange property.

Then you could use something like this, VBA code.

The .NET code shouldn't be much different.

PS I only tried this with a simple pivot table, nothing fancy like external data sources.
Option Explicit

Sub CopyPivotTable()
Dim pt As PivotTable
Dim ws As Worksheet

    Set ws = Worksheets("Sheet1")
    
    Set pt = ws.PivotTables("PivotTable1")
    
    pt.TableRange1.Copy pt.TableRange1.Offset(, pt.TableRange1.Columns.Count + 2)
    
End Sub

Open in new window

0

Featured Post

Office 365 Training for Admins - 7 Day Trial

Learn how to provision tenants, synchronize on-premise Active Directory, implement Single Sign-On, customize Office deployment, and protect your organization with eDiscovery and DLP policies.  Only from Platform Scholar.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
I was prompted to write this article after the recent World-Wide Ransomware outbreak. For years now, System Administrators around the world have used the excuse of "Waiting a Bit" before applying Security Patch Updates. This type of reasoning to me …
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

696 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question