how to add a total to a pivot table

[bren]

In runtime, if the toolbar is enabled, the user can add a total by
opening the "Pivot Table Field List" and dragging a "Totals" field from
the list window and dropping it on the pivot table.

I want to do this in code before the pivot table is displayed.

Data for my pivot table comes from an MDX query string assigned to the
CommandText property of the pivot table.

In the MDX query, I have three measures defined ON COLUMNS, but only
one of them is being displayed. I can't see how to configure another to
display in Design Time, so I'm trying to do it in code.

There is an InsertTotal method on the PivotView object. It uses an
AddTotal method on the same PivotView object.

I can't figure out from which FieldSet to get my Field to use as the
second parameter of the AddTotal method. How would I reference in code
the "Pivot Table Field List" > "Totals" > "Total Cost" field?

Here's my attempt, but I'm getting an "Invalid number of arguments"
exception.

oView = AxPivotTable1.ActiveView
oTotal = oView.AddTotal("Total Cost",
oView.FieldSets("Totals").Fields("Total Cost"),
PivotTotalFunctionEnum.plFunctionSum)

I think I'm getting close to an answer; maybe a friendly push will get
me going.

Thanks, Bren

 

[Alvin Bruney - MVP ASP.NET]

Here's some example code:
http://www.amazon.com/gp/reader/141...NwXFOOrGKYiJaSFlOjRycSK18+xqfawA=#reader-page

--
Regards,
Alvin Bruney

Shameless Author Plug
[The Microsoft Office Web Components Black Book with .NET]
www.lulu.com/owc, Amazon, Barnes & Noble etc
Forth-coming VSTO.NET

 

[bren]

Alvin,

Thanks for your reply to my questions.

It's funny; I bought your book, and I already have it open to that
exact page you mention. That's where I got the code that I tried; I
listed that code in this posting, shown below.

The problem I'm still having is with the FieldSets. Your snippet of
code implies the instantiation of a FieldSet("Price").

Set total = pView.AddTotal("Total Profit per Acre",
view.FieldSets("Price").Fields(0), c.plFunctionSum)

In my original question: I can't figure out from which FieldSet to get
my Field to use as the second parameter of the AddTotal method. How
would I reference in code the "Pivot Table Field List" > "Totals" >
"Total Cost" field as it appears in the "Pivot Table Field List"
window?

First of all, my app is in VB.NET, and I'm querying an SSAS 2005 cube.
(can you confirm that OWC11 works with SSAS 2005; I saw a posting from
MS saying it does't work well) In my app, I'm not setting up FieldSets
or DataAxes or Totals, etc. I set the CommandText with an MDX query,
and the PivotTable shows the results. My problem is it's not showing
all of the measures I've included in the query, and a related question
is, why not? If I could answer that, I wouldn't need to code to add a
fieldset.

So, if I can't configure the PivotTable to display the measures in
DesignTime and have to code it, which FieldSet is instantiated by the
CommandText property and the Refresh() method that would contain the
fields that I see in the "Pivot Table Field List" user window? I assume
I could use that fieldset in your code snippet.

Your help is truly appreciated (as well as the effort you put into your
Black Book)

Thanks, Bren

 

[Alvin Bruney - ASP.NET MVP]

The price in my example comes from the record set or underlying table. In
your example, it should be

Set total = pView.AddTotal("Totals",
view.FieldSets("Total Cost").Fields(0), c.plFunctionSum)

This would insert a total cost column in the pivot table and sum the results
based on the totals field extracting values from the underlying data source.


--
Regards,
Alvin Bruney [MVP ASP.NET]

[Shameless Author plug]
The Microsoft Office Web Components Black Book with .NET
Now Available @ www.lulu.com/owc
Forth-coming VSTO.NET - Wrox/Wiley 2006

 

[Antoine Prevot]

Hi Bren,

dunno if that'll help you but here's what i do :
- delete all references to the original total
- add a "calculated" total instead of a simple total

Why a calculated total ? Because this way i can add some extra formatting to
the original MDX statement : color coding or language parameter ... ie :

[Measures].[Sales] ->
'[Measures].[Sales]', back_color = 'iif([Measures].[Sales] < 0, 0,
16777215)', fore_color = 'iif([Measures].[Sales] < 0, 0, 0)', LANGUAGE =
'1036'

