Introduction to MSSQL Server Analysis Services: Presentation Nuances: CrossTab View - Same Dimension - Page 10

Verify Results - from a Reporting Perspective

Let's use the MDX Sample Application to practice setting up a report to display the data as requested by consumers. This is a great way to get a feel for the adequacy of the components we have designed for this purpose, as the same MDX we construct to return the data requested by the information consumers within the Sample Application will likely be used as the source query within the reporting application. (We will use the above structural preparation for a report that we will construct using MSSQL Server Reporting Services as the "front end" in an article we publish later, in the MSSQL Server Reporting Services series here at Database Journal.)

We recall that the requirement was that we display U.S.A. on the "x-" axis and Warehouse City, represented by a "City, State" combination, to appear across the "y-" axis. The consumers also only wish to present the Warehouse Profit and % Profit Contribution along the "x-" axis, which, as we will see, is easily accommodated with the other requirements. Let's initialize the MDX Sample Application, and take the following steps:

We are initially greeted by the Connect dialog, depicted in Illustration 35.

The illustration above depicts the name of my server, MOTHER1, and properly indicates that we will be connecting via the MSOLAP provider (the default).

The MDX Sample Application window appears.

The MDX Sample Application window should resemble that shown in Illustration 36, complete with the information from the Warehouse cube displaying in the Metadata tree (left section of the Metadata pane).

We will begin creating our query with a focus on returning results in the same general formation as the Data View we left in the Cube Editor. We will then move from the core query to a more precise presentation of the data as it was requested by the information consumers (and for which they provided a "draft" that was reproduced in Table 1 above).

We will initially retrieve our two axes, containing members of the same dimension (Store) with the primary Warehouse Profit measure, by taking the following steps.

-- ANSYS34-1 Core Query - Same Dim in Both Axes of Crosstab
SELECT 
    { [Store].[All Stores].[USA]} ON COLUMNS,
    {[Store Location].Members} ON ROWS
FROM  
   [CROSSTAB REPORTING]
WHERE
   ([Time].[1998], [Measures].[Warehouse Profit])

The results dataset appears as depicted in Illustration 37.

In the results dataset we note that all store locations appear, and that there are several "blank" cells. This is largely a result of our having asked for "USA only" in our column specification. Because the column is retrieving USA only, values at the intersections of USA and non-domestic locations are null.

We will further polish the results set by ridding it of the nulls, with the NON EMPTY() keyword, in the next steps, both for cosmetic effects (making the dataset more compact), and for eliminating potential confusion among the information consumers. We will do so within a modification to our last query.

-- ANSYS34-2 Core Query - Eliminating "Empties"

   
NON EMPTY

   
NON EMPTY({[Store Location].Members}) ON ROWS

The query, with our modifications to the ON ROWS specification, resembles that shown in Illustration 38.

The query executes, once again, and the results dataset appears as depicted in Illustration 39.

Because we will now rearrange a couple of things to bring the query output into alignment with the presentation requested, we will create a new query at this stage. The core axes portion remains the same, but we will be using a CROSSJOIN() function to combine our measures with the existing dimension in the ON COLUMNS specification. Moreover, we will be adding the % Profit Contribution calculated member / measure within those measures.

We will initially retrieve our two axes, containing members of the same dimension (Store), with the primary Warehouse Profit measure and the % Profit Contribution calculated measure, by taking the following steps.

-- ANSYS34-3 Final Query Supporting Presentation Requirements
SELECT 
  CROSSJOIN({[Store].[Store Country].[USA]},{[Measures].[Warehouse Profit], 
      [Measures].[% Profit Contribution]}) ON COLUMNS,
  NON EMPTY({[Store Location].Members}) ON ROWS
FROM  
   [CROSSTAB REPORTING]
WHERE
   ([Time].[1998])

The results dataset appears as depicted in Illustration 40.

We have now provided for all aspects of the requested data presentation. Our structural changes, coupled with the appropriate query within the respective reporting application, will allow for the generation of a report that resembles the draft we received from the information consumers. We can also support complementary charts and other report objects that might rely upon the values we have retrieved.