Crystal XI SQL RPT - Need to Fetch XLS Data 1

[jpstrzoch]

Hello,

I am using Crystal XI to run a report linked to an SQL DB. I have created the report to show specific item IDs that meet the Selection Expert criteria. GH1 is grouped on the Item ID field.

I have an administrator that would like to maintain an XLS file with "status" codes for specific item IDs. This XLS list would not be the entire list resulting from the RPT and Selection Expert criteria described above. I want to see all of the SQL DB returns but use the XLS “status” field for specific item ID’s to drive conditional formatting. When connecting SQL and XLS I linked on Item ID using the Database Expert. When creating the connection using Database Expert I elected Access/Excel with a database type of Excel 5.0.

When I run the report I only get the item IDs that are listed on the XLS file and not the original list I obtained from the SQL DB RPT run without the link to the XLS file.

I don't understand how to overcome.

Thank you in advance for your assistance.

James J. Pstrzoch

 

[MJRBIM]

You need an LEFT OUTER join between the two tables (table -> spreadsheet).

That will show you all records in the table - and only populate the spreadsheet field if it exists.

Crystal uses EQUAL as it's DEFAULT join, which means the record has to exist in both tables.

 

[MJRBIM]

P.S. - Using XLS as a source for RPTs is a bad idea. If the data is that important it should be in a database, not just in a spreadsheet on the network.

 

[jpstrzoch]

Thank you very much for the response. Follow-up question. If I need to do a left outer join and the default is equal to, what should I change it to?

 

[MJRBIM]

In CR-XI (R2), Go to "Database Expert"

Click on the LINKS tab.

Highlight and right-click the link between you two tables.

Click "Link Options".

Change the "Join Type" to LEFT OUTER.

Click all the required OKs to get back to the RPT design.

 

[jpstrzoch]

Thank you very much.