I. Overview
1. Problem description
Obtain the value of a column corresponding to the horizontal expansion, and perform the calculation of the proportion of the value in the group. The following is an example of a template for illustration.
The salesperson expands horizontally to obtain the sales volume of the salesperson Sun Lin column, and then calculate the proportion with the Sum column. That is to say, no matter which column Sun Lin is in, he can get his sales value. Results as shown below:
2. Realization ideas
Use the level coordinates to obtain the sales volume corresponding to Sun Lin, and use the formula Cellx[!0]{conditional expression} to participate in the calculation. Because this example is horizontal expansion, the formula is Cellx[;!0]{conditional expression}.
Note: The ";" sign is required before the "!" in the formula, don't forget it.
II. Example
1. Data preparation
Create a new dataset ds2, and enter the query statement select * from Sales_Volume.
2. Report design
Design the report as shown in the figure below, expand cell B1 horizontally, add formula to cell D2: B2[;!0]{B1 = "Sun Lin"} / C2, and set its format to percentage to retain two decimal places.
3. Effect preview
Save the report, click pagination preview, the effect is as shown in the figure below:
Note: Support mobile terminal preview.
III. Completed template
The completed template can refer to: %FR_HOME%\webapps\webroot\WEB-INF\reportlets\doc-EN\ReportApplication\BasicApplication\proportion_after_horizontal_expansion.cpt
Click to download the template:
proportion_after_horizontal_expansion.cpt