Solved

# crystal report show highest values of 5 running fields

Posted on 2014-10-10
381 Views
Hi guys, kindly help me out to this problem.
i want to create a report that summarize the "comments" in a field in database.
i already did that part. and it shows 5 running totals for 5 different comments.
the problem is how can i show in group footer the first two highest comments.
my formula for summarizing comments field is like this:

@sku_problem
if {@START_REMARKS} startswith "SY"    then
4 else 0

@STORE_CODE
if {@START_REMARKS} startswith "ST"    then
5 else 0

where @START_REMARKS is like:

Low Volume
Soon to Open
Sale Event
System SKU Problem
Store bar code request
Hold Store
Manual generate OIS

-karlo
0
Question by:karlo mark medina
• 26
• 13
• 7

LVL 100

Expert Comment

ID: 40374376
What is the group?

DO you only have 1 comment per store?

Can you using sample data show what it is you want?

mlmcc
0

Author Comment

ID: 40374394
Thanks for attending my concern sir,
Actually sir i do have my existing report. Shipment Order report
it computes the date difference from orderdate to shipdate.
i have formula to sum up the the Shipment number accdg to days processed.
0-3 days           4-7 days                 8-14 days               and 15-above days.

here's the problem:

i need to show the two highest comments for that four categories (days process 0-3 days ,4-7 days, 8-14 days  and 15-above days.)
E.g
0-3 days           4-7 days                 8-14 days               and 15-above days.
3                          2                                1                                   0

in 0-3days category i got comments like
SKU problem = 1
store code = 2
hold store = 3

how can i get the hold store  & store code comment and place it beside 0-3days category. since they are the two highest comment on that category.

-karlo
0

LVL 100

Expert Comment

ID: 40374398
What formulas are you using to get the counts?

mlmcc
0

Author Comment

ID: 40374403
@sku_problem
IF {ORDERS.STATUS}IN ['95','92'] THEN
(if {@START_REMARKS} startswith "SY"    then
4 else 0)

-and create a running fields to sum up all.and evaluate when @sku_porblem=4

@store_code
IF {ORDERS.STATUS}IN ['95','92'] THEN
(if {@START_REMARKS} startswith "ST"    then
5 else 0)

and create a running fields to sum up all.and evaluate when @store_code=5

same formula applies for other comments sir.

since i got all the the figures/sum of all comments.
i can now say what will be the remarks must be posted in report footer besides those 4 categories i mentioned ealier sir. i can just import the report as excel and type those comments. but i need it automated.
is this possible sir?

-karlo
0

LVL 100

Expert Comment

ID: 40374415
I am still not clear as to how you are processing the data.

You have records with order num, date ordered, date shipped and comments

Your report shows the counts of orders shipped based on a range of days it took to ship

Why are you setting values for the comments?

Are you adding the comments or the assigned values up in some way?

I think what you want is to show there were 10 orders that took 0-3 days and 4 comments on SKU and 2 on store code is that correct?

mlmcc
0

Author Comment

ID: 40374421
I think what you want is to show there were 10 orders that took 0-3 days and 4 comments on SKU and 2 on store code is that correct?
-yes you got my point.
-first i will get all the orders with their corresponding category(days processed)
-then sum up all the comments(7 types of standard comments to be exact) from per every category(days processed)
-get the two highest comments per category(days processed)
-show the two highest comments per category on the groups footer.
sample output

0-3days
4-7                         hold store,sku problem
8-14                     sku problem,hold store
15-above              hold store,sku problem

note. 0-3days category dont need comment.
0

LVL 100

Expert Comment

ID: 40374435
You keep saying sum up the comments.

If you have 16 orders for a particular range
4 comments on soon to open

I would expect Low volume and Soon to Open to be listed

However if they get values based as it seems on the order listed above
SKU will be valued at 4 and Store at 5. Low Volume at 1 and Soon to Open at 2.
Which comments do you want to see?

mlmcc
0

Author Comment

ID: 40374454
if you have 16 orders for a particular range
4 comments on soon to open

I would expect Low volume and Soon to Open to be listed

However if they get values based as it seems on the order listed above
SKU will be valued at 4 and Store at 5. Low Volume at 1 and Soon to Open at 2.
Which comments do you want to see?

