How to show #ERROR as Zero or as Nothing in matrix cell when groups and calculated field are based on switch-function in SSRS?

Petr Ilin 1 Reputation point
2020-08-28T17:31:47.12+00:00

Imagine simple BI task: We have two grouping columns and one calculated field. like

select group1, group2, sum(sales) Sum_of_sales from dbo.table1
group by group1, group2

When for some combination of grouping columns there's no ROW in dataset - report shows me Empty. It's ok.
BUT!

Now i'm building matrix with groups depending on parametrs. Some sort of programmatic matrix. User deciedes what to analyze by himself. He chooses X_dimension and Y_dimension and target CalculatedField

Grouping expression for first group now looks like

=Switch(
Parameters!param1.Value = "var1", Fields!groupingColumn1.Value,
Parameters!param1.Value = "var2", Fields!groupingColumn2.Value)
And for second group

=Switch(
Parameters!param2.Value = "var3", Fields!groupingColumn3.Value,
Parameters!param2.Value = "var4", Fields!groupingColumn4.Value)
With target value:

=Switch(
Parameters!param3.Value = "var5", Count(Fields!sales_sum.Value),
Parameters!param3.Value = "var6", Sum(Fields!sales_sum.Value)
)

When there are sales in combinations of groups - it's ok. It shows me exact value - like i would sum it with calculator. But when THERE'S NO DATA for some partition - it just raises error in that cell. And it seems like no way out. Pls help.

P.S. I tried all possible variants such as

iif(sum(...) is Nothing, 0 , sum(...))
sum(iif(value > 0 , value , 0))

and combination of them i tried comparing with ZERO, Nothing , and checking isNothing

but i still get error in cell

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,813 questions
0 comments

1 answer

Sort by: Most helpful
Most helpful Newest Oldest
  1. ZoeHui-MSFT 33,296 Reputation points
    2020-08-31T04:10:49.113+00:00

    Hi,

    I couldn't know exactly what you mean.

    The error occurs on target value?

    When you use

    =Switch(
    Parameters!param3.Value = "var5", Count(Fields!sales_sum.Value),
    Parameters!param3.Value = "var6", Sum(Fields!sales_sum.Value)
    )

    If there is no data, it will show error?

    Could you please share an example dataset sample so we could understand the issue correctly.

    Pleas also check the String type of the Fields!sales_sum.Value.

    If you want to use sum(iif(value > 0 , value , 0)), you need to change like sum(cdbl(iif(value > 0 , value , 0)))

    Regards,

    Zoe

    0 comments