Designing efficient reports

This guidance explains how to use different data extraction definitions to design efficient reports. Designing efficient reports reduces the time required to run the SQL queries that are generated by the query definitions in a worksheet and then render the results in Excel.

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

Q&A Cloud does not by default limit the number of queries that may be run but running too many queries will affect the performance of Q&A Cloud for all users on the same Q&A DataLink Server.

Consideration for these points can improve the performance of Q&A Cloud:

  • Designing reports optimally using Summary Reports instead of Summary Link formulas.
  • Running reports outside of usual busy periods or normal office hours.
  • Running database routines in SunSystems Cloud.
  • Deploying Q&A Cloud on multiple server nodes.
  • Creating separate reports for different Business Units.

Example Reports

A pack of example reports is available for Q&A Cloud. The example reports illustrate typical management report formats and demonstrate how report design can be optimized to generate efficient queries using different data extraction techniques.

Before designing reports the design of the example reports should be reviewed because the choice of data extraction method can significantly affect performance.

Business Units

Querying multiple Business Units in reports increases the number of SQL queries required for each report. Create separate reports for each Business Unit and a further report for the aggregation of the Business Units. Entities such as analysis data can also be separated into different reports and then aggregated.

Summary Links

Note: Use Summary Links sparingly. Use Summary Reports instead.

Summary Reports

This extraction method 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 for sorting the data, for example the IFERROR(VLOOKUP...), SUMIFS and XLOOKUP functions. Consult the Microsoft Excel Help for details.

The performance of the data extraction and rendering using Summary Reports can be further improved by clearing these options on the Report Style tab:

  • Apply Auto Format
  • Output Table Names
  • Output Data Item Names
  • Totals.

The performance can also be improved by clearing the Enable Drilldown Functionailty option on the Options tab.

Detail Reports

The performance of the data extraction and rendering of Detail Reports can be further improved by clearing these options on the Report Style tab:

  • Apply Auto Format
  • Output Table Names
  • Output Data Item Names
  • Totals.

Reference Links

There are no options that can be changed to affect the performance of Reference Links.