?
Solved

Date Parameters in SSRS 2005

Posted on 2009-04-26
5
Medium Priority
?
622 Views
Last Modified: 2012-06-27
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

0
Comment
Question by:gvamsimba
  • 3
  • 2
5 Comments
 
LVL 16

Accepted Solution

by:
Auric1983 earned 2000 total points
ID: 24240936
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
 

Author Comment

by:gvamsimba
ID: 24240969
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
 
LVL 16

Assisted Solution

by:Auric1983
Auric1983 earned 2000 total points
ID: 24240994
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
 

Author Closing Comment

by:gvamsimba
ID: 31574661
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
 
LVL 16

Expert Comment

by:Auric1983
ID: 24241702
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

Featured Post

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Hi, I have heard from my friends that it’s not possible to create Label Printing report using SSRS. I am amazed after hearing this words not possible in SSRS. I googled lot and found that it is possible to some of people know about the Report Bui…
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Despite its rising prevalence in the business world, "the cloud" is still misunderstood. Some companies still believe common misconceptions about lack of security in cloud solutions and many misuses of cloud storage options still occur every day. …
Screencast - Getting to Know the Pipeline

864 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question