AnsweredAssumed Answered

ft_Summarize in OLEDB Enterprise with filter

Question asked by SergioSancovsch on Jul 29, 2019
Latest reply on Jul 30, 2019 by SergioSancovsch

Want to do a query in OLEDB Enterprise to totalize a rate (integrate Tag MW to get in MWh) but adding a filter just as we do in excel PI Datalink (in this case, to eliminate noise):


The query calculates correctly without any filter using the following query:


SELECT eh.Path + eh.Name Element, ea.Name Attribute, s.*
FROM [TEST].[Asset].[ElementHierarchy] eh,
      SELECT N'Total' SummaryType
) t
INNER JOIN [TEST].[Asset].[ElementAttribute] ea ON ea.ElementID = eh.ElementID,
[TEST].[Data].[ft_Summarize] s
WHERE eh.Path = N'\ELEMENT1\' -- root elements
AND ea.Name = 'AttributeMW' --reference to Tag1
AND s.ElementAttributeID = ea.ID -- first InterpolateRange TVF argument
AND s.StartTime = N'y-1d' -- second InterpolateRange TVF argument
AND s.EndTime = N'y' -- third InterpolateRange TVF argument
AND s.TimeStep = N'1d' -- fourth InterpolateRange TVF argument
AND s.SummaryType = t.SummaryType -- fifth EventFrameSummarize TVF argument
AND s.CalculationBasis = N'TimeWeighted' -- sixth EventFrameSummarize TVF argument
AND s.TimeType = N'MostRecentTime' -- seventh EventFrameSummarize TVF argument


Couldn't find a way to add filter in this query. Is there another way to get the total filtering data using other elements values such as in excel?