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
01-13-2017 07:59 AM
Kerresha, thank you for your post. The syntax for dynamic parameters in Reporting Dashboards is @{ParameterName}. Also, based on the SQL you have above, you would be able to replace the @ToDate and @FromDate with a single parameter that is defined as a Date Range or Date Time Range.
Thank you,
Todd Bailey
01-19-2017 12:38 PM
07-16-2018 08:22 AM
07-16-2018 09:07 AM
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.