I tend to favor custom data references. I am walking proof that if all you have for a solution is a hammer then sooner-or-later every problem begins to look like a nail.
@Asle: You could create an AF data reference that works with AFTable and AFTables objects with LINQ to allow more complex queries to take place in memory. I suppose from your explanation that the table (from ORACLE) contains an equipment id of some sort, a date where the fault occurred and the category of faults. If it is the case, you would have to perform a query to count faults for chosen equipment id and category and do a second one to obtain an ordered list of years where a category of faults happened for a chosen equipment id.
You can manipulate the table itself with AFTable.Table property. This is a native System.Data.DateTable object.
SELECT COUNT(*) FROM MyTable WHERE EQUIPMENTID = 'ID' AND CATEGORY = 'CATEGORY'
SELECT [DATE] FROM MyTable WHERE EQUIPMENTID = 'ID' AND CATEGORY = 'CATEGORY' GROUP BY [DATE] ORDER BY [DATE]
You generated a csv list of years that you append to the number of faults and you get what you want!
Thanks for replying. I was going to try your suggestion, Mathieu, but then I made a small discovery:
Since writing this yesterday I have upgraded from OLEDB Ent. 2010 R2 to R3, and apparently the new version handles string arrays better than the previous one. Where I yesterday only had "System.String" as value for all my attributes when querying the data through OLEDB, it now seems like the strings in the array are concatenated almost like I want it to be.
The data now looks like this: [1998, 2002, 2005]
I still can't make it work directly in AF or PI System Explorer, but my main requirements were to have these data available in SQL Server and Reporting Services. So with a little substring magic I can now output the years just like I want it. Excellent :)
There seems to be one or more extra characters added after the square brackets, but with this SQL I'm able to display just the years:
SUBSTRING([Observation years, minor faults],2,LEN([Observation years, minor faults])-3) AS ObservationTimes
I'll keep your suggestion at hand when (not if) our client asks to have the data available as an attribute as well.
I would definitely like to have this available directly within the Table Lookup functionality in AF though. There are other cool stuff you can do, but they all seem to be related to numerical values only. And the same thing with Formula DR's. Numerical only, and nothing for string operations. More string stuff is high on my wanted list
Asle Frantzen @ AmitecIf you only care to use the results with PI OLEDB Enterprise and you are using these results in a .NET environment, then there might be no need to create a custom Data Reference.
If I change the data type to 'string array' I will get all the results, but then I can't utilize the results further on through OLEDB Enterprise.
In a nutshell, PI OLEDB Enterprise can handle a string array... just PI SQL Commander doesn't render it (other than say its type: System.String). In a .NET environment where you are using ADO.NET to query PI OLEDB Enterprise, you can read the value into a String array.
Hope this helps!
I got it wrong in my last post though - it wasn't in PI SQL Commander I noticed that the output was [1998, 2002, 2005] but rather in the SQL Server linked server query.
In PI SQL Cmdr. the output is still System.String.
I think I'm covered so far though, and then I can get the custom DR in action when I have a little bit more time on my hands - and also when the client starts requesting it.