Designing efficient reports

Infor Query & Analysis uses SQL queries to extract data from your data source. Each SQL query extracts the requested data and then generates and completes the query. Designing efficient reports requires the use of different data extraction methods in Q&A

Efficiently designed reports reduce the time required to complete the SQL queries and result in improved performance in data extraction and the display of the spreadsheet report.

Q&A does not limit the number of queries that are included in a spreadsheet report. However, the number of running queries does affect the performance of Q&A for all users who are using the same Q&A DataLink Server.

Q&A generates and processes queries via Internet Information Services (IIS) on the Q&A DataLink Server. Queries are not generated and processed on your client computer.

Following these recommendations can improve the performance of Q&A:

  • Designing efficient reports, for example by minimizing the number of Summary Links and using Summary Reports and Excel functions to aggregate data.
  • Running reports outside of usual busy periods or normal office hours using the Alert feature in Q&A.
  • Running housekeeping routines in the data source application to clear data entries.
  • Creating separate reports for different business units.
Example reports for Q&A are available in these spreadsheets:
  • Infor Q&A 11.3 Example Reports for SunSystems 6.xlsm
  • Infor Q&A 11.3 Example Reports for SunSystems 6 (performance improvements).xlsm

The performance improvements spreadsheet includes changes to the design of some reports to illustrate how using different data extraction techniques in a report can improve the speed of data extraction and the calculation of values.

These reports have been redesigned in the Infor Q&A 11.3 Example Reports for SunSystems 6 (performance improvements).xlsm spreadsheet:
  • Balance Sheet
  • Profit & Loss
  • P&L consolidated
  • Quarterly P&L

In the report designs in the Infor Q&A 11.3 Example Reports for SunSystems 6.xlsm spreadsheet, individual Summary Links queries are used to extract the data into each relevant cell in the report design.

In the report designs in the Infor Q&A 11.3 Example Reports for SunSystems 6 (performance improvements).xlsm spreadsheet, instead of using individual Summary Links to extract the data, one Summary Report query is used to extract the data into cells that are outside the report design. Then, the SUMIFS Excel function is used in each cell in the report design to calculate the values in a range that meet criteria that you specify.

Note: To compare the different techniques used in these report designs, open one of these reports in each spreadsheet and select cells in each report that contain the same expression. Then compare the expressions used in each report.

Business Units

It is best to include Business Units in separate reports to reduce the number of SQL queries required for each report. To achieve this, create separate reports for each Business Unit and a further report for the aggregation of the Business Units. Entities like Analysis data can also be separated into different reports.

Summary Links

Note: Use Summary Links sparingly. Consider using Summary Reports instead to extract the data and Excel functions to aggregate the data, for example the SUMIFS, XLOOKUP and VLOOKUP functions.

Summary Links extract and summarize reference information for one or more records using an extraction type such as Sum, Minimum, Maximum or Count.

One formula can output multiple data items for the selected range of records. Summary Links return a single, aggregated value for a selected range of records and are executed in batches. Batch sizes affect the time required to read the formulas and display the results in the report. By default, the batch size is 500 but this can be changed.

Note: For details of how to change batch sizes, see the Q&A Configuration Guide.

As a single value is returned, often many of this extraction type are used in a report - each using a single SQL query. This does cause a significant demand on the Q&A DataLink Server as each SQL query requires time to extract the data, and generate and complete the query.

Summary Reports

Summary Reports also use a single SQL query to return a single, aggregated value. This extraction type is more efficient than Summary Links because the same amount of data can be extracted in one Summary Report as can be extracted in multiple Summary Links.

Excel functions can be used to aggregate and sort the data, for example the SUMIFS, XLOOKUP and VLOOKUP functions. Consult the Microsoft Excel Help for details about using these functions.

The performance of the data extraction and rendering of Summary Reports can be improved by clearing these options on the Report Style tab:
  • Apply Auto Format
  • Output Table Names
  • Output Data Item Names
  • Totals

The performance of Summary Reports can also be improved by clearing the Enable Drilldown Functionality option on the Options tab.

Detail Reports

Detail Reports return a list of rows from the database, with one row being returned for each record in your data source.

The results of the query are not aggregated.

The performance of the data extraction and rendering of Detail Reports can be improved by clearing these options on the Report Style tab:
  • Apply Auto Format
  • Output Table Names
  • Output Data Item Names
  • Totals

Reference Links

Reference Links return the value of a single record.

A single query can output multiple data items for that record. Each Reference Link uses a single SQL query.

If a report contains many Reference Links formula, to improve performance consider using Detail Reports instead with Excel functions to aggregate and sort the data.