Question Re: SQL calculation in expression editor CR 2008 in Technology Q&A

SQL calculation in expression editor CR 2008

Former Member — Wed, 10 Feb 2010 13:36:37 GMT

This SQL expression works on MS SQL Server 2005 but not in the SQL Expression Editor of Crystal Reports.

Why? Any alternatives?

(
declare @Trash Decimal
declare @Done Decimal

set @Trash =
(
  select count(*) from dbo.NL_NM_NUON 
  WHERE MAN_CAMPAIGNNAME = '9.304_Winback TM CM Jan 2010 - Bosch'  AND
  (
    fldFormalities In ('Persoon is verhuisd', 'Adres matcht niet met telefoonnummer',
	    'Weigering Gesprek', 'Dubbel in bestand', 'Persoon overleden',
	    'Beslisser tijdens actieperiode afwezig')
    OR
    fldEndresult = 'Is sinds kort klant bij NUON'
  )
)

set @Done =
(
  select count(*) from 
  NL_NM_NUON Where
  NL_NM_NUON.MAN_CAMPAIGNNAME = '9.304_Winback TM CM Jan 2010 - Bosch' AND
  STATUS IN ('U','F')
)

Select (@Trash / @Done) * 100
)

Edited by: Valmont on Feb 10, 2010 2:36 PM

Re: SQL calculation in expression editor CR 2008

Former Member — Wed, 10 Feb 2010 16:54:20 GMT

Create two different sql expressions like this

%Expression1:

(
  select count(*) from dbo.NL_NM_NUON 
  WHERE MAN_CAMPAIGNNAME = '9.304_Winback TM CM Jan 2010 - Bosch'  AND
  (
    fldFormalities In ('Persoon is verhuisd', 'Adres matcht niet met telefoonnummer',
	    'Weigering Gesprek', 'Dubbel in bestand', 'Persoon overleden',
	    'Beslisser tijdens actieperiode afwezig')
    OR
    fldEndresult = 'Is sinds kort klant bij NUON'
  )

%Expression2:

(
  select count(*) from 
  NL_NM_NUON Where
  NL_NM_NUON.MAN_CAMPAIGNNAME = '9.304_Winback TM CM Jan 2010 - Bosch' AND
  STATUS IN ('U','F')
)


Now Create a formula like this

({%Expression1}/{%Expression2})*100

HTH

Raghavendra.G

Re: SQL calculation in expression editor CR 2008

Former Member — Thu, 11 Feb 2010 07:22:11 GMT

Yeah I did but was hoping I could I do it in a single query. Just to experiment with it.

Thanks!