In the "Cross Tab" object of Pixel Perfect template designer, it seems not possible to have a global total defined, for instance as "sum(x)/sum(y)". In fact, the total is always calculated as aggregation of the details rows. This is an important limitation, as Cross Tab can not be used with percentages or ratios.
The only workaround seems to suppress the total rows and manually add totals labels after or before the object. Does anyone has smarter solutions?
You are correct that the Pixel Perfect Cross Tab object in NPrinting/Qlik automatically aggregates detail rows rather than calculating a complex ratio (like a global) at the total level. The limitation arises because it performs a simple Sum of the results in the cells above.
Here are some more robust solutions to handle ratios/percentages in Cross Tab totals, moving away from manual labels.
1. The Best Practice: Pre-calculate in Qlik Sense/View
The most reliable solution is to calculate the final percentages or ratios within your Qlik script or data model before bringing the data into NPrinting.
Why: NPrinting is designed to report data, not perform complex analytics.
How: In your Qlik load script, calculate RatioField = [Numerator] / [Denominator]. Then, in Pixel Perfect, simply use Sum(RatioField) or Avg(RatioField) if appropriate, or use the pre-calculated total row from Qlik.
2. Solution: Create a "Calculated Field" in the Designer
You can create a new formula within the Pixel Perfect designer that calculates the ratio, rather than using the default sum behavior.
Open the Level node in the Pixel Perfect template.
Right-click on the data row (Table Row) and select Add a Calculated Field.
Set the expression to your formula, for example: [FieldA] / [FieldB] (using the data sources).
Drag this new calculated field into the cell where the percentage is needed.
Crucial Step: Select the field, go to the property panel, and ensure the summary behavior is set to handle the formula rather than just Sum.
3. Solution: Use the BeforePrint Event
For more advanced control, you can use the BeforePrint event of the specific Total Cell to calculate the ratio based on raw data.
Select the Total Cell cell.
Open the Scripts section in the Properties panel and select BeforePrint.
Use the Summary function to get the values from the raw data and calculate the ratio.
Example: e.Value = GetCurrentColumnValue("Num") / GetCurrentColumnValue("Denom") (Syntax varies slightly based on the specific DevExpress version underlying NPrinting).
Summary Checklist
Avoid: Using the default Sum function on a pre-calculated percentage (e.g., Sum(0.1 + 0.2 + 0.3) will give 0.6 instead of the required 0.2 average).
Use: Pre-calculated fields or calculated formulas inside the template.
