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

Pie / Pivot chart in Access report

Hello everyone,

I would like to create a Pie chart on my report which is going to show the progress of my Projects.

My database contains numerous projects and each has let‘s say 5 stages which carry certain number of % out of the total 100%. Let’s say that for project 1 first stage is over: through a form I would enter that the first stage is done, meaning that for example 15% of the project is realized (I connected % and stage-IDs, so this works fine). After a while the second stage will also be done, so now additional 20% is finished. This means in total 35% of the project is done, and this is what I want my pie to show: a full circle with 2 cut parts, each for the two stages.

When I make a report I group the stages on project level. Now when I insert the Pie chart it shows each stage on a separate pie, which is meaningless for my report.

I don’t know how to make the Pie show together all the stages which refer to the same Project.

Can I do this through simply entering the pie chart on report, or do I need to create a Pivot chart, and how?
Can anyone give me some advice?

Thank you all.
0
DraganaRadic
Asked:
DraganaRadic
  • 7
  • 6
  • 2
2 Solutions
 
als315Commented:
You can try to add 100 - sum of completed stages to some temporary tablle
Test example (open Form1, edit data, then press report button)
Db-CHART.mdb
0
 
Jeffrey CoachmanCommented:
Please clarify.
Looking at your percentage figures, how are you sure that they will add up to 100%?
If there are 5 stages, @ 100%, this is 20% per stage.
In your example you listed stage1 as only 15%, then stage 2 as 20%...

Also a Pie chart can only really display all values adding to 100%.
It can sow a "partial percentage.

In other words, if you just have 15% and 20% The pie chart treats this as the whole, or 100%.
Again a pie chart cannot easily display a 20% slice and 15% slice if it does not know what the total will be.
Make sense?  

This is why a typical pie is based on "Values", and the pie chart itself calculates the percentages.

This is why you should always include a sample of your expected output.

JeffCoachman
0
 
DraganaRadicAuthor Commented:
als315>  Thank you for the comment,
I opened your sample DB, but it gives an error note when I press the EDIT DATA or REPORT button on Form1.

Can you give me a clue on how I can use your idea "add 100 - sum of completed stages to some temporary tablle
" ? As I see on your Pie chart, it summs the 2 entered stages, but is doesn't have this sum to 100 part. On the Pie, it only shows the 2 stages, not the 2 stages as a part of total 100.
Do I need an extra field in the Query for that?

Thanks

JeffCoachman> R you saying that if I change the percentages to regular numbers the Pie Chart is going to work as I want it to?  don't expect it to: even if I change the Formatting from persentage to Number, Pie is still gonna take my numbers as 100%. Hence the problem, how do I make the Pie look at the summ of my stages as part of the total 100.

I know my stages will summ to 100 when they all are entered , because for each I have assigned a specific percentage so that they sum to 100. They carry different percentage because they are not of the same importance and lenght.
0
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
als315Commented:
I think you have problems with my russian version of Access.
Try again.
I have now also new idea for you: you can combine all unfinished stages into one and show it on the chart
Db-CHART.mdb
0
 
DraganaRadicAuthor Commented:
als315> Thank you!

This is exactly what I wanted.
I just can't see where did you give the instruction for the "to do" part. I looked in all design views but I can't find it.
Can you explain to me how you introduced this "to do" part? Is in the chart itself, or in the query?

Sincerely,
Dragana
0
 
DraganaRadicAuthor Commented:
Ok, pardon me.
I forgot to look at the code on the REPORT button.
just dissreguard my last question!

I will try to input your chart into my database now, and hopefully you have solved my problem

Sincerely,
Dragana
0
 
Jeffrey CoachmanCommented:
I see that als315 has you sorted, so I'll leave it to him...
0
 
DraganaRadicAuthor Commented:
als315>
Would you care to look at the sample database, it's very basic:

As I guessed, I didn't manage to connect the Pie with the ProjectID-s.

When you open the report rptProject you will see that the Pie shows all the stages, no matter to which project they belong. And I would need to make some kind of filter/restriction, so that the Pie is connected to the Project and it only shows those stages which belong to the Project in question.

Also, since now there is intermediate table (tblStage) I tried to modify the code for opening the rptProject from form frmProject, but I didn't do it well.

Thank you a lot.
Dragana
SampleDatabase.accdb
0
 
als315Commented:
Your idea is not clear for me:
1. You've moved percents to tblStage (it seems to be reasonable only if Stage name will always be unique, may be better to have some table with ProjectID, StageID, Percent and Completed). In your example Stage1 will take 10% fo¿ all projects. Is it correct?
2. Table0 was proposed as temporary table only for chart, not as source for form (subform). In this case you can clear it and fill with query.
3. Do you like to have all projects in one report or you like to print report for selected project?
0
 
als315Commented:
0
 
DraganaRadicAuthor Commented:

1. Stage1 will always take 10%; Stage2 always 20%, and so on. That's why I put percentages in that table, so the user can not insert the pecentages, they are always predefined. The user cna only chose the name.

2. I am very bad in using querys, and I intend to use something like Table0 in my real Database and use it to make a form/subform, like I did in the Sample Database.

3. I will have a list of all projects on one report which will contain all the data about projects and should also show the finished stages for each of the projects.

On the Sample base u attached, the REPORT button opens the project which is currently shown on the form, but it shows correctly the Stages.

But, in my real database, i open the reports from a separate form which lists all available reports. The user choses in a drop down list a report and opens it by clicking a OPEN button. If they chose a report with Project data, it will contain a list of all projcts with it's data (amongst other the finished stages).  
0
 
als315Commented:
It is not so simple to combine in one report many charts and simple to make separate report for selected project.
Do you really need all projects in one report?
0
 
als315Commented:
This sample is based on your original idea, but with separate reports for every project.
SampleDatabase.accdb
0
 
DraganaRadicAuthor Commented:
The last database you attached works properly.

You gave me the baseline, and I will work from there and rethink my current report.

Now the report is a list of all the projects in one city, and I will omit the stages there. But I will make a report for each project separatly where I will include the Pie. In case I need it somewhere else, I will follow the logic you gave me here, and hopefully I ll manage.

I am so surprised that making the chart turned out to be so complicated, and I expected it to be the easy collorful part.

I will close this question now, and when I restructure the report and if I encounter a problem (which I most certainly will) I will open a new one.

Thank you very much for all the help!
Dragana
0
 
als315Commented:
You are welcome
0

Featured Post

Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

  • 7
  • 6
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now