4. Using QueryBuilder

Exercise Objectives

The objective of this exercise is to learn how to use the QueryBuilder to modify the layout and selections for the data that is displayed in graph and crosstab presentations. This is necessary to control the type of reporting and analysis that you will perform.

Exercise Description

You will use the QueryBuilder to perform the following tasks that modify the layout and selections for the data that is displayed in your graph and crosstab:

Exercise Tasks

Modifying the layout of dimensions in crosstabs

To change the layout of the dimensions that are displayed in the crosstab:

  1. If the crosstab that you defined in the previous exercise is not already open, then, in the System-Navigator pane, right-click Sales Analysis Crosstab and choose Presentation Editor.
  2. On the toolbar that is located above the crosstab, choose the QueryBuilder tool (which is located in the upper left corner and displays the Show Query Builder tooltip).
  3. In the QueryBuilder, select the Layout tab.
  4. To rearrange dimensions, simply 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:
  5. Choose Apply to apply the changes.

Changing the data selections for dimensions in crosstabs

There are three ways to specify selections for a dimension:

Similarly, the QueryBuilder has two tabs that reveal the contents of the current dimension selection:

To change the Geography selection to Asia and countries in Asia:

  1. In the QueryBuilder, select the Dimensions tab.
  2. In the Choose Dimension drop-down box, select Geography.
  3. Click the Remove All Items shuttle button ('<<') to remove the current selection.
  4. In the Available list, click the drill symbol ('+'), which is located to the left of Worldwide, to expand Worldwide and choose Asia. Click the Add Selected Items shuttle button ('>') to move Asia to the Selected list.
  5. In the Selected list, choose Asia. Click the drill symbol ('+'), which is located to the left of Asia, to expand Asia. The list of Asian countries appears.
  6. Choose Apply to apply the changes.

To change the Channel selection to Retail:

  1. In the Choose Dimension box, select Channel.
  2. Click the Remove All Items shuttle button ('<<') to remove the current selection.
  3. In the Available list, click the drill symbol ('+'), which is located to the left of All Channels, to expand All Channels and select Retail. Click the Add Selected Items shuttle button ('>') to move Retail to the Selected list.
  4. Choose Apply to apply the changes.

To change the Product selection to All Products and the Product Divisions:

  1. In the Choose Dimension box, select Product.
  2. Click the Remove All Items shuttle button ('<<') to remove the current selection.
  3. In the Available list, select All Products. Click the Add Selected Items shuttle button ('>') to move All Products to the Selected list.
  4. In the Selected list, select All Products. Click the drill symbol ('+'), which is located to the left of All Products, to expand All Products. The list of product divisions appears.
  5. Choose OK to apply these changes to the crosstab and close the dialog.

The crosstab should display in the following format: Countries in Asia (China, India, Japan, Malaysia, Singapore, Taiwan and Thailand) appear in rows, Product Divisions (Accessory, Audio and Video) appear in columns, and Channel Retail and Time 2000 appear in the page.

Notice that the crosstab is a live, data-aware object. You can continue to edit the crosstab in-place using the Presentation Editor. This includes drilling down on dimension members to show more details, applying formatting, and so on.

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

In the later sections of this tutorial, you will learn more about how to modify the look and feel of the crosstab by using the integrated presentation editor tools.


Modifying the layout of dimensions in graphs

To change the layout of the dimensions that are displayed in the graphs:

  1. If the graph that you defined in the previous exercise is not already open, right-click Sales Analysis Graph and choose Presentation Editor.
  2. In the toolbar that is located above the graph, choose the QueryBuilder tool.
  3. In the QueryBuilder, select the Layout tab.
  4. To rearrange dimensions, simply 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: (Increase the size of the dialog box, if necessary)
  5. Choose Apply to apply the changes.

Using QueryBuilder to specify advanced queries

In this part of the exercise, you will use the QueryBuilder to modify the Geography selection in the graph so it shows the top 5 countries based on sales. As mentioned earlier, the QueryBuilder provides the ability to specify conditions that will filter your results without requiring you to know the SQL to resolve the query. In addition, you will set the selections for Product and Channel similar to the previous task.

To specify the ranking query, you must construct the following steps for the Geography dimension in QueryBuilder:

  1. Select all the countries in Asia.
  2. Keep only the top 5 of the Asian countries, based on sales.

To change the Geography selection to the Top 5 Countries in Asia:

  1. In the QueryBuilder, select the Dimensions tab.
  2. In the Choose Dimension drop-down box, select Geography.
  3. Click the Remove All Items shuttle button ('<<') to remove the current selection.
  4. In the Available list, click the drill symbol ('+'), which is located to the left of Worldwide, to expand Worldwide, and choose Asia. Click the Add Selected Items shuttle button ('>') to move Asia to the Selected list.
  5. In the Available list, select the Conditions tab.
  6. In the Conditions list, there are templates that can be customized to match the condition that you want to specify. Open the Top/Bottom folder and choose the template Top 10 based on Sales.
  7. Click the Add Selected Items shuttle button ('>') to move the condition to the Selected list.
  8. In the Selected list, select the added condition. Click the hypertext links that you want to change and enter text so that the condition reads as follows:
    Keep Countries/Areas: Top 5 based on Sales
    You can refer to the BI Beans Help system for a detailed description of the QueryBuilder functions.
  9. To preview the results of the Geography selection, in the Selected list, select the Members tab. The results of the ranking query are displayed. These should be China, India, Japan, Malaysia and Taiwan.
  10. Choose Apply to apply the changes.

To change the Channel selection to Retail:

  1. In the Choose Dimension box, select Channel.
  2. Click the Remove All Items shuttle button ('<<') to remove the current selection.
  3. In the Available list, select the Members tab. Click the drill symbol ('+'), which is located to the left of All Channels, to expand All Channels and select Retail. Click the Add Selected Items shuttle button ('>') to move Retail to the Selected list.
  4. Choose Apply to apply the changes.

To change the Product selection to All Products and the Product Divisions:

  1. In the Choose Dimension box, select Product.
  2. Click the Remove All Items shuttle button ('<<') to remove the current selection.
  3. In the Available list, select All Products. Click the Add Selected Items shuttle button ('>') to move All Products to the Selected list.
  4. In the Selected list, select the Steps tab.
  5. Click the drill symbol ('+'), which is located to the left of All Products, to expand All Products.
  6. Choose OK to apply the changes and close the dialog box.

Your graph shows the Top 5 Asian Countries based on Sales in Asia (China, India, Japan, Malaysia and Taiwan) along the X-axis. The bars represent each of the Product Divisions (Accessory, Audio and Video) and All products.

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

This is the default look and feel of a bar graph. Later, you will learn how to use the customizer tools in the Presentation Editor to change the look and feel of the graph and how data is represented.

3. Creating Presentations | Overview | 5. Formatting Presentations