Multiple Data Source through Excel Sheets 1

[Khawer]

I have a report that extracts information from 2 separate excel sheets. Sheet1 has employee information and Sheet2 has Employees Beneficiary and Vol Beneficiary info. In Sheet2 there may be null values for both Beneficiary and Vol Beneficiary like the first line illustrated below in sheet2. The tables are linked by Co No and Emp No. In the crystal report layout (shown on the bottom) I would like it to display one line for the Beneficiary and another line for Vol Beneficiary serparated by commas. Is there a way to do this. Maybe creating an Array? Any advise regarding this problem would be great.


sheet 1 <-(Imported Excel Table name)
Name |Emp No |Address |Dept NO |Co No
---------------------------------------------------------
John D. |1 |123 Street |12 |123
Bill C. |2 |231 Street |17 |123
Ann F. |3 |342 Street |56 |123
Bob T. |4 |345 Street |89 |123


sheet 2 <-(Imported Excel Table name)
Emp No |Beneficiary |Vol Beneficiary |Co No
----------------------------------------------------------
1 | | |123
1 |Sam D. | |123
1 | |Don D. |123
1 | |Bob H. |123
1 | |John D. |123
1 |Dude F. |Sammy I. |123

*** One Employee shown there may be employees with multiple Beneficiaries and Vol Beneficiaries
------------------------------------------------------------
Crystal Report layout ( The way it is supposed to look like)

Emp No: 1
Name : John D.
Address: 123 Street
Dept No: 12
Co No: 123
Beneficiary: Sam D., Dude F., Sammy I.
VolBeneficiary: Don D., Bob H., John D.

**Beneficiary and Vol Beneficiary are supposed to displayed in 2 separate lines with information divided by commas

 

[Khawer]

Im using Crystal Report version 9

 

[lbass]

I think you could place the first five rows in a group header with the group inserted on Employee Number. Then place the beneficiary and the vol beneficiary fields in the detail section. Then create four formulas:

//{@reset} to be placed in the group header:
whileprintingrecords;
stringvar benef := "";
stringvar volben := "";

//{@accum} to be placed in the detail section:
whileprintingrecords;
stringvar benef;
stringvar volben;

if not isnull({sheet2.beneficiary}) then
benef := benef + {sheet2.beneficiary} + ", " else
benef := benef;
if not isnull({sheet2.volbeneficiary}) then
volben := volben + {sheet2.volbeneficiary} + ", " else
volben := volben;

//{@displayben} to be placed in the group footer:
whileprintingrecords;
stringvar benef;
left(benef,len(benef)-2);

//{@displayvolben} to be placed in the group footer:
whileprintingrecords;
stringvar volben;
left(volben,len(volben)-2);

Then suppress the detail section.

-LB

 

[Khawer]

LBass: Thank you that worked. But I have a small problem for some of the employees. Lets say that in the benefit sheet there are employees with with multiple lines of beneficiaries and those lines are repeating due to the beneficiaries name being repeated like shown below:

sheet 2 <-(Imported Excel Table name)
Emp No |Beneficiary |Vol Beneficiary |Co No
----------------------------------------------------------
1 | | |123
1 |Sam D. | |123
1 |Sam D. | |123
1 |Sam D. | |123
1 | |Sammy I. |123
1 | |Sammy I. |123
1 | | |123
2 |Johnny | |123
2 |Johnny | |123
2 |Johnny |Sammy I. |123
2 |Johnny |Sammy I. |123

I was able to fix the multiple names by modifying the code you provided but it eliminated anybody that had only one value and skipped the name and showed a blank on Crystal. Please help. Thanks in advance

whileprintingrecords;
stringvar benef;
stringvar volben;


if not isnull({sheet2.beneficiary})
xor {sheet2.beneficiary} <> Previous ({sheet2.beneficiary}) then
benef := benef + {sheet2.beneficiary} + " : "
else if not isnull({sheet2.beneficiary}) and {sheet2.beneficiary} = Previous ({sheet2.beneficiary}) then
benef := benef;


if not isnull({sheet2.volbeneficiary})
xor {sheet2.volbeneficiary} <> Previous ({sheet2.volbeneficiary}) then
volben := volben + {sheet2.volbeneficiary} + " : "
else if not isnull ({sheet2.volbeneficiary}) then
volben := volben ;


** I have tried different comparison operators and for some reason "xor" worked out ok with the multiple names and conflicts with the single values.

 

[lbass]

Try changing {@accum} to:

whileprintingrecords;
stringvar benef;
stringvar volben;

if not isnull({sheet2.beneficiary})
and instr(benef,{sheet2.beneficiary}) = 0 then
benef := benef + {sheet2.beneficiary} + ", " else
benef := benef;
if not isnull({sheet2.volbeneficiary}) and
instr(volben,{sheet2.volbeneficiary}) = 0 then
volben := volben + {sheet2.volbeneficiary} + ", " else
volben := volben;

-LB

 

[Khawer]

Thanks LB, That worked.

 

[kimchavis]

How do i add the excel spreadsheet as a data source?

Im using an oracle database, if that helps.

Thank you so much.

 

[Khawer]

Kimchavis: you would need to create a new connection in database expert, under Access/Excel (DAO)