Solved

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

Posted on 2010-09-07
11
615 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
 
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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

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.
Using Word 2013, I was experiencing some incredible lag when typing.  Here's what worked for me....
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.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

912 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

22 Experts available now in Live!

Get 1:1 Help Now