I am using Crystal Reports XI. 

 

I have a Crystal Report and in it is a sub report.  

The main rpt has an Excel file (named Sheet1_ in Crystal Rpt even though on my desktop the name of the XLS file is different) and a SQL Server table linked (inner joined where two fields equal) in the Database Expert and this produces the proper results for this main report.

I want the sub rpt to use the same Excel file and the same SQL Server table. I am trying for the sub rpt to enter into Add Command the following to link the XLS file and the SQL Server table with the following code:

Select Sheet1_.Account_No From Sheet1_ X
Left Outer Join Pt_User_Defined_Fields P
On X.Account_No = P.Account
Where P.Account Is Null

When I click OK in the Add Command To Report for the sub report after entering the above query, I get the following error:

Database Connector Error: '42S02:[Microsoft][ODBC SQL Server Driver][SQL Server]Invalid object name 'Sheet1_'.[Database Vendor Code: 208]'

The real path on my PC and the real name of the Excel file is as follows:

C:\Users\michaelw\Desktop\My_CR_ShowPatientsNotInUserDefinedTbl\ Second_And_Last_Excel_File_For_Language.xls

The main report works I guess because I am linking (inner joining the XLS file and SQL Server table where each field equals) via the Database Expert.

When I try to use the Add Command for the sub report to query to show the accounts in the Excel file that do not have a row in the SQL Server table, I get the Database Connector Error: '42S02:[Microsoft][ODBC SQL Server Driver][SQL Server]Invalid object name 'Sheet1_'.[Database Vendor Code: 208]'.

- - -

 

I tried the following in the Add Command under the path of the Excel file of the sub report:

SELECT `Sheet1_`.`Account_No`
FROM   `Sheet1$` `Sheet1_`
LEFT OUTER JOIN Pt_User_Defined_Fields
ON `Sheet1_`.`Account_No`= Pt_User_Defined_Fields.Account
WHERE Pt_User_Defined_Fields.Account Is Null

Now though I get the Database Connector Error: 'DAO Error Code: 0xce0 Source: DAO.Database Description: Join expression not supported.'

I tried the following in the Add Command under the SQL Server CRSS Database in the Database Expert of the sub report:

SELECT `Sheet1_`.`Account_No`
FROM   `Sheet1$` `Sheet1_`
LEFT OUTER JOIN Pt_User_Defined_Fields
ON `Sheet1_`.`Account_No`= Pt_User_Defined_Fields.Account
WHERE Pt_User_Defined_Fields.Account Is Null

I get the following error though: Failed to retrieve data from the database. Details: 42000:[Microsoft][ODBC SQL Server Driver][SQL Server]Line 1:Incorrect syntax near ''', [Database Vendor Code:170]

So I tried the following in the Add Command under the SQL Server CRSS Database in the Database Expert of the sub report:

Select Sheet1_.Account_No From Sheet1_ X
Left Outer Join Pt_User_Defined_Fields P
On X.Account_No = P.Account
Where P.Account Is Null

I got though the Database Connector Error: '42S02:[Microsoft][ODBC SQL Server Driver][SQL Server]Invalid object name 'Sheet1_'.[Database Vendor Code:208]'

I would be very grateful if anybody has any idea.  I just want to accomplish in the sub report in a way that is okay with Crystal Reports the equivalent of the following query:

 

Select ExcelFileName.Account_No From ExcelFileName X

Left Outer Join SqlServerTableName.Account S

On X.Account_No = S.Account

Where S.Account IS NULL

 

Thanks very much in advance.

 

Hviezdoslav

 

I thank you very much for your reply.  I had tried in the sub report to use the Database Expert to link the Account_No field of the XLS file to the Account field of the SQL Server table using Left Outer Join, Not Enforced, with != Link Type but I get account numbers that are in the SQL Server table since those account numbers are in the XLS file.  I want to show only the acct numbers in the XLS file that are not in the SQL Server table. 

 

My main report links the two via the Database Expert with Inner Join, Not Enforced, and the = Link Type. 

 

In the sub rpt using Database Expert, I tried Left Outer Join, Enforced From, and !=, and again I get all of the acct numbers in the XLS file including accounts that are in the SQL Server table. 

 

When I try in sub report via Database Expert to Left Outer Join, Enforced To, and !=, I get bunches upon bunches of records and the first account # 10011 in the XLS file is listed repeatedly for 293 pages in the Preview of the report before the 2nd account number 10078 of the XLS file begins to be listed repeatedly for bunches upon bunches of pages.

 

I tried in sub rpt via Database Expert to link the XLS to the SQL Server tbl using Left Outer Join, Enforced Both, and !=, but the first account # in the XLS is listed for the 293 pages, et cetera.

 

I tried in sub rpt in Database Expert to link XLS to SQL Server table using Inner Join, Not Enforced, and != as the Link Type, but I still get in the results some accounts that are in the SQL Server table. 

 

When using for sub rpt the Database Expert to link the XLS file to the SQL Server using Inner Join, Enforced From, and !=, again I get in the results some account numbers that are in the SQL Server table. 

 

When I try in Database Expert of sub rpt to link XLS file to SQL Server tbl using Inner Join, Enforced To, and != for not equal Link Type, I get the first account number in the XLS file for nearly 300 pages of the report, then I get the second account number in the XLS file for bunches upon bunches of pages, et cetera.

 

Using Database Expert in sub rpt to link the XLS file to SQL Server table using Inner Join, Enforced Both, and the !=, I get the first account number in the XLS file for nearly 300 pages of the report, then I get the second account number in the XLS file for bunches upon bunches of pages, et cetera.

 

There is no testing SQL Server database and client/server application for this proprietary software here at work unfortunately.  I cannot get permission to get onto the Prod SQL Server.  Were I to be able to get onto SQL Server and were I to have two SQL Server tables in which I wanted rows from one SQL Server table that are not in the other SQL Server table, I think (though I could be wrong of course) that I could use something like the following:

 

Select c.CustomerId, O.OrderId

From Customers c

Left Outer Join Orders o

On c.CustomerId = o.CustomerId

Where o.OrderId Is Null

 

I cannot get onto the SQL Server though, plus I do not know how to use an Excel file in the SQL Server query/view.

 

Anyway Dell, I very much appreciate your reply.  I cannot figure out how to use a Crystal Report to show the records in the Excel file that are NOT in the SQL Server table for the sub report.  The main report is easy and I can show the records that are both in the XLS file AND in the SQL Server table.

 

Thanks,

 

 

 

 

 

 

 

Yes, what you said worked.

 

In the sub report, in Database Expert, I linked the XLS file to the SQL Server table using Left Outer Join, Not Enforced, and the = or EQUAL for the Link Type. 

 

In the sub report, I had nothing in the Select Expert.  So I added the SQL Server table and field (Pt_User_Defined_Fields.Account) to the Select Expert.  I chose "formula:" in the drop down and then as you instructed I typed the following:

 

IsNull({Pt_User_Defined_Fields.Account})

 

Sure enough, the results in the sub report show the records in the Excel file that are NOT in the SQL Server table.

 

I am very grateful to you, Dell.  Thanks very much. 

 

Hviezdoslav