Formatting a Table

---

Many of the formats you applied to individual cells can also be applied to a set of cells that make up the table block. Within a table block, there are three types of cells:

• Block Header Column headings that usually are object or variable names

• Block Body Values from the variables

• Block Footer Any subtotals

You can select the individual header, footer, or body, or else the entire table. Figure 17-5 shows how the mouse cursor and table highlights change depending on which component you select. As you move your mouse around the table, the shape will change to a right- pointing arrow when you select either the header, body, or footer. To select only one column, hold the mouse over the column until it becomes a down-pointing arrow. To select the entire table, you may find it easiest to select the table component with Report Manager, Structure view. When the entire table is selected, the table border shows black square markers.

Figure 17-5: Select components of a table prior to formatting

Alerters

An alerter is a type of formatting you use to highlight rows of data. You can use different fonts and colors to highlight a row, or you can tell the alerter to display certain text. In Figure 17-6, the alerter displays different text messages in different colors based on the wine rating. Alerters are evaluated from top to bottom: if the first condition of >= 95 is met, then this formatting/result applies; if the first condition is not met, then BusinessObjects proceeds to evaluate the second condition. If none of the conditions are met, then the cell content and format remain unaffected. An alerter also can display the original values but change the formatting when certain conditions are met. For example, an alerter may highlight expenses that are 10 percent over budget in red and when they are on or under budget, display them in green. Working with alerters requires two steps: first, select the column(s) to which you will apply the alerter; then create the alerter.

Figure 17-6: Alerters allow you to highlight rows according to formulas and conditions.

Inserting a Column for Text Alerters

If you want to highlight certain values in a different color or format, select the column within the table and then create the alerter. However, if you want the alerter to display a particular text or a different variable as shown in the preceding figure, you must first insert a new, blank column.

1. Position the cursor in the column next to where you want to insert a new column.

2. From the pull-down menu, select Insert | Column. BusinessObjects asks you if you want to insert the column to the right or left of the current column.

   

3. Choose an option and click OK.

4. Click on the new column where you will apply an alerter.

BusinessObjects uses a contextual formatting technique so that the new column uses the same formatting as the surrounding columns.

Creating the Alerter

To create the alerter as displayed in Figure 17-6:

1. Select Format | Alerters and click Add.

2. The Edit Alerters dialog box appears. From the Definition tab, enter a name and a description for the alerter.

   

3. Select the Conditions tab shown in Figure 17-6.

4. Under Variable To Compare, select the variable that will determine if the row should be highlighted or not. This is most often a measure variable—for example, Expense Variance, or in this example, Rating. You also can click Add to create a new variable for comparison.

5. Under Operator 1, click the DOWN ARROW to specify equal to, greater than, and so on. In this example, select >=.

6. Once you specify an Operator, a Value box appears. Enter a value or click the drop-down box to select from a List Of Values. In this example, enter 95 to alter the format for wines greater than or equal to 95.

7. In the Result Column, the default display is Cell Content formatted with red text. With Cell Content, the formatted table displays the values of the cell but allows you to change the color, font, border, and shading. For example, it will continue to display the wine rating (or an expense variance) but will highlight it in red. To specify a format for this condition, click the drop-down arrow and select Format.

   

8. To have it display text such as Over Budget or Excellent Rating, use the same drop-down arrow and select Text. When you select Text, you will be prompted to Enter Your Text. In this example, enter Excellent Rating.

9. Continue entering additional conditions and results. Click OK to close the Edit Alerters dialog box.

10. From the Alerters dialog box, click Apply to apply the alerter to the currently selected column.

11. Click OK to close the dialog box and see the effects of the alerter, shown here:

    

Column Headings

In formatting cells that are column headings, you can change the contents of the cell to shorten the column heading as well as format the headings. In a standard tabular report, the column headings are in the first row of the table. If you create a financial table with Format | Rotate Table or if you create a crosstab report, the column names may also be in the first column.

Long Column Names

Sometimes the name of the variable is much longer than the values the column will contain. For example, if your product codes are only six digits and the object name is Global Material Identification, the column heading and width is unusually long for what would otherwise be a small column. By default, BusinessObjects will use the following formula for a column heading:

=NameOf(<Variable(Query N with Universe)>) 

Where

Variable is the name of the object.

N is the query number within the document.

Universe is the name of the data source.

The N and Universe options appear only when there is more than one variable with the same name in a document.

You can modify the name of the column heading to show a smaller name.

1. Select the cell that contains the long column heading.

2. Type the shorter name directly over the formula. You can do this in the cell within the Report window or within the formula bar if the formula toolbar is displayed.

Formatting Header or Column Headings

In the following example, you will change the header part of the table so that the font is automatic (black), the border shows a darker line, and the cells contain no shading. Recall that by default, BusinessObjects aligns text cells left. This is fine for columns that contain text values, but for column headers that display measures, you want them to be right- aligned along with the numeric values that appear below them.

1. Move your cursor to the left of the table until it becomes a right-pointing arrow.

2. Click next to the row that contains the column headings. The row of header values changes color to show you selected the entire row.

