Solved

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

Posted on 2010-09-07
11
612 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
Comment Utility
I need to add an extra summary line to a pivot tabel report.
0
 
LVL 85

Expert Comment

by:Rory Archibald
Comment Utility
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
Comment Utility
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
 
LVL 85

Expert Comment

by:Rory Archibald
Comment Utility
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
Comment Utility
Thanks for your help.
0
Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

 
LVL 33

Expert Comment

by:Norie
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
How can I copy a pivot table using VB.Net or VBA?
0
 
LVL 33

Accepted Solution

by:
Norie earned 500 total points
Comment Utility
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

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

Entering a date in Microsoft Access can be tricky. A typo can cause month and day to be shuffled, entering the day only causes an error, as does entering, say, day 31 in June. This article shows how an inputmask supported by code can help the user a…
Recently Microsoft released a brand new function called CONCAT. It's supposed to replace its predecessor CONCATENATE. But how does it work? And what's new? In this article, we take a closer look at all of this - we even included an exercise file for…
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

763 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now