Forum Discussion
Paginated Report conditional formatting issue when Amount is NULL and totals are in same row scope.
Hi smit16299,
In Paginated Reports, total and margin rows like Total Revenue or Gross Margin (%) are not provided by the dataset but are generated by SSRS during rendering using group aggregates. As a result, any textbox linked to a dataset field will show as NULL for these rows. This NULL value is a rendering artifact, not missing data.
Because of this, using conditional formatting with IsNothing() is unreliable, especially when totals and detail rows are in the same row group. While InScope() can help with structure, it does not reliably identify totals in this case.
The best approach is to base formatting on the row’s purpose rather than field values. By using CUSTOM_DISPLAY_NAME or a row-type flag to specifically target total or margin rows and applying formatting at the row or static cell level, you can achieve consistent results. This method is considered best practice for financial and P&L reports.
Thank you.
Community SupportHi smit16299,
This behavior is normal for Paginated Reports and isn’t caused by your expression logic. Rows like Total Revenue, Gross Margin (%), and Net Income are created by SSRS during report rendering and aren’t present in the dataset. As a result, if a textbox is linked to a field with a NULL value, SSRS might skip the BackgroundColor style, leaving cells blank or without color. To address this, avoid using IsNothing() or amount-based logic for conditional formatting. Instead, set the BackgroundColor based on row identity using CUSTOM_DISPLAY_NAME, and apply it to the row or a static header cell rather than the value cell. This ensures consistent formatting, even when the amount is NULL. For financial and P&L reports, it’s best to color only semantic total or margin rows and keep detail rows white, since NULLs in totals are just rendering artifacts and formatting should reflect row meaning, not field values.
Thank you.
Hello v-sgandrathi ,
Can you explain that in little bit in detailed?- v-sgandrathi
Hi smit16299,
In Paginated Reports, total and margin rows like Total Revenue or Gross Margin (%) are not provided by the dataset but are generated by SSRS during rendering using group aggregates. As a result, any textbox linked to a dataset field will show as NULL for these rows. This NULL value is a rendering artifact, not missing data.
Because of this, using conditional formatting with IsNothing() is unreliable, especially when totals and detail rows are in the same row group. While InScope() can help with structure, it does not reliably identify totals in this case.
The best approach is to base formatting on the row’s purpose rather than field values. By using CUSTOM_DISPLAY_NAME or a row-type flag to specifically target total or margin rows and applying formatting at the row or static cell level, you can achieve consistent results. This method is considered best practice for financial and P&L reports.
Thank you.
- v-sgandrathi
Hi smit16299,
We wanted to check if you had a chance to review our last reply. Let us know if it helped or if you need more guidance, we're always happy to help further.
Looking forward to hearing from you!
Thank you.
