Displaying related records horizontally? 1

[Ruairi]

I have a report with 2 columns of data, a species name and a grade name. A species can have many related grades, like this:
SPECIES GRADE
------- -----
DF 1SM
DF 2SM
DF 3SM
DF 4SM

I would like to see the data like this instead:

SPECIES GRADES
------- ------
DF 1SM, 2SM, 3SM, 4SM

Is there a simple way to do this in CR? can i create a string variable, concatenate each grade to it in a hidden details section, and print it as shown in a group footer based on the change of species name? any help on this would be appreciated.
here is the querey used to get the data:

select 'SName'=S.name, 'GName'=G.name
from spgr
JOIN species AS S on (spgr.speciesrec = S.rec)
JOIN grade AS G on (spgr.graderec = G.rec)
order by S.name, spgr.rank

TIA Ruairi

Could your manufacturing facility benefit from real time process monitoring? Would you like your employees to be able to see up to the minute goal and actual production?
For innovative, low cost solutions check out my website.

http://www.plccontroltechnologies.com

 

[IdoMillet]

Sybase has a LIST aggregate function that does the
same thing in SQL.

Assuming your DBMS doesn't have such a function,
your best bet is to indeed creat a string variable
and concatenate each grade to it...
It's a relatively simple process.

Cheers,
- Ido

 

[Ruairi]

Ido,
Thanks for your quick response. I am trying the method of concatenating the strings into one string variable for display in the group footer. Here is what i started with

{formula @Grades, in the group header (group changes with species.name)}
Global StringVar Grades := "" ;

{formula @AccumGrades, in the detail section. Also placed in the group footer section for display.}
Global StringVar Grades := Grades + {spgr_ttx.GradeName} + " "

This returns an error about strings being > 256 characters. If i change the querey to only return one species worth of records it works. This tells me the @Grades formula is only being executed once. I was hoping that being in the group header it would execute once pre group, clearing the string. I then added WhileReadingRecords to the @Grades formula, hoping this would cause it to trigger every time a new group was started. This resulted in only the last grade for each species being listed in the group footer, telling me that it is being executed for each detail section (it's in the header section, not the detail). Can you tell me what i'm doing wrong, i haven't used global vars in CR before? Thanks Ruairi

Could your manufacturing facility benefit from real time process monitoring? Would you like your employees to be able to see up to the minute goal and actual production?
For innovative, low cost solutions check out my website.

http://www.plccontroltechnologies.com

 

[IdoMillet]

Ruairi,

Try to add
WhilePrintingRecords;
as the first line in both formulas.

Cheers,
- Ido

 

[Ruairi]

That worked perfectly. Thank You. Ruairi

Could your manufacturing facility benefit from real time process monitoring? Would you like your employees to be able to see up to the minute goal and actual production?
For innovative, low cost solutions check out my website.

http://www.plccontroltechnologies.com