How to join string values inside a report?

[Roland]

I want to join or concatenate field strings from the detail section of my report and then present the result on the group header. The report functions I found are only applicable to numeric data (Total, Count, Average, First, Last, Minimum, Maximum, etc). It is just like the function "Total" but instead of getting the total value (10,20,30, result = 60), I want to simply join the strings (result = "Items: 10, 20, 30"). Is this possible? Any help will be appreciated.

[Stan Mathews]

Re: How to join string values inside a report?

10,20,30, result = 60

These are not strings, they are numeric values.

You convert numeric values to strings with the str() function. You concatenate strings with the plus sign.

To incorporate the strings in the header or footer you will probably want to use *collect().

[Roland]

Re: How to join string values inside a report?

Thanks Stan. I cannot however find the *collect() function. I am using Alpha Five Version 10.5. Will this work for this version?

The problem is not really the conversion nor the concatenation as we can use "str" and "+". The problem is how can I extract the items from the detail view of the report. Please see illustration below.

Item Classes: PW20, AS35, MN33 (This is the header section of the group. I need to get each unique classes from the detail section below.)
Material : Wood

Id Name Material Color Class (This is the detail section)
1 Inset Panel Wood Red PW20
2 Plane Panel Wood Blue AS35
3 Raised Panel Wood Gray PW20
4 Lite Panel Wood Green MN33

[Stan Mathews]

Re: How to join string values inside a report?

Probably not in the autocomplete yet.

http://wiki.alphasoftware.com/*collect+Function

Version 10.5 Build 3221_3583 and later

[Roland]

Re: How to join string values inside a report?

Thanks again Stan. I should have consulted the online help not the local help.
Anyway, the *collect function works. The only drawback is, it should be put on the footer section. If it is put on the header, it cannot collect all items.

[Stan Mathews]

Re: How to join string values inside a report?

You can derive the same result using table.external_record_content_get() wrapped in strtran() in a calculated field, but it can be a bit tedious to construct. The calculated field can be placed anywhere you like. Try it in the interactive window first.

This example for AlphaSports....

tst = table.external_record_content_get("customer","alltrim(lastname)","lastname","bill_state_region = 'MA' .and. unique_key_value()")

? tst
= Cario
Copen
Dawson
Fuller
Graham
Hite
Jones
Kelley
King
McDonald
McMiggan
Mederos
Peabody
Queen
Richardson
Smith
Smithson
Wendell
Wong

? strtran(tst,crlf(),", ")
= "Cario, Copen, Dawson, Fuller, Graham, Hite, Jones, Kelley, King, McDonald, McMiggan, Mederos, Peabody, Queen, Richardson, Smith, Smithson, Wendell, Wong"

If your report has groups you have to get a little more out of your filter. To have the calculated field in the group header grouped on bill_state_region you would want the filter expression

"bill_state_region = "+quote(bill_state_region)+" .and. unique_key_value()")