Introduction to MSSQL Server 2000 Analysis Services: Putting Actions to Work in Regular Cubes - Page 4
March 15, 2004
We arrive at the Define the action syntax dialog. The appearance of this dialog depends upon our selection of Action type in the previous dialog. Here we are provided with a place to type, or cut and paste, the syntax that will appear in the browser that our Action, by its nature as an HTML-type Action, will initialize when a user calls the Action from the cube browser or another application.
We will need to do a bit more than to simply supply HTML, however, if we want to use information from the cube in our example. We will need to embed some simple MDX within the HTML to bring out some of the data elements that the information consumer sees in the launched browser display.
While we could return data from another cube in the FoodMart 2000 Analysis Services database, we will keep distractions to a minimum for purposes of this exercise, and build an HTML page that sports a handful of basic MDX expressions to populate the intended display. We will do this in such a way that the MDX can be examined independently, instead of in a single, nebulous clump that mixes HTML and MDX, without consideration for easy review.
18. Type (or cut and paste if you like) the following into the Syntax box:
"<html><head> <title>Store Information</title> </head> <body BGCOLOR=""#99CCCC""> <ul> <table CELLSPACING=""2"" CELLPADDING=""2"" BORDER=""0"" Bordercolor=""#FF3333"" ALIGN=""LEFT"" VALIGN=""TOP"" WIDTH=""300""> <tr><td VALIGN=""TOP"" ALIGN=""LEFT"" WIDTH=""100%"" height=""100%"" colspan=2> <span name=""storeno""> <h3>"+store.currentmember.name+"</h3> </span> <hr> </td> </tr> <tr><td width=""55%""> <div class=""title"">Location:</div> </td><td colspan=2> <span name=""title"">"+store.parent.name+"</span> </td></tr> <tr><td width=""55%""> <div class=""title"">Local Manager:</div> </td><td colspan=2> <span name=""title"">"+store.currentmember.properties("Store Manager")+"</span> </td></tr> <tr><td width=""55%""> <div class=""title"">Store Type:</div> </td><td colspan=2> <span name=""title"">"+store.currentmember.properties("Store Type")+"</span> </td></tr> <tr><td width=""55%""> <div class=""title"">Floor Area:</div> </td><td colspan=2> <span name=""title"">"+store.currentmember.properties("Store Sqft")+" Sq. Feet</span> </td></tr> </table> </font> </ul> </body> </html>"
I have intentionally left spaces in the syntax to separate the portions that call upon MDX (specifically the .CurrentMember and .Parent functions, as well as functions using member properties), to make them more readily understandable.
While MDX basics are beyond the scope of this tutorial, my MDX Essentials series is a good place to begin learning hands-on MDX. For a tutorial covering the use of the .CurrentMember function, see my article MDX Essentials: MDX Member Functions: "Relative" Member Functions. For a tutorial that focuses on the use of the .Parent function, see MDX Member Functions: The "Family" Functions.
To assist in building syntax, the MDX Builder button on the Define the action syntax dialog will initialize an MDX "helper" applet. Personally, I find such "helper" tools of little benefit, but the MDX Builder might be useful in enabling a developer to mentally instantiate the layout of the cube design, see the set of functions and other components that are available, and so on.
The expression store.currentmember.name in the second string above is intended to retrieve the Store name - upon which we right click in the client application / browser - from the cube, and to present it in the top row of a small table. This will act as the "title" of the simple HTML page that we are creating.
The third string sets up a row containing the label "Location," and presents the geographical location of the Store, which is the hierarchical level to which the Store belongs in the cube hierarchy (and is thus its parent). We are simply retrieving the name of the Store's parent with this line of the syntax.
Strings four, five and six in our syntax example exploit the retrieval of member properties. Member properties are attributes that can be valuable in just such scenarios as this; they allow the storage of data that we might want to make available for easy access, but that we do not want to house among the population of largely quantitative data for which we have designed our cube. In this case, we retrieve the name of the Manager, the Type of Store, and the square footage floor area for the store under consideration.
The top and bottom strings of the syntax we have entered construct the HTML page with a basic presentation format for the data we are retrieving.
The Define the action syntax dialog of the Action Wizard, with our newly input syntax partially shown, appears as depicted in Illustration 8.
19. Click Next to accept the input and proceed to the next dialog.
Many syntax errors will be detected when clicking the Next button. If you experience such an error during our exercises, back up and check your typing.
We arrive at the Finish the Action Wizard dialog, where we name the Action and we are given an opportunity to review a summary of its setup.
20. Type the following into the Action Name box.
21. Click Finish to accept the settings and create the Action.
The Action Wizard closes, and we see the Action appear in the Actions folder of the tree pane of the Cube Editor. Our Action is now in place and ready for use by the intended information consumers.