asked on

Date Parameters in SSRS 2005

HI Below is my code in SSRS 2005. I want to create 3 parameters.. the first two are 'from and to' dates based on
b.fromdate and b.untildate respectively (in the form of Calender) and the thrid parameter should be a drop down list of costcentre (c.costcentre)..

The default display order for results in this report is by ascending Applicant Reference, with the option for users to choose to sort by Surname, Check Status and Check Result.
The report will contain a row per applicant vacancy where the vacancy falls between the dates specified. Hence, there may be zero, one or more rows for an individual.

Can anybody help me out please ? Many thanks...

select a.APPLIC_REF as ApplicantReference , a.FORENAME,a.SURNAME,a.BIRTHDATE,
b.fromdate,b.untildate,c.JOB_NAME as jobname, c.COSTCENTRE,d.BRANCH,
e.CHECK_DATE as CRBdate,e.CHECK_STATUS as CRBstatus,e.CHECK_RESULT as crbresult,e.CHECK_DATE as photodate,
e.CHECK_STATUS as photostatus,e.CHECK_RESULT as photoresult,e.CHECK_DATE as meddate,
e.CHECK_STATUS as medstatus,e.CHECK_RESULT as medresult, e.CHECK_DATE as OSPCdate,
e.CHECK_STATUS as OSPCstatus , e.CHECK_RESULT as OSPResult
from dbo.APPLIC a join dbo.APPLICANT_BACKGROUND  e on a.APPLIC_REF=e.APPLIC_REF
join dbo.APPVAC b on b.APPLIC_REF=a.APPLIC_REF and b.APPLIC_REF=e.APPLIC_REF
join dbo.VACANCY c on c.VAC_REF=b.VAC_REF
join dbo.LOCATION d on d.LOC_REF=c.LOCATION 
order by a.applic_ref

Open in new window

ASKER CERTIFIED SOLUTION

Auric1983

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

gvamsimba

ASKER

thanks auruk and then what abt this requirement ?
The default display order for results in this report is by ascending Applicant Reference, with the option for users to choose to sort by Surname, Check Status and Check Result

SOLUTION

Auric1983

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

gvamsimba

ASKER

HI Auric, Your parameters in the code did not give me the right figures. but below is the parameters which i used and i got my report correctly..

where c.costcentre in (@costcentre)
and (b.fromdate BETWEEN (@StartDate) AND (@EndDate))
and (b.untildate BETWEEN (@StartDate) AND (@EndDate))

Thanks

Auric1983

Sorry - I wasn't 100% clear on how you wanted to use the parameters. It's still early in the morning here and I've only had one cup of coffee...

Glad you were able to change it around a little bit to suit your needs.