CODEWITHSUNDEEP
sqlcrystal-reports
Joshua Daiter
Joshua Daiter

Reputation: 51

Optional parameter in report SQL statement

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;

Upvotes: 0

Views: 1082

Answers (3)

4444
4444

Reputation: 3680

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}

Upvotes: 0

bbsimonbb
bbsimonbb

Reputation: 28992

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)))

Upvotes: 1

Ankur Alankar Biswal
Ankur Alankar Biswal

Reputation: 1182

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..

Upvotes: 0

Related Questions