-i just create this formula @store_code
IF {ORDERS.STATUS}IN ['95','92'] THEN
(if {@START_REMARKS} startswith "ST"    then
5 else 0)
so that when i create running total field. i count the value and in evaluate using the formula
{@store_code}=5

so that my running total for that @store_code count only the values that has "5" values. thats how i get the total
values for every comment..

i think my approach is very noob but i think i were able to manage to distinguish what comment has the highest value.

and sir, i got 7 formula for seven comments.
and set a unique value for every formula so that my count in running fields count only the values that i set in my evaluate formula.
0

Author Comment

ID: 40374502
attachemnt-report2.xlsx
0

LVL 34

Expert Comment

ID: 40374672
I think at least part of the confusion is because you're using numeric values to identify the comments, but the numeric values don't really mean anything.  As I understand it, you're just counting the different values, not actually adding them up.  For example, @sku_problem is 4 and @store_code is 5, but that doesn't make @store_code more "valuable".  They're just identifying numbers, like a store # or a customer #.

It might be clearer if you replaced the numeric values with strings.  For example, you could change @sku_problem from

IF {ORDERS.STATUS}IN ['95','92'] THEN
(if {@START_REMARKS} startswith "SY"    then
4 else 0)

to

IF {ORDERS.STATUS}IN ['95','92'] THEN
(if {@START_REMARKS} startswith "SY"    then
"SK" else "")

Then you'd do a count when @sku_problem = "SK".

Then again,  an even simpler approach would be something like this:

// sku_problem
IF {ORDERS.STATUS}IN ['95','92'] THEN
(if {@START_REMARKS} startswith "SY"    then
1 else 0)
ELSE 0

That formula just produces a 1 when the conditions are met, and then you can (theoretically) just do a summary on that formula to get the count, instead of using a running total.

I added an Else at the end for the first If, just because.  :-)

Also, technically, if @START_REMARKS is already extracting the first 2 characters from the field, as mentioned in your first post, then you don't need startsWith.  You could just use {@START_REMARKS} = "SY".  That could be more efficient, since startsWith is basically a wildcard string comparison (eg. Like "SY*").

Of course none of that actually addresses your question, but it hopefully gets us closer to a solution.  At this point I'm thinking that it will come down to looking at your counts, however they're produced, finding the two highest counts, and producing the appropriate comment for each of those counts.

James
0

Author Comment

ID: 40376243
you got my process sir.
now i do have those sum every comment.

problem is how can i show the highest sum of those variable for a single formula?
im thinking of this logic:

if @sum1>@sum2 and @sum1>@sum3 and @sum1>@sum4 and @sum1>@sum5 and @sum1>@sum6 and sum1>sum7 then
({@sum1})

is this possible?
or there are more efficient ways?

thanks
0

LVL 34

Expert Comment

ID: 40377363
Do you just want to show the highest count (the actual number), or the comment that's associated with the highest count?  Your last post is just producing the count ({@sum1}), not the comment.

Do you want to show more than one result, like the 2 highest?

Checking to see if a count is the highest is fairly simple.  In your last post, you had @sum1 - @sum7.  You could use something like:

if {@sum1} = Maximum ( [ {@sum1}, {@sum2}, {@sum3}, {@sum4}, {@sum5}, {@sum6}, {@sum7} ] ) then
{@sum1}

If you wanted to show the associated comment, you could put that comment at the end, after the Then.

Of course you could presumably have a tie for the highest count, and if you're showing the comments, there's the question of how you'd want to handle a tie like that.  Just show one of the comments that has the highest count, or all of them?

Now, if you want to show more results, like the 2 highest counts/comments, that gets trickier.  Finding the second highest count is more complicated.  At this point, I'm thinking that the simplest thing would be to sort the counts, in descending order, so that you know that the first value is the highest, the second is the second highest, and so on.  There would also be the question of how to handle ties; like if the highest counts were 5, 4 and 4; do you show both of the 4 counts/comments?

James
0

LVL 100

Expert Comment

ID: 40377444
Will there always only be 7 comments you are interested in?

DO the comments change over time?

mlmcc
0

Author Comment

ID: 40378875
hi Sir james
- Do you want to show more than one result, like the 2 highest?
yes if possible
-there's the question of how you'd want to handle a tie like that.  Just show one of the comments that has the highest count, or all of them?
is this hard to code?if yes, maybe i can pick one from the two

