Skip to content

Working with Excel Layouts for Operational Reports

Deprecation Notice: From the 21R1 release of IFS Cloud, this excel plug-in has been deprecated. For simple excel reports, the Quick Reports functionality in IFS Cloud Web supports an export to Excel option. For advanced Excel reports, IFS Business Reporter can be used.

The Excel plug-in allows you to generate layouts that can be shown in Microsoft Excel 2007 or later. You can design these layouts in a spread sheet using Excel. Such spread sheet is called an Excel Plug-in Template. This section will list the possibilities you have when designing these.

Designing the Template

You start from an empty Excel Spread sheet and can add special tags to cells that the Excel Plug-in will read and replace with data.  Currently the data can either be Report Data or a dataset defined by a SQL statement. You have no help at all when you write your tags so it can be good to have the Report Designer with the schema of the report populated. If you intend to write your own SQL, we recommend to have IFS Query Builder or other similar tool at hand.

Report Data Tables

[@REPORT-DATA]

This will simply add data to the spread sheet in a form of a table grid with a header row and data rows. The columns will have titles that are translated (or not) depending on the configuration in the RPL plug-in property <use-display-names>. All available columns will be shown unless the configuration in the RPL has been used to control the columns. (<include-columns> or <exclude-columns>)

You can control the formatting style for the header by amending the cell style of the cell where you placed this tag and you can control the data style by amending the style of the cell just below the tag.

If you need more control over how the data is presented you can set options.

[@REPORT-DATA {Options:InsertRows=ON, DisplayNames=OFF}]

By adding options you have much more control of your presentation. All options are case insensitive.
The following options are currently supported:

Option Type Default Value Comment
InsertRows BOOLEAN* false Sheet-rows will be inserted for each data row. Content below will be pushed down, and cell references are updated.
AutoColumnWidth BOOLEAN* true Column widths will be adapted to the data. Pretty much like what happens when you double click a column border in Excel.
DisplayNames BOOLEAN* true Show translated names for column headers.
Header BOOLEAN* true Generate the header.
Hidden BOOLEAN* false Hide the data rows.
AutoFilter BOOLEAN* false Add filter controls to the columns that will allow the user to simply sort and filter the data.
HeaderStyle COPY/KEEP copy COPY: Cell style is copied from the first column on to all other columns as they are generated.
KEEP: Cell style is kept in the header cells. You can set fonts and colors individually for each column header.
DataStyle COPY/KEEP/FIRST-ROW copy COPY: Cell style is copied from the first cell below the tag itself and onto all generated data cells.
KEEP: Cell style is kept in all data cells. You can individually set them. Works best if you have a very fixed and known set of data.
FIRST-ROW: Cell style will be copied from the first cell row below the header. This allows you to set the data style for each column separately.

* all Boolean types can either be set as ON/OFF, YES/NO or TRUE/FALSE.

It is also possible to control the columns. You can decide exactly what columns are to be shown and in what order.

[@REPORT-DATA {Columns: NAME, DESCRIPTION Desc, TOTAL_SUM "Total Price"}]

As you see, it is possible to give alias names. Pretty much like you would for a SQL statement. You can also combine with options.

[@REPORT-DATA {Columns: NAME, DESCRIPTION Desc, TOTAL_SUM "Total Price"; Options: InsertRows=ON;}]

If you do, you must separate different configuration sections with a semicolon (;). The sections can be in any order.
If you want, perhaps for design reasons, you can leave a column empty and it will skip a column in the sheet.

You can add formulas that will be copied down for each row.

[@REPORT-DATA {Columns: NAME, DESCRIPTION Desc, Total Price=D21F21-(D21F21*G21); Options: InsertRows=ON;}]

The formula itself will be evaluated and any references inside will be corrected according to Excel standards. Pretty much as if you where doing a "fill down" in Excel.

Last but not least,  you can filter on rows as well. This easy but and you must be aware of the structure of the report. Since the Excel Plug-in works on a flattened data set, it is difficult to see the original structure of the report. However, we have generated a new column that represents the structure. We call it the "Block Type" and the column name is "_BLOCK_TYPE". Notice the underscore (_) at the beginning of the column name. This is always the first column in your data set and it will contain the current block type. e.g. "ORDER_LINE" or "ORDER_LINE_ITEM"

