If the crosstab that you defined in the previous lesson is not already open, in the Applications-Navigator pane, right-click Sales Analysis Crosstab and click Open.

On the toolbar in the upper left corner of the crosstab, click the Query Editor tool.

The Query Editor tool launches the re-entrant Query Wizard.

3.

In the Query Wizard, select the Layout tab.

Note: The Layout tab that is contained within the re-entrant Query Wizard is also represented as a Step in the Presentation Wizard. In the previous lesson, the instructions for creating your crosstab and graph did not include steps that demonstrated this Query Builder feature. Therefore, you can also specify the layout of a presentation object when you first create that object.

To rearrange dimensions, select a dimension, and use a drag-and-drop operation to move the dimension to the desired position.

Rearrange the dimensions so that the layout appears as follows:

Click OK to to apply the changes and exit the wizard.

Observe that the layout of the crosstab has changed.

From the File menu, select Save All to save your work.

The re-entrant Query Wizard is opened.

In the Query Wizard, select the Dimensions tab.

Note: The Dimensions tabs that is contained within the re-entrant Query Wizard is also represented as Steps in the Presentation Wizard. In the previous lesson, the instructions for creating your crosstab and graph did not include steps that demonstrated this Query Builder feature. Therefore, you can also specify the dimension selections for a presentation object when you first create that object.

In the Choose box, select Geography.

Click the Remove All Items shuttle button ('<<') to remove the current selection.

In the Available list, select the Members tab and click the drill symbol ('+'), which is located to the left of World total, then again click the drill symbol ('+') to expand Americas. The list of sub-regions appears.

Click the drill symbol ('+') located to the left of Northern America and Southern America to display the countries associated with each sub-region.

Using the mouse and the 'Shift' key, multi-select the following Geography dimension members: 'Americas', 'Northern America', 'Canada', 'United States of America', 'Southern America', 'Argentina' and 'Brazil'.

Click the Add Selected Items shuttle button ('>') to move Americas group to the Selected List.

Click Apply to apply the changes. Do not exit the Query Wizard.

Ensure that the Dimensions tab is selected in the Query Wizard. In the Choose box, select Channel..

Click the Remove All Items shuttle button ('<<') to remove the current selection.

In the Available list, click the drill symbol ('+'), which is located to the left of Channel total. Then, click on the Add all Items shuttle button ('>>') to move all of the channels to the Selected list.

Click Apply to apply the changes. Do not exit the Query Wizard.

Ensure that the Dimensions tab is selected in the Query Wizard. In the Choose box, select Product.

Click the Remove All Items shuttle button ('<<') to remove the current selection.

In the Available list, select Product total. Click the Add Selected Items shuttle button ('>') to move Product total to the Selected list.

In the Selected list, select Product total. Click the drill symbol ('+') to expand Product total. The list of product categories appears.

Click OK to apply the changes and exit the Query wizard.

The crosstab should display in the following format:

From the File menu, click Save All to save your work.

Click the Query Editor tool on the toolbar of the Sales Analysis Crosstab.

In the Query Wizard, click the Dimensions tab and select Geography in the Choose box. The current selection for Geography dimension is displayed in the Selected region.

Click the Save button at the bottom of the Selected region. The Save Selection As dialog box is displayed.

In the Save Selection As dialog box, enter America Areas as the name.

Accept the default Save steps option and click OK to return to the Query Wizard.

Notes:

Saved steps: The "Save steps" option creates an XML object that records the steps that result in the dimension selection. This object can be thought of as a dynamic saved selection. For example, if you create a saved selection that contains a condition, you should choose this option. Each time the query is run, this kind of saved selection is re-evaluated using the current state of the dimension.

Saved members: The "Save members" option creates an XML object that records a static list of values that are specified for the dimension when the saved selection is created. Therefore, this object contains a list of dimension members that never change.

Now, still in the Query Wizard's Dimension tab, create a second regional selection for European Regional members by following these steps:

The new saved selections are ready for reuse.

Use America Areas as the default selection for the crosstab. To do this:

To preview the members that will be returned by a saved selection, click the Members tab in the Selected region.

Click OK to close the Query Wizard.

From the File menu, select Save All to save your work. Then, close the crosstab.

If the graph that you defined in the previous lesson is not already open, right-click Sales Analysis Graph and click Open.

