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

Question

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

Answer 1

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