Hi sir mlmcc
-Will there always only be 7 comments you are interested in?
yes, only seven
-DO the comments change over time?
no, they are using standard comments.
0

Author Comment

ID: 40379122
i were able to show the highest comment on the report.
but how can i show the second and third highest?
maybe if tie occurs, the report will pick one.
0

LVL 34

Expert Comment

ID: 40379745
I've come  up with something that I think will work.  The idea is to use two arrays:
The first array contains the counts, and we'll use that array to sort the counts from highest to lowest.
The second array contains the comments, which we will sort at the same time as the first array, so we end up with the comments sorted from highest count to lowest.

Then you can just output the first X entries in the comment array to display those comments.

The num_list array will contain the counts to be sorted.  If, as indicated earlier, your counts are produced by formulas named @sum1 - @sum7, you can put those in the array.  That's what I did in the formula below.  If your counts come from something else, you'll need to change the statement that fills num_list.

The comments array contains the comments (Duh :-).  You need to put the comments in the same order as the counts.  If you fill num_list with @sum1, @sum2, @sum3, ... @sum7, then the comments array should be filled with the comment for @sum1, then @sum2, then @sum3, and so on.  I took the comments in the formula below from your first post.  Change or rearrange them as desired/required.

At the end, I output the first 3 values in the comments array, which should be sorted from highest count to lowest at that point.  I put a Line Feed (ChrW (10)) in between the comments, so they'll be on separate lines.  When you put the formula on the report (presumably somewhere in the report footer), you'll need to go to the field format and set the "Can Grow" option.

I did not try to address the "tied count" issue.  You could do it by checking the counts in num_list.  I didn't try to do anything, because I don't know what you want to do, but, for example, if you're showing the highest 3 comments, you could add tests to see if the 4th, 5th, etc. count was the same as the 3rd, and display those comments too if the counts were the same.

James

``````WhilePrintingRecords;
Local NumberVar i;
Local NumberVar j;
Local NumberVar k;

Local NumberVar tmp_num;
Local NumberVar Array num_list;

Local StringVar tmp_str;

// Redim the arrays to hold the required number of values
Redim num_list [ 7 ];

// Put the counts in the array
num_list := [ {@sum1}, {@sum2}, {@sum3}, {@sum4}, {@sum5}, {@sum6}, {@sum7} ];

// Put the comments in the array, in the same order as the counts above
comments := [ "Low Volume", "Soon to Open", "Sale Event", "System SKU Problem",
"Store bar code request", "Hold Store", "Manual generate OIS" ];

j := UBound (num_list);

// Sort the totals, from highest to lowest, and sort the comments along with them
if j > 1 then
for i := 1 to j - 1 do
for k := i + 1 to j do
if num_list [i] < num_list [k] then
(
tmp_num := num_list[i];
num_list[i] := num_list[k];
num_list[k] := tmp_num;

);

// Output the first 3 comments (which are now sorted from highest count to lowest)
comments [ 1 ] + ChrW (10) + comments [ 2 ] + ChrW (10) + comments [ 3 ]
``````
0

Author Comment

ID: 40381304
Hi Sir james, Almost there, your formula is showing 3 comments, but i think theres an error.
Yes it shows on top the highest comment which is very helpful, but the 2nd and third comment always Soon to open and Sale event. since i have a total for those comments,
i can see that i only have Low Volume comment for that particular date. the rest are zero value. why is that the formula showing Soon to Open and Sale Event?

-karlo
0

Author Comment

ID: 40381312
maybe because of this line :
// Output the first 3 comments (which are now sorted from highest count to lowest)
comments [ 1 ]+ ", "  + comments [ 2 ]+ ", "  + comments [ 3 ]

since comment 2  is Soon to Open
comment 3 is Sale Event
formula will show those even though theres no value?
0

Author Comment

ID: 40381402
i still has this error. i have proc_days where count the days processed for an order to ship out per area;
and placed it to details section:
e.g
order              proc_days                area
1                            20                           nsm
2                            1                            vis

so before your code i place the code:
1st formula if area=nsm and (proc_days[20 to 40]) then
yourcode here..

i did create a formula two has get all the comment from proc_days 1-10 days
2nd formula if area=vis and (proc_days[1 to 10]) then
yourcode here..

this produce correct output
but the first shows only blank area even though i check on details section and
found that there's a line that has this data:

