SSRS Report Builder IIF error with aggregate

Question

I've been struggling with an IIF error when trying to create a variable aggregate.

I'm using Report Builder 3.0

I have a report where users determine what fields are in a report. These fields are passed as a multivalue parameter. I use a lookup from a different dataset to determine its placement in the report, and using the same order dataset to determine if the field is numeric or not (meaning I want to sum the value based on row-level grouping). The headers pull in using the same field lookup, which works fine. At the row level I'm trying to return either the sum of the field value based on row grouping, or if non-numeric, return the value. Based on similar posts I understand that both the true and false parts are processed. I attempted to offset this error by nesting another IIF. If I remove the sum function the data returns non-numeric data fine. However if I include the sum function numeric data is processed fine however non-numeric data returns #error.

What am I missing?

Here is a definition of the data I'm referencing below:

lookupvalue: returns the fields selected by the user based on predefined order in a stored procedure.

Fieldisnumeric: indicates if the field selected is numeric or not, 0 is false, 1 is true

Fielditem: the field item in the tablix being referenced

dtsselectedfields: the dataset I'm looking up the column order and numeric properties of a field.

The number 1 indicates the first position in the variable count of fields selected by the user. Additional fields are hidden based on the count of fields passed in the parameter and are incremental (e.g 1-n).

=iif(Lookup(1, Fields!LookupValue.Value,
Fields!FieldIsNumeric.Value,"dtsSelectedFields")=0,
Fields(Lookup(1, Fields!LookupValue.Value, Fields!FieldItem.Value,
"dtsSelectedFields")).Value,iif(Lookup(1, Fields!LookupValue.Value,
Fields!FieldIsNumeric.Value, "dtsSelectedFields")=0,
Fields!MV.Value,sum(Fields(Lookup(1, Fields!LookupValue.Value,
Fields!FieldItem.Value, "dtsSelectedFields")).Value)))

****edit 12/1/2015**** For additional clarity, I'm providing additional details. Below is 'dtsSelectedFields' dataset.

FieldItem_____LookupValue_____FieldIsNumeric
Item1Desc__________1__________________0
Item1Total__________2__________________1
Item2Desc__________3__________________0
Item2Total__________4__________________1

Let's say I have one column of data, and this column would first look for a LookupValue of 1. This would return the FieldItem, 'Item1Desc'. Because this field is not numeric, I would want to return the value of Item1Desc. However let's assume my first selection was actually 'Item1Total' and I don't want to return the non-numeric Item1Desc field. In this case, because 'FieldIsNumeric'=1 indicating a numeric field, I want to take the sum of this field.

1. Is it possible to nest an aggregating function in an IIF statement on only one part of an IIF statement? I.e. the true part or false part?
2. And if so, what am I doing wrong?

An example of the tablix:

sample layout
Column 1 Header____________Column 2 Header___________Column 3 Header
Column 1 Data______________Column 2 Data_____________Column 3 Data

Sample data

Product___________________Country of Origin_________________Units
ABC Envelopes___________________China______________________15
LMN Packets_____________________India_______________________30

In the example above, user selects 3 columns, 'Product', 'Country of Origin', and 'Units'. There are other fields available that would cause multiple rows if I grouped by them in the stored procedure (for example acquisition price). Based on the lookup I return the column description as a header. The row-level detail is described as above (e.g. Return the product name and country of origin, but sum up the units).

Answer 1

As a workaround for my issue above, I found an (ugly?) solution.

As mentioned above, a user can select any number of columns and the report organizes them in columns based on a predefined order according to a stored procedure. (E.g. a product description would come before the sum of the units if those two columns were chosen, but a product ID may come before the product description, but only if the ID was chosen.)

For every possible number of columns a user can select, I added two columns in the report (i.e. two for each field).

The first two columns will reference the lookupvalue=1. I then set the expression of each field in the detail to 1) a sum of the value, or 2) the value itself. I then set the column visibility to the results of the 'FieldIsNumeric' column. So the summed numeric column which would return an error for non-numeric data would be hidden when FieldIsNumeric=0, and the non-numeric column referencing the lookup value =1 would be shown, and vice versa.

Needless to say additional columns would follow the same logic in sets of two, each referencing the sequential lookup value (e.g. columns 3 and 4 would reference lookupvalue=2, columns 5 and 6 would reference lookupvalue=3, and so forth. Each column within the matching pair would be displayed or hidden based on the returned value of FieldIsNumeric in the same lookup dataset.

I'm definitely open to suggestions, but thought I'd post this as a workaround solution.