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

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

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,095 questions
No comments
{count} votes

1 answer

Sort by: Most helpful
  1. answered 2020-08-31T04:10:49.113+00:00
    ZoeHui-MSFT 18,671 Reputation points Microsoft Employee

    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

    No comments