Define the Report Layout in Microsoft Excel

After you have defined the rows and columns in the report wizard, you can go to the layout in Microsoft Excel to make manual additions to the report.

For example you can insert, along with Microsoft Excels own functions, a number of IBM® Cognos Controller functions specifically adapted to fetch data from Cognos Controller's database. You can also insert charts, define print areas and define calculations, such as summations, variances, relative changes and ratios.

You can also run (i.e. fill the cells with values) from here. Remember to save all changes before you run the report.

Note: You need to select the Add-Instab/Controller/Reports/Run Report menu (Excel 2007) to process the report.

Procedure

  1. On the Reports menu, click Create. The Create Reports window opens.
  2. Select a report and click the Go to Layout in Excel button. The report opens in Excel and is filled with the rows and columns previously defined on the Axes, Row Definition and Column Definition tabs. The layout is not formatted. The cells are not filled with data.

    Note that rows 1-26 and the column A-C are system rows and columns. Do not enter anything, re-order or format these rows and columns.

  3. Edit the layout by selecting the relevant cells and clicking the appropriate toolbar buttons, depending on what you want to format. You have access to all functionality in Excel, see the Excel help for more information.
    • To create a formula, enter an equal sign (=) and then the cell references, for example, =+E7*D10/100. Summations, variances, relative changes and key ratios are defined here.
    • If you want to link to graphs, or other sheets, you can create hyperlinks from the Insert tab/Links group/Hyperlink menu (Excel 2007).
    • If you, for example, want the negative values to be emphasized in red, you can enter conditional formatting from the Home tab/Styles group/Conditional Formatting/Manage Rules menu (Excel 2007).
  4. Enter Cognos Controller Functions from the Formulas tab/Function Library group/Insert Function menu (Excel 2007). If you want to display the full list of available functions select the Cognos Controller function category in the Insert Function window, and click OK. Enter the required parameters and click OK. The functions in the Reports menu are so called index functions and they can only be used in a Cognos Controller Report and not in the IBM Cognos® Controller Link for Microsoft Excel. One example is fAcc().

    Set print settings from the Office Button/Print/Print Preview tab/Print group/Page Setup menu (Excel 2007).

    To save the report in a Cognos Controller report format, choose Save report. The report including the layout and additional Excel and Cognos Controller functions used in the Excel sheet will be saved. If you click the Office Button/Save As (Excel 2007), then the report will be saved as an IBM Cognos Controller Link for Microsoft Excel workbook. An IBM Cognos Controller Link for Microsoft Excel workbook is updated by pressing the F9 key (update worksheet by pressing F10). Changes made to the layout in an IBM Cognos Controller Link for Microsoft Excel workbook will not be saved in the Report Generator. Once an IBM Cognos Controller Link for Microsoft Excel workbook is created, its connection to the Cognos Controller Report Generator is cut, but the original report still remains in Controller reports.

  5. Click Run Report from the Add-Ins tab, Controller/Reports in Excel 2007, or run the report from Reports/Run in IBM Cognos Controller. Remember to save changes before you run the report.