none
SSRS Date and time Parameter

    Question

  • Hi Forumers'

    Im trying to design a report in SSRS with date and time parameters

    I would like to combined the fromdate,fromtime and stored the value to @Fromdatetime and

    same with also todate,totime into @ToDatetime. i will used this in a where clause condition.

    can you give me guys an idea. btw, i'm using Text queries not stored procedure.

    Right now I tried this query but i got an error.

    The variable name '@FromDatetime' 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

    Thank you.

    jov

    Friday, June 29, 2012 6:00 AM

Answers

  • Hi There

    Thanks for your posting. I dont know why would you have two different parameter for date and time if you could achieve this functionality by using one parameter

    please follow the steps mentioned below to create data and time togather

    Steps to take

    1. Create a parameter with date/time type
    2. Go to default value and choose the specify value radio button
    3. Click on add and in the value expression please put =Now

    I am putting the screenshot for your help.

    I hope this will resolve your problem

    Many thanks

    Syed Qazafi Anjum

    Please click "Mark as Answer" if this resolves your problem or "Vote as Helpful" if you find it helpful.

    • Proposed as answer by Sanjeewan Kumar Friday, June 29, 2012 6:54 AM
    • Marked as answer by Lenoj Wednesday, July 04, 2012 12:15 PM
    Friday, June 29, 2012 6:39 AM
  • Hey Lenoj,

    Here is what you can do:

    1. If you see parameters @FromDatetime and @ToDatetime in your report, right click and delete them.


    2. Go to your Dataset properties by double clicking on the report dataset and navigate to the section named parameters. What we will do here is combine the @fromDate and @fromTime into a single value that represents a datetime and assign that to the @FromDatetime query parameter. We shall then do the same to the @toDatetime query parameter as well.

    Under Parameters, where you see Parameter Name of @FromDatetime, corresponding to that you see its Parameter Value with an "fx" button adjacent to it. Click on this button to open the expression editor and type in an expression as:

    =Parameters!fromDate.Value & " " & Parameters!fromTime.Value

    Click OK and do the same for the @ToDatetime:

    =Parameters!toDate.Value & " " & Parameters!toTime.Value

    Click OK and OK until you are out of the dataset properties window.

    HTH.

    Let me know if you need more info.

    Cheers,

    IceQB


    Please mark correct answers :)

    • Marked as answer by Lenoj Wednesday, July 04, 2012 12:15 PM
    Friday, June 29, 2012 7:12 AM

All replies

  • Don't use declare. Edit your query to:

    Select 
    v.JOURNALID
    ,v.TRANSDATE
    ,v.ITEMID
    ,v.QTY
    ,v.COSTAMOUNT
    ,v.JOURNALTYPE
    ,v.BOMLINE
    From 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

    Friday, June 29, 2012 6:09 AM
  • Thank you very much..
    • Edited by Lenoj Friday, June 29, 2012 6:32 AM
    Friday, June 29, 2012 6:31 AM
  • Hey Lenoj,

    Here is what you can do:

    1. If you see parameters @FromDatetime and @ToDatetime in your report, right click and delete them.


    2. Go to your Dataset properties by double clicking on the report dataset and navigate to the section named parameters. What we will do here is combine the @fromDate and @fromTime into a single value that represents a datetime and assign that to the @FromDatetime query parameter. We shall then do the same to the @toDatetime query parameter as well.

    Under Parameters, where you see Parameter Name of @FromDatetime, corresponding to that you see its Parameter Value with an "fx" button adjacent to it. Click on this button to open the expression editor and type in an expression as:

    =Parameters!fromDate.Value & " " & Parameters!fromTime.Value

    Click OK and do the same for the @ToDatetime:

    =Parameters!toDate.Value & " " & Parameters!toTime.Value

    Click OK and OK until you are out of the dataset properties window.

    HTH.

    Let me know if you need more info.

    Cheers,

    IceQB


    Please mark correct answers :)

    Friday, June 29, 2012 6:34 AM
  • Hi There

    Thanks for your posting. I dont know why would you have two different parameter for date and time if you could achieve this functionality by using one parameter

    please follow the steps mentioned below to create data and time togather

    Steps to take

    1. Create a parameter with date/time type
    2. Go to default value and choose the specify value radio button
    3. Click on add and in the value expression please put =Now

    I am putting the screenshot for your help.

    I hope this will resolve your problem

    Many thanks

    Syed Qazafi Anjum

    Please click "Mark as Answer" if this resolves your problem or "Vote as Helpful" if you find it helpful.

    • Proposed as answer by Sanjeewan Kumar Friday, June 29, 2012 6:54 AM
    • Marked as answer by Lenoj Wednesday, July 04, 2012 12:15 PM
    Friday, June 29, 2012 6:39 AM
  • Thank you very much guys for your time to reply. i will try this later. on the training right now. thanks again.
    Friday, June 29, 2012 6:48 AM
  • Hey Lenoj,

    Here is what you can do:

    1. If you see parameters @FromDatetime and @ToDatetime in your report, right click and delete them.


    2. Go to your Dataset properties by double clicking on the report dataset and navigate to the section named parameters. What we will do here is combine the @fromDate and @fromTime into a single value that represents a datetime and assign that to the @FromDatetime query parameter. We shall then do the same to the @toDatetime query parameter as well.

    Under Parameters, where you see Parameter Name of @FromDatetime, corresponding to that you see its Parameter Value with an "fx" button adjacent to it. Click on this button to open the expression editor and type in an expression as:

    =Parameters!fromDate.Value & " " & Parameters!fromTime.Value

    Click OK and do the same for the @ToDatetime:

    =Parameters!toDate.Value & " " & Parameters!toTime.Value

    Click OK and OK until you are out of the dataset properties window.

    HTH.

    Let me know if you need more info.

    Cheers,

    IceQB


    Please mark correct answers :)

    • Marked as answer by Lenoj Wednesday, July 04, 2012 12:15 PM
    Friday, June 29, 2012 7:12 AM
  • Hey Lenoj,

    Here is what you can do:

    1. If you see parameters @FromDatetime and @ToDatetime in your report, right click and delete them.

    2. Go to your Dataset properties by double clicking on the report dataset and navigate to the section named parameters. What we will do here is combine the @fromDate and @fromTime into a single value that represents a datetime and assign that to the @FromDatetime query parameter. We shall then do the same to the @toDatetime query parameter as well.

    Under Parameters, where you see Parameter Name of @FromDatetime, corresponding to that you see its Parameter Value with an "fx" button adjacent to it. Click on this button to open the expression editor and type in an expression as:

    =Parameters!fromDate.Value & " " & Parameters!fromTime.Value

    Click OK and do the same for the @ToDatetime:

    =Parameters!toDate.Value & " " & Parameters!toTime.Value

    Click OK and OK until you are out of the dataset properties window.

    HTH.

    Let me know if you need more info.

    Cheers,

    IceQB


    Please mark correct answers :)

    Friday, June 29, 2012 7:12 AM
  • THank you very much guys. it's already running.
    Wednesday, July 04, 2012 12:16 PM
  • Hi Guys,

    i have additional requirements on this thread.

    I have a parameter name @timezoneoffset= 8 and i was set up as hidden in parameter visibility.

    how could in include it in my script  or from this portion:

    =Parameters!fromDate.Value & " " & Parameters!fromTime.Value

    Regards,

    Wednesday, January 30, 2013 5:23 AM