正确需要嵌套的 IIF 或 SWITCH 语句语法

问题描述

有人可以告诉我在 SSRS 的这个公式中我遗漏了什么吗?或者更好的是有人可以用 NESTED IIF 语法写同样的东西吗?

Can somebody please tell me what I am missing in this formula in SSRS? Or better yet can somebody please write this same thing in a NESTED IIF syntax?

Switch(
    (Parameters!StartMonth.Value <= 1 And Parameters!EndMonth.Value >= 1),
     (Code.CalculateFraction(
                             (Fields!retail1.Value -Fields!cost1.Value) , Fields!cost1.Value
                            ) *100
     ),
    (Parameters!StartMonth.Value <= 2 And Parameters!EndMonth.Value >= 2),
     (Code.CalculateFraction(
                               (
                                 (Fields!retail1.Value +Fields!retail2.Value)-
                                 (Fields!cost1.Value + Fields!cost2.Value)
                               ) ,
                             (Fields!cost1.Value + Fields!cost2.Value)
                            ) *100
     )
   )

这真让我发疯.为简单起见，我只在这里放置了 2 次迭代.我有其中的 12 个，接下来的每一步我都必须总结零售 1 到零售 12 和成本 1 到成本 12.

This is seriously driving me crazy. For simplicity I have just put 2 iterations here. I have 12 of these and every next step I have to sum up retail1 until retail12 and cost1 until cost12.

我一开始就无法为这两个人做对.

I cant get it right for these two in the first place.

我现在正在尝试这个并且仍然返回第一个条件中的值

I am trying this now and still returns the value in the first condition

=iif(
        Parameters!StartMonth.Value <= 1 AND Parameters!EndMonth.Value >= 1,
        ReportItems!txtTotal2.Value,
        iif(
        Parameters!StartMonth.Value <= 2 AND Parameters!EndMonth.Value >= 2,
        ReportItems!txtTotal3.Value,
            iif(
            Parameters!StartMonth.Value <= 3 AND Parameters!EndMonth.Value >= 3,
            ReportItems!txtTotal4.Value,
            Nothing
               )
           )
    )

编辑 2:

弄清楚什么是不正确的.

FIGURED OUT WHAT WAS INCORRECT.

我的整个逻辑都不正确，无法得到我期望的结果.就我而言，很明显，无论我使用什么，无论是 IIF 还是 switch，都只会执行第一个语句，因为它是真的.

My entire logic was incorrect to get to the result that I was expecting. It was obvious in my case that whatever I use, be it IIF or switch only the first statement would execute because it was true.

但我不得不改变逻辑才能得到我想要的结果.

But I had to change the logic to get to the result that I wanted.

iif(
        Parameters!EndMonth.Value = 1,
        ReportItems!txtTotal1.Value,
                  Parameters!EndMonth.Value = 2,
              ReportItems!txtTotal2.Value,
             and so on
            )

这解决了我的问题.谢谢你们，我很感激.

This solved my problem. Thanks guys I appreciate it.

推荐答案

尝试使用分离的 IIF 语句:

Try using separated IIF Statements:

=iif(
    Parameters!StartMonth.Value <= 1 AND Parameters!EndMonth.Value >= 1,
    ReportItems!txtTotal2.Value, Nothing
    ) OR

 iif(
    Parameters!StartMonth.Value <= 2 AND Parameters!EndMonth.Value >= 2,
    ReportItems!txtTotal3.Value, Nothing
    ) OR

 iif(
    Parameters!StartMonth.Value <= 3 AND Parameters!EndMonth.Value >= 3,
    ReportItems!txtTotal4.Value, Nothing
    )

这篇关于正确需要嵌套的 IIF 或 SWITCH 语句语法的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！