Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations TouchToneTommy on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Parameter ???s Crystal XI R2

Status
Not open for further replies.

pungigis

Programmer
Dec 5, 2007
71
US
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???
 
(
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
 
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.
 
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
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top