Interacting with the OLAP Grid

Although you don t have quite the slice and dice flexibility with Crystal Reports that you may have with your particular OLAP analysis tool, you might be surprised by how easy it is to rearrange the appearance of an OLAP grid object. When you first create the OLAP grid, you choose which dimensions you want to use as rows and which you want to use as columns. If you choose multiple row or column dimensions, the order in which you add them determines the grouping order for the rows or columns .

You may want to change the order of the OLAP grid s row or column grouping, or you may want to swap or pivot the rows and columns, drill down on higher-level dimensions, and so forth. There are a large number of manipulation choices in Crystal Reports (many of these options are only available when viewing the OLAP grid in the Preview tab ”limited options exist in the Design tab):

• Pivot the rows and columns Select the OLAP grid. Then, right-click and choose Pivot OLAP Grid from the pop-up menu. You can also choose Format Pivot OLAP Grid from the pull-down menus .

• Reorder dimensions Simply click the row or column heading of the dimension you want to reorder. As you hold down the mouse button, you will notice a little piece of paper icon appear on the mouse pointer. This indicates that the dimension can be dragged and dropped below or above another dimension to reorder the dimensions. You can even drag and drop a dimension from a row to a column, or vice versa, provided that at least one dimension will be left in the row or column of the OLAP grid.

• Drill down and drill up Double-click a higher-level member. This, in essence, is the same as rerunning the OLAP Expert and using the Select Row Members option to show the next level of the hierarchy. You can then drill down on the lower levels that you just exposed, until there are no more lower levels to show. To drill up (hide the lower levels you already showed), just double-click the higher-level member again ”the lower-level members will disappear. You can also select the member name , right-click, and choose Expand Member or Collapse Member from the pop-up menu.

• Total columns or rows Select any row or column member (you can t total when you ve selected an individual number cell ) and right-click. Choose Automatic Totals from the pop-up menu. A submenu will appear giving you totaling choices.

• Add custom calculations You may add one or more additional rows or columns (members) based on a calculation. These additional calculated members can consist of such calculations as contribution, growth, ranking, variance, or other statistical calculations that you can create using the MDX query language derived from Microsoft Analysis Services.

  To create the calculated member, select the member row or column heading that you want to use as the basis for your calculation. Right-click and choose Add Calculated Member from the pop-up menu. The Calculated Members dialog box will appear.

  

  There are three choices: the Calculation Expert tab, the Data Analysis Expert tab (new in version 10), and the Calculation tab. You can create a calculation using one of the three tabs, depending on the type of calculation you want to create.

  The Calculation Expert tab allows calculations using contribution, growth, ranking, and variance. The Data Analysis tab allows calculations using trend line, moving average, and linear regression. And, the Calculation tab allows you to create a more complex calculation from scratch using the MDX calculation language.

  Once you ve finished your calculation, an additional member will appear in the grid showing your calculation. To edit or delete the calculated member, select the member name at the left or top of the grid, right-click, and choose Edit or Delete options from the pop-up menu.

  Note  

  For more in-depth coverage of OLAP calculations and MDX calculations, consult Crystal Reports online help, or documentation with your online OLAP analysis tool, such as Microsoft Analysis Services books online.

• To create on-the-fly filters Even though you may have limited your OLAP grid by using a Slice, you can further filter the OLAP grid interactively while you re viewing it. Your filter, based on the numbers rather than on the member hierarchy, can consist of a comparison to the numbers , or a top or bottom N or percentage.

  To add a filter, select the member name column or row that you want to use as a filter (you can t click a number in a cell ”it has to be at the edge of a column or row, and only a member name that shows some filter-able numbers ”not all rows or columns ”can be filtered), and right-click. Choose Add Filter from the pop-up menu. The Define Filter dialog box will appear.

  

  Once you ve applied your filter, you ll see a reduced set of rows or columns in the grid, with the filter limiting what appears. You ll know you ve added a filter to a row or column if you point to it and your mouse pointer changes to an X. If you want to modify or remove the filter, right-click on the column or row again and choose Edit Filter or Remove Filter from the pop-up menu.

