Adding input parameters to Custom SQL

[Kerresha_Rowe]

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

[Hannah_Nelson]

Thank you so much Todd! I'll take a look!

[Jason_Knight]

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.

[Kerresha_Rowe]

Thank you Jason

[Nghia_Phan]

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.

[Larissa_Armand]

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.