The IIF()
statement that comprises the definition of the ApproxSqFootage calculated
member uses the numeric, versus string, option for returned
values. (We discussed these options in String / Numeric Functions: Introducing the IIF() Function). Because member properties, by
definition, are strings (even when storing numeric information), the
string must be converted to a number for the function to allow us the "0"
in the second return value expression in the function.
The
purpose of the second WITH MEMBER section of the query is to create a calculated
member to provide SalesPerSqFt for each Store location. This
value is simply defined as Store Invoice divided by the new ApproxSqFootage
value.
12.
Insert the
following syntax, between [Measures].[Store Invoice] and the closing
bracket of the ON COLUMNS line (first line under the SELECT
keyword of the query:
,[Measures].[ApproxSqFootage],[Measures].[SalesPerSqFt]
NOTE: Be sure to insert the comma after [Measures].[Store
Invoice], and before the added syntax, so that a comma separates the three
measures. Placement of commas is important within later steps as well, where
one appears in a similar placement to that in Step 12 above.
We are
simply adding the two new calculated members to the SELECT clause to
facilitate their retrieval in our query.
Our modified query appears, with the modified sections in red rectangles, as shown in Illustration 3.
Illustration 3: Modified Query in Query Pane (with
Modifications in Rectangles)
13.
Execute the
query by clicking the Run Query button in the toolbar.
Analysis
Services populates the Results pane, and the DataSet depicted in Illustration 4 appears.
Illustration 4: Results DataSet with New Calculated
Members
While
it appears that the columns we wish to present have materialized, our
calculated members do not appear to be presenting the expected data in every
case. First, we see the dreaded 1.#INF in several of the SalesPerSqFt
fields, an indicator, in this case, of a "divide by zero"
condition.
Next,
we see that some of the ApproxSqFootage fields are populated by zeros -
not that our function is not working: it certainly is, or these would be
nulls. However, the zeros indicate "no square footage," for whatever
reason, captured for the associated Stores, and our instructions from
the information consumers clearly stated that they were interested in
presenting "only operating store locations, which had invoice activity /
area values for year 1998." (One of the locations in our current DataSet,
HQ, is obviously an "overhead account" included in the Stores
dimension for some other reason - a reason that, while perfectly appropriate
within another context, does not apply from the perspective of our immediate
criteria.)
Finally,
it is obvious that the Stores are not ordered by SalesPerSqFt in
descending order, another request of the intended audience. In cases like
this, I usually consider sorting, as well as formatting, in a later step
anyway, because I like to get the population of the DataSet correct first. We
will remedy the other issues at this point, and then manage the ordering and
formatting next.
Let's
resolve the "holes" in the data first. We can do this quite easily
with the highly useful FILTER() function, as we shall see in the next
steps.
14.
Select File
-> Save to resave file MDX27-2 in
the location chosen earlier. Leave the file open for the next step.
15.
Replace the
comment line with the following:
-- MDX27-3 Internal Sales / Store Area Analysis: Step 3: Filter Empties
and Zeros
NOTE: Be sure to comment out the second line if the above text "wraps"
in the Sample Application. Mine did not, in this or later steps, as we can see
in the associated illustrations; the "wrap" in the text above is a
function of page margins for this article.
16.
Save the file
as MDX27-3, to protect MDX27-2.
17.
Insert the
following syntax, immediately within the left curly brace ("{")
on the ON ROWS line of the SELECT clause,
FILTER(
between the brace and [Store].[Store Name].Members .
18.
Insert the
following syntax, immediately to the right of [Store].[Store Name].Members,
within the ON ROWS line of the SELECT clause,
, ([Measures].[ApproxSqFootage] > 0))
We are
placing the FILTER() function within the curly braces, and placing [Store].[Store
Name].Members into the FILTER() function, as the set, or first entry,
which is followed by the search condition ([Measures].[ApproxSqFootage] > 0)).
Our modified query
appears, with the modified sections in red rectangles, as shown in Illustration 5.
Illustration 5: Modified Query in Query Pane (with
Modifications in Rectangles)
19.
Execute the
query by clicking the Run Query button in the toolbar.
The
Results pane is
populated with the DataSet depicted in Illustration 6.
Illustration 6: Results DataSet with FILTER() in Place
As we
can see in the illustration above, our filter seems to have done the trick.
The population of the DataSet appears to fit the business requirements. All
that remains is ordering, and a couple of cosmetic touches, to please the
intended audience.
20.
Select File
-> Save to resave file MDX27-3 in the
location chosen earlier. Leave the file open for the next step.
21.
Replace the
comment line with the following:
MDX27-4 Internal Sales / Store Area Analysis: Step 4: Order DataSet /
Formats
22.
Save the file
as MDX27-4, to protect MDX27-3.
23.
Insert the following
syntax, immediately within the left curly brace ("{") on the ON
ROWS line of the SELECT clause,
ORDER(
between the brace and FILTER (inserted in the last set
of modifications).
24.
Insert the
following syntax, immediately to the right of ([Measures].[ApproxSqFootage]
> 0)), which we added as part of the FILTER() function in the
last set of modifications, within the ON ROWS line of the SELECT
clause,
, ([Measures].[SalesPerSqFt]), BDESC)
» 
Comment and Contribute
