• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 4329
  • Last Modified:

Use a second axis in Acces PivotChart

I am relatively new to using pivot charts.  I am creating a company performance presentation and I have found a lot of uses for the pivot charts.  One thing that I see as an option is to add a second vertical axis to the chart.  When I do this though, it seems as though all of the options for one axis affect both of them.  My initial assumption was that I would be able to show a secondary scale on the chart to have a sort of overlay chart mixed in.  

My end goal is to have a chart using columns for each month that are stacked with on time shipments on bottom and late shipments on top of the column  (Upper Right chart on the column charts dialog in access 2007 -- called 100% Stacked Column) .  I have this chart already done and working.  What I want to add is a second axis that would overlay a chart of total shipments on top of the percentage chart.  the overlay would simply be a line chart, but it would add a lot of meaning to see how our on-time shipments were affected by the volume of shipments.  

So the things I need to know are:  How to use a second axis independently of the first axis, and how to bind data to that second axis/  

0
VoodooFrog
Asked:
VoodooFrog
  • 15
  • 10
1 Solution
 
Kelvin SparksCommented:
Have used these before but with Access graph.

Secondary axis always uses last column of data. You need to create manually, and choose style, labels etc.

You can turn off and on through VBA (at least you can with an Access Graph). I tend to stay away from the pivot ones, because users can play around with them too much.
0
 
VoodooFrogAuthor Commented:
kelvin -- I am not sure I understand what you are referring to -- could you illustrate an example of this?  I had set it up to have 2 columns of data (if I am thinking correctly) but I still could not change the scale independently.  
0
 
Jeffrey CoachmanMIS LiasonCommented:
VoodooFrog,

I am using Access 2007.

As with all charting questions, it is hard to visualize what you have without seeing it.
Can you post a sample of your database with the chart as it exists currently.

Then explain what you are trying to accomplish.

Also, does this have to be a pivot chart? I'm thinking it might be easier to use a standard chart.

JeffCoachman
0
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
VoodooFrogAuthor Commented:
I have attached 2 charts printed from the current pivotchart.  And no -- it does not have to be a pivot chart.  I used them in order to create many different views quickly, but learning about the second axis and how to use it is really the point of this question.  If I can get a good knowledge of how to apply that to a normal chart I can go back and work through how to create the pivot of it on my own.  

The point of the whole thing is to illustrate how increased shipments affects our on-time deliveries.  
On-time-Deliveries-all-companies.pdf
Sales-Numbers----Total.pdf
0
 
VoodooFrogAuthor Commented:
I can add a database to this as well if it is really necessary -- since this is for reporting only though, I am able to re-form the data to however would be necessary in order to get the chart to work.  the numbers here are generated through multiple queries and sources already.  
0
 
Jeffrey CoachmanMIS LiasonCommented:
VoodooFrog,

In order to do this, both sets of data must be in the same source.

JeffCoachman
0
 
VoodooFrogAuthor Commented:
they are in the same table  -- the data is a single source.  The first chart is a count of all shipments, and a count of all shipments that are late.  The second is the total $ amount of the shipments.  All of these items are taken from the ShipmentDetail table.  
0
 
VoodooFrogAuthor Commented:
if there is some advanced tutorial floating on the net I would be interested in that too -- I have done some searching, but nothing I find seems even close to what I am looking for.....  
0
 
VoodooFrogAuthor Commented:
I made a sample table in excel to illustrate the date points that I want to use

      On Time      Late      Total
Jan-08      95%      5%      100
Feb-08      96%      4%      110
Mar-08      92%      8%      130
Apr-08      89%      11%      180
May-08      85%      15%      200
Jun-08      90%      10%      150
Jul-08      95%      5%      125
Aug-08      90%      10%      150
Sep-08      85%      15%      200
Oct-08      85%      15%      200
Nov-08      80%      20%      300
Dec-08      95%      5%      200


0
 
Jeffrey CoachmanMIS LiasonCommented:
VoodooFrog,

I don't think this can be done with a pivot chart.

I used a standard chart, and I think it came out OK.

And yes, it does confirm your theory that the higher the total, the higher the late shippments.
:-)

Here is my sample.

JeffCoachman
Access--EEQ23514374-TrenLineSeco.mdb
0
 
Jeffrey CoachmanMIS LiasonCommented:
;-)
0
 
VoodooFrogAuthor Commented:
I am still working figuring out how you did that -- but I can see that it was done the way I was looking for.  Any instruction sheets are still appreciated....  =D

Thanks for the help you have put in on this.  
0
 
Jeffrey CoachmanMIS LiasonCommented:
VoodooFrog,

Yes it was quite a challenge!
:-O

