SSRS Date and time Parameter
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 :)
June 29th, 2012 3: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
Please mark correct answers :)
Free Windows Admin Tool Kit Click here and download it now
June 29th, 2012 3:12am
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 30th, 2012 9:37am
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 30th, 2012 9:46am
Thank you very much..
June 30th, 2012 10:08am
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 :)
Free Windows Admin Tool Kit Click here and download it now
June 30th, 2012 10:12am
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
Create a parameter with date/time typeGo to default value and choose the specify value radio buttonClick 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.
June 30th, 2012 10:16am
Thank you very much guys for your time to reply. i will try this later. on the training right now. thanks again.
Free Windows Admin Tool Kit Click here and download it now
June 30th, 2012 10:25am
THank you very much guys. it's already running.
July 4th, 2012 8:34am