Data Labels in PivotChart

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I've created a very useful PivotChart. It sums up the areas occupied by business units for each floor in a building. The Chart Type is Stacked Bar. The Page Field is the Building. The Category axis is the Floor. The Pivot sums the square feet occupied by each business unit. What I want to finish this off with is to label each piece of the stack with the name of the business unit. Unfortunately the built-in data labelling options are limited to "value" - namely the area, or "label" - which shows the floor number

How do I get the business unit name (shown in the legend) onto the graph? I'm intrigued by Debra Dalgleish’s tool for labelling the last point in a series. When I run it against my chart, inserts the legend labels all right, but the top floor of the building is always the end of the series, and it labels zero values, so I get a chart with all the business unit names on the top floor - and all ones with no space printed over one another! Perhaps I'm looking to alter Debra's tool to label each non-zero data element the legend labels...

-Dave
 
I've successfully augmented Debra Dalgleish's code to
label all the data elements, but still have the problem of
labels being generated for the zero-value items...

-Dave.

-----Original Message-----
I've created a very useful PivotChart. It sums up the
Click to expand...
areas occupied by business units for each floor in a
building. The Chart Type is Stacked Bar. The Page Field
is the Building. The Category axis is the Floor. The
Pivot sums the square feet occupied by each business
unit. What I want to finish this off with is to label
each piece of the stack with the name of the business
unit. Unfortunately the built-in data labelling options
are limited to "value" - namely the area, or "label" -
which shows the floor number.
How do I get the business unit name (shown in the legend)
Click to expand...
onto the graph? I'm intrigued by Debra Dalgleishâ?Ts tool
for labelling the last point in a series. When I run it
against my chart, inserts the legend labels all right, but
the top floor of the building is always the end of the
series, and it labels zero values, so I get a chart with
all the business unit names on the top floor - and all
ones with no space printed over one another! Perhaps I'm
looking to alter Debra's tool to label each non-zero data
element the legend labels....
 
Dave -

After running Deb's code, change the number format of the labels to
something like this:

0.0;-0.0;"";

Adjust as necessary: the one I included uses 0.0 for positive numbers,
-0.0 for negatives, and "" for zeros. There's still technically a label,
but it has no characters.

- Jon
 
You folks are great! ...and proper thanks to the code-writer

The data labels from the PivotTable legend are text strings (like "Visa Department"; my client is a bank!), so changing the number format doesn't have any effect. I've got the manual alternative to select data points one at a time an deleting them, but that's really tedious. I've got 100 points to delete in some cases. I'd prefer to only create the labels when the string length is > 0, but don't know what variable to test in the loop..

-Dave
 
Dave -

This code finds the last nonzero point and applies the label to it:

Sub LastPointLabel()
Dim mySrs As Series
Dim nPts As Long, iPt As Long
Dim ErrNum As Long
If ActiveChart Is Nothing Then
MsgBox "Select a chart and try again.", vbExclamation, "No Chart
Selected"
Else
For Each mySrs In ActiveChart.SeriesCollection
With mySrs
nPts = .Points.Count
For iPt = nPts To 1 Step -1
If .Values(iPt) <> 0 Then
On Error Resume Next
mySrs.Points(iPt).HasDataLabel = True
mySrs.Points(iPt).DataLabel.Text = mySrs.Name
ErrNum = Err.Number
On Error GoTo 0
If ErrNum = 0 Then Exit For
End If
Next
End With
Next
End If
End Sub

- Jon
 
Back
Top