We suggest that you run the report once with a simple [@Report-Data{Options:DisplayNames=OFF] tag so you can see the full data and the available columns.

You can filter on block data with the following syntax:

[@REPORT-DATA {BlockFilter: MODULE}]

and you can add more filters by separating with a comma (,)

[@REPORT-DATA {BlockFilter: _MODULE, LOGICAL_UNIT}]

It will then filter out a union of rows from both filters.

You can of course combine with both Columns and Options.

[@REPORT-DATA {Columns: NAME, DESCRIPTION Desc; BlockFilter:IDENTITY; Options: InsertRows=ON}]

It is also possible to do normal filtering on any other available column. Then you specify the column and the value you want to filter out.

Use the key work Filter instead and use "=" or "!=" and then specify the filter value.

The following example would do exactly the same as the example above.

[@REPORT-DATA {Columns: NAME, DESCRIPTION Desc; Filter:_BLOCK_TYPE=IDENTITY; Options: InsertRows=ON}]

Here is an example the shows how "!=" can be used to find rows with values in them.

[@REPORT-DATA {Columns: NAME, DESCRIPTION Desc; Filter:NET_PRICE!=; Options: InsertRows=ON}]

Lines with empty cells in the NET_PRICE column will then be left out.

Report Data Values

Report Data tables are good for presenting data in a grid with column headers. But if you want to design a static layout with fields in fixed position, you can instead use the following syntax.

[@REPORT-VALUE{DEL_ADDRESS_1}]

A report value tag will find the first value of the specified column. This can be used to add fields from the header of a header detail data structure. In the underlying flattened data these values will be repeated for every row since it is parent data so it will be enough to pick the first value.

If you want to filter the data on a block, you can add a second parameter with the block type.

[@REPORT-VALUE{DEL_ADDRESS_1,IDENTITY}]

It will now show the value of the first row in the block.

Report Data Labels

Normally you would like to accompany the value with a label that explains what the value represents. Use the following syntax:

[@REPORT-LABEL{DEL_ADDRESS_1}]

It will try to find the translated column name and show it. If no translation is available it will instead show the column name.

SQL Tables

It is possible to show data that is unrelated to the report. In this scenario you have to write your own SQL statements. Use a Query Builder tool to verify/test the code you insert.
Remember that all queries will be executed as the person who ordered the report. Hence only query views that end users can access or you will get runtime security errors.

Lets start with the most simple example:

[&SELECT * from CUSTOMER_INFO_PUBLIC]

this will list all customers. You can add any SQL and do not need to prefix views and methods with the App Owner.

[&SELECT * from CUSTOMER_INFO_PUBLIC where DEFAULT_LANGUAGE_DB='[#CurrentLanguage]']

Notice that you can use CSV context substitution variables (eg. #TODAY#) and Report Rule Engine syntax (eg [#ReportId]) within the statement. For more details go here.

You can also use Options to control how the data is presented. It uses the same rules as described in for the report data tag above.

[&SELECT * from CUSTOMER_INFO_PUBLIC {Options: InsertRows=ON, AutoFilter=ON}]

Expressions

In cells, mixed with flowing text or in tags, you can add CSVs and RRE expressions. These will be evaluated in a nested order with the innermost first. This gives you a huge flexibility for very dynamic layouts.

[@X-PATH] will be evaluated to the corresponding value from the report xml.

Module name is: [@MODULE_REP/MODULES/MODULE/NAME]

[&SQL/PLSQL] will be executed in the database as the person who ordered the report.

Executing user is: [&FND_SESSION.Get_Current_User()]

or

[&select name from customer_info_public where customer_id='8800'] is the best customer!

You can also use context substitution variables.

Today is #TODAY# and you are printing job [#PrintJobId]

A full list of RRE substitution variables can be found here.

Examples

NOTE: *If you use these examples, make sure the DOCUMENT MODEL is set to OFF! *

Customer Ledger Age Analysis

Sales Quotation

Deploying the Template

When the template is ready to be tested you will have to import it from Solution Manager. Find the "Report Layout Templates" form. We recommend using the Report Id in the file name so you can dynamically reference it from the plug-in layout. e.g. CUSTOMER_ORDER_REP_TEMP.xlsx. Using the context menu you can import the actual file or you can simply drag and drop the file into the form.

To test the template, order the report and choose the plug-in layout you have installed for this report. We suggest to use the one from the example here.
Set the excel-template to your report layout template name. In the example below, we've used a substitution variable to make the name dynamic.

. <report-layout> . <plugin> <properties> <use-display-names>ON</use-display-names> <excel-template>[#ReportId]_TEMP.xlsx</excel-template>  <include-columns></include-columns> <exclude-columns></exclude-columns>

Debugging / Trouble Shooting

There is not direct way to trace and debug the template. Instead you must import the template and do a test-formatting. Any errors that occurs will be passed back and shown on the print job, so check the print manager error message. Here you will see fatal error that prevents the report from rendering. Minor errors, like syntax errors, will still result in a successful rendering but the excel sheet will contain error messages rather than the expected data. This will effectively direct you to the actual place where the syntax error occurred.