SSRS Date and time Parameter

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

June 29th, 2012 9:00am

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

Free Windows Admin Tool Kit Click here and download it now
June 29th, 2012 9:09am

Thank you very much..
  • Edited by Lenoj Friday, June 29, 2012 6:32 AM
June 29th, 2012 9:31am

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
Free Windows Admin Tool Kit Click here and download it now
June 29th, 2012 9:39am

Thank you very much guys for your time to reply. i will try this later. on the training right now. thanks again.
June 29th, 2012 9:48am

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

Free Windows Admin Tool Kit Click here and download it now
June 29th, 2012 10:12am

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

June 29th, 2012 10:12am

THank you very much guys. it's already running.
Free Windows Admin Tool Kit Click here and download it now
July 4th, 2012 3:16pm

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,

January 30th, 2013 8:23am

Hi lenoj,

i have also same requirement can you help me if you have resolved this issue.

Free Windows Admin Tool Kit Click here and download it now
February 19th, 2015 4:33am

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics