split the value using comma as delimiter and then pass each value to query to get the description

[Former Member]

Hi,

As i am new to crystal reports,  I need help in getting one field.

In my report i am using 2 tables:

one is like :

code_id	code_desc
1	aaa
2	bbb
3	ccc
4	ddd

and the another is :

reason_ind
1
2,3,4
2,3
1,2,3,4
3,4

In this report i have a column : Reason_desc.

Here i have to show code_desc for those values of reason_ind matches with code_id in the above table.

Ex:

reason_ind	reason_desc
1	aaa
2,3,4	bbb,ccc,ddd
2,3	bbb,ccc

Let me know if you need further clarifications.

Thanks in advance.

Regards,

Swathi.

[abhilash_kumar · Accepted solution]

Hi Swathi,

Here's how you can do this at the report level:

1) Create a Main report pointing to the second table from that screenshot.

2) Drag and drop the Reason_ind column on the Details Section

3) Insert a Subreport pointing to the First table and place it on the Details Section

4) Right-click the Subreport > Select Change Subreport Links > Move the Reason_Ind field to the pane on the right > Uncheck the option 'Select data in Subreport based on field'

5) Get inside the Subreport and go to Report > Selection Formulas > Record and use this code:

ToText({Code_Id},0,"") IN Split({@Pm-Reason_Ind},",")

6) In the Subreport, create a Group on the Code_Desc field

7) Suppress the Report Header, Group Header, Details and Group Footer sections

😎 Create a Formula with this code and place it on the Group Header 1 section:

whileprintingrecords;

stringvar s := s + {Code_Desc} + ", ";

9) Create one more formula and place this on the Report Footer section:

whileprintingrecords;

stringvar s;

Left(s,len(s)-2);

Hope this helps.

-Abhilash

[Former Member · Accepted solution]

Thanks Abhilash, for quick response.

I tried this and getting error at the formula:

Regards,

Swathi

[abhilash_kumar · Accepted solution]

If the datatype of the CODE field is string then you don't need to convert it to string. Just use:

{Command.Code} IN

-Abhilash

[Former Member · Accepted solution]

yes, the CODE field is string.

It's working

Thank you so much Abhilash... !

Regards

Swathi

[Former Member · Accepted solution]

Hi Abhilash ,

It's working for some extent.

My report showing total of 30 records.

first 20 records OK , but for last 10 records getting error as:

Regards

Swathi

[abhilash_kumar · Accepted solution]

Change this code to:

whileprintingrecords;

stringvar s;

If s = "" then "" else

Left(s,len(s)-2);

[Former Member · Accepted solution]

working fine. 

[Former Member · Accepted solution]

Hi Abhilash,

One more doubt.

In the end of description comma is there. How to remove it?

Regards,

Swathi.

[abhilash_kumar · Accepted solution]

That's exactly what this line of code is supposed to do:

Left(s,len(s)-2);

If you've coded exactly as above and if it still doesn't work, try changing the formula on the Group Header to:

whileprintingrecords;

stringvar s := s + {Code_Desc} + If Not(OnLastRecord) Then ", " else "";

And then change the formula on the Report Footer to:

whileprintingrecords;

stringvar s;

-Abhilash

[Former Member · Accepted solution]

It's fine.

Thanks a lot Abhilash!