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:
- Change the layout and selections for the data that appears in your
crosstab. For example, show Retail Sales for year 2000 in Asian countries
at the Product Division level.
- Change the layout and selections for the data that appears in your
graph. For example, show the Top 5 Countries in Asia based on Retail Sales
at the Product Division Level.
Exercise Tasks
Modifying the layout of dimensions in crosstabs
To change the layout of the dimensions that are displayed in
the crosstab:
- 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.
- 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).
- In the QueryBuilder, select the Layout tab.
- 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:
- Page dimensions: Channel, Time
- Row dimension (on the left): Geography
- Column dimensions: Measure above Product
- Choose Apply to apply the changes.
Changing the data selections for dimensions in crosstabs
There are three ways to specify selections for a dimension:
- Members: Ad-hoc method of adding and removing specified dimension members
to affect the current selection
- Conditions: Rules and conditions that are applied to selection criteria
or extend selection criteria
- Favorites: Saved selections that can be applied to a query
Similarly, the QueryBuilder has two tabs that reveal the contents of the current
dimension selection:
- Steps: Organize the current selection based on the criteria that comprises
the query. This criteria can be a combination of any of the following:
members, conditions, and favorite steps.
- Members: Show the result of the current set of selection steps.
To change the Geography selection to Asia and countries in Asia:
- In the QueryBuilder, select the Dimensions tab.
- In the Choose Dimension drop-down box, select Geography.
- 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 Worldwide, to expand Worldwide and choose
Asia. Click the Add Selected Items shuttle
button ('>') to move Asia to the Selected list.
- 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.
- Choose Apply to apply the changes.
To change the Channel selection to Retail:
- In the Choose Dimension 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 All Channels, to expand All Channels and select
Retail. Click the Add Selected Items shuttle
button ('>') to move Retail to the Selected list.
- Choose Apply to apply the changes.
To change the Product selection to All Products and the Product
Divisions:
- In the Choose Dimension box, select Product.
- Click the Remove All Items shuttle button ('<<')
to remove the current selection.
- In the Available list, select All Products. Click the Add
Selected Items shuttle button ('>') to move All
Products to the Selected list.
- 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.
- 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:
- If the graph that you defined in the previous exercise is not already open,
right-click Sales Analysis Graph and choose Presentation
Editor.
- In the toolbar that is located above the graph, choose the QueryBuilder
tool.
- In the QueryBuilder, select the Layout tab.
- 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)
- Page dimensions: Channel, Time
- Bar dimensions: Product
- Group dimensions: Measure to the left of Geography
- 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:
- Select all the countries in Asia.
- Keep only the top 5 of the Asian countries, based on sales.
To change the Geography selection to the Top 5 Countries in
Asia:
- In the QueryBuilder, select the Dimensions tab.
- In the Choose Dimension drop-down box, select Geography.
- 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 Worldwide, to expand Worldwide, and choose
Asia. Click the Add Selected Items shuttle
button ('>') to move Asia 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. Open the Top/Bottom
folder and choose the template Top 10 based on Sales.
- Click the Add Selected Items shuttle button ('>')
to move the condition to the Selected list.
- 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.
- 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.
- Choose Apply to apply the changes.
To change the Channel selection to Retail:
- In the Choose Dimension 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 All Channels,
to expand All Channels and select Retail. Click the Add
Selected Items shuttle button ('>') to move Retail
to the Selected list.
- Choose Apply to apply the changes.
To change the Product selection to All Products and the Product
Divisions:
- In the Choose Dimension box, select Product.
- Click the Remove All Items shuttle button ('<<')
to remove the current selection.
- In the Available list, select All Products. Click the Add
Selected Items shuttle button ('>') to move All
Products to the Selected list.
- In the Selected list, select the Steps tab.
- Click the drill symbol ('+'), which is located to the left
of All Products, to expand All Products.
- 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