I don't think there is a setting change the way DataLink output errors like this. So we would have to try some indirect ways to do this.
Using DataLink, I can think of options like:
- use DataLink to output result to one set of cells (probably hidden) and use Excel IF function or VBA to look at the result and put into a desired format into a second set of cells.
- combine Excel IF function with DataLink function, like:
=IF(PIAdvCalcDat("CDT158", "*-2h","*","30m","average","time-weighted",0,1,0,"DEVPI")="[-11059] No Good Data for Calculation",
I like the 1st option better because the 2nd option doesn't allow you to modify the function or resize the DataLink function output easily. Maybe others on the community have better ideas?
We too handle this error by shortening it to NA (Not Available) as output message in the cell, i.e. :
=IF(PICalcVal("CDT158",*-1d,*,"average",1,0,"PI-SERVER")="[-11059] No Good Data For Calculation",
Thank you very much for the input, I have used option 1 on a mock up demo I did a couple years back, just wanted to see how many other options people have successfully implemented.
Option two floods the mind with interesting prospects, embedding the Datalink call in the Excel function could lead to some interesting methods when solving unique problems.
Very helpful and insightful
P.S. I also learned something new today about the VCampus Forums. I replied to Hans post originally via e-mail (Blackberry), I didn't realize that e-mail reply would not post to the discussion. My bad, I assumed the technologies were linked (may be impossible feat as far as I know).
Here's an interesting outcome of your suggested option #2, I shared this syntax with two colleagues.
Colleage #1 observation was : fasicinating approach, but is it optimal? At least on our system the evaluation will most of the time be false so wont this way cause a query to happen twice, once to see if = to no good and once again to return the final result.
Is this true or does PI do something internal to keep this optimal.
Colleage #2 doesnt believe the hit to be bad enough to worry about.
We did not perform any bench test to prove one way or the other, just thought we would post the question and maybe the feedback will help others also.
I do agree that it probably runs the query twice, you can verify this with SDK tracing. This is another reason I didn't think this is the best option to use. PI Server do have caching for the data read and PI SDK has caching for PI SDK objects like PIPoint. These optimizations would make the 2nd call faster.
It's kind of hard to tell if this would affect performance, since there are many factors involved here. I guess you can do a small test and evaluate this.
Something like IFERROR would work better, because it should only make 1 query to PI. Unfortunately it detects an error from Excel but not from PI SDK.
I agree that option 1 seems more generic as with option 2 you can't handle array (15m sampled data for example) but you can't also handle unique value (like the average in you formula) with percent good.
I never did something like option 2, i'll test it at office tomorrow as i'm curious