Link to home
Start Free TrialLog in
Avatar of Dovberman
DovbermanFlag for United States of America

asked on

I would like to understand how to set up a date report parameter. Reporting Services

I have created a date selection drop down from which the user selects a date filter for the report.

I am having difficulty with the filter actually finding the data that fits the criteria.

Here is what I have:
Dateset that returns rows for the matrix:
Declare @StartDate Varchar(10)

SELECT ........

WHERE
  (srtr_Date >=@StartDate
 AND
 srtr_Date < DATEADD(day,1,@StartDate))

Dataset that populates the dropdown :
SELECT DISTINCT CONVERT(varchar(10),srtr_Date,101) AS StartDate FROM SiteReqTrans
ORDER BY CONVERT(varchar(10),srtr_Date,101) ASC

Returns
03/05/2008
03/06/2008
etc.

The dropdown displays correctly.

Data is stored in the table as datetime.
3/5/2008 12:46:42 PM

Data for 03/05/2008 exists. The data is returned in the matrix until I set up the report parameter.

Any ideas?
Thanks
Avatar of Bob Learned
Bob Learned
Flag of United States of America image

Are you getting a date without a time, and comparing to a date with a time?

Bob
Avatar of Dovberman

ASKER

A date with the time string is stored. The time string is
A date formated as mm/dd/yyyy is listed in the dropdown.

The date parameters are declared in a stored procedure ad DateTime.

The parameters are no longer being declared as Varchar(

Dateset that returns rows for the matrix: 'Typo. should have been "Dataset that returns..."
Declare @StartDate Varchar(10)

Is now: Declare @StartDate DateTime

Thanks,
I always use BETWEEN:

    WHERE srtr_Date BETWEEN @StartDate AND @EndDate

Bob
I should have mentioned that I need to select rows for the start date and rows for the end date. The requirement is to compare the status on two selected dates.
Ok, now I am just confused.

Would it be like this?

        Start date             End date
       -------------              ------------
       3/5/2008               3/10/2008
       3/6/2008               3/7/2008

Or, like this?

      3/5/2008
      3/10/2008
      3/6/2008
      3/7/2008

Bob
Here is a better description:

SQL Server 2005 Managment Studio

Stored Procedure parameters

ALTER PROCEDURE [dbo].[rs_getOccupancyHist]

@StartDate Datetime
,@EndDate DateTime

AS ....

works when I execute the stored procedure
and manually supply parameters

StartDate  03/07/2008
EndDate    03/11/2008

EXEC      @return_value = [dbo].[rs_getOccupancyHist]
            @StartDate = N'03/07/2008',
            @EndDate = N'03/11/2008'

--------------------------------
Visual Studio Design environment for RDL.

Dataset OccupancyHist is based on the stored procedure 'rs_getOccupancyHist'

Datasets for parameter dropdowns

StartDateSelection
SELECT DISTINCT TOP 10 srtr_Date AS startDate FROM SiteReqTrans
ORDER BY srtr_Date  DESC

EndDateSelection
SELECT DISTINCT TOP 10 srtr_Date AS EndDate FROM SiteReqTrans
ORDER BY srtr_Date  DESC

Returns DateTime list in following format.

3/7/2008 12:46:42 PM

Report-> Parameters

Name:    StartDate
Data type: DateTime
Prompt: Select a start Date

All boxes unchecked

Available values:

Dataset: StartDateSelection
From query selected
Value field: StartDate
Labelfield: StartDate
Default values:  Null

Selected dates from dropdown list
Clicked View Report

Error 'Query Execution failed or data set OccupancyHist'
Procedure or function 'rs_OccupancyHist'
expects parameter '@StartDate',
which was not supplied.

Error 'Query Execution failed or data set OccupancyHist'
Procedure or function 'rs_OccupancyHist'
expects parameter '@EndDate',
which was not supplied.

Somehow the parameters passed from the dropdown lists are
not recognized by the stored procedure.
So I can virtually come and look over your shoulder, can you show me a screen shot of how that is set in the report designer?

Bob
Thanks for taking the time to look at this.
ReportParamError.doc
More complex than initially estimated.
ASKER CERTIFIED SOLUTION
Avatar of Bob Learned
Bob Learned
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
The trick was to set up the report parameter
as a string that represents the date. Then cast the passed value as a date time type.

Thanks for all your help.

Details:
Create the date selection dataset as an mm/dd/yyyy string.
SELECT DISTINCT TOP 10 Convert(varchar(10),srtr_Date,101) AS EndDate FROM SiteReqTrans
ORDER BY Convert(varchar(10),srtr_Date,101)
This displays the dropdown date selection options
in mm/dd/yyyy format.

Then use the stored procedure to cast the
passed string variable as a Datetime type.

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

ALTER PROCEDURE [dbo].[rs_QuickTest_DBM]

@strEndDate varchar(10)

AS

DECLARE @EndDate AS DateTime

--Cast passed value as a datetime type.
Set @EndDate=CAST(@strEndDate  AS DateTime)
                         
SET NOCOUNT ON;

SELECT TOP 5 * FROM SiteReqTrans

WHERE (srtr_Date>=@EndDate
    AND
   srtr_Date < DATEADD(day,1,@EndDate))
ORDER BY srtr_Date Desc
The trick was to set up the report parameter
as a string that represents the date. Then cast the passed value as a date time type.

FYI, The RDL recognizes stored procedure parameters by default and prompts for them prior to preview even if you do not specify them in the Report-> Parameters options.

I have not found documentation for this behavior.

Thanks for all your help.
Would it be possible to post just a .png screen shot of the resulting change that helped, so that someone else could get a clear understanding of your solution?

Bob