Formula for Percent of Progress

[Lanie]

This is just driving me nuts!

I need to show the total percent of progess based upon the number of records used (which varies from 1 to 8) and the % of Progress which will be 100% when done.

Let me see if I can describe the scenario:
ROWS IN DETAIL % OF COMPLETION
LINE 1 100%
LINE 2 100%
LINE 3 100%
LINE 4 100%
LINE 5
LINE 6
LINE 7
LINE 8

I need to show the total % of completion based upon the variable number of lines. The number of lines will never be the same.

Then I need to have it in the group so that when all lines are at a total of 100% complete - it disappears from the report totaly.

Please let me know if this does not make any sense and I will try to phrase it a little better.

Thanks for any and all assistance. Lanie Lanie
laniet@ij.net
etroidl@conaxfl.com

 

[rosemaryl]

Dear Lanie,

Let me see if I understand.

A group can have a variable number of lines, with each line representing the percentage of completion.

For example, group 1 has 10 lines each representing 10% and once it is complete 100%, you want all lines to be suppressed.

Gropu 2 has 5 lines each representing 20% of the total...

Is this correct? If not, I need clarification.

Rosemary Lieberman
rosemary@microflo.com,

http://www.microflo.com

Microflo provides expert consulting on MagicTSD and Crystal Reports.

 

[Lanie]

Rosemary,

Yes, I believe your stating it correctly. Let me expand a little...Each group is a Project # and the detail lines are the activities for that Project. As each is completed it will remain the status report until all activities are complete, then the group and all associating detail lines must be removed from the report.

Right now I have all activities/detail lines set at 100% progress.

We meed to rollup the details to meet a certain percentage and then disappear. I can't figure this out, since we could have (for example) 30 projects with varying number of activies/details for each project.

I almost seems that I would have to set up several project profiles each set at different percentages to accomodate the correct percentage based upon the number of activites. Then in write a formula that when the activies total 100% it disappears from the list.

In addition we need a formula that tracks the percent of completion based on the number of activities completed and which shows on the report somewhere.

Use this field then to base the 100% completion formula.

Hope this gives a better understanding.

Thanks for you help. Lanie Lanie
laniet@ij.net
etroidl@conaxfl.com

 

[rosemaryl]

Dear Lanie,

I was out sick yesterday and just got your response. Give me a little bit and I will see what I can come up with!

ro Rosemary Lieberman
rosemary@microflo.com,

http://www.microflo.com

Microflo provides expert consulting on MagicTSD and Crystal Reports.

 

[rosemaryl]

Dear Lanie,

I still have some questions, but here is a start.

I am assuming that you want to show the percentage each line comprises of the Project.

First group by Project Number and then in the details place your Line# information.

Now create a running total: {#RTotal0}

Field to Summarize: Your Line# field
Count,
Evaluate on Each Record,
Reset on change of Group (Project#)

Now place the running total in the detail.

Create the following formula (@counter):

count({Table.Line#},{Table.Project#})

This gives us the total lines.

Now create this formula and place in the detail section to give you each line#'s percentage of the total:

//@Percent
whileprintingrecords;
({#RTotal0} / {@counter})*100

This will display:

Project 1
Line Percent of Total
Line 1 20%
Line 2 40%
Line 3 60%
line 4 80%
Line 5 100%

Project 2

Line Percent of Total
Line 1 33.33%
Line 2 66.67%
Line 3 100.00%

So far so good.

It seems from your original example, that there is a field that gets populated for each record showing that line's percentage of completion. Is that correct?

And what you want to do is when all of the % Of Completion details are at a 100% to suppress. Is that correct?

Give me some field names and a little more explanation and I will see what we can do.

ro


Rosemary Lieberman
rosemary@microflo.com,

http://www.microflo.com

Microflo provides expert consulting on MagicTSD and Crystal Reports.

 

[Lanie]

Ro,
The forumla for the percent of completion is working. THANK YOU.

But now I am only getting detail lines which have a % of completion and the rest are not being displayed.
Example:

Project 1
Line Percent of Total
Line 1 20%
Line 2 40%
Line 3 60%
line 4 80%
Line 5 100%
Line 6 not showing at all
Line 7 not showing at all

We need to have this display so that we can see that a certain activity is not yet complete.

I think this has something to do with the relationships which I currently have all 6 tables set to Left Outer Join.

The detail is using fields from four of these tables. I have tried several different join types, but none seem to work.

Any suggestions?
Lanie Lanie
laniet@ij.net
etroidl@conaxfl.com

 

[rosemaryl]

Dear Lanie,

When you say that Line6 and 7 aren't showing. You mean they are not on the report or the percentage isn't displaying.

The fields you use in counter and running total need to be something that is displayed for every line!

Give me an example of the tables and fields.

Happy to help.

ro Rosemary Lieberman
rosemary@microflo.com,

http://www.microflo.com

Microflo provides expert consulting on MagicTSD and Crystal Reports.