When you display reference fields in a report (as well as in the situations where you need to display a presentation, such as displaying a value using the Message function), note that in order to display a value that is a reference, the application executes an additional query to get the field presentation. This reduces performance. To avoid this, we recommend that you get the presentation field for the reference field right in the query and then display the presentation field in the report. And it is feasible to output the reference field itself to cell details, so that users can open the cell value to view the item.
Example:
SELECT CompanySalesTurnovers.Items AS Items, CompanySalesTurnovers.SalesDocument.Counterparty AS SalesDocumentCounterparty, CompanySalesTurnovers.QuantityTurnover AS QuantityTurnover, CompanySalesTurnovers.SalesAmountTurnover AS SalesAmountTurnover FROM AccumulationRegister.CompanySales.Turnovers AS CompanySalesTurnovers TOTALS SUM(QuantityTurnover), SUM(SalesAmountTurnover) BY OVERALL, Items, SalesDocumentCounterparty
When the query result is output to a spreadsheet document, the application executes additional queries to get presentations of the Items and Counterparty fields, which reduces the report performance. To avoid getting presentations during the report display operation, add presentation fields to the query and pass them to the report.
Example:
SELECT CompanySalesTurnovers.Items AS Items, CompanySalesTurnovers.Items.Presentation AS ItemPresentation, CompanySalesTurnovers.SalesDocument.Counterparty AS SalesDocumentCounterparty, CompanySalesTurnovers.SalesDocument.Counterparty.Presentation AS CounterpartyPresenation, CompanySalesTurnovers.QuantityTurnover AS QuantityTurnover, CompanySalesTurnovers.SalesAmountTurnover AS SalesAmountTurnover FROM AccumulationRegister.CompanySales.Turnovers AS CompanySalesTurnovers TOTALS SUM(QuantityTurnover), SUM(SalesAmountTurnover) BY OVERALL, Items, SalesDocumentCounterparty
The ItemPresentation and CounterpartyPresentation fields displayed in the spreadsheet document should be taken from this query.
Note that the output form wizard available in Designer follows the above rules. It automatically adds the required presentation fields into a query and ensures they are displayed in the report.
It is also worth mentioning that getting attributes from reference fields using . (dot) while a report is being displayed can significantly impact the performance. So in order to display the attributes of reference fields, you should get them directly in the query instead of via a reference when the report is displayed.
For example, to display the Code field of an item, use the following query:
SELECT CompanySalesTurnovers.Items AS Items, CompanySalesTurnovers.Items.Presentation AS ItemPresentation, CompanySalesTurnovers.Items.Code AS ItemCode, CompanySalesTurnovers.SalesDocument.Counterparty AS SalesDocumentCounterparty, CompanySalesTurnovers.SalesDocument.Counterparty.Presentation AS CounterpartyPresenation, CompanySalesTurnovers.QuantityTurnover AS QuantityTurnover, CompanySalesTurnovers.SalesAmountTurnover AS SalesAmountTurnover FROM AccumulationRegister.CompanySales.Turnovers AS CompanySalesTurnovers TOTALS SUM(QuantityTurnover), SUM(SalesAmountTurnover) BY OVERALL, Items, SalesDocumentCounterparty
And display the ItemCode field in the report instead of getting the Code attribute using a reference.
Next page: Sample queries for hierarchical catalog operations