WayneStiven

PI SQL parameters

Discussion created by WayneStiven on Sep 26, 2012
Latest reply on Dec 5, 2012 by rene

Hello fellow PI people.

 

I am trying to generate some SSRS reports using PI data. I have set up elements in PI Asset Framework, tested the SQL using PI SQL Commander and then ran the SQL successfully in the SSRS report.
My problem is that I want to run my report for a date range and I cannot get the SQL to work in SQL Commander with date parameters. I have tried @ parameters and ?s but neither work.
To get around the problem I have had to extract a large range of interpolated data and then done the date selection within SSRS (see sample SQL). This is not satisfactory.

 

 Can someone help explain how I can fix this issue. Thanks

 

SELECT eh.Name Element, ea.Name Attribute, i.Time, i.Value
FROM Energy.Asset.ElementHierarchy eh
INNER JOIN Energy.Asset.ElementAttribute ea ON ea.ElementID = eh.ElementID
CROSS APPLY Energy.Data.InterpolateRange (ea.ID, N'*-1w', N'*', N'1m') i
WHERE eh.Path  in (N'\Stockley\INFEED\')
and eh.Name in ('INFEED.Analyser')
and ea.Name in ('Ash','Sulphur','Moisture','Volatiles','CoalType',
'Tonnes')
and Time between ? and ?    

Outcomes