Change name of labels in a PivotChart

[Maverick]

I am using PivotCharts to graphically display my data. However, when I place
the data into the chart it names the labels with the calculation (e.g. sum of
X, count of X). How do I edit these? The only posting in the forum I saw
recommended doing the sum in the query and then base the PivotChart on the
query. However, the PivotChart still wants to perform a calculation and
places the calculation name in front on the label.

I also went the route of using MSGraph as an object. This works well except
how the data is place in the datasheet makes it so that the data is seen as
the series instead of each set of data. Thus, I am having each plot point
change color in the bar graph. Each bar correctly represents the information,
but it is going to look like a crayon box threw up on it after a lot of data
is entered.

So, that brings me back to the question of renaming the labels for each
series.

I am using Access 2007 but the end users are mostly on 2003.

Thanks in advance.

 

[Maverick]

I did find a way around the issue and it does resolve my need. However, I
would still like to know if it is possible to rename the labels.

My solution was to create nested IIF statements in a query that resulted in
the category names I wanted. Then I simply put that field as my series and
did a count on the primary field (ID). Works like a charm but creates the
added hastle (and resources) of putting in this calculation for each
PivotChart I wish to create. Hopefully someone can still come up with an
answer to this issue.

Thanks again.

 

[Larry Daugherty]

Hi Maverick,

I can't come up with a better solution and don't know if there is one.
My last use of pivots was in Access 2.0!

Since you are still looking for a better solution to your issue, I
suggest that you actually take steps to make your own solution more
elegant and developer friendly. While all that you did to solve your
own issues is still fresh in your mind is the best time to move it
into a more elegant expression. There must be ways you can generalize
and parameterize your solution in a callable function() in a module
that will minimize the work you have to do when you need it in the
future. It may be more work than seems worthwhile when you do it but
it will save you lots of time and effort when you use it.

Further, if you lurk these newsgroups both to learn and to help you
might be able to help others resolve similar problems.

I'm glad you solved your problem and

HTH

 

[Maverick]

Larry,

I wish there were an elegant solution. However, the nesting of the IIF
statements is dependent on the data. This makes it impossible to make it
universal and certainly can't be callable.

An example of the IIF statement follows:

IIf(([OverDue]<=0)*(-1),"On Schedule",IIf(([OverDue]>0 And
[OverDue]<=7)*(-1),"1-7 Days",IIf(([OverDue]>7 And [OverDue]<=30)*(-1),"8-30
Days",IIf(([OverDue]>30)*(-1),"Over 30 Days",""))))

The text in quotes results in the names of my labels as it becomes the
values of the series.

I do wish to have the dual role of both learning and helping. Hopefully this
will make sense to those looking for this same solution.

And....hopefully someone will still have an answer to my original question.

Thanks.

 

[Douglas J. Steele]

Can you not store the groups in a table?

MinVal MaxVal Label
-99999 0 On Schedule
0 7 1 - 7 Days
7 30 8 - 30 Days
30 99999 Over 30 Days

and then join your existing query to that table using a non-equi join?

In other words, the ON clause of your join would be:

ON Table1.Overdue > Groups.MinVal AND Table1.Overdue <= Groups.MaxVal


Incidentally, there's no need that I can see for the *(-1) in your IIf
statement.