• Sort the rows or columns by numeric values Normally, rows and columns are ordered according to the member name at the top of the column or the left of the row. However, you can sort the rows or columns by the numbers if you choose. To do this, select the row or column member name you want to sort (you can t click a number, and the row or column you choose must be sort-able ”not all columns or rows will offer the option), right-click, and choose Add First Sort from the pop-up menu. A submenu will appear with sorting options. Choose your desired sort option. Notice that the selected row or column is now sorted by the numbers. Also notice that you ll see a double-arrow cursor appear when you point to the row or column member name, indicating that a sort exists.

  If you have duplicate values in a row or column and you wish to add a second level of sorting, click the next column or row member name that you want to sort, right-click, and choose Add Next Sort. Choose similar options.

  To change or remove an existing sort, select a row or column member name (one with the double-arrow cursor, indicating an existing sort), right-click, and choose Change Direction of Sort or Remove Sort.

The OLAP Worksheet

If the interactivity options discussed previously in the chapter aren t enough, Crystal Reports includes even more new OLAP interactivity with the OLAP Worksheet. The OLAP Worksheet is actually largely based on Business Objects Crystal Analysis Pro OLAP Analysis tool, providing a great deal of slice, dice, swap, and analyze interactivity right in Crystal Reports.

To display the OLAP Worksheet, select the desired OLAP grid object in the Preview tab (you can t launch the OLAP Worksheet in the Design tab). Then, right-click and choose View Cube from the pop-up menu. The OLAP grid will be displayed in an additional tab in Crystal Reports labeled Cube View, as shown in Figure 19-7.

Figure 19-7: OLAP Worksheet Cube View tab

Once you ve launched the OLAP Worksheet, you may return to the report s regular Design or Preview tabs by just clicking them, and then return to the Cube View tab by clicking it. To close the Cube View tab and return to a regular report view, click the red x to the left of the page navigation buttons .

Interactivity in the OLAP Worksheet is somewhat similar to that of the OLAP grid in the preview tab discussed earlier in the report. There is also additional functionality to swap and nest dimensions.

These are among the highlights of OLAP Worksheet interactivity:

• Click plus or minus signs to drill down or drill up (expand or compact members).

• To drill through to base relational database data that makes up an individual cell number, right-click on the cell and choose Drill Through from the pop-up menu (only limited OLAP cube servers provide this functionality, so this option may not be available).

• Right-click a member name (row or column name) to choose options from the pop-up menu. Many of these options are similar to those discussed earlier in the chapter relating to the OLAP grid. Some notable additions to the pop-up menu include formatting options, including the Highlighting Exception option, which will color cells that fall within selected numeric ranges.

• Click down arrows next to a dimension to launch the Member Selector (Figure 19-3 earlier in the chapter) where you can choose to display individual members of the hierarchy in the grid, or slice the grid (if you choose a dimension at the bottom of the grid).

• To swap a row or column dimension with another row or column dimension, or a filter dimension (at the bottom of the Cube View tab), click the dimension name that you wish to swap. Then, drag the dimension to the other dimension that you wish to swap with. When you see the double-arrow, release the mouse button.

• To nest or stack dimensions (add an additional row or column dimension within another dimension), click the dimension name that you wish to nest. Then, drag the dimension to the existing row or column dimension that you wish to nest it under. Look very carefully for the single arrow. When you see it, release the mouse button.

• To move a dimension back to the bottom of the Cube View tab (there must be at least two or more nested dimensions to move one of them to the bottom), click the dimension name that you want to move. Drag the dimension to the bottom of the Cube View tab between the rows or columns of existing dimensions. Look very carefully for the single arrow (a double arrow will swap the dimensions). When you see it, release the mouse button.