Correct expression with IIF does not work

Question

I work with CRM online. This is why I use fetchXML. This is my fetch:

`<fetch aggregate="true" distinct="false" no-lock="false" mapping="logical">
<entity name="lead" >
<attribute name="fullname" alias="lead_name" groupby="true" />
<attribute name="new_bpf_stage" alias="lead_stage" groupby="true" />
<attribute name="new_substep" alias="lead_substep" groupby="true" />
<attribute name="leadid" alias="lead_id" groupby="true" />
<filter type="and" >
  <condition attribute="createdon" operator="ge" value="@PeriodBeginning" />
  <condition attribute="createdon" operator="le" value="@PeriodEnding" />
</filter>
<link-entity name="new_duration_life_cycle"    from="new_lead_duration_life_cycleid" to="leadid" link-type="outer" >
  <attribute name="new_duration" alias="current_duration" groupby="true" />
  <attribute name="new_active" alias="current_is_active" groupby="true" />
  <attribute name="new_transition_date" alias="current_transition_date" aggregate="max" />
  <filter type="and" >
    <condition attribute="new_active" operator="eq" value="1" />
  </filter>
</link-entity>
<link-entity name="new_duration_life_cycle"    from="new_lead_duration_life_cycleid" to="leadid" link-type="outer">
  <attribute name="new_duration" alias="duration_sum" aggregate="sum" />
</link-entity>
</entity>

` This is expression code:

=IIF(Fields!current_is_activeValue.Value = "True", 
DATEDIFF ("d", CDate(Format(Fields!current_transition_dateValue.Value, "MM.dd.yyyy hh:mm:ss")), Now()) + 
IIF(DATEDIFF ("h", CDate(Format(Fields!current_transition_dateValue.Value, "MM.dd.yyyy hh:mm:ss")), Now()) Mod 24 > 8, 
    8, 
    (DATEDIFF ("h", CDate(Format(Fields!current_transition_dateValue.Value, "MM.dd.yyyy hh:mm:ss")), Now())) Mod 24)/8 +
IIF(Fields!current_duration.Value = "",
    0,  
    CDbl(Replace(Fields!current_duration.Value,",",".")))
,
Fields!current_duration.Value)

In this code I check some attribute and if its value is true I calculate field value. But if some values not exist (it is in case current_is_activeValue.Value = ""), in the appropriate cell will displays error. But error must not exist: must displays Fields!current_duration.Value that must be "". It looks like expression calculates both IIF cases anyway. Has anybody trouble like this?

"d" and "h" are not valid DATEDIFF intervals in SSRS you may use DAY and HOUR or DateInterval.Day and DateInterval.Hour. See documentation here. Also, I'm not sure that the periods in the format string are allowed. You may have to change "MM.dd.yyyy hh:mm:ss" to "MM/dd/yyyy hh:mm:ss" or "MM-dd-yyyy hh:mm:ss". — Kidiskidvogingogin

programmie programmie · Accepted Answer · 2016-03-18T16:58:33

Tried breaking up your expression to visualize Iffs :

First IIF's IF condition

 =IIF(Fields!current_is_activeValue.Value = "True",

this whole section is the THEN clause for first IIF. As you can see all the three values are added and so the two IIF inside of this clause will be calculated.

DATEDIFF ("d", CDate(Format(Fields!current_transition_dateValue.Value, "MM.dd.yyyy hh:mm:ss")), Now()) 
+ 
IIF(DATEDIFF ("h", CDate(Format(Fields!current_transition_dateValue.Value, "MM.dd.yyyy hh:mm:ss")), Now()) Mod 24 > 8, 
8, 
(DATEDIFF ("h", CDate(Format(Fields!current_transition_dateValue.Value, "MM.dd.yyyy hh:mm:ss")), Now())) Mod 24)/8 
+
IIF(Fields!current_duration.Value = "",    0,     CDbl(Replace(Fields!current_duration.Value,",",".")))

Else for the first IIF

,
Fields!current_duration.Value)

It is calculating both IIFs. You may need to edit the expression.

Correct expression with IIF does not work

2 Answers