SSRS Parameter - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Analysis Server and Reporting Services (2008)

SSRS Parameter

Author

Topic

Villanuev
Constraint Violating Yak Guru

478 Posts

Posted - 2012-06-28 : 03:54:10

Hi Forumer's

I have an SSRS report with parameters.
I have to pull out records based on date and time.
These parameters are 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 cut-off time they what to run the report. I would like to combine these parameter as one like (fromdate,fromtime) and (fromtime,totime) the output i would like to place it in fromdatetime and Todatetime .
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 click the Query designer, it display the define query parameter then when i click 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 @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 very much. your help is very much appreciated.

jov

Villanuev
Constraint Violating Yak Guru

478 Posts

Posted - 2012-06-28 : 05:31:45

If my requirements is not clear. here we go.

I have a parameter in SSRS report like this.
How could i combine those dates and time and use for my where clause condition. what metyhod should i do. Any idea guys.
btw, im not using stored procedure instead im using text queries.

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

Villanuev
Constraint Violating Yak Guru

478 Posts

Posted - 2012-06-29 : 02:34:39

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources