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

[Todd_Bailey]

 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

[Kerresha_Rowe]

Thank you Todd.

[Hannah_Nelson]

Hi Todd, am I missing in the Dashboard MRG where the different parameters that are available for use are defined?

[Todd_Bailey]

Hannah,
The MRG does not specifically list out each parameter that is available for a particular data provider type. However, on page 412 (of the OnBase 18 MRG) it shows the syntax needed for custom parameters in the Custom SQL data provider type.
Thank you,,
Todd