Help with formula 2

[jmriddic]

Hi all,

The following formula is supposed to add up the number of times the following fields have a certain value. So if two of the fields have the value then the value of the field. I am not even getting a zero when testing this and I do have records that have the value in question.

numbervar x:=0;
IF{v_cc_data.cc_segment_number}="2" then x=x+1;
IF{v_cc_data.cc_segment_number2}="2" then x=x+1;
IF{v_cc_data.cc_segment_number3}="2" then x=x+1;
IF{v_cc_data.cc_segment_number4}="2" then x=x+1;
x

 

[Turkbear]

Hi,
Where is the formula in the layout..Rememeber that the formula's evaluation point is critical..

Try this method, instead ( assuming all the fields are in each record):

Set 5 variables :
@seg1
IF{v_cc_data.cc_segment_number}="2" then 1 else 0
@seg2
IF{v_cc_data.cc_segment_number2}="2" then 1 else 0
@seg3
IF{v_cc_data.cc_segment_number3}="2" then 1 else 0
@seg4
IF{v_cc_data.cc_segment_number4}="2" then 1 else 0
@X
@seg1 + @seg2 + @seg3 + @seg4


insert them in the details section ( you can supress the display of seg1-seg4 and just show the value of X)





To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[lbass]

My guess is that you have some nulls. Either go to file->report options-> and check "Convert nulls to default values" or replace each field with a formula that has a null check, e.g.:

if isnull({v_cc_data.cc_segment_number}) then 0 else
{v_cc_data.cc_segment_number}

...and then use the formulas in your earlier formula.

-LB

 

[jmriddic]

Hi,

Would we not have to declare them first and how is X populated as well as the other fields. Thanks.

 

[jmriddic]

I tried this and now it expects a number after the second if
numbervar x:=0;
if isnull({v_cc_data.cc_segment_number})
then 0
else if{v_cc_data.cc_segment_number}="2" then x=x+1;

 

[lbass]

Sorry, I didn't notice that you were working with a string field, but if you want to do this all in one formula, then use the following:

numbervar x:=0;
stringvar seg1 := if isnull({v_cc_data.cc_segment_number})
then "0" else {v_cc_data.cc_segment_number};
stringvar seg2 := if isnull({v_cc_data.cc_segment_number2})
then "0" else {v_cc_data.cc_segment_number};
stringvar seg3 := if isnull({v_cc_data.cc_segment_number3})
then "0" else {v_cc_data.cc_segment_number};
stringvar seg4 := if isnull({v_cc_data.cc_segment_number4})
then "0" else {v_cc_data.cc_segment_number};
if seg1 ="2" then x=x+1;
if seg2 ="2" then x=x+1;
if seg3 ="2" then x=x+1;
if seg4 ="2" then x=x+1;
x

-LB

 

[tseaman]

When assigning a value to a variable you must use ":=" operator not "=". "=" by itself is performing a logical comparison that the variable on the left equals the value on the right. ":=" actually sets the variable on the left to the value on the right.

numbervar x:=0;
IF{v_cc_data.cc_segment_number}="2" then x:=x+1;
IF{v_cc_data.cc_segment_number2}="2" then x:=x+1;
IF{v_cc_data.cc_segment_number3}="2" then x:=x+1;
IF{v_cc_data.cc_segment_number4}="2" then x:=x+1;
x

 

[jmriddic]

Hi LB,

Thanks for the help. its definitely working now. Might look u guys for some help on some other formulas in the report.

 

[jmriddic]

Hi LB and all,

Your formula for me work. Thanks. Maybe you can help me with a related formula. The example I brought up before use the value 2. I have have other categories using the values 1-8 and the formula you suggested help me calcualte the number of occurences of that category in the record. These categories are used as the columns in my table on the report and I have a couple of occurences(formulas) of Min and Max values I need to calcualte for each category. In each of the occurences I have to consider the following fields
post_sten1 through post_sten4 for one set of Min/Max values
and pre_sten1 through pre_sten4 for the other.

I believe I will have to the segment_number fields used in my first formula as part of the formula so the min/max are pulled under the proper category. Any suggestions?

 

[lbass]

tseaman is correct about the colons. In my test formula here, I used x := x + 1, but then I accidentally omitted them in my post because of copying and pasting your original formula. Sorry about that.

