Solved: OWC Pivot Table Questions

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

Code:

---

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

---

I obtain -1. But if I try

Code:

---

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

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

Code:

---

'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

Hi Stan :)

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

Thanks,
Dennis

Hi Dennis....

No need. I figured it out.

Code:

---


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 :clap:

Stan

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

Well, not quite a solution:motz2:

still leaves the Grand Total as gray. I tried

Code:

---

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

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

Code:

---


'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

Stan - Great that You got it to work :)

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

Kind regards,
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]