Passing a date range parameter back to SQL Command Object

[muffntuf]

Hi,

I tried this before but need to try again. I need to have a report group on Quarters, but be able to run the report with either a selection on the Quarters group or by a date range -- BUT I need to pass the parameter back from the report selection criteria to the SQL COMMAND OBJECT which has the same date parameter range in several inline views within the command object: Here is an example of part of the code:
SELECT DISTINCT

ARH_MAIN.INV_NBR,
ARH_Main.Primary_So_Nbr,
ARH_Main.DataSourceCode,


MAX(GLT_Main.Tran_Date) Tran_Date,

GLT_Main.Single_Document,

SALES.GROSSSALES 'GROSS SALES',

ISNULL(SALES.SKATE,0) 'SKATE',
ISNULL(SALES.BASICS,0) 'BASICS',
ISNULL(SALES.PLAY,0) 'PLAY',

ISNULL(OSD.OSDAMT,0) 'OTHER SALES DISCOUNTS',


'AREA' =
CASE
WHEN REPS_MAIN.Rep_Division = '1' THEN 'North America'
WHEN REPS_MAIN.Rep_Division = '3' THEN 'International'
WHEN REPS_MAIN.Rep_Division = '4' THEN 'National'
WHEN REPS_MAIN.Rep_Division = '6' THEN 'OEM'
WHEN REPS_MAIN.Rep_Division = '5' THEN 'Basics'
ELSE 'Unknown' END,

ISNULL(ARH_MAIN.Primary_sls_Rep,'NA') 'PrimaryRep',
ISNULL(REPS_MAIN.Name,'NA') 'RepName',
ISNULL(SOH_MAIN.Ord_Type_Desc,'NA') 'OrderType'

FROM dbo.M2K_ARH_Main ARH_MAIN

-- GLTMain
INNER JOIN dbo.M2K_GLT_Main GLT_Main
ON ARH_Main.ID = GLT_Main.Single_Document
AND GLT_Main.DataSourceCode = ARH_Main.DataSourceCode and
GLT_Main.Tran_Date between '2005-08-01'and '2005-08-31'

-- REPS
INNER JOIN dbo.M2K_SOH_Main SOH_Main
ON SOH_Main.So_Nbr=ARH_Main.Primary_So_Nbr
AND SOH_Main.DataSourceCode=ARH_Main.DataSourceCode

INNER JOIN dbo.M2K_SALES_REPS_Main REPS_MAIN
ON SOH_MAIN.PRIMARY_REP = REPS_MAIN.ID
AND SOH_MAIN.DATASOURCECODE = REPS_MAIN.DATASOURCECODE

--GROSS SALES
LEFT OUTER JOIN

(SELECT DISTINCT

GLT_Main.Single_Document,
GLT_Main.DataSourceCode,

'GROSSSALES' =
SUM(ISNULL(GLT_Dist.Cr_Amt,0) - ISNULL(GLT_Dist.Db_Amt,0)),

--DIVISION
'SKATE'=
SUM(CASE WHEN RIGHT (GLT_Dist.Acct_Nbr,2) = '97'
THEN ISNULL(GLT_Dist.Cr_Amt,0) - ISNULL(GLT_Dist.Db_Amt,0) ELSE 0 END),
'BASICS'=
SUM(CASE WHEN RIGHT (GLT_Dist.Acct_Nbr,2) = '95'
THEN ISNULL(GLT_Dist.Cr_Amt,0) - ISNULL(GLT_Dist.Db_Amt,0) ELSE 0 END),
'PLAY'=
SUM(CASE WHEN RIGHT(GLT_Dist.Acct_Nbr,2) <>'95'
OR RIGHT(GLT_Dist.Acct_Nbr,2)<>'97'
THEN ISNULL(GLT_Dist.Cr_Amt,0) - ISNULL(GLT_Dist.Db_Amt,0) ELSE 0 END)

FROM dbo.M2K_GLT_Main GLT_Main

LEFT OUTER JOIN dbo.M2K_GLT_Distribution GLT_Dist
ON GLT_Dist.ID = GLT_Main.ID
AND GLT_Dist.DataSourceCode = GLT_Main.DataSourceCode
AND (GLT_Dist.Acct_Nbr between '01-3000-00-00-00' and '01-3999-99-99-99'
or GLT_Dist.Acct_Nbr between '02-3000-00-00-00' and '02-3999-99-99-99')

WHERE
GLT_Main.Single_Document IN
(SELECT DISTINCT ARH_Main.ID FROM dbo.M2K_ARH_Main ARH_MAIN
WHERE ARH_Main.DataSourceCode = 'LS')
AND GLT_Main.DataSourceCode = 'LS'
AND GLT_Main.Tran_Date between '2005-08-01'and '2005-08-31'

GROUP BY
GLT_Main.Single_Document,
GLT_Main.DataSourceCode

)SALES
ON SALES.Single_Document = ARH_MAIN.INV_NBR
AND SALES.DataSourceCode = ARH_Main.DataSourceCode
-- OTHER SALES DISCOUNTS