I don't understand what you are looking for for the min/max values. Please provide some sample data and a more detailed explanation of what you mean.

-LB

 

[jmriddic]

Hi LB,

Lets see if I can explain. I have basically artery blockage(its for a cardiovas system) categories 1-8 which the formula you suggested to me help calculate the number of occurences of the category over 4 screens which explains the segment numbers 1-4 which hold the value of the category. I have two different fields I need the min/max tore_sten and post_sten. They also can be documented up to 4 times over the 4 screens which explains the different occurences when I said pre_sten1,pre_sten2-etc. in essence its the same type of data in each occurence but the occurence in the report needs tp fall under the actual category. So if Joe was documented with category 2 three times on the record and had a presten of 10,20, and 30 and a category 1 once with a presten of 10 then it should show the following on the report:

1 2 3 4
Min-10 Min-10 Min-
Max-10 Max-30 Max-

Hope this helps or I can explain some more tomorrow.

 

[lbass]

Could you please show some detail level data with column headers that explain what fields they represent? I can't see how the pre-sten and post-sten fields relate to the category fields. Also, are these values really within the same detail row?

-LB

 

[jmriddic]

Hi LB,

It would probably work if I could show how the report is laid out as far as the table goes but I cannot attach the report. Yes, in this case the category(ie the segment_number field whehter be number,number2,number3 or number 4) would have to be part of the formula since they group the values for the field by the category in the table. This is why it has taken me such a long time to figure out what the user wanted as far as this part of the report goes but that is how they organize their info.

 

[lbass]

Sorry, can't help you without seeing some sample detail rows.

-LB

 

[jmriddic]

Hi LB,

Are you meaning are they part of the same record? yes, they are.

Name seg#1 seg#2 seg#3 pre_sten1 pre_sten2 pre_sten3
Joe Blow 1 2 2 75 85 90
John Dow 1 3 5 80 84 82

And visualize post_sten being on the same row. On the detail they organized per case. But in the report which is totally on the report trailer she wanted to organize the min/max per the segnumber value which is more reflective of how each screen was set up because the first field is the segment number and any fields filled in(pre_sten and post_sten %,for example) would apply to the category value put in.

 

[lbass]

Using your sample data, then, the results would like the following?

1 2 3 4 5
min 75 85 84 0 82
max 80 90 84 0 82


If so, then create formulas like the following:

//{@accum} to be placed in the detail section:
whileprintingrecords;
stringvar array b := [{@seg1},{@seg2},{@seg3},{@seg4}];
numbervar array d := [{@presten1},{@presten2},{@presten3},{@presten4}];
numbervar array f;
numbervar c;
numbervar e;
numbervar array g;
numbervar h;

for c := 1 to ubound(b) do(
if b[c] = "1" then(
e := e + 1;
if e <=1000 then
(redim preserve f[ubound(f)+1];
f[e] := d[c]));

if b[c] = "2" then(
h := h + 1;
if h <=1000 then
(redim preserve g[ubound(g)+1];
g[h] := d[c])));

Note that arrays b and d should contain all segments and all prestens--there are four each? Each formula in the segment array and in the presten array should contain checks for nulls (setting them to "0" for segment or 0 for prestens). You would have to do similar formulas for poststens, which I am not showing here. I am also only showing the formulas for segment values of "1" and "2". You would have to carry out the logic for values "3" to "8", which would involve adding to the above formula and then creating separate display formulas for each value, as in the following:

//{@displminmax1} to be placed in the report footer:
whileprintingrecords;
numbervar array f;
numbervar e;
numbervar min;
numbervar max;

for e := 1 to ubound(f)-1 do(
if f[e] = f[1] or
f[e] < min then
min := f[e] else
min := min;
if f[e] > max then
max := f[e] else
max := max);

"Minimum = "+totext(min,0,"")+chr(13)+
"Maximum = "+totext(max,0,"");

//{@displayminmax2}:
whileprintingrecords;
numbervar array g;
numbervar h;
numbervar min2;
numbervar max2;

for h := 1 to ubound(g)-1 do(
if g[h] = g[1] or
g[h] < min2 then
min2 := g[h] else
min2 := min2;
if g[h] > max2 then
max2 := g[h] else
max2 := max2);

"Minimum = "+totext(min2,0,"")+chr(13)+
"Maximum = "+totext(max2,0,"");

Format these formulas to "can grow".

-LB