Numberic parameter entry - to display Names

[thiagarr]

Hi,

I use CR11 ith SQL server 2000.

I have a report where the parameter accepts sales rep Number and the report currently displays the numbers separated by a comma. New requirement is to display the sales rep name instead of the number in the report.

I have modified the formula to display the corresponding Name field from the report and the report displays only the first character of the rep name instead of the whole name. I am sure I am missing something and would appreciate any help in solving this mystery.

Sample Data:

Rep # Name
25 John
34 Peter
48 Paul

Parameter Entry: 25 34 48
Current report display 25, 34, 48

New display requirement: John, Peter, Paul
Current display (Using the formula below): J, P, P

The formula is as follows:


StringVar Array x;
ReDim x[Count({?Sales Person})];
stringvar sales_rep;

NumberVar i := 1;

While i <= Count({?Sales Person})

Do
(
//x := ToText({?Sales Person},0);
x := {sales.sales_rep};

i := i + 1;
);


Original report had the following


x := ToText({?Sales Person},0); (which worked fine)

which has been replaced by

x := {sales.sales_rep};

Other than that the formula is exactly the same.

Any suggestion / help would be highly appreciated.

Thanks and regards,

TR

 

[dgillz]

So you are still entering a series of numbers, but you want to display the names?

Where on the report do you want this? The report header? Is the report sorted or grouped by salesperson #?

Software Sales, Training, Implementation and Support for Macola, eSynergy, and Crystal Reports

http://www.gainfocus.biz

"If the phone doesn't ring, it's me".....Jimmy Buffet

 

[thiagarr]

Hi,

The display of the names is to be in Report Header (RHB). The report is grouped by the Region followed by Rep Nmae.


Thanks and regards,

TR

 

[dgillz]

In the Group Footer place this formula to build a list of names. Suppress it if you do not want it to display:

WhilePrintingRecords;
If OnLastrecord then Stringvar list:=List+" "+{name} else
Stringvar list:=List+{name}+", "

Then to display it in the report footer:

WhilePrintingRecords;
Stringvar list;

I do not know how to display this in the report header. Maybe LB can take it from here.



Software Sales, Training, Implementation and Support for Macola, eSynergy, and Crystal Reports

http://www.gainfocus.biz

"If the phone doesn't ring, it's me".....Jimmy Buffet

 

[lbass]

Create a formula like this:

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

for i := 1 to j do(
display := display + (
select {?parm}
case 101 : 'John Barth'
case 102 : 'Jane Austen'
case 103 : 'P D James'
case 104 : 'Evelyn Waugh'
)
+ ", "
);
left(display, len(display)-2);

If you have too many sales reps to hard code like this, then insert a subreport in the report header that is grouped by sales rep, with the sales rep name in the group header, and create a parm within the subreport that has the same name as the one in the main report, add it to the record selection formula:

{table.salesrep} = {?parm}

and then link the sub to the main report by linking the parameters to each other.

-LB

 

[thiagarr]

LB / DGILLZ,

Thank you for both your responses and the suggestions. I will try them on Monday and post back here.

Yes, we have lots of sales rep entries and I will have to use the sub report option, looks like.

BTW, any idea why the display resulting from my formula brings only the first character of the name and not the full name? Is this something to do with the logic or code??



Thanks and regards,

TR

 

[dgillz]

Where is your current formula located?

I put this formula in a report and it returned "true".

Software Sales, Training, Implementation and Support for Macola, eSynergy, and Crystal Reports

http://www.gainfocus.biz

"If the phone doesn't ring, it's me".....Jimmy Buffet

 

[thiagarr]

DGILLZ,

My apologies to you and LB,

When I copied the formula, I did not copy the last line which reads:

join( x , ",");

In effect, my whole formula reads as follows:


StringVar Array x;
ReDim x[Count({?Sales Person})];
stringvar sales_rep;

NumberVar i := 1;

While i <= Count({?Sales Person})

Do
(
//x := ToText({?Sales Person},0);
x := {sales.sales_rep};

i := i + 1;
);
join( x , ",");

Once again, my apologies for the wrong information.



Thanks and regards,

TR

 

[lbass]

The reason you are getting only one letter per sales rep is that the sales person field is not an array, so when you use a subscript with it, it is choosing the letter that is in the position within the field that corresponds to the subscript.

-LB

 

[thiagarr]

LB,

Thank you for the reason behind the single character display.

DGILLZ,

The formula is located in Report Header B.

Thank you both for your help.

Thanks and regards,

TR

 

[thiagarr]

LB,

I went the subreport route and got the details as required in my report.

Thank you very much for your help and suggestions.

DGILLZ, Thank you for your suggestions too.

Thanks and regards,

TR