In the toolbar of the Sales Analysis Graph, click the Query Editor tool.

The re-entrant Query Wizard is opened.

In the Query Wizard, select the Layout tab.

To rearrange dimensions, select a dimension, and use a drag-and-drop operation to move the dimension to the desired position. Rearrange the dimensions so that the layout appears as follows:

Click OK to to apply the changes and exit the wizard. The modified layout of the graph is shown here:

From the File menu, select Save All to save your work.

Click the Query Editor tool on the toolbar of the Sales Analysis Graph.

Select the Dimensions tab in the Query Wizard.

In the choose box, select Geography.

Click the Remove All Items shuttle button ('<<') to remove the current selection.

In the Available region, click the Saved Selections tab. Your two Geography Saved Selections are displayed.

Select America Areas and then click the Add Selected Items shuttle button ('>') to move the saved selection to the Selected List.

Ensure that the Dimensions tab is selected in the Query Wizard. In the Choose box, select Product.

Click the Remove All Items shuttle button ('<<') to remove the current selection.

In the Members tab of the Available list, expand Product total by clicking on the drill symbol ('+'). Select Photo and click the Add Selected Items shuttle button ('>') to move Photo to the Selected list.

In the Available list, select the Conditions tab.

In the Conditions list, there are templates that can be customized to match the condition that you want to specify.

Expand the Top/Bottom folder and select the template Top 10 based on Sales Revenue. Click the Add Selected Items shuttle button ('>') to move the condition to the Selected list.

In the condition, click the '10' hypertext link and change it to '5' so that the condition reads as follows:

Keep Product: Top 5 based on Sales Revenue

This condition will Keep the top 5 products in the Photo division. This means the Photo division member itself will not be part of the member selection that is returned by this condition.

Now, you will qualify this ranking condition so that it will be re-evaluated when any new Geography or Time member is selected from the Page dimension tiles in the graph.

First, click the Edit Step tool (pencil icon) to display the Edit Step box.

An OLAP ranking condition returns the appropriate dimension members for the condition by using a specific combination of values for the remaining,or qualifying, dimensions in the query. The values for these dimensions are displayed in the For box.

For example, as shown in the Edit Step dialog above, our ranking condition would return the top 5 products for Channel total, Americas, Promotion total and 1998.

In an OLAP ranking condition, you can use the Qualify button to select a different dimension member for each of the qualifying dimensions in the query.

Even more, you can specify that the ranking condition should be re-executed each time you select a new member from a qualifying dimension that is in the Page Items region of the crosstab. To do this, choose the Selected <Dimension> page item option in the dimension value drop-down list.

Click on the Qualify button.

The Qualify Measure-Sales Revenue dialog box opens.

In the column ‘Include Sales Revenue In’, select Each Geography for the Geography dimension.

In the column ‘Include Sales Revenue In’, select Each Time for the Time dimension.

Click OK.

The Edit Step dialog box now shows that the "Top 5 Products" condition will be re-evaluated for all Geography and Time dimension members, but will only be evaluated for the Channel total and Promotion total dimension members.

Click OK to complete the condition and close the Edit Step dialog box.

Click the Sort button below the Selected list in the Query wizard to display the Sort Members dialog box.

Choose the Sort members by option and then click Add.

The Edit Sort Criterion dialog box opens.

In the Edit Sort Criterion dialog, select Product name from the Sort By options list, then click OK.

The Sort Members list box will show the condition as follows:

Click OK to close the Sort Members dialog box.

In the Choose box, select Channel.

Click the Remove All Items shuttle button ('<<') to remove the current selection.

In the Available list, select the Members tab. Click the drill symbol ('+'), which is located to the left of Channel total to expand it.

Click on the Add all Items shuttle button ('>>') to move all the channels to the Selected list.

Click OK to apply the changes and close the Query Wizard. The graph will look as shown below:

From the File menu, select Save All to save your work.

Click the Time dimension and select 1999. The graph updates with a new set of top 5 products.

Click the Geography dimension and select Canada. The graph updates with a new set of top 5 products.

Click the Geography dimension and select Argentina. Once again, the graph updates with a new set of top 5 products. Notice that there is only one channel of distribution in Agentina - Direct.

Once again, click the Geography dimension and select Americas. The graph updates with a new set of top 5 products.

From the File menu, select Save All to save your work. Choose File > Close to close the graph.