I am developing SSRS reports that use PI data.
I am trying cobble together something that allows me to ask parameterized questions (as opposed to writing explicit functions for every attribute I am asked to plot)
For my proof of concept using the first Attribute , i follow this procedure
1. use dataModeler in SQL Commander Lite (SCL) to create a table valued function that calls GetSampledValues
2. on my linked server, write an openQuery so I can pass in elementId, dates, timestep
My challenge is that I will be asked to write a lot of these.
If there are NO alternatives, thats fine, Ill just get busy... but it seems like I am setting myself up for a maintenance nightmare.
I have tried creating a table valued function (TVF) that takes TemplateName, and AttributeName as parameters. This does not seem to work.
I can take the guts out of the TVF create script and run that in SCL. That works. I can not take that same query and port it to the LINKED SERVER (LS). It chokes on the LESS THAN sign.
Ideally, I would like to have something on my Linked Server that looks like this
--- these would all be parms i pass in to my stored procedure
Declare @templateName varchar(100),
SELECT e.Name, e.id, e.PrimaryPath, s.*
SELECT e.ID, e.Name, e.Template, e.PrimaryPath
FROM [Master].[Element].[Element] e
where e.[Name] = @ElementName
and e.PrimaryPath like ''%'' + @ParentName + "%''
CROSS APPLY [Master].[Element].[tvf_Gregs_GetSampledValues]
e.ID, --Element ID
@startTime, --Start Time
@endTime --End Time
@TimeStep --Time Step
WHERE e.Template = @TemplateName