Post reply

SSRS Report Parameter

  • June 28, 2012 at 1:40 am

    #258208

    Hi guys,

    I have an SSRS report with parameters.

    I have to pull out records based on date and time.

    I have this paramter fromdate,Todate its a date parameter like "6/28/2012" and FromTime,Totime its a time with a format like this 12:00 AM and

    i place this in pulldown menu, they have to choose what cutt off time they what to run the report. any idea guys what the problem or issues that

    i encountered.

    Here is what i do in my script in dataset Query type text:

    WHen i clik the Query designer, it display the define query parameter then when i clik the Run, i got an error

    an error occured while executing the query

    incorrect syntax near "ToDateTime"

    must dclare the scalar variable "@FromDateTime"

    must dclare the scalar variable "@ToDateTime"

    must dclare the scalar variable "@ToDateTime"

    Declare @FromDate datetime, @Todate datetime,

    @FromTime nvarchar(8), Totime nvarchar(8),

    @FromDateTime datetime, @ToDateTime datetime

    Set FromDatetime=dbo.getCombinedDateTime(@fromDate,@fromTime)

    Set ToDatetime=dbo.getCombinedDateTime(@ToDate,@ToTime)

    Select

    v.JOURNALID

    ,v.TRANSDATE

    ,v.ITEMID

    ,v.QTY

    ,v.COSTAMOUNT

    ,v.JOURNALTYPE

    ,v.BOMLINE

    From INVENTJOURNALTRANS v with (nolock)

    Where v.TRANSDATE between @FromDatetime and @ToDatetime

    and v.JOURNALTYPE=3

    and v.BOMLINE=0

    Thank you in advance. your help is veru much appreciated.

    jov

  • June 28, 2012 at 7:16 am

    #1506596

    The error told you about where to look, indicating that you had a problem with your declare statement.

    You are declaring Totime nvarchar(8), that should be @Totime nvarchar(8) instead

  • June 28, 2012 at 6:54 pm

    #1506908

    Thank you guys for the reply.

    Now my requirements is like this.

    I have a parameter in SSRS report see sample.

    i want to combine the fromdate,fromtime and todate,totime then stored

    it into FromDatetime and ToDatetime see sample. I will use this to pullout the records based on this 2 dates. how could i do this in SSRS. what method should i do. any idea guys. thanks.

    btw, im not using stored procedure instead im using text queries.

    The result should be like this

    FromDatetime: 2012/06/01 12:00 AM-----ToDatetime: 2012/06/28 12:00 PM

    or

    FromDatetime: fromdate,fromtime------Todatetime:Todate,Totime

    My QUERY in SSRS

    Select

    v.JOURNALID

    ,v.TRANSDATE

    ,v.ITEMID

    ,v.QTY

    ,v.COSTAMOUNT

    ,v.JOURNALTYPE

    ,v.BOMLINE

    From INVENTJOURNALTRANS v with (nolock)

    Where v.TRANSDATE between @FromDatetime and @ToDatetime

    and v.JOURNALTYPE=3

    and v.BOMLINE=0

  • June 28, 2012 at 8:52 pm

    #1506926

    Guys,

    Using this query, i got an error. any idea guys. thanks.

    The variable name '@FromDate' has already been declared. Variable names must be unique within a query batch or stored procedure.

    DECLARE @fromDateTime as datetime, @toDateTime as datetime

    SET @fromdatetime = dbo.getCombinedDateTime(@fromDate,@fromTime)

    SET @todatetime = dbo.getCombinedDateTime(@toDate,@toTime)

    Select

    v.JOURNALID

    ,v.TRANSDATE

    ,v.ITEMID

    ,v.QTY

    ,v.COSTAMOUNT

    ,v.JOURNALTYPE

    ,v.BOMLINE

    From INVENTJOURNALTRANS v with (nolock)

    Where v.TRANSDATE between @FromDatetime and @ToDatetime

    and v.JOURNALTYPE=3

    and v.BOMLINE=0

  • June 29, 2012 at 12:35 am

    #1506958

    I got it..

    Select

    v.JOURNALID

    ,v.TRANSDATE

    ,v.ITEMID

    ,v.QTY

    ,v.COSTAMOUNT

    ,v.JOURNALTYPE

    ,v.BOMLINE

    From dbo.INVENTJOURNALTRANS v with (nolock)

    Where v.TRANSDATE between dbo.getCombinedDateTime(@fromDate,@fromTime) and dbo.getCombinedDateTime(@toDate,@toTime)

    and v.JOURNALTYPE=3

    and v.BOMLINE=0

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply