Conditional Sum trouble

Can someone help me figure this out? I'm trying to total up 4 columns which
have been X'd to indicate inventory types: RAWX, NSFGX, BUYX and SFGX.
I've tried:
=iif( Fields!RAWX.Value = "X", Sum(Fields!BKIC_PROD_AVGC.Value *
Fields!BKIC_PROD_UOH.Value), 0)
=iif( Fields!NSFGX.Value = "X", Sum(Fields!BKIC_PROD_AVGC.Value *
Fields!BKIC_PROD_UOH.Value), 0)
=iif( Fields!BUYX.Value = "X", Sum(Fields!BKIC_PROD_AVGC.Value *
Fields!BKIC_PROD_UOH.Value), 0)
=iif( Fields!SFGX.Value = "X", Sum(Fields!BKIC_PROD_AVGC.Value *
Fields!BKIC_PROD_UOH.Value), 0)
The reoport runs without errors, but I get a Grand Total for RAWX and 0 for
the other three columns.
If I change the expressions to:
=Sum(iif( Fields!RAWX.Value = "X", (Fields!BKIC_PROD_AVGC.Value *
Fields!BKIC_PROD_UOH.Value), 0))
=Sum(iif( Fields!NSFGX.Value = "X", (Fields!BKIC_PROD_AVGC.Value *
Fields!BKIC_PROD_UOH.Value), 0))
=Sum(iif( Fields!BUYX.Value = "X", (Fields!BKIC_PROD_AVGC.Value *
Fields!BKIC_PROD_UOH.Value), 0))
=Sum(iif( Fields!SFGX.Value = "X", (Fields!BKIC_PROD_AVGC.Value *
Fields!BKIC_PROD_UOH.Value), 0))
I get #ERROR in all columns and a warning for each field:
"The value expression for the textbox â'RAWXâ' uses an aggregate function on
data of varying data types. Aggregate functions other than First, Last,
Previous, Count, and CountDistinct can only aggregate data of a single data
type.
BKIC_PROD_AVGC and BKIC_PROD_UOH are the same data type.
What is this message trying to tell me?Did you try this for the RAWX column?
=iif( Fields!RAWX.Value = "X", Sum(Fields!BKIC_PROD_AVGC.Value) *
Sum(Fields!BKIC_PROD_UOH.Value), 0)
--
I would also try to display the conditional value for the other three
to verify that those fields equal your "X" value
For example (for the NSFGX column), just put
= (Fields!NSFGX.Value = "X")
and see if it returns 'True' or 'False'
that may lead you in the right direction.
--
Also, I'm not sure how your report is set up, but did you notice that
for each of your columns, you're referencing the same values (
Fields!BKIC_PROD_AVGC.Value and Fields!BKIC_PROD_UOH.Value )?

Conditional Column Mapping

Hey all! I have a bunch of questions, but let's start with this one:

Incoming from my flat file, I have two columns:

employee_id
dept_id

These indicate who did the work, and for which department (people can work for more than one department). In my destination table, I have the following two columns:

employee_id_sales
employee_id_wrhs

I want to map the employee id either to employee_id_sales or employee_id_wrhs, depending on the dept_id from the flat file.

How do I specify conditional column mapping?

I'm really new to SSIS, so I might be missing something obvious.

Thanks!

-- Jim
I'd use a derived column transformation...

New column name: employee_id_sales
Expression: dept_id == 1 ? employee_id : NULL(DT_WSTR,20)

New column name: employee_id_wrhs
Expression: dept_id == 2 ? employee_id : NULL(DT_WSTR,20)

The NULL() function should represent whatever data type you are really working with. I just used DT_WSTR as an example.

Then coming out of the derived column transformation, you have your two columns that you simply map to the similarly named column in the destination.|||Outstanding! Thanks!

Cheers!

-- jim