Solved

Use a second axis in Acces PivotChart

Posted on 2008-06-25
26
4,071 Views
Last Modified: 2013-11-28
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
Comment
Question by:VoodooFrog
  • 15
  • 10
26 Comments
 
LVL 22

Expert Comment

by:Kelvin Sparks
ID: 21871308
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
 

Author Comment

by:VoodooFrog
ID: 21874246
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
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 21877113
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
 

Author Comment

by:VoodooFrog
ID: 21877595
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
 

Author Comment

by:VoodooFrog
ID: 21877609
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
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 21881306
VoodooFrog,

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

JeffCoachman
0
 

Author Comment

by:VoodooFrog
ID: 21883721
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
 

Author Comment

by:VoodooFrog
ID: 21883777
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
 

Author Comment

by:VoodooFrog
ID: 21884533
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
 
LVL 74

Accepted Solution

by:
Jeffrey Coachman earned 500 total points
ID: 21885824
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
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 21911061
;-)
0
 

Author Comment

by:VoodooFrog
ID: 21911232
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
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 21911458
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
Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 

Author Comment

by:VoodooFrog
ID: 21911701
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
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 21913022
VoodooFrog,

Can you get to this point on your own?

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

JeffCoachman
untitled.JPG
0
 

Author Comment

by:VoodooFrog
ID: 21915807
yes I can do that easily -- it is putting the total on the second axis that throws me off...  
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 21918527
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
 

Author Comment

by:VoodooFrog
ID: 21919530
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
 

Author Comment

by:VoodooFrog
ID: 21919531
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
 

Author Comment

by:VoodooFrog
ID: 21919611
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
 

Author Comment

by:VoodooFrog
ID: 21919628
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
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 21919801
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
 

Author Comment

by:VoodooFrog
ID: 21920415
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
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 21922191
OK,

So, ...problem solved?

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

JerffCoachman
0
 

Author Comment

by:VoodooFrog
ID: 21924889
--- 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
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 21928125
;-)
0

Featured Post

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
Regardless of which version on MS Access you are using, one of the harder data-entry forms to create is one where most data from previous entries needs to be appended to new records, especially when there are numerous fields and records involved.  W…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

758 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now