Split a string and compare value in loop to get the description.

[Former Member]

Hi,

I am passing a string {?CompCode} to a subreport which will use the string to fetch the corresponding description and concatenate the description fields and display it in the sub-Report.

String consists of comma u2018,u2019 separated code value and I have to display the description field corresponding to the Code value.

I am using the below code but which is only displaying description field corresponding to the first code value. Remaining descriptions are not getting displayed. Can you please help me to resolve this issue?

I am using Crystal Report 2008.

Thanks in Advance

Regards

Atanu

Local StringVar Array CompCodeName := Split({?CompCode},","); numbervar idx:=0; numbervar MaxArray := UBOUND(CompCodeName); stringvar Cat:=""; For idx := 1 to MaxArray Do ( if CompCodeName[idx] = Table.Comp_Code then Cat:= Cat & ' ' & Table.Comp_Name idx:= idx+1; ); Cat;

Edited by: Atanu Mukhopadhyay on Apr 22, 2010 4:58 PM

Now

I have changed the code as mentioned below and now the output is concatenated value of the Description field (Table.Comp_Name) corresponding to the first Company Code.

Say If

Company_Code Company_Name -

---

-

---

01A XXCom 01B YYCom 01C ZZCom

Now If I pass the string 01A,01B,01C then I am getting an out put ;XXCom;XXCom;XXCom Instead of XXCom;YYCom;ZZCom

StringVar Array CompCodeName:= Split({?CompCode},","); NumberVar Limit := UBound(CompCodeName); Numbervar Indx := 0; StringVar CompCode:= ""; StringVar CompCodeOutPut := ""; For Indx := 1 To Limit Do ( If Table.Comp_Code= ToText(CompCodeName [Indx]) Then CompCode := Table.Comp_Name; CompCodeOutPut := CompCodeOutPut & ';' & CompCode; indx = indx +1; ); CompCodeOutPut

[Former Member · Accepted solution]

It appears that you are not selecting all of the company records to drive the report. Change the record selection formula to

{Table.CompName} in Split({?CompCode},",")

which will return all of the records for each of the company codes. Then use a formula on the detail format to concatenate the multiple records using a shared variable to pass back to the main report.

HTH,

Carl

[Former Member · Accepted solution]

Yes the string is generated out of the LOV selected during execution of the report. The crystal report is generated on top of BEx query. And I need to display the description of the selected code in the Sub report itself. Here User may select range as well as discrete values as part of the report parameter selection. Please mention the code in detail.

Thanks for your quick response. Let me know if you need any detail.

Regards

Atanu

[Former Member · Accepted solution]

I assume when you say "select a range or discreet values" you mean that they can click on one of the LOV values, hold the Shift key and click several records down...

The subreport should be over the table that has company code and description. Put the selection formula above into the record selection of the subreport, then use the technique in the thread entitled something like "Concatenate strings". I think there's another one where the title mentions something like "displaying data across the page instead of down". (I can't put my finger on the posts right now...) Basically, use a shared variable to append the current detail record's description to the shared variable. In this case, you'd want to clear the shared variable on the subreport header format.

HTH,

Carl

[Former Member · Accepted solution]

[Here|;'s a link to a thread that's trying to do the same thing, although it's neither of the two I was thinking about...

And [one of the ones that I was talking about|;.

HTH,

Carl

Edited by: Carl Sopchak on Apr 22, 2010 4:05 PM - added second link

[Former Member · Accepted solution]

Thanks for the links you have mentioned above. But the problem remains same. I am not able to select Description of the corresponding codes that are selected as part of the LOV selection during the execution of the report. If I am using the formula

{Table.CompName} in Split({?CompCode},",")

It is returning first CompName only instead of all the CompName whose CompCodes are selected.

Regards

Atanu

[Former Member · Accepted solution]

Is {?CompCode} a string of comma separated values as originally stated, or is it a multiple-value (LOV) parameter (which is treated as an array)?? Big difference! If the later, the selection would be simply

{Table.CompName} in {?CompCode}

HTH,

Carl

[Former Member · Accepted solution]

CompCode is coming from an LOV in the main report. I am concatenating this Compcode in the main report and passing it to sub report to get the description from a separate BEx query.

Thanks

Atanu

[Former Member · Accepted solution]

Please post the formula that concatenates the values.

Carl

[Former Member · Accepted solution]

I am using the below code.

numbervar idx:=0;
stringvar Cat:="";
// Concatenate all but the last array element
for idx := 1 to UBound ({?CompCode})-1 do
(
if minimum({?CompCode}[idx]) = maximum({?CompCode}[idx]) then 
Cat := Cat & minimum({?CompCode}[idx])& ", "
else
Cat := Cat & minimum({?CompCode}[idx]) & " , " & maximum({?CompCode}[idx]) & ", "
);
// fix idx if the is only 1 array element
if UBound ({?CompCode}) = 1 then idx:=1; 
// catch last member of array with no trailing comma
(
if minimum({?CompCode}[idx]) = maximum({?CompCode}[idx]) then 
Cat := Cat & minimum({?CompCode}[idx])
else
Cat := Cat & minimum({?CompCode}[idx]) & " , " & maximum({?CompCode}[idx]) 
);
// output value
Cat

[Former Member · Accepted solution]

So {?CompCode} is a multi-value range parameter?? Not too sure how you'll end up accomplishing this... The formula that you have will not work because if {?CompCode}[1] is the range 1 to 1000, your string of values will just be "1, 1000". You really need every value in between.

Just for debugging purposes, remove the range from the parameter to see if it will work. You'll have to select every value where a range would have been, but that is not really a difficult task (click/shift-click)... (Also, if it does work, then you can just use the Join() function to do the concatenation...)

HTH,

Carl

[Former Member · Accepted solution]

If I select all the values using Click/Shift-Click then also JOIN() function is not working. It is showing an error "A string array is required here". We can change the range parameter and instead ask user to select value in the earlier mentioned way. But, in that case how we can display the description?

Thanks

Atanu

[Former Member · Accepted solution]

If you use range array values, you're going to have to program for the fact that you do not have all of the discrete values that you'd have if you were not using ranges. What you'll have to do is build the actual full condition, and pass that into an SQL Command to get the desired results. In pseudo-code (I'm a bit short on time right now):

condition = ''
For each array element
  if condition <> ''
    condition = condition + ' or '
  end if

  if element is range
    condition = condition + ' field between ' + minimum(element) + ' and ' + maximum(element)
  else
    condition = condition + ' field = ' + minimum(element)
  end if
end for

then pass this to the SQL Command (call the parameter {?Condition}):

select *
from table
where {?Condition}

HTH,

Carl