Report Design
 Crystal Reports Forum : Crystal Reports 9 through 2016 : Report Design
Message Icon Topic: SQL & Excel database Report Post Reply Post New Topic
Author Message
JWagner
Newbie
Newbie
Avatar

Joined: 07 Apr 2017
Location: United States
Online Status: Offline
Posts: 3
Quote JWagner Replybullet Topic: SQL & Excel database Report
    Posted: 17 Apr 2020 at 7:43am
I need to find all records for employees in my SQL database.  I have an excel file as xls linked to my report and it works fine if I use the excel sheet to search by the last name, but when I try to search for the full name, it does not return any records.  I have to use the * wildcard as some names may or may not have the employees middle initial.  Search criteria and 1st few records of the spreadsheet are below.  I am not a programmer and all CR has been self taught.
{ORDERS.OpenDate} > DateTime (2019, 01, 01, 00, 00, 00) and
({Buysel.Name1Full} Like [{Sheet_.Name}] or {Buysel.Name2Full} Like [{Sheet_.Name}])

Excel
Name Last First
Emily*Albrecht Albrecht Emily
Peter*Anderson Anderson Peter
Rachel*Berger Berger Rachel
Carli*Bergquist Bergquist Carli

IP IP Logged
kevlray
Admin Group
Admin Group
Avatar

Joined: 29 Oct 2009
Online Status: Offline
Posts: 1520
Quote kevlray Replybullet Posted: 30 Apr 2020 at 4:40am
One thing to remember in formulas, if there are NULL values in the data, make sure 'Default Values for NULLs' is selected in the formula editor.  Also I think you may need to modify your formula to like this.
{ORDERS.OpenDate} > DateTime (2019, 01, 01, 00, 00, 00) and
({Buysel.Name1Full} Like "*"+ [{Sheet_.Name}] or {Buysel.Name2Full} Like "*"+[{Sheet_.Name}])

IP IP Logged
JWagner
Newbie
Newbie
Avatar

Joined: 07 Apr 2017
Location: United States
Online Status: Offline
Posts: 3
Quote JWagner Replybullet Posted: 30 Apr 2020 at 7:54am
Thanks for the info.  I cannot figure out what is wrong.  but, all the records it is pulling are not even on my excel datasource.  I've tripled checked the linking.  It is even pulling records with nothing in that field.  So, to me it sounds like a linking issue, but ?   SQL query is below

bigdog
 SELECT DISTINCT "ORDERS"."Branch", "ORDERS"."CloseAgent", "ORDERS"."GFNo", "ORDERS"."Service", "TOMain"."Description", "Buysel"."City", "ORDERS"."Status", "Buysel"."Name1Last", "Buysel"."Name1First", "Buysel"."Name2Last", "Buysel"."Name2First", "ORDERS"."IsLocked", "ORDERS"."OpenDate", "ORDERS"."RegID", "Buysel"."Name1Full", "Buysel"."Name2Full"
 FROM   (("ccWin"."dbo"."ORDERS" "ORDERS" LEFT OUTER JOIN "ccWin"."dbo"."TOMain" "TOMain" ON "ORDERS"."TONum"="TOMain"."TONum") LEFT OUTER JOIN "ccWin"."dbo"."BUYER" "BUYER" ON "ORDERS"."GFNo"="BUYER"."GFNo") LEFT OUTER JOIN "ccWin"."dbo"."Buysel" "Buysel" ON "BUYER"."BuyerSellerID"="Buysel"."BuyerSellerID"
 WHERE  "ORDERS"."OpenDate">={ts '2019-01-01 00:00:01'} AND "ORDERS"."Status"<>'Cancelled' AND  NOT ("ORDERS"."GFNo" LIKE 'mis%' OR "ORDERS"."GFNo" LIKE 'tes%') AND "ORDERS"."Branch"<>'ADMIN'
 ORDER BY "ORDERS"."Status", "ORDERS"."CloseAgent", "ORDERS"."GFNo"
 EXTERNAL JOIN Buysel.Name1Full={?\\nutella\shared\Administrative and Policies\MW Crystal Reports\Employee List 04-17-20.xls: Sheet_.Name} AND Buysel.Name2Full={?\\nutella\shared\Administrative and Policies\MW Crystal Reports\Employee List 04-17-20.xls: Sheet_.Name}


\\nutella\shared\Administrative and Policies\MW Crystal Reports\Employee List 04-17-20.xls
 SELECT DISTINCT `Sheet_`.`Name`
 FROM   `Sheet$` `Sheet_`
 WHERE  `Sheet_`.`Name`={?bigdog: Buysel.Name1Full} AND `Sheet_`.`Name`={?bigdog: Buysel.Name2Full}






Edited by JWagner - 30 Apr 2020 at 11:06am
IP IP Logged
kevlray
Admin Group
Admin Group
Avatar

Joined: 29 Oct 2009
Online Status: Offline
Posts: 1520
Quote kevlray Replybullet Posted: 04 May 2020 at 4:24am
I really do not know for sure.  I have never joined an Excel sheet to SQL.  I would have to do more research on the EXERNAL join.  I have only used it with other MS-SQL databases.
IP IP Logged
kevlray
Admin Group
Admin Group
Avatar

Joined: 29 Oct 2009
Online Status: Offline
Posts: 1520
Quote kevlray Replybullet Posted: 04 May 2020 at 12:38pm
I could not find any information on the 'External Join' command.  I had once had to connect to an external database (i.e. database on another server) using OPENQUERY. I did find this type of command for TSQL.  I have never used it myself.

INNER JOIN  OPENROWSET ('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database=\\Bdzserver\db_creation\postaldistricts.xls;HDR=YES', 'SELECT * FROM [Sheet1$]') As d
ON d.[PostalDistricts] = a.[PostalDistrict]


Edited by kevlray - 04 May 2020 at 12:43pm
IP IP Logged
JWagner
Newbie
Newbie
Avatar

Joined: 07 Apr 2017
Location: United States
Online Status: Offline
Posts: 3
Quote JWagner Replybullet Posted: 06 May 2020 at 4:22am
Thanks for the info and your efforts  Smile
IP IP Logged
Post Reply Post New Topic
Printable version Printable version

Forum Jump
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot delete your posts in this forum
You cannot edit your posts in this forum
You cannot create polls in this forum
You cannot vote in polls in this forum



This page was generated in 0.031 seconds.