Multiple Excel Datasources-merging in Crystal

[kkowalski]

I Need help desperately:
Crystal Reports 9 on WinXP, using Excel worksheets at datasource.

I have several excel worksheets imported as datasources and linked by a common ID field. Each spreadsheet contains a different subset of the larger spreadsheet.

The main sheet contains an ID# and demographics, each of the other worksheets contains the same ID# and a special program field descriptor. The main sheet contains all users, but the special program sheet only contains a subset of the main sheet IDs (those who meet the program criteria). I need to combine all the special program fields into one master report.

I want to say something like IF (ID# in Mainsheet exists in specialsheet then include special program descriptor, if not, write "no") and then repeat that for each special program file.

Anyone have any formula help for this scenario? Greatly appreciated.

Sincerly, Dealine looming.

 

[synapsevampire]

In case you've not heard this, Excel is a horrible data source.

If possible, create an MS Access database and Link (not import) the spreadsheets into the database. Then create a query to UNION All of the specialsheets into one datasource.

Now you can use Access's superior Query tool to construct the data for use by the Crystal Report.

The alternative in Crystal would be to make sure that you're using a Left Outer join from the main datasource to the other spreadsheets, and then group by the ID, and then in the group footer place your fields (suppress the details section).

Now create a formula for your special prgram desccriptor as in:

whileprintingrecords;
StringVar Descriptor:="No";
If {mainspreadsheet.id} = {specialsheet1.id} then
Descriptor:={specialsheet1.descriptor}
else
If {mainspreadsheet.id} = {specialsheet2.id} then
Descriptor:={specialsheet2.descriptor}
else
If {mainspreadsheet.id} = {specialsheet3.id} then
Descriptor:={specialsheet3.descriptor}
else
If {mainspreadsheet.id} = {specialsheet4.id} then
Descriptor:={specialsheet4.descriptor};
Descriptor

Obviously I've assumed all of the technical details about the spreadsheet (table) names and field names because you've supplied nothing along these lines.

Plus you never stated which descriptor you want from the special fields if there are more than one match.

Anyway, this should at least get you close.

-k

 

[kkowalski]

Your assumptions on the number of sheets and the fields is correct. The crystal formula you gave would work if the special program sheet contained ALL the IDs from the mainsheet. What happens in the scenario you gave is that my entire data report is whittled down to the subset of members that are in ALL the special programs. I need the formula to look and if the ID doesn't exist in the special sheet, to write NO, if it does write the specialsheet value.You're close and that's the direction I was headed, I just can't get it to do that compare of the ID#s and still leave the mainsheet data there if the member is NOT in the special program field.

Thanks.

 

[synapsevampire]

You're mistaken, a left outer join from the main to the child tables preserves the main tables rows unless you are adding filtering to the child tables, if that's the case, don't do so.

Try again and use left outers from the main to the child tables.

-k