order              proc_days                area
1                            20                           nsm

and when i try tragging the @area and @proc_days it shows this output

order              proc_days                area
2                            1                            vis

and btw i placed 1st and 2nd formula in report footer.

-karlo
0

Author Comment

ID: 40381560
i do have this formula @proc_days2
IF {ORDERS.STATUS} IN ['92','95'] THEN
//(if {STORER.SUSR2} = 'NSM' THEN
(if{@proc days}>0 then
{@proc days} else
0)

and @proc_day -computes days processed from order to ship date
WhilePrintingRecords;
DateVar Array Holidays;
Local DatetimeVar Start := {@Adjusted Item Create Timestamp};
Local DatetimeVar End := {@zzLast Edit Date};
local numbervar days;
Local NumberVar i;
For i := 1 to Count(Holidays) do
(
if DayOfWeek (Holidays[l]) in 2 to 7 and Holidays[l] in [Start to End] then
days := days + 1;
);
Datediff("d", Start, End) - Datediff("ww", Start, End, crSunday) - days;

now i used your formula and it look like this:
whileprintINGrecords;
IF {ORDERS.STATUS} IN ['92','95'] THEN
(if {STORER.SUSR2} = 'FSS-NCR' AND ({@proc_days2} IN [4 to 7])  THEN
(Local NumberVar i;
Local NumberVar j;
Local NumberVar k;

Local NumberVar tmp_num;
Local NumberVar Array num_list;

Local StringVar tmp_str;

// Redim the arrays to hold the required number of values
Redim num_list [ 7 ];

// Put the counts in the array
num_list := [ {@FINAL_LOW_VOLUME}, {@FINAL_SOON_TO_OPEN}, {@FINAL_SALE_EVENT}, {@FINAL_SKU_PROBLEM}, {@FINAL_STORE_BARCODE}, {@FINAL_HOLD_STORE}, {@FINAL_MANUAL_GENERATE} ];

// Put the comments in the array, in the same order as the counts above
comments := [ "Low Volume", "Soon to Open", "Sale Event", "SKU Concern",
"Store bar code request", "Hold Store", "Manual generate OIS" ];

j := UBound (num_list);

// Sort the totals, from highest to lowest, and sort the comments along with them
if j > 1 then
for i := 1 to j - 1 do
for k := i + 1 to j do
if num_list [l] < num_list [k] then
(
tmp_num := num_list[l];
num_list[l] := num_list[k];
num_list[k] := tmp_num;

);

// Output the first 3 comments (which are now sorted from highest count to lowest)
comments [ 1 ]+ ", "  + comments [ 2 ]+ ", "  + comments [ 3 ])

)

The problem is this formula checks only on the last data in details.
even though i got other STORER.SUSR2, it only checks for last data in details section
example formula
IF {ORDERS.STATUS} IN ['92','95'] THEN
(if {STORER.SUSR2} = 'FSS-LUZ' AND ({@proc_days2} IN [4 to 7])  THEN
statement when true.

sample date in detail section

order       storer.susr2             days proc
1                     fss-luz                       7
2                      fss-ncr                      7

then the formula you gave does show the record for fss-ncr
even though i create another formula with same code but different susr2 for fss-luz,
it only shows blank
0

Author Comment

ID: 40381650
but when i drag n drop your formula to details. it shows correct data.
0

LVL 34

Expert Comment

ID: 40381803
To address one of your earlier questions, yes, the formula will always show 3 comments, even if the counts for some of those are 0.  I just didn't think about that, and was assuming that you would always have at least 3 different comments.

You can handle that by changing

comments [ 1 ]+ ", "  + comments [ 2 ]+ ", "  + comments [ 3 ]

to

(if num_list [ 2 ] > 0 then ", "  + comments [ 2 ] else "") +
(if num_list [ 3 ] > 0 then ", "  + comments [ 3 ] else "")

As for not showing other comments, this one formula is supposed to handle _all_ of the comments.  The idea was not to have a separate copy of the formula for each comment, but to put this formula in a footer (I was assuming the report footer), and it would look at the final counts for _all_ of the comments, sort the counts and comments, and then output the top 3 comments.

What are you putting in num_list?  For example, what is in @FINAL_LOW_VOLUME?

In an earlier post, you had formulas named @sum1, @sum2, etc.  Given the "sum" in the name, I assumed that those were the total counts for each comment.  That's what you want to put in num_list.

Going way back to my first post, let's say that you had a formula named sku_problem that looked like this:
IF {ORDERS.STATUS}IN ['95','92'] THEN
(if {@START_REMARKS} = "SY"    then
1 else 0)
ELSE 0

That formula produces a 1 when certain conditions are met.  Otherwise, it produces a 0.

If you use Sum ({@sku_problem}), that produces a total count of the records in the report where those conditions were met.

That Sum is what you'd put in num_list in my formula:

num_list := [ <other counts> ..., Sum ({@sku_problem}), ... <other counts> ];

The "other counts" would be Sum functions for similar formulas for the other comments.

Hopefully now you better understand what I was trying to do in that formula.

James
0

Author Comment

ID: 40383401
Hi Sir James, thank you for assiting me to finish this task. i really appreciate it :)
now
What are you putting in num_list?  For example, what is in @FINAL_LOW_VOLUME?
@FINAL_LOW_VOLUME = sum1. i just used sum1 to describe that i have formula that sum up all the "1"(true) values when lets say @low_volume(counter,just like @sku_problem) is true.

