Skip to Content

     This blog is a continuation of the second blog written in this series. So refer to the The power of Flex in dashboarding (Xcelsius 2008)-Part 1 and the The power of Flex in dashboarding (Xcelsius 2008)-Part 2 part for the underlying concepts.

      Although Excel has over 300 built-in functions, there are times when you can’t achieve the result with a standard function or a combination of standard functions. At these times, a custom VBA function can be very handy, and the user would not be aware of their existence. In the Xcelsius environment, these VBA functions will become irrelevant. Now, how can I create a user defined function (e.g.: Reversing a string field) in Xcelsius 2008? Xcelsius 2008 only supports a limited number of Excel functions. All these Excel functions have equivalent functions mapped in Flash. With the help of Flex, you can create custom functions, which can be used in Xcelsius 2008. Even though the execution is happening at the Flash end, it will provide the similar functionality of a VBA function.

Now, I will give you an example of creating an Excel function using Flex. This function will reverse the string field, which is passed as an argument.

Below are the steps used to create the function.

1) Use the code snippet provided below for creating the MXML file. The function should be having a ‘public’ modifier.

XcelsiusFunction.mxml

          <!–[CDATA[

               import xcelsius.spreadsheet.ICellSDK;

               import xcelsius.collections.ITableSDK;

               import xcelsius.spreadsheet.FunctionError;

               

               // function utilities

               // various checking to return the correct format, expecting a string

               private function getStringUtil(arg:):

               {

                    var rv:String;

                    if(arg is ICellSDK)

                    {

                         arg =(arg as ICellSDK).value;

                    }

                    if(arg is xcelsius.spreadsheet.FunctionError)

                    {

                         return arg;

                    }

                    if(arg is String)

                    {

                         rv=arg;

                    }

                    else if(arg is Number)

                    {

                         rv=String(arg);

                    }

                    else if(arg==true)

                    {

                         rv=”TRUE”;

                    }

                    else if(arg==false)

                    {

                         rv=”FALSE”;

                    }

                    else if (arg==null)

                    {

                         rv=””;

                    }

                    else

                    {

                         return new xcelsius.spreadsheet.FunctionError(xcelsius.spreadsheet.FunctionError.VALUE);

                    }

                    return rv;

               }

                                             

               // PROPER Function

               // capitalize the first letter of a word and the rest is lowercase.

               public function reverse(…args):*

               {

                    var v:*;

                    var a0:String;

                    var range:ITableSDK;

                    

                    //Take the first parameter

                    if (args[0] == undefined)

                    {

                         a0 = “”;

                    }

                    else

                    {

                         // if it’s a range then get item at the first cell

                         // since Excel proper function behaves the same

                         if (args[0] is ITableSDK)

                         {

                              // Excel proper function only takes in a cell, throws VALUE error otherwise

                              return new xcelsius.spreadsheet.FunctionError(xcelsius.spreadsheet.FunctionError.VALUE);                    

                         }

                         else

                         {//call the check and return the string or error

                              v = getStringUtil(args[0]);     

                              if(v is xcelsius.spreadsheet.FunctionError)

                              {

                                   return v;

                              }

                              else

                              {

                                   a0 = v;

                              }

                         }

                    }     

                    //Split the word and get into Array          

                    var wordsArray:Array = a0.split(“”);

                    //Reversing the string

                    var temp:String =wordsArray.reverse().join(“”);

                    return temp;                    

               }                                        

          ]]–>

     

 

2) Compile the code using Flex 2.01 SDK to get the SWF file.

3) Create the XLX file using Xcelsius Add-On Manager. Below is the screen shot showing the parameter information for XLX creation.

!https://weblogs.sdn.sap.com/weblogs/images/252188557/XcelsiusFunctioncreation.JPG|height=395|style=vertical-align: middle; border: 7px solid black;|alt=XcelsiusFunction|width=554|src=https://weblogs.sdn.sap.com/weblogs/images/252188557/XcelsiusFunctioncreation.JPG!

4) Install the XLX file as an add-on in Xcelsius designer.

5) Test the function using two input text fields. See the screen shots below.

 

!https://weblogs.sdn.sap.com/weblogs/images/252188557/Testingfunction1.JPG|height=331|style=vertical-align: middle; border: 7px solid black;|alt=Test 1|width=546|src=https://weblogs.sdn.sap.com/weblogs/images/252188557/Testingfunction1.JPG!

Testing the function-Step 1

 

!https://weblogs.sdn.sap.com/weblogs/images/252188557/Testingfunction2.JPG|height=323|style=vertical-align: middle; border: 7px solid black;|alt=Test 2|width=462|src=https://weblogs.sdn.sap.com/weblogs/images/252188557/Testingfunction2.JPG!

Testing the function-Step 2

 

test 3

Testing the function-Step 3

 

With these powerful functionalities achieved using Flex, you can get creative and enhance your dashboard to meet any requirements. So it will definitely take your dashboard design to the next level.

<br /></p>

To report this post you need to login first.

1 Comment

You must be Logged on to comment or reply to a post.

Leave a Reply