Using Excel Templates for Reports

Generate report data directly to Excel templates to take advantage of the analytical features of Excel. The Rubicon report generator writes the report data to the first worksheet of the Excel template where it is accessible to your custom code and macros.

Any editable report, and some program reports, can be generated to an Excel template. Follow this process to generate report data to the first worksheet of the spreadsheet:

To create a report to generate data to an Excel template:

Create the report (unless you are using a supplied report)

  1. Create a data-driven report in the Report Editor and bind the data columns to the report columns. You do not need to format the report table columns.

    See Adding Data-Driven Components for details

  2. Turn off publication of additional report details. This is not required, but additional rows may be printed in your output if you do not:
    • In the Report Properties screen, clear the Show Input Parameters check box.
    • For data-driven components, open the component properties screen. On the Table > Heading tab, clear the Display rowcount check box
  3. Save and Publish the report

    See Publishing and Sharing Reports with Other Users for details

Create an Excel template

  1. Create an Excel file. You can choose whether to send the report data to the first worksheet in the report, or to send each report table to a separate worksheet.
  2. Leave the first row of each output worksheet blank. The report table headers will be written to this row when the report is generated.

    Note

    If your report has a report header, ensure that the first cell of the first row is formatted as text. Alternatively, remove the report header in the Report Editor.

  3. On the second row, format one column for each report data column that will be generated. All columns should be formatted as Text, except for Date columns which should be formatted as Date.
  4. Enter sample data in each column in row 2 where the report data will be generated. This sample data will be overwritten by the report data when the report is generated.

    Note:

    Any formatting applied to this sample data will be used for the whole report table including the column headers.

    Confirm that numerical values are formatted as text. A number that is formatted as text has a small green triangle in the top left corner of the Excel cell.

    Note

    Any formatting applied to this sample data will be used for the whole report table including the column headers.

    Confirm that numerical values are formatted as text. A number that is formatted as text has a small green triangle in the top left corner of the Excel cell.

  5. Save the spreadsheet, in the xlsx format, to a location where it will be accessible to the report generator, such as on a shared network drive. The report generator must be able to locate the spreadsheet.

    Note

    If you are previewing the report, the report generator will run on your local machine. If you are running the report, or scheduling the report to run the report generator will run on the server.

Generate the report

  1. Double-click on the report icon in the application menu.
  2. In the Generate As field, choose Excel from template
  3. In the Template File field, enter the location of the Excel spreadsheet.
  4. Select the Use Multiple Tabs check box if you have several report tables and you want to send each to a separate worksheet.

You can now Run or Preview the report. See Previewing a Report and Scheduling a Report for details.