01-13-2017 07:51 AM
I am creating a Custom SQL data provider and i would like to define input parameters.
When i use the following query i get the error must declare scalar variable @ToDate. When i declare the parameters the dialog box to configure the parameters was not displayed.
Can parameters be used with Custom SQL data provider?
if yes. Is there a special syntax to be used when using parameters?
Select lc.lifecyclename,
q.statename,
u.username,
wf.exittime,
wf.entrytime,
Convert(decimal,wf.exittime - wf.entrytime) as TimeinQ,
count(*) as DocCount,
MIN(Convert(int,wf.exittime - wf.entrytime)) as MinTime,
MAX(Convert(int,wf.exittime - wf.entrytime)) as MaxTime,
AVG(Convert(int,wf.exittime - wf.entrytime)) as AvgTime
FROM hsi.wflog wf
INNER JOIN hsi.useraccount u ON wf.exitusernum = u.usernum
INNER JOIN hsi.lcstate q ON q.statenum = wf.statenum
INNER JOIN hsi.lifecycle lc ON wf.lcnum = lc.lcnum
WHERE wf.exittime >= @v_StartDate
AND entrytime between @ToDate and @FromDate
AND (wf.statenum = @StateNum OR @StateNum = 0)
AND (wf.lcnum = @LCNum)
GROUP BY lc.lifecyclename, q.statename,u.username, wf.exittime, wf.entrytime
07-17-2018 08:02 AM
01-13-2017 08:00 AM
Yes, parameters can be used in custom SQL data providers, and there is a special syntax. Instead of "@v_StartDate", you should have "@{v_StartDate}". Make a similar change for your other parameters. See also the section "SQL Query Parameterization" in the Reporting Dashboards Module Reference Guide for more information.
01-19-2017 12:38 PM
02-20-2019 05:00 PM
Hi Todd:
I am not a SQL expert. what is the syntax and where to put the parameters in the SQL. For an example:
SELECT Student_ID, Name, Date from Table_A
WHERE Student_ID = 11111 and (parameters @{fromDate} to @{toDate})?
The Date column is used to search the date range in the Dashboard.
Thank you.
02-07-2020 08:07 AM
Page 265 of the version 18 MRG says:
Note: The Date Time Range data type is only valid after a SQL BETWEEN clause.
Took some time to find this in the documentation, so I thought I'd mention it here in case anyone is referencing this post.
Find what you came for
We want to make your experience in Hyland Connect as valuable as possible, so we put together some helpful links.