Forum Discussion

smit16299's avatar
smit16299
Frequent Visitor
8 months ago
Solved

Paginated Report conditional formatting issue when Amount is NULL and totals are in same row scope.

  Hi Community, I’m facing a conditional formatting issue in Power BI Paginated Reports (SSRS) and wanted to highlight it here to get inputs or confirm best practices. Scenario I have a matrix ...
  • v-sgandrathi's avatar
    v-sgandrathi
    8 months ago

    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.

smit16299's avatar
smit16299
Frequent Visitor
8 months ago

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:

 

 
=IIF( Fields!CUSTOM_DISPLAY_NAME.Value = "Total Revenue" OR Fields!CUSTOM_DISPLAY_NAME.Value = "Total Expenses" OR Fields!CUSTOM_DISPLAY_NAME.Value = "Total Other Expenses" OR Fields!CUSTOM_DISPLAY_NAME.Value = "Total Cost of Goods Sold" OR Fields!CUSTOM_DISPLAY_NAME.Value = "Gross Profit (Loss)" OR Fields!CUSTOM_DISPLAY_NAME.Value = "Gross Margin (%)" OR Fields!CUSTOM_DISPLAY_NAME.Value = "Total Operating Expenses" OR Fields!CUSTOM_DISPLAY_NAME.Value = "Operating Profit (Loss)" OR Fields!CUSTOM_DISPLAY_NAME.Value = "Operating Margin (%)" OR Fields!CUSTOM_DISPLAY_NAME.Value = "Income Before Taxes" OR Fields!CUSTOM_DISPLAY_NAME.Value = "Net Income (Loss)", Switch( Parameters!ColorParam.Value = "Blue", "#0466c7", Parameters!ColorParam.Value = "Orange", "#fbe3dc", Parameters!ColorParam.Value = "Green", "#408340", Parameters!ColorParam.Value = "Violet", "#5e4b8b", Parameters!ColorParam.Value = "Forest", "#00a86b", Parameters!ColorParam.Value = "Aritek", "#f87316", True, "White" ), "White" )

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 only need colored in total values only not in Revenue, Expenses, etc.
Only need colored on Total Revenue, Total Expenses, Gross Margin %, 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.