Link to home
Create AccountLog in
Avatar of ajege
ajege

asked on

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

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
Avatar of ajege
ajege

ASKER

I need to add an extra summary line to a pivot tabel report.
Avatar of Rory Archibald
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.
Avatar of ajege

ASKER

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).
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.
Avatar of ajege

ASKER

Thanks for your help.
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.
Avatar of ajege

ASKER

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.
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.
Avatar of ajege

ASKER

How can I copy a pivot table using VB.Net or VBA?
ASKER CERTIFIED SOLUTION
Avatar of Norie
Norie

Link to home
membership
Create a free account to see this answer
Signing up is free and takes 30 seconds. No credit card required.
See answer