Question:
How do I display the maximum value out of multiple aggregation fields?
Answer:
To achieve the goal, users can write a custom aggregation and use it in a crosstab report. Follow these steps:
- Create a new Crosstab style report. In the screenshot below, the "@Customer_Country" field is specified in the Columns section. The "Sum of @Cost" and "Sum of @Unit Price" fields are specified in the Summaries section.
- In the Resources box of the wizard, click "New Crosstab Formula", create a formula ("Formula1" in below example). The formula is defined as:
number summaries[] = [@(@Customers_Country:current, sum(@Cost)), @(@Customers_Country:current, sum(@"Unit Price"))];
return maximum(summaries);
- Insert the formula into the Summaries box, as shown in the screenshot below. Exit the wizard.
- Run the report. The screenshot below displays the intended visual result; i.e. the red section contains the maximum values from the aggregate values in the previous rows.
Further Reading