i would like to cut those comments for different criteria. like

days_processed = proc_days2
area = orders.susr2

IF {ORDERS.STATUS} IN ['92','95'] THEN
(if {STORER.SUSR2} = 'FSS-NCR' AND ({@proc_days2} IN [4 to 7])  THEN

so i will show the top three comments is susr2 = fss-ncr and proc_days2 in 4-7 days
your formula + mine worked, but only checks for the last record of data.
right now the formula shows always on sus42=vis maybe because of ascending order.
i wonder if i can show those three top comments for different susr2 and proc_days2

susr2 are fss-ncr,fss-luz,fss-vis,fss-min,ntw,nns,nsm,min,vis
proc_days2 are 4-7days 8-14days and 15-above days

please dont get tired helping me. thanks
0

LVL 100

Expert Comment

ID: 40383486
How are you displaying the data?

Do you want all the "Highest" comments reported on in the report footer?

If you want all in the report footer you probably need to have an array for each day range.

mlmcc
0

Author Comment

ID: 40383555
Do you want all the "Highest" comments reported on in the report footer?
yes sir.

If you want all in the report footer you probably need to have an array for each day range.
0

LVL 34

Accepted Solution

James0628 earned 500 total points
ID: 40383611
So, you want to add another set of conditions to break down the counts produced by the original conditions?

For example, you have @sku_problem that produces a 1 when certain conditions are true.  But then you only want to count those records where @sku_problem is 1, and SUSR2 is 'FSS-NCR' and @proc_days2 is 4 - 7 ?

If so, the problem with trying to add the new tests to my formula is that the totals have already been calculated, and when you add the new tests, they only see the values in the current record (which will be the last record in the report, if you put my formula in the report footer).

If you want to add some more conditions to the count, the obvious idea would be to put them in other formulas, and do a Sum on the new formulas instead (those would be the values that you put in num_list).

For example, to add the new tests to the @sku_problem count, as described earlier, create a new formula like this:

IF {ORDERS.STATUS} IN ['92','95'] THEN
if {STORER.SUSR2} = 'FSS-NCR' AND ({@proc_days2} IN [4 to 7])  THEN
{@sku_problem}
ELSE
0
ELSE
0

That formula will produce a 1 if SUSR2 is 'FSS-NCR' and @proc_days2 is 4 - 7, _and_ the conditions in @sku_problem are met.  A Sum on that formula will give you a count of the records where all of those conditions were met.  You'd put that Sum in the num_list array.

That's kind of a "brute force" approach, without trying to refine things at all.  For example, the new tests include STATUS IN ['92','95'], but that test is presumably also included in @sku_problem, so you don't really need it in the formula above.

If you have a number of different "categories", like counts for SUSR2 = 'FSS-NCR' and @proc_days2 in 4 - 7, and counts for SUSR2 = 'FSS-NCR' and @proc_days2 in 8 - 15, and counts for SUSR2 = something else and @proc_days2 in 1 - 5, you would have a separate set of formulas like the one above for each "category", and a separate copy of my formula for each "category" that would do Sum's on the corresponding set of formulas.

James
0

LVL 100

Expert Comment

ID: 40384180
James method will work.  However it may be easier to maintain and modify if you add a subreport to the report footer to do the actual calculations and display.  In that way you won't need an array for each grouping.

How is the main report setup?

Do you have it grouped on the date ranges?
If so you might be able to do it with a single formula and an output string.

mlmcc
0

Author Comment

ID: 40385745
here's the actual report sir. please do check it. thanks
OUTPUT-STATUSTRIAL14.rpt
0

Author Comment

ID: 40385872
Hi sir james
@sku_problem 2
IF {ORDERS.STATUS} IN ['92','95'] THEN
if {STORER.SUSR2} = 'FSS-NCR' AND ({@proc_days2} IN [4 to 7])  THEN
{@sku_problem}
ELSE
0
ELSE
0

That formula will produce a 1 if SUSR2 is 'FSS-NCR' and @proc_days2 is 4 - 7, _and_ the conditions in @sku_problem are met.  A Sum on that formula will give you a count of the records where all of those conditions were met.  You'd put that Sum in the num_list array.

-first statement has no error and it gives 1 when sku_problem2 is true and sku_problem is true.problem is when i try sum({sku_problem 2}) it says field cannot be summarized.
0

LVL 34

Expert Comment

ID: 40385910
It looks like the problem is the inclusion of WhilePrintingRecords in proc_days2, and proc_days (since proc_days2 references proc_days).  It seems that you can't do a summary on a formula that includes WhilePrintingRecords, probably because the summaries are calculated before the printing pass.

Try removing that from proc_days and proc_days2 and see if you can do a Sum.  FWIW, there doesn't seem to be much point in including it in those formulas, since the formulas referenced in proc_days ({@Adjusted Item Create Timestamp} and {@zzLast Edit Date}) don't include it.

James
0

Author Comment

ID: 40385917
hi sir james. tried removing those whileprintingrecords ans summarizing @sku problem2
still "field cannot be summarized"
0

LVL 34

Expert Comment

ID: 40386292
Did you remove it from proc_days2 and proc_days?

If so, can you post the report with the formula that's giving you that error, so that I can look at it?  The report that you posted earlier didn't have the sku_problem2 formula, so I had to guess at the problem based on what was in that report and what you had posted here.

James
0

Author Comment

ID: 40387733
hi sir james sorry for bugging. i created sku_problem2 for your suggested formula
IF {ORDERS.STATUS} IN ['92','95'] THEN
if {STORER.SUSR2} = 'FSS-NCR' AND ({@proc_days2} IN [4 to 7])  THEN
{@sku_problem}
ELSE
0
ELSE
0

and yes i did remove whileprintingrecords in both proc_days and proc_days2
i attached the report here please do check
0

Author Comment

ID: 40387741
0

LVL 34

Expert Comment

ID: 40388633
It worked for me.  I d/l'ed that report, created a formula named sku_problem2 and copied and pasted the formula from your post # 40387733.  Then I created another formula and put  Sum ({@sku_problem2}) in it.  At first, that gave me the "cannot be summarized" error, but after I removed WhilePrintingRecords from proc_days and proc_days2, the error went away and that formula gave me a total on the report.  The total was 0, but I'm guessing that the data saved with that report doesn't include any records that acually meet those conditions.  The main thing is, I didn't get an error, and I did get a total.

If you're still getting the error, I have to assume that you didn't actually remove WhilePrintingRecords from both proc_days and proc_days2.  Either that, or there is some difference between the report that you posted and the report that you're working on.

James
0

Author Comment

ID: 40393718
Thank you sir james as well as sir mlmcc!!
0

LVL 34

Expert Comment

ID: 40394176
So, you got it to work?  I'm guessing that you still had WhilePrintingRecords in one of the formulas.  IAC, you're welcome.  I'm glad we were finally able to get it working.

James
0

Author Comment

ID: 40395987
yes sir! report is now working fine. just added condition to select comment [ 1 ] if it has value. sometimes there's no comment  at all for some period. Thanks again!!
0

LVL 34

Expert Comment

ID: 40397134
You're welcome again.  :-)

James
0

Author Comment

ID: 40400977
0

Author Comment

ID: 40401223
Hi Sir. I were able to reduce 3 proc_days2 when an order falls to this condition
Local NumberVar reduction;
if {RECEIPT.SUPPLIERCODE} = 'G001' then
reduction := 3;

by applying the variable reduction to:
(if {STORER.SUSR2} = 'FSS-NCR' AND (({@proc_days2} - reduction) IN [8 to 14])
then count_14FSSNCR := count_14FSSNCR + 1 else

However, How can I add the the reduced order to its correct category
example

order       proc_days2              0-3             4-7           8-14        15above
1                   3                              1                0                  0              0
2                    6                              0                 1                0                0

total   by category                      1                 1                 0                  0

by that example,
since order #2 has proc_days2 of 6days, applying the said formula it will reduce 3 to proc_days2 so final proc_days2 is would be 6-3=3 . for now
the total proc_days per category are included above,
what it should be is like this

total   by category                      2                 0                 0                  0

+1 to 0-3 since we reduced order #2 by 3 since it reflects true to condition above. please help. thanks
0

LVL 34

Expert Comment

ID: 40401799
I would expect a change like the one that you described to change the counts, but without seeing the whole formula, with your changes, and how you're using it, there's really no telling.

You say that you want to reduce the result from proc_days2 by 3, if SUPPLIERCODE is 'G001'.  Is that a global adjustment that applies to every G001 record, or only under certain conditions, like when STORER.SUSR2 is 'FSS-NCR' ?

If it applies to every record, and you _only_ need to see the adjusted value, not the original value, then how about changing proc_days?  Then, proc_days2 would get the adjusted value from proc_days, and every formula that uses proc_days2 would automatically get the adjusted value.

And, actually, now that I look at it, proc_days already appears to be subtracting 3 days if SUPPLIERCODE is 'G001'.  Do you want to subtract _another_ 3 days?

James
0

Author Comment

ID: 40403391
You say that you want to reduce the result from proc_days2 by 3, if SUPPLIERCODE is 'G001'.  Is that a global adjustment that applies to every G001 record, or only under certain conditions, like when STORER.SUSR2 is 'FSS-NCR' ?

---yes sir this is a global condition. already done this. the problem is. I cant add Orders that has been reduced and ranked down into lower category. sample

if an order has proc_days2 of 6 days and his receipt.suppliercode = 'G001'
so it should be 6-3 = 3. total proc_days2 will be 3. so the counter for 4-7 category wont give this data a "1" since his proc_days2 is lower than "4" .

What i want is that order should be added to 0-3 category since it did return true for our condition.

what is happening is that for example i have 5 orders

order    proc_days2     reduced(proc_days2 - 3)     0-3    4-7     8-14     15above
1                   0                       -3                                      1         0         0            0
2                   3                         0                                      1         0         0             0
3                   4                          1                                     0         0          0            0
4                   3                           0                                    1         0          0            0
5                    7                           4                                   0          0           0            0

lets assume all orders has suppliercode of 'g001' that is why reduced has been executed. notice the order # 3 and 5. its final proc_days2 has been reduced by 3.that is why their category got rank down. so that order must be added on their correct category. for order# 3 it should fall to 0-3 category. so it should be 0-3 category + 1
for order # 5 it should fall to 4-7 category. so 4-7 category + 1.
right now report just showing zero if the condition above is true, but not adding those ranked down orders to their correct category.
sum of orders=5 but the sum of orders to all category is 3 only.

what i needed is  like

order    proc_days2   reduction(proc_days2 - 3)     0-3    4-7   8-14  15above
1                   0                       -3                                        1       0      0          0
2                   3                         0                                       1       0      0          0
3                   4                          1                                      1       0      0          0
4                   3                           0                                     1       0      0          0
5                    7                           4                                    0       1      0          0

total orders must be equal to sum of orders to all category

0

LVL 34

Expert Comment

ID: 40403870
OK, can you give me a hint here?  Your report has literally _hundreds_ of formulas.  Exactly which ones should I be looking at?

FWIW, I was able to dig through everything and find some formulas and data that I could test with, and it worked as expected.  When proc_days decreased the number of days, the count moved into the new category (the days was decreased from 9 to 6, and the count moved from 8-14 to 4-7).  So, the idea appears to work, which implies that the problem is in your implementation, but without knowing exactly which formulas you're having problems with and where/how you're using them, I can't say more than that.

I do have a number of general observations, some of which might be relevant.

Do you actually need those hundreds of formulas, or are some maybe leftovers from different things you've tried to get these counts, or even from another report?  It makes it really difficult to try to track things down and follow the "process", especially for someone like me, who doesn't know which of those formulas you really need and what they're for.  I believe they are also slowing down the report generation -- A lot.  If there are a lot of formulas that you don't actually need, I would start by trying to clean up the report and delete those formulas.  If you do actually need most of them, maybe they could be "simplified" somehow, so there aren't so many.

Some formulas include WhilePrintingRecords and some don't.  I don't know if that's actually causing problems, but if any of those formulas "interact" (formulaA references formulaB, or one formula uses a variable that's set by another formula), then it's almost certainly not good.  It could be affecting your counts.  I really have no idea.

Even worse (or at least stranger), some formulas have the WhilePrintingRecords inside an IF statement.  I didn't even realize that that was possible.  I don't know if CR will actually evaluate a formula like that during different passes, based on the IF condition, but even if it won't, it's confusing to have it like that.

You're doing a summary on some formulas, so those formulas _can't_ include WhilePrintingRecords.  It's possible that you actually need it in some of the other formulas, but I'd be inclined to remove it from all of the formulas, and see what happens.  If necessary, you can always add it back to the formulas that need it (but not inside an IF statement).

Some of your fields are null.  At least in the data saved with the report.  That can cause any number of problems.  CR traditionally doesn't handle nulls well in formulas.  From the CR 10 Help:

In general, when Crystal Reports encounters a null valued field in a formula, it immediately stops evaluating the formula and produces no value. If you want to handle null field values in your formula, you must explicitly do so using one of the special functions designed for handling them: IsNull, PreviousIsNull or NextIsNull.

The nulls _could_ be causing some problems for your counts.  They didn't prevent me from getting counts in the tests that I did, but they might cause problems under other circumstances.  IAC, you really should handle them.

If you don't actually need to see the null values in the report, go to File > Report Options and check the "Convert Database NULL Values to Default" option.  Then a null string field will be seen as en empty string, a null numeric field will be seen as 0, etc.  That will handle the nulls for pretty much any formula, except the record selection formula (where any tests may be passed to the db server, instead of being evaluated by CR).

James
0

Author Comment

ID: 40409757
Hi Sir james. report is working fine now. and you are right. null values are the ones
preventing the report to produce the right output. however, the report is too slow to load. maybe because of those formula. I deleted some of the formula that i used for testing,still the report is so slow to load. how can i fix that sir?

-karlo
0

LVL 34

Expert Comment

ID: 40410346
If the formulas are slowing it down (and that's just a guess), then it's really hard for me to say.  I don't want to try to analyze your hundreds of formulas, with many formulas referencing other formulas, to try to figure out what all of them are doing, how they're connected, and which ones you may not need.

I would start by deleting any formulas that you don't need.  Not just remove the formula from the report, but delete the formulas completely (just to make sure that CR isn't still evaluating them).  If you're not sure which formulas you really need, now would be a good time to figure it out.  It's something that you should know.

If you're actually using most of those formulas, then it might be possible to "simplify" them in some way, so that you don't need so many formulas.  I don't understand what all of those formulas are doing well enough to offer much in the way of suggestions, but, for example:

You have proc_days and proc_days2.  As I recall, proc_days2 just takes the result from proc_days and modifies it slightly.  Many other formulas reference proc_days2, and every time they do, CR _may_ be doing some extra work, because proc_days2 references another formula (proc_days).  Likewise, I believe proc_days referenced a couple of other formulas.

As I recall, you didn't seem to be using proc_days anywhere else, so you weren't using the unmodified result anywhere.  In that case, you could do everything in one formula.  Since you're using proc_days2 in other formulas, I would put everything in proc_days2 and delete proc_days.  And I don't think the formulas referenced in proc_days were doing anything complicated, so I'd put the code from them in proc_days2 too (and delete those other formulas), so that proc_days2 was self-contained and didn't reference any other formulas.

I think you had a number of other formulas that were more or less just one line/statement.  Wherever you use those formulas, you could just use that line/statement instead, and get rid of the formula.

James
0

## Featured Post

Hot fix for .Net Crystal Reports 10.2.3600.0 to fix problems with sub reports running on 64 bit operating systems ISSUE: Reports which contain subreports fail with error "Missing Parameter Value" DEPLOYMENT SERVER OS: Windows 2008 with 64 biâ€¦