Why my formula not working!? 3

[jehanzebn]

Dear all I have created a report with number of set date ranges, daily, last 5 days,last 4 weeks,year to date etc.

The report is pulling everything correctly meaning the total number of jobs, lenses, sub total and grand total are coming correctly. However when I introduced the percentages, they don't come up correctly.

What I am trying to do is to get the report setup with YTD date range and then divided the actual report into Daily, Last 5 days, last 4 weeks and year to date.

So it looks like this

daily records - Last 5 days records - Last 4 weeks records- Year to date records

Then each has subtotals and grand totals.

I want to show each percentage, for example if the last 4 weeks record has 5 then the percentage should be from the grand total of the last 4 weeks jobs.

However that is not the case because I have setup the report records to Yeartodate and when I run the report, though the running totals are fine, the percentage is coming out of the grand total of the year to date.

I am frustrated and sick and tired of trying every thing I could and now I don't know what else to try.

Could someone please help with this.

here is my formula sequence I used for percentage for each one of the date format.

CR Jobs percentage - this is the cr percentage for the daily records

Whileprintingrecords;
Numbervar Jobs:=0;
Jobs:={#TotalCR};
Jobs%DistinctCount({foccredsum.doc_no});

FOC Jobs percentage - This is the FOC percentage for daily records

Whileprintingrecords;
Numbervar Jobs=0;
Jobs:={#TotalFOC};
Jobs%DistinctCount ({foccredsum.doc_no});

FOCR Jobs percentage - This is total percentage for daily records

Whileprintingrecords;
numbervar crd:=0;
numbervar focd:=0;
numbervar focrd:=0;
crd:={@JobsPercentageCRDaily};
focd:={@JobsPercentageFOCDaily};
focrd:=crd+focd;
totext(focrd,2)+"%"

When I set the report record criteria to Currentdate, the percentage comes up correctly for daily date range however then Last 5 days and last 4 weeks shows no data. Year to date shows the same data as current date as it lies on it.

I suspect that I have to setup something on the 1st and 2nd formula before the distinct Count but I don't know what.

I tried like this
CR Jobs percentage edited - used the date range here in this formula but this didn't work either.

Whileprintingrecords;
Numbervar Jobs:=0;
Numbervar jobcounter:= if {foccredsum.line_type}="C" and {foccredsum.date_created} in ((Minimum(last7days))-1) to((Maximum(Last7days))-1) then DistinctCount({foccredsum.doc_no});
Jobs:={#TotalJobsCRL5D};
if jobcounter=0 then 0 else
Jobs%jobcounter;

Can someone please help me. I am using Crystal Reports 2008 with Informix database via ODBC connection.

Many thanks in advance

Regards

Jehanzeb

Jehanzeb
Crystal Reports 2008
Visual Studio 2008
Informix Database

 

[lbass]

In general you should avoid variables unless necessary. I see no reason for using them above.

If you are trying to do percentages at a group level, then you have to use the group condition in the distinctcount, as in the following formula (which you can use instead of using variables):

{#TotalCR} % DistinctCount({foccredsum.doc_no},{table.groupfield})

-LB

 

[jehanzebn]

IIbass thanks for your quick reply. When I use this group field formula it still doesn't produce the correct percentage. It still takes the distinctcount/Grand total of YTD instead of it's own respective grand total.

Any other ideas I can try?

Regards

Jehanzeb

Jehanzeb
Crystal Reports 2008
Visual Studio 2008
Informix Database

 

[jehanzebn]

I eventually need to put the percentage on the 2nd Group to make it look better. Though that won't be hard to do. It's getting the correct percentage on per group when the date range is set to year to date and the percentage you need is current date, last 5 days and last 4 weeks. That is where I am getting problems with.

The distinctCount({foccredsum.doc_no},foccredsum.reason_code})

does not produce the results according to the date range i.e. last 5 days or last 4 weeks. To get the correct results I need to accommodate last 5 days date range for this formula.

Once setup then we can use the TotalJobs % DistinctCount....

I am running out of options real fast!

Help please.

Regards

Jehanzeb
Crystal Reports 2008
Visual Studio 2008
Informix Database

 

[Madawc]

Have you looked at all the options in running totals? You could derive the various figures that way and then calculate the result in a formula field. The software will do a lot of the work for you, if you'll let it.

Madawc Williams (East Anglia, UK). Using Windows XP & Crystal 10

 

[jehanzebn]

I am not sure what you meant Madawc.

I am using running totals and they are setup like for example Last 5 days is

Field to summarize: foccredsum.doc_no
Summary type: DistinctCount
Evaluate - Formula:{foccredsum.line_type}="C" and
{foccredsum.date_created} in ((Minimum(last7days))-1) to((Maximum(Last7days))-1)
Reset: Group 3

And I am using a formula like this

Whileprintingrecords;
{#TotalJobsCRL5D}%DistinctCount({foccredsum.doc_no},{foccredsum.reason_code});

This is placed in the Group 3 footer.

The report Record formula is the only thing which is setup with year to date, that is what is causing the percentage to change. However I cannot change that because I need to get year to date percentage, plus if I remove that I cannot get the year to date data either.

Hope I am explaining things right here :-S.

Regards

Jehanzeb
Crystal Reports 2008
Visual Studio 2008
Informix Database

 

[Madawc]

You're writing them youtself. Click on Running Totals in the formula box and let the software do the work.

The use of Crystal's automated totals is outlined at FAQ767-6524. It takes a bit of time to get used to them but in the long run it pays off.

Madawc Williams (East Anglia, UK). Using Windows XP & Crystal 10

 

[jehanzebn]

Madawc, Are you sure that we are talking about the same thing here?

I mean my running totals are perfectly fine, I can get Daily, Last 5 days, Last 4 days, Year to date records perfectly.

It's the percentages which is causing an issue. When I use DistinctCount({foccredsum.doc_no},{foccredsum.reason_code}) that is when it causes an issue.

Regards
J



Jehanzeb
Crystal Reports 2008
Visual Studio 2008
Informix Database

 

[lbass]

My formula should have worked. Your running totals needs to to have a reset at the particular group level to get the correct percentage. If you still don't get the correct result, you should show sample data and the result you expect to get.

-LB

 

[lbass]

PS. the formula using the rt and distinctcount must be in the group footer.

-LB

 

[jehanzebn]

Morning all,

I have used what you suggested Ibass but, it still doesn't bring the right results. I still suspect I need to use the date criteria in my formula somewhere where it should filter the percentages according to that date.

I tried the following method

Percentage Formula

{@TotalFOCRT} % DistinctCount({foccredsum.doc_no},{foccredsum.reason_code});

{@TotalFOCRT}

Whileprintingrecords;
Numbervar x:=0;
Numbervar y:=0;
Numbervar z:=0;

x:={#TotalFOC}; //TotalFOC is set to currentdate to currentdate and line_type="F"
y:={#TotalCR};//TotalCR is set to currentdate to currentdate and line_type="C"
z:=x+y;
z;

This Shows, 2 /8 = 0.34% instead of 25%.

Another method

I used another formula but that show the results like this i.e.
1 - 1 /1 = 100%
2 - 1/3 = 33.33%
3 - 3/6 = 50%
----
6

In other words it's cumulative. How can I stop reseting the grand total so the records should be divided by grand total on the report footer.

Here is the formula used

whileprintingrecords;
Numbervar FOCRP;
FOCRP:={@TotalFOCRT};
If FOCRP=0 then 0;
If {foccredsum.date_created} in currentdate to currentdate then
FOCRP/{@TotalJobsFOCRGT}*100
Else
0;

TotalFOCRT is getting reset at the group header level where as FOCRGT is not getting reset at any level to get Total Jobs at the report footer.
Many thanks

Regards

Jehanzeb

Jehanzeb
Crystal Reports 2008
Visual Studio 2008
Informix Database

 

[lbass]

You should not be using variables here, and you should not be resetting them within the same formula where you are declaring them. There is no reason to use variables.

-LB

 

[jehanzebn]

So what you are saying is to use the formula like this

{@TotalFOCR}
Whileprintingrecords;
{#TotalFOC}+{#TotalCR};

And use that in the report without reset?

Regards

Jehanzeb
Crystal Reports 2008
Visual Studio 2008
Informix Database

 

[lbass]

You do not need to add these in a separate formula, and you do not need to use "whileprintingrecords". Use a formula like this (I'm not sure how your rts are set up, but they should be reset at the reason_code group level:

({#TotalFOC}+{#TotalCR}) % DistinctCount({foccredsum.doc_no},{foccredsum.reason_code})

You also seem to want to add some date criteria. These should be built into the evaluation area of the rts. If you want further help, show actual detail level data, and the results you expect to achieve.

If you are trying for a report grand total, then you shouldn't use the group condition in the distinctcount and the running totals should be set to reset: never.

This is all very confusing, because you are presenting incorrect approaches, but without showing us the actual data you are working with.

-LB

 

[jehanzebn]

Morning Ibass, I am sorry if I haven't explain the situation properly though I have tried my very best to express my problem.

I did explain how my running totals are setup few posts back, though I have realized that I don't have to create massive amounts of variables and have changed the setup. I created two Groups, 1 Group which shows the Names of the Reason Code. Second Group Reason Code in ascending order. You will see the screenshot below.

The Running Total are setup like below.

Counting CR Job

Field to summarize: foccredsum.doc_no
Summary type: DistinctCount
Evaluate - Formula:{foccredsum.line_type}="C" and
{foccredsum.date_created} in Currentdate to Currentdate
Reset: Group 2

Counting FOC Job

Field to summarize: foccredsum.doc_no
Summary type: DistinctCount
Evaluate - Formula:{foccredsum.line_type}="F" and
{foccredsum.date_created} in Currentdate to Currentdate
Reset: Group 2

Counting SUB CR Job

Field to summarize: foccredsum.doc_no
Summary type: DistinctCount
Evaluate - Formula:{foccredsum.line_type}="C" and
{foccredsum.date_created} in Currentdate to Currentdate
Reset: Group 1

Counting Sub FOC Job

Field to summarize: foccredsum.doc_no
Summary type: DistinctCount
Evaluate - Formula:{foccredsum.line_type}="F" and
{foccredsum.date_created} in Currentdate to Currentdate
Reset: Group 1

Counting Grand Total CR Job

Field to summarize: foccredsum.doc_no
Summary type: DistinctCount
Evaluate - Formula:{foccredsum.line_type}="C" and
{foccredsum.date_created} in Currentdate to Currentdate
Reset: none

Counting Grand Total FOC Job

Field to summarize: foccredsum.doc_no
Summary type: DistinctCount
Evaluate - Formula:{foccredsum.line_type}="F" and
{foccredsum.date_created} in Currentdate to Currentdate
Reset: none

Similar running totals for all three Last 5 days, last 4 weeks and year to date except the evaluation for them is changed. For example Yeartodate would be

Year to date

Field to summarize: foccredsum.doc_no
Summary type: DistinctCount
Evaluate - Formula:{foccredsum.line_type}="F" and
{foccredsum.date_created} in Yeartodate
Reset: Group 2

Last 5 days

Field to summarize: foccredsum.doc_no
Summary type: DistinctCount
Evaluate - Formula:{foccredsum.line_type}="F" and 
{foccredsum.date_created} in ((Minimum(last7days))-1) to((Maximum(Last7days))-1)
Reset: Group 2

Last 4 Weeks

Field to summarize: foccredsum.doc_no
Summary type: DistinctCount
Evaluate - Formula:{foccredsum.line_type}="C" and 
{foccredsum.date_created} in (Minimum(Last4WeeksToSun)) to ((Maximum(Last4WeeksToSun))-2)
Reset: Group 2

Group2 is set to ascending order of Reason Code.
Group1 is set to Specified format, i.e. named Reason Code

Then I created formulas to get Total Jobs by adding the two together. As Ibass suggested, no need for any variables.

Code for setting up Total Jobs CurrentDate - Record

//Daily Total Job
({#DailyCRJob}+{#DailyFOCJob})

This is placed on the Group Footer 2 (which is set to ascending).

Code for setting up Sub Total Jobs CurrentDate

//Daily Sub Total Jobs
({#DailySubCRJob}+{#DailySubFOCJob})

This is placed in the Group 1 which is set to Specified format

Code for setting up Grand Total Jobs CurrentDate

//Daily Grand Total Jobs
({#DailyGrandCRJob}+{#DailyGrandFOCJob})

This is placed in the Report Footer

Similarly, all others are setup accordingly, with same concept above.

Now I created 2nd section on Group Footer 2. So it looks like Group Footer 2A has Records, and Group Footer 2B has percentages.

These percentages don't work out properly. That is the main problem. Even though I am using Running Totals accordingly the report still shows the percentages from Grand Total of Year to Date.

Here is the design view

Here is the preview Mode

Now if you look at the record under daily jobs, you will see 1 record (Reason Code - 999). Underneath is the given percentage(in Grey color). It states 0.01%. Now if you look at the grand total of daily Job it states 42. 1 / 42 = 2.3% and not 0.01%. 0.01% comes when you divide 1 by Grand Total of Year to date job which is 14594.

The report should be taking percentage from their own respected Grand totals and not from the Year to date grand total.

I hope I made some sense here now?

Regards

J

Jehanzeb
Crystal Reports 2008
Visual Studio 2008
Informix Database

 

[jehanzebn]

Yehaaaaaaaaaa!!! It has been sorted now.

I had to use the formula like this

{@L4WTotalLens}/{@L4WGrandTotalLens}*100. Obviously using Runningtotals as you suggested Ibass.

You are great!!! Many Many Thanks!!!

Kind Regards

Jehanzeb

Jehanzeb
Crystal Reports 2008
Visual Studio 2008
Informix Database

 

[jehanzebn]

Well no Yeaaahaa... as it is not working again! I don't know what happened but now it's not working.

The percentages are messed up.

Regards

J

Jehanzeb
Crystal Reports 2008
Visual Studio 2008
Informix Database

 

[lbass]

You didn't show the content of the percentage formulas you used and where you placed them.

-LB

 

[jehanzebn]

Here is how percentage is being calculated.

Percentage is placed on the Group Footer 2B.

Daily Job Percentage

If {@DailyTotalJob}=0 then 
0 
Else
{@DailyTotalJob}/{@DailyGrandTotalJob}*100

Last 5 Days Percentage Job

If {@L5DTotalJob}=0 then 
0 
Else
{@L5DTotalJob}/{@L5DGrandTotalJob}*100

Last 4 Weeks Jobs

If {@L4WTotalJob}=0 then 
0 
Else
{@L4WTotalJob}/{@L4WGrandTotal}*100

Year to Date Jobs

If {@YTDTotalJob}=0
Then
0
Else
{@YTDTotalJob}/{@YTDGrandTotalJob}*100

The formula contents are given in my above post.

The percentage is coming up from Sub totals instead of grand totals for example.

Regards

Jehanzeb
Crystal Reports 2008
Visual Studio 2008
Informix Database

 

[lbass]

You can't use a running total in the denominator when you are trying to achieve a percentage in a section above the one in which the denominator achieves its full value. You can see this by placing the denominator by itself in the section where you are trying to do the percentage. You must use summaries like distinctcounts with group conditions corresponding to the group level you are evaluating or without the group condition in the case of the grand total. I showed you this in previous posts.

There is no reason to add the running totals in a separate formula as you have done--in effect you have made it difficult to see that you are working with running totals that change values depending upon the section they are in and the reset value--because you have hidden them in formulas.

-LB