Problems with Parameters in Select Statement

[PTW]

I am trying to create and distribute a report with Crystal Reorts 7.0 Pro. The user needs to be able to enter a date range.

In very simple tests, I can get this to work simply by adding a date range parameter and some code to the record selection formula. However, on the actual report I need to use a UNION and some subqueries, and it seems too complex to work with the record selection formula.

Here is the working SELECT statement with the dates hard-coded in (I apologize for the length). The ones I want the user to change are the DOCDATE fields:

SELECT PM00200.VENDNAME, PM20000.VCHRNMBR, PM20000.VENDORID, PM20000.DOCTYPE, PM20000.DOCDATE, PM20000.DOCNUMBR, PM20000.DOCAMNT, PM20000.TRXSORCE
FROM
CDI.dbo.PM20000 PM20000, CDI.dbo.PM00200 PM00200
WHERE
(SELECT COUNT(B.DOCNUMBR) FROM CDI.dbo.PM20000 B WHERE B.DOCDATE = PM20000.DOCDATE AND B.DOCAMNT = PM20000.DOCAMNT AND B.DOCNUMBR = PM20000.DOCNUMBR AND B.DOCTYPE = 1 AND B.VOIDED = 0) > 1
AND PM20000.VENDORID = PM00200.VENDORID
AND PM20000.DOCDATE >= '05/01/2000'
AND PM20000.DOCDATE <= '11/09/2000'
AND PM20000.VOIDED = 0
AND (PM20000.DOCTYPE = 1 OR PM20000.DOCTYPE = 5)

UNION

SELECT
PM00200.VENDNAME, PM30200.VCHRNMBR, PM30200.VENDORID, PM30200.DOCTYPE, PM30200.DOCDATE, PM30200.DOCNUMBR, PM30200.DOCAMNT, PM30200.TRXSORCE
FROM
CDI.dbo.PM30200 PM30200, CDI.dbo.PM00200 PM00200
WHERE
(SELECT COUNT(B.DOCNUMBR) FROM CDI.dbo.PM30200 B WHERE B.DOCDATE = PM30200.DOCDATE AND B.DOCAMNT = PM30200.DOCAMNT AND B.DOCNUMBR = PM30200.DOCNUMBR AND B.DOCTYPE = 1 AND B.VOIDED = 0) > 1
AND PM30200.VENDORID = PM00200.VENDORID
AND PM30200.DOCDATE >= '05/01/2000'
AND PM30200.DOCDATE <= '11/09/2000'
AND PM30200.VOIDED = 0
AND (PM30200.DOCTYPE = 1 OR PM30200.DOCTYPE = 5)

I have tried before to use the syntax &quot;DOCDATE >= {?DateRange}&quot; where DateRange is a parameter, but this does not work.

Is there any other way I can try to get parameters into the SELECT statement other than through the record selection formula? Thank-you for any help...I've spent days trying to get this to work!

 

[Caren]

I prefer to have them enter 2 separate parameter fields, one for the begin date and one for the end date. I find that it is easier to work with than working with a range. Define each parameter as discrete rather than a range. In either case the user still needs to enter both dates. Another advantage of this is that you can display the criteria in the heading. That doesn't work with a range. I know this doesn't really answer your question but may help.

 

[MalcolmW]

The SQL statement that you put in your post - is that what you are using in SCR, or are you basing your report on a Crystal Query that has this SQL in it?
I presume from your post that you have manually editted the SQL statement in SCR. Because of this, SCR will no longer attempt to make changes to the SQL statement beyond the initial select statement - hence, substituting values for parameters is out of the question.
To avoid this problem, you will need to base your report either on a Crystal Query, a view, or a stored procedure. You can pass parameters to all of those. Malcolm
wynden@telus.net
November is &quot;be kind to dogs and programmers&quot; month. Or is that &quot;dogs or programmers&quot;?

 

[PTW]

Thanks Malcolm. I created the Select statement in Crystal Query, and it properly prompted me for the parameters. However, when I built a report based on the Query, I was NOT prompted for the parameters. Is there something else I have to do to make that work?

Thanks again!

 

[MalcolmW]

I'll have to wait to I get back to a machine that has SCR installed - I normally used stored procs, so I don't know that one off the top.
Anyone else familiar with Crystal Query and parameters? Malcolm
wynden@telus.net
November is &quot;be kind to dogs and programmers&quot; month. Or is that &quot;dogs or programmers&quot;?