I have my notes at home,
I'll try to remember to post them tonight.

JeffCoachman
0
 
VoodooFrogAuthor Commented:
I appreciate that -- if you would like me to open another question for you to respond in please let me know.  I do not want to short you any points.  
0
 
Jeffrey CoachmanMIS LiasonCommented:
VoodooFrog,

Can you get to this point on your own?

-100% Stacked chart
-All three series plotted (Total Series dominates)

JeffCoachman
untitled.JPG
0
 
VoodooFrogAuthor Commented:
yes I can do that easily -- it is putting the total on the second axis that throws me off...  
0
 
Jeffrey CoachmanMIS LiasonCommented:
OK,

Then go into design view and double-click the chart.
This will put you in "Edit mode".

The toolbar will look like this:

Now click slowly on the "Total" series until all of them are selected.
Then rignt click on any one of them and select "Format Data Series"
Then select the Axis tab, and select "Secondary Axis"
Click OK.

Then right click on the series again, and select "Chart Type", and change it to "Line"

This should do it for you.
You might want to change the line color and Marker to black.

Post back if you need more assistance!
;-)

Jeff
untitled.JPG
0
 
VoodooFrogAuthor Commented:
thanks got it!  

Now just working on getting it in the pivot chart (I do like the flexibility they offer)  

so far I have the seperate lines -- and I can plot the individual data labels on top of eachother -- but I cannot get the second axis scale to shift to be related to the line chart -- I will enclose a file of where I am at.  Hopefully I will get this today.  
0
 
VoodooFrogAuthor Commented:
thanks got it!  

Now just working on getting it in the pivot chart (I do like the flexibility they offer)  

so far I have the seperate lines -- and I can plot the individual data labels on top of eachother -- but I cannot get the second axis scale to shift to be related to the line chart -- I will enclose a file of where I am at.  Hopefully I will get this today.  
Pivot-2-axis.png
0
 
VoodooFrogAuthor Commented:
and got it!

if you are interested -- open a pivot chart in 2007 and open the property field, in the General tab go to the 'Select' drop down and select chart workspace.  

Go to 'Series Group' and you can see the different groups you have added -- at the bottom of this screen you can set the groups to have their own axis.  you have a lot of utility in the series group tab that can add or change the report structure faster than in the typical chart window.  2007 has improved the interface of pivotcharts, which is why I prefer them.  The standard charting interface is clunky compared to the versatility of the pivotchart now.  But learning it in the standard chart gave me a big heasdstart -- thanks for all of your work on this!

One other big benefit I found in all of this searching is that I can use conditional formatting -- in this case we could set a threshold that we have to maintain for on time shipments -- any months missing the mark can be specially flagged to draw even more attention to it.  

I am attaching screens of what I found -- if you have any questions about this process in pivotcharts I can help if possible =D


pivot-chart-main-options.png
0
 
VoodooFrogAuthor Commented:
and the second file -- submitted early on the last post

on this -- I believe there is the capability of having numerous groups and each group can have it's own scale (picked on the bottom section) so you can make some really advanced charts very quickly here.  
pivotchart-series-group-tab.png
0
 
Jeffrey CoachmanMIS LiasonCommented:
If you notice, both "axes" are the same scale.
This means that you did not have the "Total" series selected when you selected "Secondary Axis".

You must somehow remember which series you had selected, and change it back to the Primary Axis.

The make sure you select the Total series and set that to "Secondary Axis".

*(Do not attempt to simply change the scale of the secondary axis, because it will still be linked to the wrong series)*


You can do this, ... or simply start over, to be sure.


JeffCoachman
0
 
VoodooFrogAuthor Commented:
if you look at the bottom picture -- the right axis is 0-350 and the left is 0-1.0

the first picture I posted was an intermediary for what I was working on in pivot charts -- I had not found the grouping tab yet and all I could manage to do was duplicate the same axis.  using the group tab I can create a new axis for any group that I choose to -- very handy and I could imaging that you could add many overlays at the same time, although the organization of that is daunting....  
0
 
Jeffrey CoachmanMIS LiasonCommented:
OK,

So, ...problem solved?

As far as replicating this in a pivot table...
I tried but could not find the equivalent options.

JerffCoachman
0
 
VoodooFrogAuthor Commented:
--- my examples were in a pivot chart -- after I got it to work in the normal I went back and found the equivelent options in pivot chart -- the last couple posts of mine detail the process.  

Post 21919531 on were all in PivotChart mode.  If you wanted any more details about the pivot chart options I can let you know anything that I did.  
0
 
Jeffrey CoachmanMIS LiasonCommented:
;-)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

  • 15
  • 10
Tackle projects and never again get stuck behind a technical roadblock.
Join Now