LEFT OUTER JOIN
(SELECT DISTINCT

GLT_Main.Single_Document,
GLT_Main.DataSourceCode,

'OSDAMT' = SUM(ISNULL(GLT_Dist.Cr_Amt,0) - ISNULL(GLT_Dist.Db_Amt,0))

FROM dbo.M2K_GLT_Main GLT_Main

INNER JOIN dbo.M2K_GLT_Distribution GLT_Dist
ON GLT_Dist.ID = GLT_Main.ID
AND GLT_Dist.DataSourceCode = GLT_Main.DataSourceCode
AND (GLT_Dist.Acct_Nbr between '01-4000-00-00-%' and '01-4600-99-00-%')

WHERE
GLT_Main.Single_Document IN
(SELECT DISTINCT ARH_Main.ID FROM dbo.M2K_ARH_Main ARH_MAIN
WHERE ARH_Main.DataSourceCode = 'LS')
AND GLT_Main.DataSourceCode = 'LS'
AND GLT_Main.Tran_Date between '2005-08-01'and '2005-08-31'

GROUP BY
GLT_Main.Single_Document,
GLT_Main.DataSourceCode


) OSD
ON OSD.Single_Document = ARH_MAIN.INV_NBR
AND OSD.DataSourceCode = ARH_Main.DataSourceCode

WHERE
ARH_Main.DataSourceCode = 'LS'


GROUP BY
ARH_MAIN.INV_NBR,
ARH_Main.Primary_So_Nbr,
ARH_Main.DataSourceCode,

GLT_Main.Single_Document,

CASE
WHEN REPS_MAIN.Rep_Division = '1' THEN 'North America'
WHEN REPS_MAIN.Rep_Division = '3' THEN 'International'
WHEN REPS_MAIN.Rep_Division = '4' THEN 'National'
WHEN REPS_MAIN.Rep_Division = '6' THEN 'OEM'
WHEN REPS_MAIN.Rep_Division = '5' THEN 'Basics'
ELSE 'Unknown' END,

ISNULL(ARH_MAIN.Primary_sls_Rep,'NA'),
ISNULL(REPS_MAIN.Name,'NA'),
ISNULL(SOH_MAIN.Ord_Type_Desc,'NA'),

SALES.GROSSSALES,
ISNULL(SALES.SKATE,0),
ISNULL(SALES.BASICS,0),
ISNULL(SALES.PLAY,0),


ISNULL(OSD.OSDAMT,0)



So Crystal I want to handle the Quarters grouping based off a date and I need the user to be able to put in the date range parameter.

I need to know how to pass the parameter back from Crystal to the SQL Command Object, if some would be so kind, and how to set up the parameter in the command object.

Thanks,
muffntuf

 

[MisterMo]

go to the command dialog box, on the right side you have the option to create parameters create them and drag them on the left panel where you SQL script is simply replace the hardcode value with it

e.g.

where (table.fielddate = 10/10/2005)
will become
where (table.fielddate = @NewParam)

Mo

 

[muffntuf]

Thank you, that takes care of the SQL Command code, do I just add a parameter(s) for start and end in Crystal?

Thanks,
muffntuf

 

[muffntuf]

Forget it I see it adds it automatically.