How to show #ERROR as Zero or as Nothing in matrix cell when groups and calculated field in SSRS?


I created a matrix, but there is no data in some cells. If the given cell is just a number, then it is empty. And if there are calculations in the cell, then an #error appears.172180-%D1%81%D0%BD%D0%B8%D0%BC%D0%BE%D0%BA-%D1%8D%D0%BA%D1%80%D0%B0%D0%BD%D0%B0-%D0%B2-2022-02-08-18-22-31.png

SQL Server Reporting Services
SQL Server Reporting Services
A SQL Server technology that supports the creation, management, and delivery of both traditional, paper-oriented reports and interactive, web-based reports.
2,790 questions
0 comments No comments
{count} votes

2 answers

Sort by: Most helpful
  1. Michael Taylor 47,711 Reputation points

    I believe the correct solution here is to fix your formula so it handles blanks (or nulls as the case may be). Perhaps something like this.

    IIf((IsNothing(SomeField)=True) Or (SomeField = ""), 0, SomeField)

    This would check the field to see if it is null or empty (I believe) and return 0 if it is. Otherwise it returns the field value. This would prevent errors but might be more complex if you're using this in a larger expression. At that point you should probably consider adding a field to your dataset to calculate this in advance so you can simplify the row display.

    0 comments No comments

  2. Isabellaz-1451 3,611 Reputation points

    Hi @Кривогорницына Виктория Андреевна

    How is your calculations look like ? when #error appears ,it means there is some error on your expression .

    Best Regards,

    If the answer is the right solution, please click "Accept Answer" and upvote it. If you have extra questions about this answer, please click "Comment".
    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

    0 comments No comments