CR 8.5: Concatenate report details into a string

[kamm]

Hi

I am fairly new to this report writing thing and was wondering if anyone could help...

I am trying to write a formula which will loop through all the records in the report for a

particular field (called ls_percentage) and concatenate these values into a string. If one of these

values is null I want to replace it with a value from another column (called ls_amount) in the

database but this value must belong to the same row (record) as the null value for ls_percentage.

The first problem I came across was that I was getting the right number of values but they were all

the same record. That is the first value had been repeated for the number of fields present. The

second problem is that I could not see a way of replacing one value with another value from the same

record or row (but a different column).

The formula as it stands at the moment is:
-----------------------------------------------------------------------------------

StringVar str := "";
NumberVar i:= 1;

For i:= 1 To Count ({ls.ls_percentage}) do
(
if IsNull({ls.ls_percentage}) then
str := str + "%: " + Cstr({ls.ls_percentage}) + "%" + chr(13)
else
str := str + "Amt: " + CStr({ls.ls_amount}) + chr(13)
);
str

-----------------------------------------------------------------------------------

Thanks

 

[rogar]

Omit the For loop. You need two formulas. Put the first formula on the detail line:
StringVar str;
if IsNull({ls.ls_percentage}) then
str := str + "%: " + Cstr({ls.ls_percentage}) + "%" + chr(13)
else
str := str + "Amt: " + CStr({ls.ls_amount}) + chr(13)
;
str

The formula will be executed with each record, using the data in that record and incrementing your string. You can suppress the formula field to hide it while it increments.

The second formula goes in the footer to display the results:
StringVar str

If you want to repeat this process, e.g. by groups, you will need a third formula in the group header to re-initialize the string:
StringVar str := "";

Be aware that a CR string cannot be longer than 255 chars.

 

[Ngolem]

Rogar is right as to how to handle the formula...by placing it in the detail section you will get the effect of cycling through the records...what you were doing was repeating the same value over and over again.

So follow his advice but I would like to offer comments on the main formula itself

*********************************************
"particular field (called ls_percentage) and concatenate these values into a string. If one of these

values is null I want to replace it with a value from another column (called ls_amount) in the

database but this value must belong to the same row (record) as the null value for ls_percentage."

StringVar str;
if IsNull({ls.ls_percentage}) then
str := str + "%: " + Cstr({ls.ls_percentage}) + "%" + chr(13)
else
str := str + "Amt: " + CStr({ls.ls_amount}) + chr(13)
;
str

************************************************

This formula does the opposite to what you want...it is only adding the Percentage when it is null...not when it isn't.

ALSO there is no test for 255 characters which you should
have....since the formula won't work if this number is exceeded...so your report must have some provision for this possiblity (at least a warning to the viewer that data is missing or starting a new string)

this is what your formual should look like I think

@ConcatenateDetails (suppressed in the detail section)

WhilePrintingRecords;
StringVar Result;
StringVar Warning;

if Not IsNull({ls.ls_percentage}) then
(
if length(Cstr({ls.ls_percentage}))+length(Result) < 255 then
Warning := &quot;Warning: some data is missing&quot;
else
Result := Result + &quot;%: &quot; + Cstr({ls.ls_percentage})
+ &quot;%&quot; + chr(13);
)
else
if length(CStr({ls.ls_amount}))+length(Result) < 255 then
Warning := &quot;Warning: some data is missing&quot;
else
Result := Result + &quot;Amt: &quot; + CStr({ls.ls_amount}) +
chr(13);
);

// do not display the value of result1 here since you will just see repeated updates

In the footer you would place the display formula

@displayResult

WhilePrintingRecords;
StringVar Result ;

Result ;

*****************************************

in the page footer you could place the warning display

@DisplayWarning

WhilePrintingRecords;
StringVar Warning;

Warning; //since warning is initialized to NULL nothing
// will show unless there is over 255 characters

In report header the intialization formula would be

@initialization

WhilePrintingRecords;
StringVar Result := &quot;&quot;;
StringVar Warning := &quot;&quot;;

if the result calc is to be repeated this would be moved to the Group header

@initialization

WhilePrintingRecords;
if not inrepeatedgroupheader then
(
StringVar Result := &quot;&quot;;
StringVar Warning := &quot;&quot;;
);

Jim Broadbent

 

[kamm]

Thanks rogar and Ngolem, that worked a treat.
Your help was invaluable