SSRS - Textbox1 uses a previous aggregate -these Aggregate functions cannot be specified as nested aggregates

Question

I'm trying to write an expression in SSRS but I keep getting the error message below:

"Textbox1 uses a first, last or previous aggregate in an outer aggregate. These Aggregate functions cannot be specified as nested aggregates"

What does this mean?

All I'm trying to do is to write an expression that:

count the number of card where the activation date <=period_start_date AND (Deactivation date is null OR Deactivation Date >=period_start_date)

This is how my expression looks like:

= Count (

              IIF(
                     (First(Fields!CUBD_CareDay_activate_DT_SHORT.Value, "DataSet1")                   <=First(Fields!CUBD_CareDay_Period_Start_DT_SHORT.Value, "DataSet1"))
              AND
             ( First(Fields!CUBD_CareDay_deactivate_DT_SHORT.Value, "DataSet1")=""
              OR
              (
             First(Fields!CUBD_CareDay_deactivate_DT_SHORT.Value,      "DataSet1")>=First(Fields!CUBD_CareDay_Period_Start_DT_SHORT.Value, "DataSet1")
      )
   )
   ,Fields!CUBD_CareDay_Unit_ID.Value
   ,0
      )

)

Any ideas or tips?

Answer 1

Hi ,

This will be your expected Expression;

=Count (IIF(activation date <=(period_start_date AND (Deactivation date is null OR Deactivation Date >=period_start_date)),card ,0)
i.e;
=Count (IIF(Fields!CUBD_CareDay_activate_DT_SHORT.Value <=
(Fields!CUBD_CareDay_Period_Start_DT_SHORT.Value AND 
(Fields!CUBD_CareDay_deactivate_DT_SHORT.Value ="" OR Fields!CUBD_CareDay_deactivate_DT_SHORT.Value >=Fields!CUBD_CareDay_Period_Start_DT_SHORT.Value))
,Fields!CUBD_CareDay_Unit_ID.Value ,0)

Thanks

Answer 2

Hi Rookie,

According to your description, you have issues on the expression which involved nested aggregation. Right?

In Reporting Services, Expression can contain calls to nested aggregate functions with the following exceptions and conditions:

• Scope for nested aggregates must be the same as, or contained by, the scope of the outer aggregate. For all distinct scopes in the expression, one scope must be in a child relationship to all other scopes.
• Scope for nested aggregates cannot be the name of a dataset.
• Expression must not contain First, Last, Previous, or RunningValue functions.
• Expression must not contain nested aggregates that specify recursive.

In this scenario, when you use Count() function will count records row by row, it will execute for all rows. But in the IIF() function, you only compare the first record of those data fields, it will only compare one time. This makes the expression not working. Also if the condition returns false, you should set "noting" as false part. The expression should look like below:

 = Count (
              IIF(
                   (Fields!CUBD_CareDay_activate_DT_SHORT.Value<=Fields!CUBD_CareDay_Period_Start_DT_SHORT.Value)
               AND
              (Fields!CUBD_CareDay_deactivate_DT_SHORT.Value=""
                OR
               (
                Fields!CUBD_CareDay_deactivate_DT_SHORT.Value>=Fields!CUBD_CareDay_Period_Start_DT_SHORT.Value
               )
              )
              ,Fields!CUBD_CareDay_Unit_ID.Value,nothing)
)

Reference:
Aggregate Function (Report Builder and SSRS)

If you have any question, please feel free to ask.

Best Regards,
Simon Hou

Answer 3

After making the suggested changes to my expression I got the following error:

The Value expression for the textrun ‘Textbox162.Paragraphs[0].TextRuns[0]’ contains an error: Conversion from string "" to type 'Date' is not valid

So instead of evaluating  

Fields!CUBD_CareDay_deactivate_DT_SHORT.Value=""

I used

IsNothing(Fields!CUBD_CareDay_deactivate_DT_SHORT.Value)

It looks like that did the trick. 

The final expression looks like 

=COUNT(
        IIf(
                  Fields!CUBD_CareDay_activate_DT_SHORT.Value <= Fields!CUBD_CareDay_Period_Start_DT_SHORT.Value
                  AND
                  (
                    IsNothing(Fields!CUBD_CareDay_deactivate_DT_SHORT.Value)
                   OR(
                   Fields!CUBD_CareDay_deactivate_DT_SHORT.Value >= Fields!CUBD_CareDay_Period_Start_DT_SHORT.Value
                      )
                    )         
           ,Fields!CUBD_CareDay_Unit_ID.Value,0)
    )

Thanks for your help!