We recently deployed a new calculated field in our quality control inspection reports using Report Writer to aggregate inspection scores across multiple criteria. The calculated field is supposed to pull data from the Inspection Results object and apply weighted scoring based on inspection type.
However, we’re seeing incomplete results in the output. When running the report for inspections completed in the last 30 days, only about 60% of the expected records appear. The missing records all seem to have certain inspection types (Type C and Type D specifically).
The calculated field formula references the Inspection_Score field and applies a CASE statement based on Inspection_Type. We’ve verified the underlying data exists in the system - we can see all inspections in the standard Inspection Results report.
IF Inspection_Type = "Type_A" THEN Score * 1.2
ELSEIF Inspection_Type = "Type_B" THEN Score * 1.0
ELSE Score * 0.8
Has anyone experienced Report Writer filtering out records when calculated fields reference specific object contexts? Is there a configuration setting we’re missing for inspection scoring logic?
Based on the symptoms you described, here’s the complete solution addressing all three aspects:
1. Calculated Field Context Issue:
The root cause is that your calculated field is referencing Inspection_Type without properly establishing the field context. In Report Writer, when you create a calculated field that references fields from related business objects, you must first add those fields as report columns (they can be hidden) to establish the context.
Fix: Go to your report definition and add Inspection_Type as a column. You can set it to hidden if you don’t want it visible in output. This ensures Report Writer includes the join and makes the field available during calculation.
2. Report Writer Configuration - Business Object Relationships:
Verify your data source configuration:
- Navigate to Report Writer > Custom Reports > [Your Report]
- Click Data Source tab > Related Business Objects
- Ensure Inspection_Header and Inspection_Results are properly joined
- Set join type to INNER JOIN on Inspection_ID field
- Verify join condition: Inspection_Results.Inspection_ID = Inspection_Header.Inspection_ID
If the relationship shows as LEFT OUTER JOIN, change it to INNER JOIN since you want only inspections that have both header and results data.
3. Inspection Scoring Logic - Handle Edge Cases:
Update your calculated field formula to handle NULL values and ensure all inspection types are covered:
IF IFNULL(Inspection_Type,"") = "Type_A" THEN IFNULL(Score,0) * 1.2
ELSEIF IFNULL(Inspection_Type,"") = "Type_B" THEN IFNULL(Score,0) * 1.0
ELSEIF IFNULL(Inspection_Type,"") IN ("Type_C","Type_D") THEN IFNULL(Score,0) * 0.8
ELSE IFNULL(Score,0) * 0.8
The key changes:
- Added explicit handling for Type_C and Type_D in the ELSEIF condition
- Wrapped both Inspection_Type and Score in IFNULL to prevent NULL propagation
- Used IN operator for multiple type matching
Additional Verification Steps:
- Check Report Settings > Advanced Options > ensure “Include rows with null calculated fields” is enabled if you want to see records even when calculations fail
- Review any report prompts or filters that might be inadvertently filtering inspection types
- Verify data security policies aren’t restricting visibility of certain inspection types for the report run-as user
After making these changes, refresh your report cache and test with a small date range first. The Type C and D inspections should now appear with properly calculated scores.
Navigate to Report Writer > Custom Reports > Your Report Name. In the Data Source section, click on Related Business Objects. You should see both Inspection_Header and Inspection_Results listed. Click the relationship icon between them and verify the join condition is set to Inspection_ID = Inspection_ID with join type as Inner Join. If it’s set to Left Outer Join or the relationship isn’t defined, that would explain why Type C and D inspections are being filtered out. Also check if those inspection types have any specific security or data visibility rules that might be affecting the query results.
One more thing to check - the calculated field context itself. In Report Writer, calculated fields inherit the context of the primary business object. If your primary object is Inspection_Results but your CASE statement logic depends on Inspection_Type from the header, you need to explicitly bring that field into the report columns first before using it in calculations. Otherwise Report Writer may not have access to that field value during the calculation phase, causing it to evaluate to NULL and filter those rows out.
I’ve seen this before with calculated fields in Report Writer. The issue is usually related to the business object context. When you create a calculated field that references multiple related objects, Report Writer needs explicit join conditions. Check if your Inspection_Type field is coming from a different business object than Inspection_Score. If they’re from related but separate objects, you need to ensure the relationship is properly defined in the report data source configuration.
That’s interesting - I didn’t have Inspection_Type as a visible column in the report, only using it in the calculated field formula. Let me add it as a column and test.
Also verify your inspection scoring logic handles NULL values properly. If Type C and D inspections sometimes have NULL scores initially before final review, your calculated field needs explicit NULL handling. Something like IFNULL(Score, 0) before applying the multiplier. Report Writer by default excludes rows where calculated fields return NULL unless you configure it to include them.