Here's the code to remove totals (i remove all of em befor inserting my own
measures) :
__________________________________________________________________
Dim PV As PivotTotal

With PT.ActiveView
Do Until .DataAxis.Totals.Count = 0
Try
.DataAxis.RemoveTotal(.DataAxis.Totals(0))
Catch ex As Exception
End Try
Loop
For Each PV In .Totals
Try
.DeleteTotal(PV)
Catch ex As Exception
End Try
Next
End With
__________________________________________________________________

Don't forget the Try/Catch statement, some calls WILL fail, and don't ask me
why

And here's a part of the code to add a measure :
__________________________________________________________________
Dim cell As PivotTotal
Dim MeasureName As String = "Measure1"
Dim MeasureAlias As String = "Measure One"
Dim MeasureMDX As String = "[Measures].[Sales]"

cell = PT.ActiveView.AddCalculatedTotal(MeasureName, MeasureAlias, MeasureMDX)
cell.HAlignment = PivotHAlignmentEnum.plHAlignRight
cell.CaptionHAlignment = PivotHAlignmentEnum.plHAlignRight
cell.AutoFit = True

PT.ActiveView.DataAxis.InsertTotal(cell)
PT.ActiveView.Totals(MeasureName).DisplayInFieldList = True
__________________________________________________________________

To make it short, i wrote a new class extending the original PivotTableClass
to do all this stuff, so I only have to do :
myPT.Clean()
myPT.InsertMeasure("Measure One", "[Measures].[Sales]")

Hope this helps

 

[Bren Besser]

Although there is some varience of opinion, I've discovered from other
forums, as well as this one that OWC does work with SSAS 2005.

I still trying to add a total to my display. With this AddTotal code:

oTotal = oView.AddTotal("Total Eff", oView.FieldSets("Total
Eff").Fields(0), PivotTotalFunctionEnum.plFunctionSum)

I get this error message:

System.Runtime.InteropServices.COMException was unhandled
ErrorCode=-2147316575
Message="Invalid number of arguments. (Exception from HRESULT:
0x80028CA1 (TYPE_E_OUTOFBOUNDS))"
Source="Interop.OWC11"
StackTrace:
at OWC11.PivotFieldSets.get_Item(Object Index)

I think the error is because the FieldSet doesn't have any fields yet
and the 0 index is out of bounds. This method doesn't make sense to me.

I enumerated through the ActiveView.Totals, and it contains the Total I
want to add to the ActiveView.DataAxis.Totals, but I can't figure out
the syntax for the AddTotal method to get it into the DataAxis Totals.

Separately, It seems to me the DataAxis Totals defaults to contain the
first Total in the ActiveView.Totals.Item(0) which for my cube is
"Total Rev" but I haven't confirmed this yet.

Any feedback, confirmation, recommendations, links to code or articles
is appreciated. I already have Alvin Bruney's Black Book.

Thanks, Bren

 

[Alvin Bruney - ASP.NET MVP]

the collections are not zero based, so you have to start at 1 not zero. See
if that helps.

--
Regards,
Alvin Bruney [MVP ASP.NET]

[Shameless Author plug]
The Microsoft Office Web Components Black Book with .NET
Now Available @ www.lulu.com/owc
Forth-coming VSTO.NET - Wrox/Wiley 2006

 

[Bren Besser]

Alvin and Tonio,

Thanks so much for your help. I made my previous post before checking
for updates and missed you most recent responses.

I'm trying out your suggestions with some success, and I'll report
again later. I just wanted to preempt any futher response from you
until needed.

Thanks again, Bren