CODEWITHSUNDEEP
sql-server-2008-r2crystal-reportsparameter-passing
Jeremy McDevitt
Jeremy McDevitt

Reputation: 115

Having a crystal report parameter as a declared SQL Value

I'm assuming this is a simple question, but I don't know Crystal Reports very well. I made a SQL Query which uses the declared dates fields of @beginning_date and @ending_date and I want Crystal to prompt for those fields when run. I added the paramter field in crystal and named it the same thing, but I'm unsure how to get them to sync up. My code is below. Thank you in advance. 

--/*
DECLARE @beginning_date char(20)
DECLARE @ending_date    char(20)

--SELECT @ending_date =  '07/31/2016' --23:59:59'
--SELECT @beginning_date = '07/01/2016' --00:00:01'
--*/

SELECT --Sum(CASE when Billing_Ledger.subtype in ('BI','ND') then
        --Billing_Ledger.amount ELSE 0 END) as 'Charges'

Patient_Clin_Tran.Clinic_id, Patient_Clin_Tran.service_id, Patient_Clin_Tran.program_id, Patient_Clin_Tran.protocol_id, billing_ledger.amount


  FROM  Billing_Ledger
    JOIN Patient_Clin_Tran ON
        Billing_Ledger.clinical_transaction_no = Patient_Clin_Tran.clinical_transaction_no
    JOIN Coverage_Plan ON
        Billing_Ledger.coverage_plan_id = Coverage_Plan.coverage_plan_id
        and Billing_Ledger.hosp_status_code = Coverage_Plan.hosp_status_code
    JOIN Payor ON
        Coverage_Plan.payor_id = Payor.payor_id
  WHERE ( Coverage_Plan.billing_type <> 'CAP' or Coverage_Plan.billing_type is null )
    and Billing_Ledger.accounting_date >= @beginning_date
    and Billing_Ledger.accounting_date < dateadd(day, 1, @ending_date)
    and Patient_Clin_Tran.Clinic_id = 'NP' and payor.name = 'Mainecare' and (Billing_Ledger.subtype in ('BI','ND'))
--GROUP BY Patient_Clin_Tran.Clinic_id, Patient_Clin_Tran.service_id, Patient_Clin_Tran.program_id, Patient_Clin_Tran.protocol_id --, Payor.Name, billing_ledger.amount, payor.type
  ORDER BY Patient_Clin_Tran.Clinic_id, Patient_Clin_Tran.service_id, Patient_Clin_Tran.program_id, Patient_Clin_Tran.protocol_id

Upvotes: 0

Views: 755

Answers (1)

user6638270
user6638270

Reputation:

Firstly you should turn your query into a stored procedure thus:

CREATE PROCEDURE [dbo].[mySPName]

@beginning_date date,
@ending_date    date

AS


SELECT Patient_Clin_Tran.Clinic_id, Patient_Clin_Tran.service_id, Patient_Clin_Tran.program_id, Patient_Clin_Tran.protocol_id, billing_ledger.amount

etc.

Now when adding the database connection to your CR report file, after selecting your server and database you will see three options tables, views and stored procedures. Simply select the new procedure from the list. CR will now automtically add the parameters, and will prompt you for values. If you leave the values blank, then at runtime CR will automatically prompt the user for these values.

You will also note that I changed the type of the parameters to date; this is necessary so that CR knows to include a calendar selector in the parameter prompt.

Upvotes: 1

Related Questions