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

You may also like

Transparent login between ASP.NET web sites

Consider this scenario: you have 2 or more ASP.NET websites hosted under different sub-domains which use the same SQL database for their “forms” type of authentication.  It would be nice for the user to login just once and transparently use the same authentication cookie again and again (if it is not yet expired).

Keep Reading

Windows 7 + VB6 + Standard User’s + The Registry = Arg!

I have spent the last couple of weeks assisting one of our client re-mediate some of the hundreds of VB6, Word, Excel and Outlook VBA add-ins in preparation for their XP to Windows 7 upgrade. One of the earliest issues we ran into was for verifying registry changes made. It looked like the registry keys these add-ins modify were not being saved.

Keep Reading

Newsletter sign up

Every couple of months we send out an update on what's been happening around our office and the web. Sign up and see what you think. And of course, we never spam.