Recently I needed to update several reports which are developed in Crystal 8.5 and draw data from SQL 2008 tables. The reports can be called from within a CRM Windows desktop application and the user can apply filtering (for example date range) before viewing or printing the report.
In this case details from several different entities had to be combined in the one report and the creation of a view in the SQL database was the obvious solution. I chose to use UNION statements to link several select statements together to produce a neat and easy to read SQL query. All worked well in the stand alone Crystal report, but as soon as the report was called from the Windows application and valid filtering was applied the user was presented with the message “There are no records to report on”.
It was back to the drawing board. To avoid UNION statements I had to link all tables using LEFT OUTER JOIN and nest fields using ISNULL statements to consolidate values. See sample code below.
ISNULL(ISNULL(ISNULL(Complaints.EntityName, AuditReportDriveby.TradesAs), SalesOrder.ShipTo), Organisation.OrgName) AS OrgName
LEFT OUTER JOIN dbo.AcctMgr ON ISNULL(ISNULL(ISNULL(AuditReportV2.Auditor, AuditReportDriveby.Auditor), Complaints.Auditor), AuditReportV2.Auditor) = dbo.AcctMgr.IdNo OR SalesOrder.OpCode = dbo.AcctMgr.OpCode