Hello all,
I need to generate a report based on dates(from and to). Is there
anyway I can include a dropdown of datetime in the parameters of the RS
interface.
for example like from mm/dd/yyyy to mm/dd/yyyy.
I have RS 2003 EE with SP1 and have no plans of installing SP2.
Any help will be appreciated.
Thanks a lot
RaviRavi,
You need to create a list of dates as a DS, and then add two params to the
report (well, just add them to the DS for the report itself) and configure
the Params to be query selections from the Dates DS.
Using Adventurewroks DB, the following suffices for selecting particular
records of SalesOrderID, ModifiedDate columns from the SalesOrderDetail
table, by date.
DataSets:
DSDates:
SELECT ModifiedDate, CONVERT(char(20), ModifiedDate, 101) AS dateselect
FROM dbo.SalesOrderDetail
GROUP BY ModifiedDate
ORDER BY ModifiedDate
DS1:
SELECT SalesOrderID, ModifiedDate
FROM dbo.SalesOrderDetail
WHERE (ModifiedDate > @.STARTDATE AND ModifiedDate < @.ENDDATE)
Form:
Table with DS1 as it's source.
Remember to edit the params in Report-Report Parameters, for both @.STARTDATE
AND @.ENDDATE, to be;
From Query; Dataset:DSDates;ValueField:ModifiedDate;LabelField:dateselect.
If you leave out the dateselect column from the first DS then the dropdown
will default to 00:00:00 time, and looks awful.
Hope this helps,
Tony
"Ravi R" wrote:
> Hello all,
>
> I need to generate a report based on dates(from and to). Is there
> anyway I can include a dropdown of datetime in the parameters of the RS
> interface.
> for example like from mm/dd/yyyy to mm/dd/yyyy.
> I have RS 2003 EE with SP1 and have no plans of installing SP2.
> Any help will be appreciated.
> Thanks a lot
> Ravi
>|||Ravi,
In addiditon to my last reply, if you want to make 'intelligent' parameters,
then your end date should be greater than your start date.
To achieve this, complete tasks as per my previous reply and add the
following;
Create a further DS called DSDates2.
SELECT ModifiedDate, CONVERT(char(20), ModifiedDate, 101) AS dateselect
FROM dbo.SalesOrderDetail
GROUP BY ModifiedDate
HAVING (ModifiedDate > @.STARTDATE)
ORDER BY ModifiedDate
and change the source for the @.ENDDATE parameter to point to DSDates2.
You will then find the End Date drop down is disabled until the Start Date
is selected.
If you wish, you can set a default for the Start Date as being the first
date found in the table (Use Top 1 selected from DSDates) for extra
useability.
Hope this assists further,
Tony
"Ravi R" wrote:
> Hello all,
>
> I need to generate a report based on dates(from and to). Is there
> anyway I can include a dropdown of datetime in the parameters of the RS
> interface.
> for example like from mm/dd/yyyy to mm/dd/yyyy.
> I have RS 2003 EE with SP1 and have no plans of installing SP2.
> Any help will be appreciated.
> Thanks a lot
> Ravi
>|||thanks logicalman,
will try that first thing on monday.
Ravi
