Solved: OWC Pivot Table Questions

[stanl]

I read and re-read XL_Dennis' excellent articles on OWC - and started working w/the Pivot Control. To better understand the methods and properties I am using a persisted XML recordset with 12 fields. I have two questions:

1. Is it possible to set the backcolor for rowaxis, columnaxis?

2. I am trying to Exclude certain fields from the Pivot list. If I display

oPIV.ActiveView.FieldSets.Item("Date_In_Stock").Fields(0).IsIncluded

I obtain -1. But if I try

oPIV.ActiveView.FieldSets.Item("Date_In_Stock").Fields(0).IsIncluded = False

I get an error - that "The fieldset 'Date_in_Stock' has no included fields. At least one must be included"

TIA

Stan

[stanl]

I'm guessing #2 isn't possible with a persisted non-OLAP datasource. But back to the question of background colors. My example Pivot now looks like the attached jpeg. [I cannot forward the complete code and DB at this time for contractural reasons]. But having defined oPIV as the Pivot Control; oView as oPIV.ActiveView and oFlds as oPIV.ActiveView.FieldSets I can set

'grid background color
oPIV.BackColor = rgb(213,179,60)
 
'the row headers and data headers 
oView.FieldLabelBackColor = rgb(240,230,117)
oView.HeaderBackColor = rgb(240,230,117)
 
'the totals and subtotals
oView.TotalBackcolor = rgb(213,179,60) 
oFlds.Item("Model").Fields(0).SubTotalBackcolor = rgb(240,230,117)

which leaves the 'grey areas' - You can right-click on say the Acura grey area and format the backcolor manually - I tried all sorts of ways, even something as exotic as

[code]
oView.RowAxis.FieldSets("Make").member.ChildAxisMembers.Label.backcolor = rgb(240,230,117)
[/code/

and kept getting 'Unknown Name' errors. Would really welcome suggestions.

TIA
Stan

[XL-Dennis]

Hi Stan

I just post here enabling me to come back later when I have more time.

Thanks,
Dennis

[stanl]

Hi Dennis....

No need. I figured it out.

 
oView.RowAxis.FieldSets("Make").Fields(0).GroupedBackColor = rgb(240,230,117)
oView.RowAxis.FieldSets("Model").Fields(0).GroupedBackColor = rgb(213,179,60)
oView.RowAxis.FieldSets("Model").Fields(0).SubTotalLabelBackColor = rgb(240,230,117)

would have been nice if they just left it Object.BackColor = rgb(r,g,b) but then

Stan

[XL-Dennis]

Stan,

I've said it before (on another board ) to You - Thanks for posting the solution You came up with.

The truth is that the OWC Object Structure is difficult to understand as well is not very logic. It also become unneccessarily more difficult as the helpfile is ------.

Anyway, nice work Stan!

Kind regards,
Dennis

[stanl]

Well, not quite a solution

still leaves the Grand Total as gray. I tried

oView.TotalBackColor = rgb(240,230,117)

with no effect, and I cannot find a way to remove the Grand Totals from the Pivot. Any Ideas?

Thank you,
Stan

[stanl]

After switching from google to dogpile for my searches, I think I can now mark the thread solved.

 
'to color grand totals
oView.RowAxis.FieldSets("Make").Fields(0).SubTotalLabelBackColor = rgb(213,179,60)
oView.RowAxis.FieldSets("Make").Fields(0).SubTotalBackColor = rgb(213,179,60)
 
'to remove grand totals
oView.RowAxis.FieldSets(0).Fields(0).Subtotals(0) = false

Stan

[XL-Dennis]

Stan - Great that You got it to work

Edit: BTW, which version of the OWC do You use?

Kind regards,
Dennis

[XL-Dennis]

Stan,

If You add the following line to Your present solution will You still be able to colouring the Grand Total?

[vba]
ptView.DataAxis.FieldSets(0).Fields(0).SubtotalBackColor = RGB(213, 179, 60)
[/vba]