Creating Parameters in SQL and pulling to Crystal

[former_member268870]

Experts,

I have created the following Stored Procedure, but are having difficulty pulling this parameter into Crystal. This is a Income Statement that should pull by User Cost Center entry.

First, is this the best way to do this?

If so, how do I use this SQL Parameter in Crystal?

I have linked the SP and created a Parameter, but then the report is blank. I have tried pasting this in the Command, but also did not get data. When I deactivate the Parameter in SQL and Crystal, then I can see the data.

I'm hoping to build this as to make the run time as short as possible.

I appreciate your input,

Marli

USE [TESTDB]
GO
/****** Object:  StoredProcedure [dbo].[yRptSP_IncomeStatementbyOperations]    Script Date: 2/20/2018 10:07:49 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[yRptSP_IncomeStatementbyOperations] 
AS 
BEGIN
/*SELECT FROM [dbo].[OACT] T0*/
DECLARE @Operations as nvarchar
/* WHERE */
set @Operations = /* T0.OcrName */ '[%0]'


;
With QRY as
------------
--Frist query to get Debit and Credits (Balance) for JE posted into IS accounts
------------
(SELECT   T0.FatherNum
	, T0.FormatCode
	, T0.AcctName
	, SUM(T1.Credit - T1.Debit) AS Balance
	, T3.OcrCode
	, T3.OcrName
	, T4.OcrCode AS Expr1
	, T4.OcrName AS Expr2
	, T2.RefDate
	, T0.[GroupMask]
FROM     dbo.OACT AS T0 INNER JOIN
             dbo.JDT1 AS T1 ON T0.AcctCode = T1.Account INNER JOIN
             dbo.OJDT AS T2 ON T1.TransId = T2.TransId LEFT OUTER JOIN
             dbo.OOCR AS T3 ON T0.OverCode = T3.OcrCode LEFT OUTER JOIN
             dbo.OOCR AS T4 ON T0.OverCode2 = T4.OcrCode
	WHERE  (T0.[GroupMask] IN (8, 7, 6, 5, 4)) 
		AND (DATEDIFF(YY, T2.RefDate, GETDATE()) = 0) AND (T1.Debit <> T1.Credit)
	GROUP BY T0.[GroupMask], T0.FatherNum, T0.AcctName, T0.FormatCode, T3.OcrCode, T3.OcrName, T4.OcrCode, T4.OcrName, T2.RefDate
	HAVING   (SUM(T1.Debit - T1.Credit) <> 0)


),
------------
--Second query to get Total by Level 3 account
------------
Totals as 
(SELECT T0.FatherNum
	, SUM(T1.Credit) AS CreditTotal
	, SUM(T1.Debit) AS DebitTotal
	, SUM(T1.Credit) - SUM(T1.Debit) AS TotalbyFather
	FROM     dbo.OACT AS T0 INNER JOIN
             dbo.JDT1 AS T1 ON T0.AcctCode = T1.Account INNER JOIN
             dbo.OJDT AS T2 ON T1.TransId = T2.TransId 
	WHERE  (T0.[GroupMask] IN (8, 7, 6, 5, 4)) 
		AND (DATEDIFF(YY, T2.RefDate, GETDATE()) = 0) 
		AND (T1.Debit <> T1.Credit)
GROUP BY T0.FatherNum
)


SELECT 'TestDB' as company
	, c.[GroupMask]
	, c.FatherNum
	, c.FormatCode
	, c.AcctName
	, Balance
	, c.OcrCode
	, c.OcrName
	, c.OcrCode AS Expr1
	, c.OcrName AS Expr2
	, Totals.TotalbyFather
	, c.RefDate
	FROM   qry c left outer join Totals
	on c.FatherNum = Totals.FatherNum
	WHERE c.OcrName = @Operations
	ORDER BY c.[GroupMask]


END

[Former Member]

You should not have to create a Parameter. When you create a report with an SP Crystal should detect and allow you to enter parameter values.

Alternatively you can use a Command which executes the SP. In the Command dialog box you can create Parameters to run SP eg.

exec yourSP param1, param2

In both cases do not create report parameters as they will not be used by SP or Command, they can only be used to control data locally eg conditional suppression etc.

Ian