Converting a report

You can convert an Exploration View to another report type in IBM® Planning Analytics for Microsoft Excel.

When you convert a report from an Exploration View, you can decide whether or not your converted reports use stylesheet formatting. In the Exploration ribbon group, select Use Excel Formats to convert reports using stylesheet formatting. Select Use Server Formats to convert reports using the server formats.

Converting to a Quick Report using TM1 data

Create a Quick Report from an IBM TM1® Exploration View when you finish your analysis and want to present data by using more advanced cell-based features from Microsoft Excel.

When you convert an Exploration View to a Quick Report, the application places the system information, rows, and columns in named ranges. The Quick Report inherits the Exploration View's formatting when its converted and not what is defined under the Options setting.

If you have views, explorations, or other Quick Reports available in the Task Pane, you can drag these to a worksheet to create a Quick Report. For more information, see Create a Quick Report.

  1. Open or create an Exploration View.
  2. Click Convert to and select an option. For example, to create the Quick Report on a new worksheet, select Convert to > Quick Report > On New Sheet.
    Note: If you are converting a truncated Exploration View (where data exceeds the row limits), the hidden rows are not automatically included in the conversion. To include hidden rows in a converted report, you need to first change the Data display row limit to 0, and then convert the report. You can find the Data display row limit setting under Options in the IBM Planning Analytics ribbon. For more information, see Data display row limit.

After the report is converted, the Quick Report is listed in the Task Pane > Workbook tab in the Quick Reports folder.

Note: Calculations created in an Exploration View need to be recreated in Microsoft Excel after converting to a Quick Report.

Converting to a Custom Report using TM1 data

Convert a TM1 Exploration View to a Custom Report when you want to manipulate individual cells. You can use data from multiple data sources in a Custom Report.

You can also create a Custom Report completely from scratch using the cell-based method.

  • You cannot convert an Exploration View to a Custom Report if the Exploration View contains alternate hierarchies.
  • The Exploration View must also have at least one row or column dimension.
  • Text values in formulas are limited to 255 characters. To create text values longer than 255 characters in a formula, use the CONCATENATE function or the concatenation operator ( & ).

When you convert an Exploration View, you have the option of converting data on the current worksheet, copying and moving the data to a new worksheet, or specifying the location for the converted data.

In the converted report, context members are defined by SUBNM formulas. Data cells are defined by DBRW formulas. You can modify these formulas by double-clicking the cells.

  1. Go to the worksheet that contains the Exploration View that you want to convert.
  2. Click Convert to > Custom Report and select an option.
    1. To convert the Exploration View to formulas and place the result on the current worksheet, select On This Sheet.
    2. To convert the Exploration View to formulas and place the result on a new worksheet, On New Sheet. By placing the results on a new worksheet, you preserve the original Exploration View.
    3. To convert the Exploration View to formulas and specify the location, which is a cell in an existing spreadsheet, select At Specified Location.
    Note: If you are converting a truncated Exploration View (where data exceeds the row limits), the hidden rows are not automatically included in the conversion. To include hidden rows in a converted report, you need to first change the Data display row limit to 0, and then convert the report. You can find the Data display row limit setting under Options in the IBM Planning Analytics ribbon. For more information, see Data display row limit.

After the report is converted, the drop zones disappear. The formatting remains the same, but the cells of the Exploration View contain TM1 formulas, which link the individual cells to data in the cube. An information area above the Custom Report shows the context members of the report.

You can continue to modify the worksheet by editing formulas and applying formatting.

Converting to a Custom Report using Cognos Analytics data

Convert an Exploration View to a Custom Report when you want to manipulate individual cells or place columns or rows in between the imported data.

When you convert an Exploration View, you have the option of converting data on the current worksheet, copying and moving the data to a new worksheet, or specifying the location for the converted data.

Before you convert an Exploration View to a Custom Report, note the following restrictions:
  • You cannot convert data from relational sources to a cell-based analysis.
  • Text values in formulas are limited to 255 characters. To create text values longer than 255 characters in a formula, use the CONCATENATE function or the concatenation operator ( & ).

It is also possible to convert your Custom Report to an Exploration View. Click a cell and then click IBM Planning Analytics > Explore. Depending on how your columns and rows are set up and how many items are in each, you may notice that certain objects are added to the context drop zone instead of being placed in a row or column.

If the default measure is also on columns, you cannot convert an Exploration View to formulas. You must first remove the default measure. For more information, see Remove measures.

  1. Open the workbook with the Exploration View you want to convert.
  2. In the IBM Planning Analytics tab, click Convert to.
  3. Choose where to place the converted data:
    1. To convert the Exploration View to formulas and place the result on the current worksheet, click On This Sheet .
    2. To convert the Exploration View to formulas and place the result on a new worksheet, from the exploration bar, click Convert to Formulas > On New Sheet . By placing the results on a new worksheet, you preserve the original Exploration View used to create the analysis and can use it again to change parameters for another analysis.
    3. To convert the Exploration View to formulas and specify the location, which is a cell in an existing spreadsheet, from the exploration bar, click Convert to Formulas > At Specified Location .

After the report is converted, the drop zones disappear. The formatting remains the same, but the cells of the report contain formulas, which link the individual cells to data items in the database.

You can continue to modify the worksheet by editing formulas and applying formatting. By default, data is updated when you edit a formula. To edit formulas without data updates, right-click the worksheet and select IBM Planning Analytics > Pause formula resolution. When you are ready to show data, right-click the worksheet and select IBM Planning Analytics > Run formula resolution.