|
|
Rank: Newbie
Groups: Member
Joined: 7/18/2008
Posts: 2
Location: Chicago, IL
|
I'm fairly new to osi-pi and excel/vba, I'm currently a junior working on my summer internship project. I need to retrieve batch records from our pi server, and put them into excel sheets. I am dealing with about 15 tags, 10second data points, and around 20-60 hours of batch data (varies per batch). I currently have an excel macro sheet for it provided by my manager, but it only allows me to sample 20 hours of data at 10s intervals (~6900 rows/column). Can anyone give me some tips on how to accomplish this:
1) Refresh batches: (find all finished runs from ~1 year ago to present)
2) Retrieve data per selected batch (ability to select a certain batch id and retrieve all data collected onto the excel spreadsheet)
3) Save data to new worksheet
Thanks in advance, any help is appreciated.
|
|
|
|
|
OSIsoft vCampus is a subscription-based, online offering that consists of providing everything people need to develop applications on the PI System. We invite you to take a "tour" of the OSIsoft Virtual Campus - also feel free to consult the FAQ or contact OSIsoft vCampus for more details.
|
|
|
Rank: Member
Groups: Member
Joined: 7/23/2008
Posts: 25
Location: Milan, Italy
|
Hi AFK it's a bit tough to see the real problem you are tackling here because we don't see the spreadsheet and how the queries are built (why the limitation at 6900 rows?), and what you are really trying to accomplish. First I would ask if you absolutely have to have data in your spreadsheet each 10s...see if you can by playing around with the query, you can specify an interval a bit more spaced out (just to see if that brings through the whole batch). Otherwise I would really encourage you to get in touch with OSIsoft Tech Support on this one. They will ask you for a copy of the spreadsheet and what you are expecting vs. what you are getting. They're paid to walk people through the steps to get more out of the system and might even suggest a better way to query or analyse the data. I used to do some tech support while I was at OSIsoft and I can assure you that their TS will never say "sorry we can't help you with that". Do let us know how it goes and if you can, the resolution of the problem.... Have fun in your internship! dc www.definitconsulting.com
|
|
|
Rank: Advanced Member
Groups: Member
Joined: 7/23/2008
Posts: 38
Location: Cheshire, UK
|
Hi Brendan,
You mention elsewhere that you have an internship with a pharma company. Are you able to say which one. We have already developed Excel add-ins that would on first glance appear to acheive what you are attempting to do, however they were developed for specific clients in the Pharma world. If you're with one of those clients I could point you to the right people to talk to over in the US, if not then I could give some sample code but the programming I have does become very involved as it is written specifically for customer needs and based around their batch architectures. I would need to remove these elements which may compromise some of your understanding.
The other option is to look at what is available on the OSIDN site (OSI Developer Network), there are some batch examples there which may give you a starting point.
Regards,
Burnikell1
|
|
|
Rank: Newbie
Groups: Member
Joined: 9/10/2008
Posts: 3
Location: San Francisco, CA
|
Use PI-BatchView to query your BatchDB for the batches of interest.
With PI-BatchView, you can actually copy and paste the records directly into Excel and have the id, unit, product, starttime, endtime land in the cells.
Then you want to write a script that loops through each row (i.e. each batch).
At this point, if you have aliases defined for your units, you're in luck because you can write a function that looks up the tag of interest according to aliase and unit... something like:
Function tagname( salias As String, sunit As String) As String
... instantiate PIBatchView object and use the object to get the alias...
End Function
If you don't have aliases defined, then you'll have to use string-concatenation directly in the cell... assuming you have a consistent tag nomenclature.
If you don't have a consistent tag nomenclature, then you'll have to write a custom VBA function with to map to the tag you want.
This is where the cool part begins. Somewhere in the workbook (I recommend a different sheet than your list of batches), create a PI-DataLink array using the PI>Compressed Data (Start Time/End Time) menu option. This menu option will require a tagname, starttime and endtime.
You get the tagname from 1 of the 3 methods I wrote above. You get the Start Time and End Time from the Batches that you queried for with PI-BatchView.
So in your loop, just copy the row and paste it into the cells. PI-DataLink will automatically go grab the data.
If you wish to put that data in Excel, then in the rest of the loop, do some range copy/paste work, and you're done!
|
|
|
|
Guest
|
