Graphic representation of a percentage on a report

timamartin
timamartin used Ask the Experts™
on
Somewhere I saw somebody mention a good way to depict a percentage as a little bar on a report. It entailed drawing a rectangle on top of a rectangle and controlling the width of one (filled) to represent the percentage and the other (unfilled) showing an outline representing 100%
Snap1156.jpg
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Most Valuable Expert 2012
Top Expert 2014

Commented:
Assuming your "back" rectangle is named "boxBack", and your "front" rectangle is named "boxFront" (and is filled with the appropriate color) then:

Me.boxFront.Width = (me.boxBack.Width / Me.SomePercentageField)

So, if your SomePercentageField is, say, 80%, the width of boxFront would be 80% of the width of the "rear" rectangle.

You'd probably do this in the report's Format or Print event of the appropriate section (generally the Detail section).
timamartinIT Manager

Author

Commented:
Detail - On Format - Code Builder
I can't get this to work -- even if I set Me.box.Width = .5

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
Me.boxFront.Width = Me.boxBack.Width / Me.Pct_Comp
End Sub
Jeffrey CoachmanMIS Liason
Most Valuable Expert 2012

Commented:
Please note that the measurements for this (using code) is in "Twips", not inches.

1 inch =1440 twips

So, for example, your Box width is probably going to be 720 not .5 inches

JeffCoachman
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

timamartinIT Manager

Author

Commented:
What happens if the process is not started and the percentage = 0% will I get a dived by 0 error?
Most Valuable Expert 2012
Top Expert 2014

Commented:
Yes; you should check for this first:

If Me.Pct_Comp <> 0 Then

timamartinIT Manager

Author

Commented:
Am I supposed to put something different in the properties sheet? Rather than the starting width of the variable rectangle?
Jeffrey CoachmanMIS Liason
Most Valuable Expert 2012

Commented:
I'll post a sample of how I do this tonight, as an example

I think to get the effect you are after you may have to "Invert" your logic

In other words, in order for a number close to the max, to leave just a sliver of whitespace, on the Right of the bar, you may have to change the way the box "Grows".

Jeff
timamartinIT Manager

Author

Commented:
Ok..here is what I have and for some reason it will not work for me. assuming that the box starts out as 1500 twips, multiplying 15 x % should give me the correct number. unfortunately, the bar size never changes. they are all the same size.
Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
If Me.Pct_Comp > 0 Then
     Me.boxFront.Width = 15 * Me.Pct_Comp
End If
End Sub

Open in new window

snap01156.jpg
Jeffrey CoachmanMIS Liason
Most Valuable Expert 2012

Commented:
Since yopu are not showning any of the values you are using, I can't say with any confidence, what is happening.

I us a formula similar to LSM's:

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
    'BoxRed is "In front of" BoxWhite.
    Me.BoxRed.Width = Me.BoxWhite.Width * Me.Percentage
End Sub

Here is the sample I promissed

JeffCoachman

Access-EEQ24463539SimplePercentB.mdb
untitled.JPG
timamartinIT Manager

Author

Commented:
I fell like an idiot because I still can't get this to work. I will go into more detail and hopefully I can.
Jeffrey CoachmanMIS Liason
Most Valuable Expert 2012

Commented:
First make sure that you try what LSM has suggested first.

My posts were just another way of doing the same thing.

Keep trying, It is probaly something simple.
;-)

Keep us posted.

JeffCoachman
timamartinIT Manager

Author

Commented:
Ok...I'm back at it...

The event that I am using is Detail -- On format --

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
     Me.boxFront.Width = 20
End Sub

at this point all I want to do is get the bar size to reflect a number in the report -- in this case 20. After I get that to happen I will move on to part 2.
timamartinIT Manager

Author

Commented:
Ok...I give up. I am uploading a simplified version of the DB
timamartinIT Manager

Author

Commented:
Whoops here is the attachment...it wouldn't let me upload the file so I changed the extension from .accdb to .txt make sure you change it back to see it. Also...if it asks for a PW it's 'status'
zz.txt
timamartinIT Manager

Author

Commented:
Is the problem because I am trying to do this in a report? There must be a way to do it.
Jeffrey CoachmanMIS Liason
Most Valuable Expert 2012

Commented:
And the password is.....?
timamartinIT Manager

Author

Commented:
 status        
timamartinIT Manager

Author

Commented:
The password is 'status'
Jeffrey CoachmanMIS Liason
Most Valuable Expert 2012

Commented:
OK
Jeffrey CoachmanMIS Liason
Most Valuable Expert 2012

Commented:
Look at this sub:

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
     Me.boxFront.Width = Me.boxBack * Me.Pct_Comp
End Sub

Notice anything missing when you compare your own code posted in http:#a24547956?

Also what is Pct_Comp, and where is it defined?

Everything you need is in the sample I posted.
You must study it "Carefully", then replicate it in your dadatbase.

JeffCoachman
timamartinIT Manager

Author

Commented:
I think there must be a global settings or something that is stopping this from working.

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
     Me.Box3.Width = 1.5
End Sub

Even this won't change the size of the bar -- nothing seems to affect it.
MIS Liason
Most Valuable Expert 2012
Commented:
timamartin,

1. Your Percet Complete Field is a Stored Value in a Table.
Typically Percent Complete is a calculated value in a query based on the amount required and the amount completed.

2. Your Percent complete is a whole number. a percentage is typically a Fraction:
For example:
Needed=55
Complete=37
Percent Complete=37/55=.6727 or 67.27%

3. Also notice that in my Report the Amount Required and the Amount done are *listed* in the Repoert.
This is not the case in your report.
This is why I asked you to examine my sample and try to replicate it in your database.
If my report had the Amount Required and the Amount done listed in it, then why dosen't yours?
Hmmmm?

4. Make sure that the Database is in a "Trusted Location", or the code will not run

5. Finally, make sure you re opening the Report in "Print Preview", not "Report View"., or the code will not run.

Care to try again?
;-)

Jeff
timamartinIT Manager

Author

Commented:
Mine eyes have been opened! Holy cow missed that little item about only working in print preview. So now for the big question....why does it only work in print preview?

Come on you knew it was coming.
timamartinIT Manager

Author

Commented:
Thanks for the help!
Jeffrey CoachmanMIS Liason
Most Valuable Expert 2012

Commented:
;-)

In a nutshell Print preview was always designed to present the report "Exactly" as it will look when printed.

That's great.

The issue is that sometimes code, large graphics, complex designs, ...ect can slow down previewing a report.
Hence "Report View" was born.
No code, no page breaks, just a quick "view" of the report.

;-)

By the way, congratulation for getting this working on your own.
(Well, almost anyway ;-) )
It shows that you are persistient, and smart.

;-)

Jeff

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial