Implementing Complex Functions with User Interface Builders
The functions I have described so far are all fairly easy to type directly into a spreadsheet cell. All references passed are to other cells on the spreadsheet, and the set of parameters is fairly easy to understand. However, many kinds of custom functions are not as easy to type directly into the spreadsheet. Functions that reference resources outside the current spreadsheet (such as shared data sources) or functions that allow a user to graphically select part of that reference might need to display custom user interfaces for creating and editing formulas that use those custom functions. The final function I implemented in the custom function library is such a function.
The OLAPLookup function fetches a single aggregate from a multidimensional data source. As I discussed in Chapter 4, a multidimensional database defines an N-dimensional structure containing values. Each value is the result of an intersection of N members (one from each dimension), and the logical address of that value's cell is the member tuple. A tuple is a fancy word that simply means a set of related attributes, which in this case is a set of unique member names that define a data point intersection.
The OLAPLookup function is a useful mechanism for pulling a few specific aggregates into your spreadsheet model to use either as inputs to a forecasting model or as values you want to analyze using other built-in or custom functions. Although the PivotTable component can obtain many aggregates from an OLAP data source, the Spreadsheet component offers no direct support for retrieving these as part of a model. Furthermore, it is often useful to have absolute control over where the aggregates display in the spreadsheet, allowing you to customize the presentation of your data any way you please.
To get this aggregate, the OLAPLookup function needs three pieces of information: the connection string for the OLAP data source, the name of the cube you want to query within that data source, and the cell's coordinates defined by a tuple of member unique names. Although you can type all this information into your spreadsheet formula, it is much better to offer your users a graphical wizard that helps them specify these parameters. It is highly unlikely that your users will know how to construct an OLE DB connection string and even less likely that they will know the unique names for the various members they want to use in the coordinates. The OLAPLookup Function wizard provides that graphical user interface, and it also provides an example of how to build a reentrant function-editing wizard.
The OLAPLookup Function
Before we discuss how the OLAPLookup Function wizard works, let's look at the code for the OLAPLookup function itself:
Public Function OLAPLookup(ConnectionString As String, _ CubeName As String, CellCoordinates As String) As Variant Dim cn As Connection ' Connection to OLAP database Dim cset As New Cellset ' OLAP cellset On Error GoTo Err_OLAPLookup ' Get a connection to the specified database Set cn = GetConnection(ConnectionString) ' If connected If Not (cn Is Nothing) Then ' Execute the MDX query to get the single cell cset.Open "select from " & CubeName & _ " where (" & CellCoordinates & ")", _ cn ' Get the value for the single cell OLAPLookup = cset.Item(0).Value ' Close up cset.Close Set cset = Nothing End If 'Connection valid ' Success Exit Function Err_OLAPLookup: ' Error! Return the error description for debugging purposes. OLAPLookup = Err.Description Exit Function End Function 'OLAPLookup() |
Retrieving a single aggregate from a cube is surprisingly easy. After getting a connection to the OLAP data source, the OLAPLookup function builds a simple MDX statement that will return just one cell. The code uses the CubeName parameter for the FROM clause and the CellCoordinates parameter for the WHERE clause. By definition, a full set of coordinates will return just one cell, so the code executes the MDX statement and returns the first (and only) cell in the cellset. It then closes the cellset, releases it, and exits.
To optimize the OLAP database connections, I implemented a function called GetConnection, which checks a collection of cached connections and returns an opened connection if one already exists for the same connection string. The code looks like this:
Private Function GetConnection(sConnString) As Connection Dim cn As Connection ' Try to get an existing connection On Error Resume Next Set GetConnection = m_colConns.Item(sConnString) ' If there was no existing one If Err.Number <> 0 Then ' Create a new connection, and open it On Error GoTo 0 Set cn = New Connection cn.Open sConnString ' If it connected OK If Err.Number = 0 Then ' Add it to the collection m_colConns.Add cn, sConnString Set GetConnection = cn Else Set GetConnection = Nothing End If End If On Error GoTo 0 End Function 'GetConnection() |
The function first tries to get an open Connection object from the m_colConns collection using the requested connection string as the key value. If a connection exists, the Err.Number value is set to zero and the function returns the Connection object. If the Err.Number value is not zero, no connection exists yet, so the code creates a new Connection object, opens it, and then adds it to the m_colConns collection using the connection string as the key value. Finally, the function returns the new or existing Connection object, which is in turn used by the OLAPLookup function to execute an MDX query to get the requested aggregate.
You might wonder why I didn't implement this function as a pool, checking out connections and returning only connections that are not currently used by other callers. Because the function library itself is apartment model_threaded, and because the Spreadsheet control evaluates functions in a serialized manner, two threads can never enter the OLAPLookup function at the same time. Since I use the Connection object only in the context of the OLAPLookup function, I do not need to mark the Connection object as "in use" because there is no danger of another client requesting the same Connection object while the first client is using it. If I implemented this function in a free-threaded library, I would have to rewrite this function to pool connections instead, or I could use the built-in connection pooling provided by the Microsoft Data Access Components (MDAC).
The OLAPLookup Function Wizard
To make it easier for a user to enter or edit an OLAPLookup formula, I created a custom user interface wizard. This wizard is exposed from the same DLL as the custom functions, so any application using the OLAPLookup function can also use the wizard without additional files. To try the wizard yourself, run the project in Visual Basic, select the cell you want to contain the function, and click the OLAP Cube toolbar button. You should then see a wizard user interface that looks like Figure 11-1.
Figure 11-1. The OLAPLookup Function wizard.
I realize that my use of the term "wizard" here is rather loose, as this is a single page dialog box. However, three clear steps are required and they must be performed in order. I could have implemented this as a three-page wizard with Next and Back navigation buttons, but the first two steps require so little screen real estate that it seemed like a waste.
To connect to a data source, click the top button on the wizard. This will launch the prompting dialog box from the Microsoft SQL Server OLAP Services provider (you can easily change the code to use another OLAP provider), in which you can enter a server name or a path to a cube file, such as the cube file in the Data folder on your companion CD. After connecting to the data source, you can select a cube within the data source. After selecting a cube, the wizard loads the tree view with the dimensions and first-level members and checks the default member from each dimension. The result should look like Figure 11-2.
Figure 11-2. Selecting a cube cell with the OLAPLookup Function wizard.
You can now change the selected member for any dimension, and the unique member name tuple displayed below the tree view should change to reflect the new selection. When you exit the wizard using the OK button, it returns a fully constructed OLAPLookup formula that the test application inserts into the selected spreadsheet cell. The Spreadsheet control then calls the OLAPLookup function I showed you earlier, and the resulting aggregate displays in the cell. The first time you access the data source, a slight delay will occur while the OLAPLookup function opens a new connection; however, further recalculations should happen almost instantaneously.
Often, a user will also want to edit this new formula and select different cell coordinates. The OLAPLookup Function wizard is reentrant, meaning it can edit an existing OLAPLookup formula. To try this, select the cell in which you inserted a new OLAPLookup function and press F2 to start editing the cell. After confirming that you want to use the wizard to edit the cell, the wizard displays with the same settings you had when you created the formula. You can then change cell coordinates or even the data source or cube name and click the OK button to change the function. To see how I automatically invoke the wizard when a user attempts to edit the cell, see the ssTest_StartEdit event handler in the test form's code module.
Accomplishing the reentrancy was rather difficult, and the solution at which I eventually arrived involved using another object to hold a structured definition of an OLAPLookup formula. This object is an instance of the OLAPLookupDef class in the OLAPLookupDef.cls file.
The OLAPLookupDef Class
OLAP member unique names are strange beasts. Each OLAP provider determines the format of a unique name, and client applications are never supposed to assume anything about the contents of a unique name. To the client application, the unique name is just an opaque string of characters. The provider can easily determine which member the unique name refers to, as well as the dimension in which the member exists. However, there is no easy or efficient way to determine a parent dimension given a member unique name.
This makes reentrancy in the wizard difficult. The OLAPLookup function takes a member unique name tuple as the last parameter. The tuple is just a set of unique names separated by commas. It is relatively easy to parse this apart using the Split function in VBA, but this is not totally reliable because a provider can embed a comma inside a member unique name that is framed by identifier tokens (such as "[" and "]"). Additionally, ADO MD offers no function to return a parent dimension name given a member name. You could iterate over all members in the cube until you found the member, but that would be exceedingly slow.
To combat these problems, I took the approach of creating a structured definition of an OLAPLookup function. Client programs pass this object to the OLAPLookup Function wizard and then hold onto it in case the user wants to edit the formula. We'll take a look at the class definition for the OLAPLookupDef class below.
Public ConnectionString As String Public CubeName As String Public CellCoordinates As Dictionary '------------------------------------------------------------------------ ' Class Init '------------------------------------------------------------------------ Private Sub Class_Initialize() Set CellCoordinates = New Dictionary End Sub |
The various member unique names that make up a cell's coordinates are stored as separate items in a dictionary. I use the parent dimension name as the key to the item. The client application (in this case, the test form) can store a set of these classes, each one associated with the address of the cell in which the user has inserted an OLAPLookup function. I accomplish this in the test form by using another Dictionary object that stores OLAPLookupDef objects using the address of the spreadsheet cell as the key.
When the test form edits an OLAPLookup function using the wizard, it checks this dictionary and hands back the existing definition object if it finds one. This code segment, taken from the EditOLAPLookup function in the test form, performs the necessary work:
|
' If the range has a formula If rng.HasFormula Then ' Check whether there is a stored OLAPLookupDef for ' the selected range If m_dictOLDefs.Exists(rng.Address) Then Set oldef = m_dictOLDefs.Item(rng.Address) End If End If ' Invoke the OLAPLookup Function wizard to create ' or edit the formula (modal) sFmla = m_wizFunctions.OLAPLookupWiz(oldef) If the selected spreadsheet cell has an associated OLAPLookupDef object, the code fetches it from the m_dictOLDefs dictionary and passes it to the wizard object's OLAPLookupWiz function. When the wizard finishes, the code continues and stores the newly edited OLAPLookupDef object back in the dictionary, using the address of the spreadsheet cell as the key:
|
Note that my use of the HasFormula property is critical to the proper operation of this routine. If the user has cleared the contents of a cell that once contained an OLAPLookup function, an OLAPLookupDef object will remain in the dictionary associated with the cell address. However, the HasFormula property will now return False because the cell no longer contains a formula. In this case, the code will hand an empty OLAPLookupDef object to the OLAPLookupWiz function and happily replace the old, invalid OLAPLookupDef object when the wizard returns.
The OLAPLookupWiz Function
The OLAPLookupWiz function is implemented in the FunctionWizards class and is the entry point for displaying the OLAPLookup Function wizard user interface. The function looks like this:
Public Function OLAPLookupWiz(Optional oldef As OLAPLookupDef = Nothing) _ As String Dim frmWiz As frmOLAPLookupWiz ' Wizard instance Set frmWiz = New frmOLAPLookupWiz ' Create the OLAPLookupDef if necessary If oldef Is Nothing Then Set oldef = New OLAPLookupDef End If ' Pass the OLAPLookupDef to the wizard Set frmWiz.OLAPLookupDef = oldef ' Show the wizard modally frmWiz.Show vbModal ' Pass back the wizard's outputs if not canceled If Not frmWiz.WizardCanceled Then ' Grab the new OLAPLookupDef object Set oldef = frmWiz.OLAPLookupDef ' Return the new function OLAPLookupWiz = "=OLAPLookup(""" & _ oldef.ConnectionString & """, ""[" & _ oldef.CubeName & "]"", """ & _ GetCoords(oldef) & """)" Else ' Wizard canceled; send back empty string OLAPLookupWiz = "" End If 'Wizard canceled End Function 'OLAPLookupWiz() |
The function starts by creating an instance of the wizard's form and sets the OLAPLookupDef public property exposed from that form. This seeds the form with the OLAPLookupDef object that the wizard will edit. Next the function calls the Show method of the form, telling it to display modally. When displayed modally, the Show method will block until the form is dismissed—meaning that the line after the Show method is not executed until the user has clicked OK, clicked Cancel, or closed the form using the Close button on the title bar.
After the Show method, the function looks at another public property of the form, WizardCanceled, to determine whether the user canceled the wizard or chose the OK button. If the wizard was not canceled, the code builds a complete OLAPLookup formula from the OLAPLookupDef properties and returns it. If the wizard was canceled, the code simply returns an empty string and the test form checks the return value for an empty string to determine whether it should update the formula of the selected spreadsheet cell.
The OLAPLookup Wizard Form
Since there is quite a bit of code (about 440 lines) in the OLAPLookupWiz form, I do not have time to discuss it all here. I will cover only the highlights in this section; however, you can refer to the OLAPLookupWiz.frm file on the companion CD to see the rest of the code.
As I noted in the previous section, the wizard form has a few public properties. The most important is the OLAPLookupDef property, which allows the caller to hand the wizard form an OLAPLookupDef object for editing. The property procedures follow:
Public Property Get OLAPLookupDef() As OLAPLookupDef Set OLAPLookupDef = m_oldef End Property 'OLAPLookupDef Get Public Property Set OLAPLookupDef(newval As OLAPLookupDef) Set m_oldef = newval End Property 'OLAPLookupDef Set |
These are simple procedures that get and set a class variable called m_oldef, which I will use throughout the rest of the form's code.
When the form is displayed, the form's Load event handler runs. Here is what that event handler's code looks like:
Private Sub Form_Load() m_fCancel = True cbxCube.Enabled = False tvwMembers.Enabled = False ' If the OLAPLookupDef is valid ' and has a connection string ' try to reload all the information If Not (m_oldef Is Nothing) Then If Len(m_oldef.ConnectionString) > 0 Then Connect SelectCbxItem cbxCube, m_oldef.CubeName txtCoords.Text = GetCoords() End If End If End Sub 'Form_Load() |
This code defaults the cancel flag to True (this flag is set to False in the btnOK button's Click event handler) and performs some initial disabling of the cbxCube drop-down list and tvwMembers tree view control. Then it determines whether the OLAPLookupDef object is valid and whether the ConnectionString property is not blank. If both conditions are true, the code calls the Connect method to connect to the data source, attempts to select the cube named in the CubeName property of the OLAPLookupDef, and sets the txtCoords text box to display the current cell coordinates.
Watch Out for the Non-Short-Circuiting If Conditions in VBA!You might think that I could have shortened part of the code I just discussed to something like this:
But in fact I cannot do so without risking a runtime error when m_oldef is Nothing. "If" conditions in VBA do not short circuit, meaning VBA attempts to evaluate every subcondition and then determines whether the overall condition is True. In C, the code would never evaluate the second part of this condition if the first part evaluated to False—False And (anything) will always be False, so there is no need to evaluate the remainder of the condition. To prove that "If" conditions in VBA do not short circuit, try running the following code in Visual Basic:
When you run this program, the message box will never appear. You will get a runtime error on the If line because VBA attempts to evaluate the subcondition obj.Count > 5 even though the first part is False.
Let's now take a look at the Connect method, called from the form's Load event handler or when the user clicks the Connect To An OLAP Data Source button on the wizard:
Private Sub Connect() On Error GoTo Err_Connect ' If the connection string is blank If Len(m_oldef.ConnectionString) = 0 Then ' Default the provider to msolap (OLAP Services) ' You can change this to another provider because the rest of ' the code is generic ADO MD code m_cnOLAP.Provider = "msolap" Else ' Connect using the connection string defined in ' the OLAPLookupDef object m_cnOLAP.ConnectionString = m_oldef.ConnectionString End If 'Len(m_oldef.ConnectionString) = 0 ' Set the prompting property to prompt if necessary m_cnOLAP.Properties("Prompt") = adPromptComplete ' Open the connection m_cnOLAP.Open ' Get the fully completed connection string m_oldef.ConnectionString = m_cnOLAP.ConnectionString txtConnString.Text = m_oldef.ConnectionString ' Load the cube's drop-down list LoadCubes ' Disable the connect button btnConnect.Enabled = False ' Exit successfully Exit Sub Err_Connect: ' Error connecting! txtConnString.Text = "Error Connecting!" Exit Sub End Sub 'Connect() |
This method first checks the ConnectionString property of the OLAPLookupDef object to see whether it is blank. If it is, the method sets the Provider property of the Connection object to "msolap" so that ADO knows what OLE DB provider to load. The "msolap" provider name signifies the provider for OLAP Services. You can change this to use any other OLE DB for OLAP provider because the rest of the wizard and OLAPLookup function code should work against any OLE DB for OLAP provider.
The code then sets the Prompt property of the underlying OLE DB provider to adPromptComplete. The Prompt property is exposed only through the Properties collection because it is actually a property of the underlying OLE DB provider rather than ADO itself. The adPromptComplete setting tells the provider to prompt the user for more connection information only if necessary. If the connection string contains everything the provider needs, it does not prompt for more information and connects to the data source.
This method next attempts to open the Connection object. If successful, it uses the LoadCubes method to load the cube drop-down list with the names of all the available cubes in the data source. Let's now take a look at the LoadCubes method:
Private Sub LoadCubes() Dim cdef As CubeDef Set m_ctlg.ActiveConnection = m_cnOLAP cbxCube.Clear For Each cdef In m_ctlg.CubeDefs cbxCube.AddItem cdef.Name Next cdef cbxCube.Enabled = True If Me.Visible Then cbxCube.SetFocus End Sub 'LoadCubes() |
The LoadCubes method uses ADO MD objects to enumerate the available cubes in the current data source. To accomplish this, the method sets the ActiveConnection property of the m_ctlg variable (an ADOMD.Catalog object) to the newly opened Connection object. After doing so, the code enumerates the cubes using the CubeDefs collection and adds each cube's name to the cbxCube drop-down list. After loading the list, the code enables the control, and if the form is visible, sets focus to it so that the user can immediately select a cube. Always remember to check the form's visible setting before calling SetFocus—calling SetFocus while the form is invisible (which it still is during the form's Load event) will generate a runtime error.
Now that the cbxCube drop-down list is populated, the user can select a cube and begin selecting members to build the OLAP cell coordinates. When the user selects a cube, the form runs the LoadMembers method to populate the tvwMembers tree view with the list of dimensions and first-level members. The first part of this method follows:
Private Sub LoadMembers() Dim cdef As CubeDef ' CubeDef object Dim dm As Dimension ' Dimension object Dim hier As Hierarchy ' Hierarchy object Dim mem As Member ' Member object Dim ndHier As Node ' Node for the hierarchy Dim ndMem As Node ' Node for the member Dim idxImage As Variant ' Index of the node's image ' Clear the tree view and the CellCoordinates if necessary tvwMembers.Nodes.Clear If cbxCube.Text <> m_oldef.CubeName Then m_oldef.CellCoordinates.RemoveAll txtCoords.Text = "" End If ' Get out if the cube name is blank If Len(cbxCube.Text) = 0 Then Exit Sub ' Get the CubeDef object for the selected cube Set cdef = m_ctlg.CubeDefs(cbxCube.Text) |
The method begins by clearing the tree view of any existing nodes and also clearing the dictionary of coordinates if the cube name has changed. (Coordinates from another cube would no longer be relevant.) It then gets the ADOMD.CubeDef object for the selected cube, which it uses to obtain the list of dimensions and first-level members in the cube:
|
There are two critical aspects of this code segment you should notice. First, I use the Tag property of the tree view node to hold an instance of an ADO MD object that the node is representing. Because the Tag property is a Variant, it can hold an object instance, which makes it easy to retrieve the object that the node represents later when Visual Basic passes a Node object in a tree view event.
Second, I preset the selected member for each dimension to the default member of that dimension. In an OLAP data source, one member in each dimension must be declared as the default member. When you execute an MDX query, any dimension not used on the result axes is implicitly included in the WHERE clause as a slicing dimension. If you do not explicitly name a member from such a dimension, the OLAP query processor will use the default member as the slicing member. Because the OLAPLookup Function wizard needs a member from each dimension, the logical choice is to use the default member for the dimension until the user chooses a different member. To obtain the default member of a dimension, use the DEFAULT_MEMBER property in a Hierarchy object's Properties collection. In this code, I use the first hierarchy defined in the dimension.
Variants and Object Default Members: A Deadly CombinationWhile building this wizard, I spent about three hours debugging a seemingly impossible situation. When I attempted to use the wizard to edit an existing OLAPLookupDef object, I received a runtime error when the code attempted to access an item in the CellCoordinates dictionary. The runtime error indicated that "the object was no longer valid," which seemed odd considering that I thought I was putting strings into the dictionary. (Strings should never expire during a program and produce an error like this.) The key phrase here is I thought I was putting strings into the dictionary.
After becoming completely befuddled by this, I used the TypeName function to ask the dictionary what type it thought the item was. I of course expected to see String come back. Instead, the TypeName function returned Property, and then it all became clear. The previous code used to look like this:
' If no coordinate exists for this dimension yet If Not (m_oldef.CellCoordinates.Exists(dm.UniqueName)) Then ' Add the default member as the coordinate m_oldef.CellCoordinates.Add dm.UniqueName, _ hier.Properties("DEFAULT_MEMBER") End If 'No coordinate for dimensionThe important piece missing was the Value property on the end of the hier.Properties("DEFAULT_MEMBER") line. Value is the default member of the ADODB.Property object, so you would expect this to still work fine. However, my problems were due to the often misunderstood behavior that occurs when you combine Variants and members that return objects with default members.
The Dictionary.Add method takes a Variant for the second parameter. Because a Variant can hold an object reference, you can pass an object to this parameter and the dictionary will happily store a reference to the object returned from the Properties collection. In this case, it does not store the value of the default member. So the whole time, I was building a dictionary full of Property objects rather than member unique name strings. The rest of my code worked fine because whenever I compared the dictionary entry to another string, VBA knew to retrieve the default member and compare using it, because comparing a string to an object makes no sense. However, when I exited the wizard, the OLAP Connection and Catalog objects were destroyed. This automatically marks the Property objects as zombied, which means they still exist as objects but all calls to methods and properties produce a runtime error like the one I saw. The moral of the story: Don't rely on default members. Always qualify your statements using the property name even if it is the default property.
The LoadMembers method loads only the first-level members from each dimension. Loading all members can take quite a while, especially if your dimensions contain numerous members. Instead, I use the tree view's Expand event to dynamically load the child members for the member the user is about to expand. Using this technique I load only as many members as the user views, which typically is not a large number. The Expand event calls the LoadChildMembers method, shown here:
Private Sub LoadChildMembers(mem As Member, nd As Node, _ sDimName As String) Dim memChild As Member ' New child member Dim ndChild As Node ' New child tree view node Dim idxImage As Variant ' Node image index ' Loop over all the children for the specified member For Each memChild In mem.Children ' If the member is the currently selected coordinate ' for the dimension If memChild.UniqueName = _ m_oldef.CellCoordinates.Item(sDimName) Then ' Use the check node icon idxImage = "Check" Else ' Use the uncheck node icon idxImage = "UnCheck" End If 'Member is current coordinate ' Add the new child member to the tree view Set ndChild = tvwMembers.Nodes.Add(nd, tvwChild, _ , memChild.Caption, idxImage, idxImage) ' Set the new node's tag to the child member Set ndChild.Tag = memChild ' Add the fake node to get the expand indicator tvwMembers.Nodes.Add ndChild, tvwChild, , FAKE_NODE Next memChild End Sub 'LoadChildMembers() |
This method enumerates the parent member's Children collection, which contains a Member object for each child member. The method loads each child member into the tree view as a child node of the parent node. The method also checks whether this child member is the currently selected member for the dimension. If it is, the method sets the child's image to reflect that. Finally, the method sets the child node's Tag property to the Member object instance so that you can access it again from the tree view node.
The last bit of code I will describe executes when the user clicks a node in the tree view. If the user clicks a member node that is not the currently selected member for the dimension, the wizard must update the CellCoordinates dictionary to note the new member unique name for the dimension. This is accomplished in the NodeClick event handler of the tree view:
Private Sub tvwMembers_NodeClick(ByVal Node As MSComctlLib.Node) Dim ndDim As Node Dim ndOld As Node ' If the node is a member and not a hierarchy If Node.Image <> 1 Then ' Uncheck the current member for the parent dimension Set ndDim = GetParentDim(Node) Set ndOld = FindNode(ndDim, m_oldef.CellCoordinates(ndDim.Key)) If Not (ndOld Is Nothing) Then ndOld.Image = "UnCheck" ndOld.SelectedImage = "UnCheck" End If ' Set the new node as the new coordinate, and check it m_oldef.CellCoordinates.Item(ndDim.Key) = Node.Tag.UniqueName Node.Image = "Check" Node.SelectedImage = "Check" txtCoords.Text = GetCoords() End If 'Node is not a hierarchy End Sub 'tvwMembers_NodeClick() |
The code first checks whether the clicked node is a member node and not a node representing the whole dimension. To do so, it checks the node's Image property, which returns the index of the image used for the node. Dimension nodes use the first image, so if the node's image does not equal 1, the code continues.
The code next finds the tree view node associated with the previously selected member for the current dimension. It does this by finding the parent dimension node and then finding the member node that matches the member unique name in the CellCoordinates dictionary for that parent dimension. Once it finds the tree view node, it sets that node's Image and SelectedImage properties to the unchecked image.
The code continues by updating the entry in the CellCoordinates dictionary for the current dimension. It then sets the Image and SelectedImage properties for the clicked node to the check image. Finally, it updates the txtCoords text box to show the updated cell coordinates.
Because this event keeps the CellCoordinates dictionary updated with the current selections, when the user closes the wizard, the function calling the wizard can iterate over the coordinates and build a complete OLAPLookup formula.
EAN: 2147483647
Pages: 111