Multi Value Param - changing codes back to descriptions 2

[cully651]

Good day!

I have a very unusual request... but first let me say that I'm using BOEXI and CR XI-R2 with an Oracle 9i db.

Here's the problem;

I have counties stored as their county codes and county names. In the multi-param I pass the codes, which works great. Now, at the top of the report, they want to see which counties they selected. I don't want to switch to passing the county names if I can help it because the string I'd end up passing to the enterprise would be extremely large (maybe too large), but I can't find a way to return the name at this point. Here's the formula I use currently which strings together all the county codes;

local stringvar CID;
local numbervar x := count({?CountyID});
local numbervar i;

for i := 1 to x do
  (CID := CID + {?CountyID}[i];
      if i < x then 
      CID := CID + " , ";
      i + 1;
  );

CID

Any ideas?

You guys are the best!!!

 

[synapsevampire]

First, your formula is the same as:

join({?CountyID},", ")

As for passing too large of a string, that isn't really an issue, but the fact that you don't explain how you're doing "In the multi-param I pass the codes" is an issue, we don't know. Are you using a dynamic parameter?

Is it part of the same recordset as used in the report, so that you could do a lookup of the code against the data returned to get the name?

Anyway, the above formula should prove useful going forward, now if you would take the time to post specifics we can specifically help you.

-k

 

[lbass]

A simple way to do this would be to add a subreport that had the county names in a detail section. Create the same parameter in the subreport and add a record selection formula like:

{table.countyID} = {?countyID}

Then link the subreport to the main report on the two parameter fields. Use the dropdown to select {?countyID} in the lower left corner of the screen, not the default {?pm-?countyID}. Suppress all sections of the subreport except the detail section.

-LB

 

[cully651]

synapsevampire,

"I pass the codes" means that each county has a code which is associated with the county name. When the user selects the counties from the list, all I'm passing is the code, not the name. And no, the parameter is not dynamic.

The data being passed is associated with the recordset in the report. I'm not sure I understand what you're asking because I think this would always be true isn't it?

Your formula join({?CountyID},", ") will prove very helpful in the future, thanks!


lbass,

Thank you, the subreport idea is workable. It'll have to be a little more complex because I'll need to grab all the return values and string them together with commas.


Thanks again! I knew you folks would have the answers!

 

[synapsevampire]

No you can have a parameter which gets data from a different recordset.

-k

 

[cully651]

Yes, of course, now I see what you mean. There would have to be pretty good parity between the recordsets in order for that to work but it's not uncommon.

 

[lbass]

Then you can place a formula like the following in the detail section of the subreport:

whileprintingrecords;
stringvar x;
if instr(x,{table.name}) = 0 then
x := x + {table.name} + ", ";

Then suppress the detail section and unsuppress the subreport footer and add this formula:

whileprintingrecords;
stringvar x;
left(x,len(x)-2)

-LB