Optional parameter in report SQL statement

Question

I would like to have a report run on the entire previous month if no parameter values are passed through to the report. How do i do this?

Another option would be to have it load into the previous month by default and then when refreshed, new parameters for dates can be selected.

Is there a way to do either of these?

This is my command window. My date paramter selects the SQL code.

select cc.NM, dst.DSCRPTN, count(*)
from DST_SUBMITTION dsts , PATIENT p, DRUG_SCREEN_TEST dst, CARE_CENTER cc
where dsts.PTNT_ID = p.PTNT_ID
and   p.CC_ID = cc.CC_ID
and   dsts.DST_ID = dst.DST_ID
and   date(APNTMNT_DT) >=  date({?startDt})
and   date(APNTMNT_DT) <=  date({?endDt})
and dsts.STS_CD in ('A', 'I')
group by cc.NM, dsts.DST_ID;

bbsimonbb · Accepted Answer · 2016-08-09 16:08:22Z

1

You can do this entirely in your SQL. Use the null coalescing operator, then calculate your default if no value is supplied. Something like...

DATE >= ISNULL(?StartDate, DATEADD(m,-1,DATEADD(mm, DATEDIFF(m,0,GETDATE()), 0)))
AND DATE <= ISNULL(?EndDate, DATEADD(d,-1,DATEADD(mm, DATEDIFF(m,0,GETDATE()),0)))

answered Aug 9, 2016 at 16:08

bbsimonbb

29.3k18 gold badges92 silver badges129 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Joshua Daiter Over a year ago

So looking at my query above, how would i go about this approach. Where would i put the code you wrote into that query?

bbsimonbb Over a year ago

On lines 6 and 7 of your query. You'll need to adjust it to fit. I'm using TSQL, it looks like you might be using something else, but the principal should be the same.

Joshua Daiter Over a year ago

Will this handle the case where it isnt NULL? Say they enter date values it should use those values

bbsimonbb Over a year ago

That's the idea. The ISNULL function returns the first argument, if present, else the second. So put your user entered parameter first, then, if that's null, the "default" second argument will be used. This is a very handy technique that gets you out of tons of trouble.

Joshua Daiter Over a year ago

This actually cant work because i cannot use an optional parameter when using the command SQL

|

Ankur Alankar Biswal · Accepted Answer · 2016-08-09 15:24:19Z

0

Create a parameter in crystal report as bellow and make "optional prompt" is TRUE. In selection export condition apply this type of formula.

if  hasvalue({?Date parameter}) then 
{table.Date} <= {?Date parameter}
else 
{table.Date} <= DateAdd ("m", -1, CurrentDate)

by this if nothing is selected in the parameter you will get the previous month result..

answered Aug 9, 2016 at 15:24

Ankur Alankar Biswal

1,1821 gold badge12 silver badges24 bronze badges

2 Comments

Joshua Daiter Over a year ago

What do i use the selection expert with? Like what table value?

Ankur Alankar Biswal Over a year ago

ok.. if u r using command then use the field which is having the date value instead of {table.Date}.

4444 · Accepted Answer · 2016-08-09 20:11:59Z

0

I would set up a formula to determine if the parameter is null. Then replace every instance of the parameter in your report with the new formula. Something like:

If HasValue({DateParameter})
Then DateAdd ("m", -1, CurrentDate)
Else {Parameter}

edited Aug 9, 2016 at 20:11

answered Aug 9, 2016 at 13:55

4444

3,73010 gold badges35 silver badges47 bronze badges

2 Comments

4444 Over a year ago

Ah, you mean a SQL parameter. That's less of a Crystal Reports question and more of a SQL-based question. I'll update your question to include that command and a [SQL] tag so you can get some more eyes on this

Joshua Daiter Over a year ago

Yea, SQL parameter depending on the crystal reports user input parameters

Collectives™ on Stack Overflow

Optional parameter in report SQL statement

3 Answers 3

6 Comments

2 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

6 Comments

2 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related