Save hours in development time and avoid common mistakes by learning the best practices to use for JavaScript.

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:

left({ORDERS.B_ADDRESS3},2)

where {ORDERS.B_ADDRESS3} is the database field where we store comments

sample comments are;

Low Volume

Soon to Open

Sale Event

System SKU Problem

Store bar code request

Hold Store

Manual generate OIS

i used left({ORDERS.B_ADDRESS3},2) to differentiate comments and summarize it as numbers.

please help me guys. thanks

-karlo

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:

left({ORDERS.B_ADDRESS3},2

where {ORDERS.B_ADDRESS3} is the database field where we store comments

sample comments are;

Low Volume

Soon to Open

Sale Event

System SKU Problem

Store bar code request

Hold Store

Manual generate OIS

i used left({ORDERS.B_ADDRESS3},2

please help me guys. thanks

-karlo

Experts Exchange Solution brought to you by

Enjoy your complimentary solution view.

Get this solution by purchasing an Individual license!
Start your 7-day free trial.

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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.

here's the problem:

i need to show the two highest comments for that four categories

E.g

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.

please help

-karlo

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

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

-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

days processed top two comments

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.

If you have 16 orders for a particular range

6 comments on low volume

4 comments on soon to open

3 comments on SKU

3 comments on Store

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

6 comments on low volume

4 comments on soon to open

3 comments on SKU

3 comments on Store

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

- you are right about this.

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.

i prepared here attachment sir. please do check please

attachemnt-report2.xlsx

attachemnt-report2.xlsx

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

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?

please help.

thanks

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

yes if possible

is this hard to code?if yes, maybe i can pick one from the two

Hi sir mlmcc

yes, only seven

no, they are using standard comments.

but how can i show the second and third highest?

maybe if tie occurs, the report will pick one.

please help experts. thanks

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;
Local StringVar Array comments;
// Redim the arrays to hold the required number of values
Redim num_list [ 7 ];
Redim comments [ 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;
tmp_str := comments[i];
comments[i] := comments[k];
comments[k] := tmp_str
);
// Output the first 3 comments (which are now sorted from highest count to lowest)
comments [ 1 ] + ChrW (10) + comments [ 2 ] + ChrW (10) + comments [ 3 ]
```

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

// 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?

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

yourcode here..

i did create a formula two has get all the comment from proc_days 1-10 days

2nd formula

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:

1 20 nsm

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

2 1 vis

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

please help guys. thanks

-karlo

IF {ORDERS.STATUS} IN ['92','95'] THEN

//(if {STORER.SUSR2} = 'NSM' THEN

(if{@proc days}>0 then

{@proc days} else

0)

and @proc_day -

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:

@7_COMMENTS_FSS-NCR

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;

Local StringVar Array comments;

// Redim the arrays to hold the required number of values

Redim num_list [ 7 ];

Redim comments [ 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;

tmp_str := comments[l];

comments[l] := comments[k];

comments[k] := tmp_str

);

// 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

You can handle that by changing

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

to

comments [ 1 ] +

(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

now

@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.

your formula shows the top three comments for the whole data.

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

days_processed = proc_days2

area = orders.susr2

that is why i added on your formula these lines:

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,fs

proc_days2 are 4-7days 8-14days and 15-above days

please dont get tired helping me. thanks

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

yes sir.

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

- how can i do that sir? please help

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trialHow 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.

Can you upload the report?

mlmcc

here's the actual report sir. please do check it. thanks

OUTPUT-STATUSTRIAL14.rpt

OUTPUT-STATUSTRIAL14.rpt

@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.

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

still "field cannot be summarized"

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

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

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

James

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

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

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

How about a different approach?

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

---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

please help sir

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

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

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.

Those are some things you could start with.

James

DB Reporting Tools

From novice to tech pro — start learning today.

Experts Exchange Solution brought to you by

Enjoy your complimentary solution view.

Get this solution by purchasing an Individual license!
Start your 7-day free trial.

DO you only have 1 comment per store?

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

mlmcc