Convert WEBI report FORALL function into Equivalent SQL Logic

[#1]

Hi,

I am trying to convert webi formulas into equivalent Oracle SQL code. But I am confused when using some functions (FOREACH and FORALL). Can anyone please look at the below formula and help me converting it into equivalent Oracle SQL code.

FYI, I am using BO 4.1 SP 9.

WEBI FORMULA 2:

[Baseline text] = If([Baseline Identifier]=0) Then "Current Cost Baseline" Else If ([Baseline Identifier] =Max([Baseline Identifier]) ForAll([Baseline Identifier];[Approval Date];[Finish FY Number];[FY1 Estimated Amount];[Total Estimated Amount] )) Then "Original Baseline" Else "Re-baseline"

(Universe Object SQL Definitions:

[[Baseline Identifier]= DW.CAPTL_INVMT_BSLINE.BASELINE_ID

[Approval Date]= DW.CAPTL_INVMT_BSLINE.APRVL_DT

[Finish FY Number]= DW.CAPTL_INVMT_BSLINE.FINISH_FY_NO

[FY1 Estimated Amount]= DW.CAPTL_INVMT_BSLINE.FY1_EST_AM

[Total Estimated Amount]= DW.CAPTL_INVMT_BSLINE.TOTAL_EST_AM

)

I was able to represent most of the formula in terms of SQL but I have some doubt in converting FORALL function into SQL terms, can you please look into it and add the equivalent FORALL sql in the code below.

Oracle SQL:

SELECT CASE WHEN DW.CAPTL_INVMT_BSLINE.BASELINE_ID=0 THEN 'Current Cost Baseline' WHEN (DW.CAPTL_INVMT_BSLINE.BASELINE_ID=MAX(DW.CAPTL_INVMT_BSLINE.BASELINE_ID)) THEN 'Original Baseline' Else 'Re-baseline' END AS "Baseline text" from DW.CAPTL_INVMT_BSLINE GROUP BY DW.CAPTL_INVMT_BSLINE.BASELINE_ID

-- FORALL - yet to add equivalent foreach function;

Thanks & Regards.

Naveen.

[former_member45129]

Try adding like below

ForAll - Remove necessary entity(Dimensions) in Groupby Clause

Foreach - Include those entities in Groupby Clause