CR 9.0 - Need expansion on multi-select field 1

[ltidquist]

I'm using CR 9.0 Professional with a SQL Database. I have a multi-select field that stores codes (ie 1,2,3). I have another table that stores the code and the descriptions for that multi-select field. In my report I need to display the descriptions for those codes (ie One,Two,Three).

I'm having no luck with this. Has anyone done this before?

Thanks in advance.

 

[lbass]

Are you talking about a numeric parameter field that you want to show the descriptions for? If so, and there are only a few codes involved, you can use a formula like this:

numbervar i;
numbervar j := ubound({?parm});
stringvar display;

for i := 1 to j do(
display := display + (
if {?parm} = 1 then
"Description1" else
if {?parm} = 2 then
"Description2" else
if {?parm = 3 then
"Description3"
)+ ", "
);
left(display,len(display)-2);

If there are too many codes, then insert a subreport that links the {?Parm} in the main report to the code in the subreport, and display the descsription field in a group header based on the same field.

-LB

 

[ltidquist]

No that's not exactly it. I've got a field that's most likely a varchar. It's a multi-select field that you can choose more than code from a lookup table. The codes are stored in this field. The lookup table stores the codes and the descriptions for those codes. I need to be able to display the descriptions for every code selected. So a record can contain several codes in that field and I need to display all of the descriptions.

Thanks again for any help!

 

[lbass]

Please show a sample of how the field displays if you place it in the detail section of the report. How many values can possible values are there?

-LB

 

[ltidquist]

Here's an example of how the data is stored:

SLP,ADL,AS,ADM,AM

The field can have an unlimited number of values to select from.

The table that stores the code/description has a separate field for code and description.

Thanks.

 

[lbass]

Insert a subreport that uses the lookup table. Link it to the main report by using the multiselect field from the main report as the linking field. Then go into the subreport->report->selection formula->record and change it to read:

{lookup.code} = split({table.multiselectfield},",")

Then create two formulas in the subreport:

//{@accum} to be placed in the detail section:
whileprintingrecords;
stringvar x := x + {lookup.description}+",";

//{@display} to be placed in the subreport footer:
whileprintingrecords;
stringvar x;
if len(x) > 1 then
left(x,len(x)-1)

Suppress all subreport sections but the report footer and place the sub in the detail section of the main report.

-LB