Parameter ???s Crystal XI R2

[pungigis]

Have a couple questions here, can I set a list of values for a parameter in a command???

I have a sql command as follows...
(SELECT M.Received, Count(M.Number) AS ActiveAccts, SUM(M.Current1) AS ActiveAmt, c.company, c.cob, c.customer
FROM dbo.Master M (NOLOCK)
INNER JOIN dbo.Customer C (NOLOCK)
ON M.Customer = C.Customer
INNER JOIN dbo.Status S (NOLOCK)
ON M.Status = S.Code
WHERE
(C.Company = '{?Company Name}'
or ('{?Customer}' = 'All' or C.Customer = '{?Customer}'))
AND C.COB = '{?COB}'
AND S.StatusType <> '1 - CLOSED'
GROUP BY M.Received)

It is not working, it is not returning anything regardless of what I put into the prompts, what I want to be able to do is type in the COB AND EITHER the Co Name OR Customer but not both. Is all even a required part to accomplish this???

 

[lbass]

(
SELECT M.Received, Count(M.Number) AS ActiveAccts, SUM(M.Current1) AS ActiveAmt [red], c.company, c.cob, c.customer[/red]

FROM ((dbo.Master M (NOLOCK)
INNER JOIN dbo.Customer C (NOLOCK)ON M.Customer = C.Customer)
INNER JOIN dbo.Status S (NOLOCK)ON M.Status = S.Code)

WHERE
(
(
'{?SelectBy}' = 'Company' and
C.Company = '{?Company Name}'
) or
(
'{?SelectBy}' = 'Customer' and
C.Customer = '{?Customer}'
)
)AND
C.COB = '{?COB}' AND
S.StatusType <> '1 - CLOSED'

GROUP BY M.Received
)

I also added parens in the From clause. I also don't think you can build in the fields in red if you are doing a 'group by' without erroring out. If you need those fields, then I think you have to set up the summaries as subqueries within the command.

-LB

 

[pungigis]

Ok, can i do a dynamic list of values in the command, the user will not always know exactly what the input should be??? Also, is it better to set up parameters like this in the command or inside crystal, the reason i ask this is there are 18 sqls that make up one report.

 

[lbass]

You can create the parameter within the command (best practice). You could also potentially create a separate (unlinked) command to use for populating a dynamic pick list. You would execute the command and then in the main report, you will see the parameter listed. You can then edit the parameter and add the pick list. I haven't done this in a while, so there might be some glitches in the process--I can't recall.

Don't know what you mean by "18 sqls"--why would this be the case? Maybe you should be using subreports.

-LB