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

gvamsimbaAsked:
Who is Participating?
 
Auric1983Commented:
gvamsimba

You will need to modify your SQL Query accordingly but here goes.

1. Modify Query to include parameters as described below - After you modify your query to include the parameters BIDS is usually intelligent enough to automatically create the parameters for you.  

2.  Click on Reports > Report Parameters  you should see three parameters listed, select the @FromDate parameter and ensure the data type is set to Date Time, repeat for the @UntilDate and click OK.

Now to create the dropdown for your cost centre

Create a new dataset in the report by click on the DataTab drop down the dataset name and select "new dataset"

We need to create a list of all of your cost centres as described below.

1. Create the dataset for the costcenter list using the query below
2. Click on Report > Report Parameters select CostCentre
3. Select "Available Values" > From Query

You simply  need to select the Dataset, the label field and the value field (in this case both costcentre)
This will create the dropdown for your costcentres



-- Modified Query w/ Parameters 
 
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 
where b.fromdate = @FromDate and b.Untildate=@untildate 
and c.costcentre=@Costcentre
 
order by a.applic_ref
 
--- Query for costcentre 
 
select distinct costcentre from VACANCY 

Open in new window

0
 
gvamsimbaAuthor Commented:
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
0
 
Auric1983Commented:
gvamsimba, if you have the fields laid out in a table object you can actually setup dynamic sorting ability by right clicking on the column header and selecting interactive sort. You will be asked to select a fieldname to sort by so for surname you could use =Fields!Surname.value

http://www.simple-talk.com/sql/learn-sql-server/beginning-sql-server-2005-reporting-services-part-2/
0
 
gvamsimbaAuthor Commented:
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
0
 
Auric1983Commented:
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.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.