In the results
set, we note that the new calculated member, Store Size, appears, with single
zeros filling the fields for which no square footage is defined in the cube
(the "nulls" are converted to zeros). We can see that the text
strings that occupy the Store SqFt member property have been converted
to numerical values, as the nulls that exist in the cube for certain members
have become zeros instead.
12.
Save the query
with changes as MXAS09-2.
Next,
let's change the SELECT clause to retrieve and display not only the Store
Names (the lowest level of the Store dimension), but to retrieve and
display the "rollup" levels in the hierarchy of Store City, Store
State, and Store Country.
13.
Modify the comment line in the
query to read as follows:
-- MXAS09 - 3: Tutorial Query - Step 3
Within the SELECT
clause of the query, immediately following the {[Measures].[Store Profit],
[Measures].[Store Size]} ON COLUMNS, line, modify the next existing line as
follows:
Step 2 Affected Line:
{[Store].[Store Name].Members} ON ROWS
Step 3 Modified Line:
{[Store].Members} ON ROWS
The modification above
changes the SELECT clause to retrieve the members of the Store
dimension in general, which will include all members - not just Store
Names. This will mean that the individual stores, as well as the level
members to which they belong, will be retrieved.
The
modified query appears in the Query pane, in its entirety, as depicted
in Illustration 6:
Illustration 6: Step 3,
with Modification Circled, as Displayed in the Query Pane
14.
Execute the Step 3 query
by clicking the Run button.
The
result dataset appears partially displayed in Illustration 7 below:
Illustration 7: Partial
Results Dataset, Query Step 3
We notice
that, at the rollup levels, we have #ERR appearing as the result in the
affected fields. This occurs because, although we have converted the Store
SqFt member property to a numeric value, we have yet to make provision for
an aggregation to occur; the square footage values are held at
the member level, and not at the various dimension levels to which the
stores belong in our cube. We will handle this with the next step, where we
will create a calculated member, based upon the Store dimension, to sum
the individual store square footages at the appropriate rollup levels.
We will
create a calculated member that correctly handles the summing, while leaving
our original calculated member in place for comparison purposes.
15.
Save the query
with changes as MXAS09-3.
16.
Modify the comment line in the
query to the following:
-- MXAS09 - 4: Tutorial Query - Step 4
17.
Within the WITH
statement of the query, immediately following the 'Val(Store.CurrentMember.Properties("Store
SqFt"))', line, add the following syntax to create a new calculated
member for Total Store Area:
MEMBER[Measures].[Total Store Area] AS 
'SUM(Descendants([Store].CurrentMember, [Store Name]) , [Store Size])'
18.
Within the SELECT
statement of the query, in the first line, modify the syntax to include
selection of the new calculated member, Total Store Area:
Step 3 Affected Line:
{[Measures].[Store Sales], [Measures].[Store Size]} ON COLUMNS,
Step 4 Modified Line:
{[Measures].[Store Sales], [Measures].[Store Size],
[Measures].[Total Store Area]} ON COLUMNS,
The
modified query appears in the Query pane, as shown in Illustration 8.
Illustration 8: Step 4,
with Modifications Circled, as it Appears in the Query Pane
19.
Execute the Step 4 query
by clicking the Run button.
The
results dataset appears as partially depicted in Illustration 9.
Illustration 9: Step 4,
Partial Results
The "parallel
calculated member," Total Store Area, appears alongside its
predecessor, and appears to have overcome the limitations of the Store Size
calculated member. We see rollups occurring accurately, in that the individual
stores' square footages now add correctly at the corresponding Store City,
Store State, and Store Country levels.
20.
Save the query
with changes as MXAS09-4.
Were
this destined to support a "real world" reporting platform for a
client, I would perform formatting at this stage, after removing the Store
Size calculated member from the SELECT statement, so as not to
display it. We will leave the query at this stage, however, with an invitation
to all readers to play with the layout further, as time is available.
With
this example, we have advanced another step within our examination of calculated
members, retrieving a member property and returning it, after a conversion with
the val function, in a way that allows it to deliver more value to the
information consumers. We have taken steps to leverage the property beyond its
standard capability to store information for the member that contains it, and
to provide rollup values through the SUM function. This demonstrates
additional possible uses for the aggregation of values using a non-measure
calculated member.
Summary and Conclusion ...
In this lesson, we extended our growing familiarity with
calculated members, to include the selection and retrieval of member
properties. We briefly discussed the nature of member properties,
providing illustrations of how they can be used to help us to meet our business
needs. We then focused upon the
selection of member properties within our calculated members, performing a
practice example in which we used member properties to provide square footage
of our store locations, after converting the values concerned to a usable
datatype, to further demonstrate possible ways that we might add value to the
projects we develop for information consumers.
»
See All Articles by Columnist William E. Pearson, III
» 