SSRS - Oracle DB, Passing Date parameter
- by davidl98
Using SSRS with an Oracle Database. I need to prompt the user when running the report to enter a date for report. What is the best way to add in the parameter in my SSRS Report. Having problem finding the right date format. under the "Report Parameter" menu, I have setup the Report Parameters using the DateTime Datatype.
Keep getting this error "ORA-01843: Not a Valid Month"
Thank you for your help.
Select
a.OPR_Name,
a.OPR,
a.Trans_Desc,
a.Trans_Start_Date,
Cast(a.S_Date as date) as S_Date,
Sum(a.Duration) as T
From (
Select
US_F.OPR_Name,
ITH_F.OPR,
ITH_F.ITH_RID,
ITH_F.TRANSACT,
Transact.DESC_1 as Trans_Desc,
To_CHAR(ITH_F.Start_Time,'DD-Mon-YY') as Trans_Start_Date,
To_CHAR(ITH_F.Start_Time,'MM/DD/YYYY') as S_Date,
Substr(To_CHAR(ITH_F.Start_Time,'HH24:MI'),1,6) as Start_Time,
To_CHAR(ITH_F.End_Time,'DD-Mon-YY') as Trans_End_Date,
Substr(To_CHAR(ITH_F.End_Time,'HH24:MI'),1,6) as End_Time,
Cast(Case When To_CHAR(ITH_F.Start_Time,'DD-Mon-YY') = To_CHAR(ITH_F.End_Time,'DD-Mon-YY')
Then (((To_CHAR(ITH_F.End_Time,'SSSSS') - To_CHAR(ITH_F.Start_Time,'SSSSS')) / 60))/60
Else ((86399 - (To_CHAR(ITH_F.Start_Time,'SSSSS')) + To_CHAR(ITH_F.End_Time,'SSSSS'))/60)/60
End as Decimal(3,1)) as Duration
from Elite_76_W1.ITH_F
Left Join Elite_76_W1.Transact
on Transact.Transact = ITH_F.Transact
Left Join Elite_76_W1.US_F
on US_F.OPR = ITH_F.OPR
Where ITH_F.TRANSACT not in ('ASN','QC','LGOT')
) a
Where a.S_Date = @Event_Date
Having Sum(a.Duration) < 0
Group By a.OPR_Name,
a.OPR,
a.Trans_Desc,
a.Trans_Start_Date,
a.S_Date
Order by a.OPR_Name
