Solved

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

Posted on 2010-09-07
11
620 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
  • 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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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 33

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 33

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 33

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

Instantly Create Instructional Tutorials

Contextual Guidance at the moment of need helps your employees adopt to new software or processes instantly. Boost knowledge retention and employee engagement step-by-step with one easy solution.

Question has a verified solution.

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

Whether you've completed a degree in computer sciences or you're a self-taught programmer, writing your first lines of code in the real world is always a challenge. Here are some of the most common pitfalls for new programmers.
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

726 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