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.
Don’t rely on IsNothing() for totals — NULLs are often rendering artifacts.
Use row identity (CUSTOM_DISPLAY_NAME) or a RowType flag to detect totals/margins.
Apply BackgroundColor based on row meaning + ColorParam.
Default all other rows to white.
Example:
=IIF(
Fields!CUSTOM_DISPLAY_NAME.Value = "Total Revenue"
OR Fields!CUSTOM_DISPLAY_NAME.Value = "Gross Margin (%)",
Switch(
Parameters!ColorParam.Value = "Blue", "#FFFFFF",
Parameters!ColorParam.Value = "Orange", "#000000",
True, "#FFFFFF"
),
"White"
)
Try these solutions and let me know if you need further clarification
Regards,
Rufyda Rahma | MIE
Hello Rufyda ,
Thank you for your response.
I have now updated my background color logic in the Paginated Report based on your suggestion and I am applying conditional formatting using the row identity (CUSTOM_DISPLAY_NAME) and a ColorParam parameter, as shown below:
From the screenshots, you can see that for rows where the value is NULL (i.e., no data exists in the database), the background color is not being applied, and those cells appear blank or uncolored.
And also I want only colored background in Total Revenue, Total Expenses, Gross Margin %, Gross Profit (Loss), etc.
Not for Revenue, Expenses, etc.
Could you please advise how best to handle this scenario so that the background color is still applied consistently even when the underlying value is NULL?
Thank you in advance for your help.