How to convert web-i formula into IDT formula at business layer

[Former Member]

Hi,

Below is a formula I written in web-i but wishes to try to create the same for another report in business layer in IDT.

= If [Bookings]ForEach([Unit])< 5000 Then "A. < 5k"

ElseIf [Bookings]ForEach([Unit]) < 10000 Then "B. < 10k" 

ElseIf [Bookings]ForEach([Unit]) < 25000 Then "C. < 25k" 

ElseIf [Bookings]ForEach([Unit]) < 50000 Then "D. < 50k"

ElseIf [Bookings]ForEach([Unit]) < 100000 Then "E. < 100k" 

Else "F. > 100k"

In IDT I know we use "CASE WHEN.... THEN" statement for "IF...THEN"

CASE

WHEN @Select(SALES_FORECAST\Measures\Bookings\Bookings) < 5000

THEN 'A. < 5k'

WHEN @Select(SALES_FORECAST\Measures\Bookings\Bookings) < 10000

THEN 'B. < 10k'

WHEN @Select(SALES_FORECAST\Measures\Bookings\Bookings) < 25000

THEN 'C. < 25k'

WHEN @Select(SALES_FORECAST\Measures\Bookings\Bookings) < 50000

THEN 'D. < 50k'

WHEN @Select(SALES_FORECAST\Measures\Bookings\Bookings) < 100000

THEN 'E. < 100k'

ELSE 'F. > 100k'

END

I tried with a smaller code but I got an error. So how can I use "ForEach()" type of command without getting any error in IDT.

CASE WHEN@Select(SALES_FORECAST\Measures\Bookings\Bookings) < 5000

THEN 'A. < 5K'

ELSE 'F. > 100K'

END 

GROUP BY 

@Select(SALES_FORECAST\Data part\Unit Details\Unit)

Code for Bookings (type = measure) is:

SUM(CASE WHEN @Select(SALES_FORECAST\Data part\Transaction Details\Data Migration Test)= 'Data Migration'

THEN ToDecimal(0)

ELSE @catalog('SALES_FORECAST')."PUBLIC"."IGOFHDS10"."GOFBOOK1"

END)

Code for Unit (type = dimension)

CASE

WHEN @Select(SALES_FORECAST\Data part\Unit Details\Unit Name) IS NULL AND @Select(SALES_FORECAST\Data part\Customer\Plant Site Name) IS NULL AND @Select(SALES_FORECAST\Data part\Customer\Customer) IS NULL

THEN

concat('Missing Unit : ',@Select(SALES_FORECAST\Data part\Customer\Owner Name))

WHEN @Select(SALES_FORECAST\Data part\Unit Details\Unit Name) IS NULL AND @Select(SALES_FORECAST\Data part\Customer\Plant Site Name) IS NULL

THEN

concat('Missing Unit : ',@Select(SALES_FORECAST\Data part\Customer\Customer))

WHEN @Select(SALES_FORECAST\Data part\Unit Details\Unit Name) IS NULL

THEN

concat('Missing Unit : ',@Select(SALES_FORECAST\Data part\Customer\Plant Site Name))

ELSE

@Select(SALES_FORECAST\Data part\Unit Details\Unit Name)

END

Code for Unit Name (type = dimension) [same logic applied to get plant site name, customer and owner name) 

CASE WHEN @catalog('EDW_TRANS')."TRANSITIONAL"."BIDSS_CONTRACT_MASTER_DATA"."UNIT_NAME_C" IS NULL

THEN

@catalog('EDW_TRANS')."TRANSITIONAL"."BIDSS_PBUS_MASTER_DATA"."UNIT_NAME_B"

ELSE

@catalog('EDW_TRANS')."TRANSITIONAL"."BIDSS_CONTRACT_MASTER_DATA"."UNIT_NAME_C"

END

Please assist so I can create the syntax without any error.

[former_member193452 · Accepted solution]

Where are you putting the code? Derived table or calculated column?

By the @catalog I believe you are using a multisource universe.

You cannot put these in the business layer they must be in the data foundation for a calculated column or derived table.

In this instance is the sql ansi92 ?  or you may have to supply the database specific syntax. (check the box for this.)

Jacqueline