Using SQL Commands in Crystal Reports

[notaprogrammer49]

I'm a business analyst that has to perform some report development or modifications to existing reports. We have no report developers on staff, so must contract out this work.

I've been working with MS Access reports for the past 5 years and have become pretty familiar with Access SQL. Since my organization has adopted Crystal Reports XI (Developer version,) I am now in the process of trying to learn CR. I've been to the basic classes and the examples from them work really well. (These courses did not cover SQL commands.)

However, it now appears that I will need to learn more VB and Crystal syntax in order to modify the more advanced basic, to complex reports. Also Crystal appears to have its' own version of SQL which is unlike MS Access or SQL Server or Oracle SQL.

I had hoped to be able to make good use of the CR SQL COMMANDS in many of my reports, and thus avoid programming in VB, etc. but am now finding that the translations very often go screwy and you don't get back the record set you are expecting.

I've been experimenting and as soon as I think I have some aspects of SQL in Crystal figured out, the whole deck I've constructed collapses and I'm back to another experiment.

I've tried converting reports that I know to work properly in Access by using Crystal SQL commands, but with very limited success. So next, I decided to create the report from scratch in Crystal without using SQL commands. This works better, but still misses the mark.

FYI, here are some SQL code comparisons, trying to output the same report:

They are not exactly the same as I couldn't find a way to perform some of the Access SQL in Crystal Reports.

CRYSTAL Generated SQL Statement ("Show SQL Query") (did NOT use SQL
command)
SELECT "SAMPLES"."Collect_Date", "SAMPLES"."Lab#",
"Monitoring_Wells"."Facility_ID", "Monitoring_Wells"."TestSiteID",
"TESTGROUPS"."TestGroupID", "TESTS"."TestID", "TESTS"."ReportedResult",
"SAMPLES"."RefID", "SAMPLES"."SampleID"
FROM (("ASPEN"."dbo"."Monitoring_Wells" "Monitoring_Wells" INNER JOIN
"ASPEN"."dbo"."SAMPLES" "SAMPLES" ON
"Monitoring_Wells"."SampleID"="SAMPLES"."SampleID") INNER JOIN
"ASPEN"."dbo"."TESTGROUPS" "TESTGROUPS" ON
"SAMPLES"."LIMS#"="TESTGROUPS"."LIMS#") INNER JOIN "ASPEN"."dbo"."TESTS"
"TESTS" ON ("TESTGROUPS"."TestGroupID"="TESTS"."TestGroupID") AND
("TESTGROUPS"."LIMS#"="TESTS"."LIMS#")

WHERE "SAMPLES"."RefID"<>N'*-E' AND ("SAMPLES"."Collect_Date">={ts
'2007-11-13 00:00:00'} AND "SAMPLES"."Collect_Date"<{ts '2007-11-13
00:00:01'}) AND "TESTGROUPS"."TestGroupID"<>N'601*' AND NOT
("TESTS"."TestID"=N'NH3' OR "TESTS"."TestID"=N'NO3' OR
"TESTS"."TestID"=N'SO4-IC')
ORDER BY "Monitoring_Wells"."TestSiteID"

NOTE THE USE OF N', among other oddities.


MS ACCESS SQL Statement (from a report that works in Access)

SELECT SAMPLES.[Lab#], SAMPLES.RefID, SAMPLES.SampleID,
Monitoring_Wells.Facility_ID, Monitoring_Wells.TestSiteID,
Monitoring_Wells.Type, Monitoring_Wells.Ground_Water_Class,
Monitoring_Wells.Plant_Name, TESTGROUPS.TestGroupID, SAMPLES.Collect_Date
AS Sample_Date, TESTS.TestID, TESTS.TestName, TESTS.TMisc2 AS Storet_Code,
Monitoring_Wells.Sampling_Method, TESTGROUPS.Anal_Method,
TESTGROUPS.Container_Preservative, TESTGROUPS.SortNum, TESTS.SortNo,
TESTS.ReportedResult, TESTS.Units, TESTS.MDL, TESTS.Flag
FROM ((Monitoring_Wells INNER JOIN SAMPLES ON Monitoring_Wells.SampleID =
SAMPLES.SampleID) INNER JOIN TESTGROUPS ON SAMPLES.[LIMS#] =
TESTGROUPS.[LIMS#]) INNER JOIN TESTS ON (TESTGROUPS.TestGroupID =
TESTS.TestGroupID) AND (TESTGROUPS.[LIMS#] = TESTS.[LIMS#])


WHERE (((SAMPLES.RefID) Not Like "*-E") AND ((TESTGROUPS.TestGroupID) Not
Like "601*") AND ((SAMPLES.Collect_Date) Between [Enter Beginning Sample
Date] And [Enter Ending Sample Sample Date]) AND ((TESTS.TestID) Not Like
"*#*"));


I could go on, but for the sake of brevity I have a couple of questions:

1) Are CR SQL Commands usable in report development?

2) If they are usable, where can I locate a good reference book that explains in detail how to use "Crystal SQL" commands? I've looked everywhere and can't find anything but an intro to the SQL command basics.

3) One of the things that really bothers me in CR is that I can't view the FINAL record set that gets produced from the SQL command and any CR modules or crystal or VB programming you may have employed in the report. I used to produce reports by first isolating the required elements in a SQL query and then designing the format of the report. I can't find a facility in CR to do this? Does one exist?

Someone told me to develop/perfect the SQL query in the native environment first, and then bring the query over in CR, but that technique appears to be unusable. CR typically chokes on the syntax, or SQL code that is not standard ANSI. I've discovered that I can adjust the Access SQL code/syntax to get CR to accept the SQL statement as correct, but often when it runs, it no longer returns the correct (same) record set!!!

Thanks in advance for any advice you can provide.

 

[notaprogrammer49]

I've set aside SQL commands for now and have created the Crystal report using a VIEW as the data source, which utilizes the (exact same) SQL query (as the original MS Access report) in it's native environment. And it works!!!

It even brings across the parameter for selection of the report date range (Enter Beginning Date, Enter Ending Date), which I could never get to work within a Crystal SQL command.

This will create some logistical problems for report distribution as the attached query does not reside on the SQL server box (the application uses a local Access database with an ODBC connection to the SQL server), so I will have to work this out. (Soon, we have been promised a network Crystal repository which should help.)

Still like to be able to use SQL commands, but for now I'll stick with views. Hope this information is helpful to other novices out there.

Must be an advanced Crystal course somewhere? I've been to two so far, and either has covered views and other connections.