Parameters in Crystal Report (without field - just from WHERE clausule)

[Former Member]

Hi all,

I just started with Crystal Reports. I made some examples with parameters, but I have problem with my own query:

SELECT
T0.[CardName] AS 'Klient',
count(distinct T0.[DocNum]) AS 'Ilo?? ofert',
count(distinct T3.[BaseRef]) AS 'Ilo?? zamówie?',
count(distinct T3.[BaseRef])/cast(count(distinct T0.[DocNum]) as numeric)*100 AS 'Wykorzystanie (%)'
FROM
[dbo].[OQUT] T0
LEFT JOIN [dbo].[QUT1] T1 ON T1.[DocEntry] = T0.[DocEntry]
LEFT JOIN [dbo].[ORDR] T2 ON T2.[DocEntry] = T1.[TrgetEntry]
LEFT JOIN [dbo].[RDR1] T3 ON T3.[DocEntry] = T2.[DocEntry]
WHERE
T0.[DocDate] BETWEEN CONVERT(date,DATEADD(YEAR, -1, GETDATE()),120) AND CONVERT(date,getdate(),120)
GROUP BY T0.[CardName]

Here in WHERE clausule I have data from last year. I want to change it into parameters (underline parts). But in SELECT clusule I didn't use T0.[DocDate] field (and I can't, becouse I don't want group by this field). So this field isn't in the Crystal's "Chose field dialog boxing". I tried many other things, but nothing works (I always get no data).

How it can be done?

[Former Member · Accepted solution]

When using a command you can not filter query using Report Parameters.

In the command dialog box, you will see in top left hand side - Parameter list, click create and create a Date parameter. You can then add this to your query by placing cursor where you want it to appear in query and then double clicking parameter.

This then behaves like a normal parameter.

Ian

[Former Member]

I tried this on bool operator but it didn't work, but then i realised there is some language bug an now its ok. Thx

[DellSC]

For more information about how to use commands in Crystal, please see my blog post here: https://blogs.sap.com/2015/04/01/best-practices-when-using-commands-with-crystal-reports/. This goes through how to correctly use parameters in your command.

-Dell