3. Use the pull-down menu Format | Cell, then select the Font tab.

4. The default font is Custom (white). This is legible with a dark blue background but not with no background. Set the Color to automatic and then click Apply.

5. Still within the Cell Format dialog, select the Border tab.

6. Under Style, select a thick line style.

7. Under Presets, select Box. Then select a thin or dashed-line style and click the middle cell border to have a dashed line separate each column heading.

8. Click Apply to reflect the Border formatting.

9. Select the Shading tab.

10. Set Fill to None and Background and Foreground Colors to Automatic. Click OK to accept the Shading changes and close the Table Format dialog box.

11. To align the column headings for the measure columns, select the two measure columns. From the toolbar, click Align Right.

If you have properly selected the full row for steps 1–10 and just the measure column for step 11, your column headings should appear as follows:

Additional Table Format Options

In the preceding examples, you selected individual cells and components of a table to display the different tabs from the Format dialog box shown in Figures 17-1 through 17-4. When you select the entire table, you have two additional tabs in the Table Format dialog box: General and Page Layout. If you are working with a crosstab report, you also have a Pivot tab.

The General Tab

The General tab contains the following options, as shown in Figure 17-7.

Figure 17-7: Table Format, General tab

• Show Header This box is enabled by default to display column headings.

• Show Footer When you add a calculation on a column (discussed in Chapter 18), this box is automatically checked and the table shows an additional row with the subtotals.

• Fold This hides the body of the table, similar to working in Outline view (discussed in Chapter 18).

• Avoid Duplicate Rows Aggregation When the universe designer sets an object to use a projection aggregate, BusinessObjects automatically shows the aggregate for the dimension and level of detail displayed in the table. You generally want to check this box only if you are trying to see the individual rows of a result set to identify calculation issues.

• Columns When you want to print one long table on a page, you can specify a number of column sets or groups; this is useful only for narrow columns. In the following example, Label Name and Unit Price fill up only half the page:

  

You can specify three column sets to have BusinessObjects repeat the number of columns three times across the page. When you modify the number in Column, you must also specify the Spacing between the column sets. The next example uses 4/16ths of an inch between columns. To have the column headings repeated on each column, select the Page Layout tab, then click Repeat header on every page. Use Print Preview to see the effect of this setting.

Note 

If you select more columns than actually fit on the page, BusinessObjects will display only what fits and will continue the next column set on another page.

• Orientation/Across By default, the column headings run across the page. If you want to rotate them down, select Down. This option may be useful for reports that contain only a few rows of data but many dimensions and measures. Changing this setting to Down has the same effect as selecting Format | Rotate Table. In WebI, this block style is referred to as a Financial block.

  

Page Layout

Most of the options in the Page Layout tab (Figure 17-8) affect what happens when you print a report. To see the effect of these settings, select View | Page Layout.

Figure 17-8: Table Format, Page Layout tab

The New Page options are useful only for very small tables. Start On A New Page will insert a page break between each table. Repeat Block On Every Page repeats a small table on top of every new page. For example, if you have a small summary table at the top of a report and a long detail table beneath, select Repeat Block On Every Page to have the summary table repeated at the top of each page as the details change beneath.

Under Page Break, Avoid Page Break In Block is useful when you have multiple tables in a report. This option will force BusinessObjects to print the block on a new page so that the second table starts on a new page, or if possible, prints on one page. This option is superfluous for reports that contain only one long table that spans more than one page and will cause a blank page to print before the block.

Note 

Remember that a master/detail report may appear to have multiple tables, but in reality, they are one table with multiple sections. If you want each new section in a master/detail report to start on a new page, then select Format | Section, then Page Layout.

The Repeat Header and Repeat Footer options are very useful for long tables that span multiple pages. The header or footer must already exist in the table for these options to have an effect. By default, all tables display headers. For a table to display a footer, you must insert calculations (see Chapter 18) or select Table Format, then the General tab, and check the Show Footer box. Headers refer to the column headings or variable names; the footer refers to the additional row used for subtotal calculations.

Hiding a Table

The Appearance tab, shown in Figure 17-4, is similar to the Appearance tab for formatting a table or section. However, the Hide Cell option will change to a Hide Block or Hide Section Header depending upon which component you selected. This option is quite powerful for multiblock reports and master/detail reports. For example, if you have a report that contains a chart for trend analysis and a detail table beneath to show budget variance, you may only want to show the detail table when the total variance exceeds a certain value. To conditionally hide the block:

1. From the Table Format dialog box, select the Appearance tab.

2. Check the box Hide Block.

3. Enter a formula or click Edit Formula to launch the Formula Editor. You do not need to specify an If statement. For example, to hide a block when the total variance for the block is less than $1,000, enter:

   = <Variance> < 1000

4. Click Apply to see the effect of this setting, then OK to close the dialog box.

In a master/detail report, you will want to hide both the block and the corresponding section header so be sure to use the same options and formula for both report components.

Note 

The Hide Block setting evaluates the entire table or mini table in a master/detail report. It will not hide individual break levels or rows. To hide these, you need to use Filters, which are covered in Chapter 18.

---