Sage Intelligence Reporting: How is your Data Rendered into Microsoft Excel

Sage Intelligence Reporting always outputs the data for a report to the first sheet in a workbook. Subsequent worksheets in the workbook are available for you to create your report output formats.

The Expression names for the columns in the report are placed in the first row of the worksheet as the column headings. Sage Intelligence Reporting then uses Named Ranges on the data columns and the used data range. Although these Named Ranges do not have to be used by the rest of the report they can be useful and provide more clarity in formulae and ranges that you use in your report formulae and PivotTables. Sage Intelligence Reporting names each column of raw data with the same name as the column heading for the column but replaces any special characters (which are not allowed in named ranges) with underscore characters.

NOTE: When you use Excel Formulae in your Sage Intelligence Reporting data Containers then you should reference columns in the raw data by their named range headings. For example if you have a data Expression named Order Date in your Container and you want a data Expression that uses a Microsoft Excel formula to extract the month from the Order Date, then you could create an excel formula expression and set the source to MONTH(Order_Date). Here we have used the standard Excel Formula MONTH and referenced the Order Date Expression. Note that an underscore has been used to replace the space character between the words Order and Date (since special characters are not allowed in named ranges).

If a column in a report has a column heading beginning with a numeric then the named range applied to the column will be prefixed with an underscore (since named ranges cannot begin with a numeric). You will also need to bear this in mind if you reference such a field using an Excel formula in a Container data Expression.

Lastly Sage Intelligence Reporting names the range of all the columns in the Raw Data range as RawDataCols and the range of the rows and columns used by the Raw Data as RawData. These two named ranges can be very useful when PivotTables feed off the Raw Data. Rather than using the column ranges (for example, Sheet1!$D:$J) for the source of a PivotTable use the named range RawData (or RawDataCols), for example, Sheet1!RawData. In this way if new display columns are later appended to a report then the range of the PivotTable(s) will not have to be extended to include these (since with the named range the inclusion will be automatic).