7 Replies Latest reply on Oct 25, 2013 9:48 AM by IPCOSRumaila

    Custom Data Reference using PI Data Link gives me "Resize to show all values". Recalculate does not work

    IPCOSRumaila

      Hi,

       

      I'm working with a Custom Data Reference. It implements the Data-functions (among others:  RecordedValues). This functionality works fine with PI Explorer. When using the Custom Data Reference with PI Data Link within Excel 2013 In one specific circumstance I get "Resize to show all values". This is when using "Compressed Data" together with Time range.   The count is shown and is correct. Right clicking on this result and selecting "Recalculate (Resize) function"  again calls the RecordedValues-function but again shows no values, only the same count and "Resize to show all values". Switching off this message in the settings, then gives me only the count and the  first value.  When using   "Compressed Data" with Number of values I can see the values, but then the maximum number of samples are indeed known in advance. 

       

      When using a PI-point or a Linked Table with PI Data Link with the same options, It never gives me the "Resize" message, so there is something wrong with my Custom Data Reference? Maybe there some function I need to  implement and add to  SupportedMethods / SupportedDataMethods?  Summary and Filters are currently not implemented.

       

      The Formula created by DataLink is {=PCOMPDATA(..)} and it calls the RecordedValues-function in the Custom Data Reference. I'm using Excel 2013.

       

      Any Ideas?

       

       

       

      Regards,

       

      Denis

        • Re: Custom Data Reference using PI Data Link gives me "Resize to show all values". Recalculate does not work

          Hi Denis,

           

          PI DataLink formulas and their results are stored in cell arrays within the active worksheet. The "Resize to show all values" dialog is displayed when the resize would cause a change of the array size which usually is the case if i.e. the amount of rows returned by a DataLink formula changes.

           

          Do you see any way to confirm the resize?

            • Re: Custom Data Reference using PI Data Link gives me "Resize to show all values". Recalculate does not work
              IPCOSRumaila

              Dear Gregor,

               

              I educated myself a bit more on array-formulas and got things more or less working:

               

              - Selecting a range of cells before creating the Formula shows all values.

               

              -Selecting the range of cells that should hold the result on an existing formula and press f2 and the ctrl-shift-enter works as well.

               

              This helps me out a bit.

               

              But I still do not understand why with Linked tables and PI-Point all of the above is not needed, And also the right-click "Recalculate (Resize)  Function" which should do the trick if I understood the manual, does not work.

               

              It would be nice If I can modify the Custom Data Reference to behave look the Pi-Point or Linked Table, ... .

               

              Regards,

               

              Denis

                • Re: Custom Data Reference using PI Data Link gives me "Resize to show all values". Recalculate does not work

                  Hello Denis,

                   

                  PI DataLink 2013 uses AF SDK 2.5 with RDA access. We are currently preparing an updated white paper about implementing AF 2.0 Data References that has detailed information about adding support for RDA methods inside Custom Data References.

                   

                  However, if your Data Reference supports RDA shouldn't have any impact on the behavior you experience. This should be more a question of why PI DataLink 2013 believes an array resize is required. Please see the following excerpt from PI DataLink 2013 manual:

                  Array size

                  When a PI DataLink function writes a function array, it automatically sizes the array to fit the returned data. Functions write the function array when you click OK or Apply on a task pane or when you click Recalculate (Resize) Function on the shortcut menu.

                   

                  Other methods that recalculate or update a function do not write a new function array. These methods only update array values; the array size remains unchanged. For example, if you specify function inputs with cell references and change the value in a cell, the function will update returned values but the size of the array remains the same. The update might return more or fewer values than the function array on the worksheet can show. Settings control what PI DataLink returns:

                    • If the update returns fewer values, PI DataLink either returns blanks or #N/Ain the cells without values.
                    • If the update returns more values than the function array can show, PI DataLink returns the text Resize to show all values at the bottom of the array unless the preference settings disable the message.

                  Empty values can change the appearance of a worksheet. To maintain a constant size, consider setting function inputs to limit returned data. For example, in the Compressed Data function, you can specify the number of values to retrieve rather than a time period during which you retrieve all values.

                   

                  You can try if disabling the "Resize to show all values" message within PI DataLink settings helps you to get around the undesired behavior but we did some testing with PI DataLink 2013 using the String Concatenation Data Reference (which has no support for RDA). We were not able to provoke the "Resize to show all values" dialog. This said we believe you might be facing an issue with PI DataLink 2013 and will engage OSIsoft Technical Support to work with you and Escalation to see what causes the dialog to show in your case.