Question Re: Convert WEBI report FORALL function into Equivalent SQL Logic in Technology Q&A

Convert WEBI report FORALL function into Equivalent SQL Logic

Former Member — Fri, 20 Oct 2017 15:24:17 GMT

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.

Re: Convert WEBI report FORALL function into Equivalent SQL Logic

former_member191664 — Fri, 20 Oct 2017 23:41:25 GMT

You can capture the actual Oracle SQL query for execution from webi tracing.

regards,

Jin-Chong

Re: Convert WEBI report FORALL function into Equivalent SQL Logic

amitrathi239 — Sat, 21 Oct 2017 14:20:38 GMT

are you not getting desired result without adding forall objects in univers case statement?

If not then add all those objects in group by cla

Re: Convert WEBI report FORALL function into Equivalent SQL Logic

Former Member — Sat, 21 Oct 2017 16:33:05 GMT

Amit,

Thanks for the reply.

Even if i add FORALL objects in the Group By clause of sql statement, the result set in webi & SQL is different.

Re: Convert WEBI report FORALL function into Equivalent SQL Logic

Former Member — Sat, 21 Oct 2017 16:42:29 GMT

Thanks Jin-Chong for you reply but I am not an Admin for BO, so I can't do WEBI tracing and it will be almost impossible to get this from my admin. So if possible, can you please try one from your system by creating couple of WEBI formulas using FOREACH and FORALL functions and see what code the system generates.

Thanks for your time in helping me with this issue..

Naveen.

Re: Convert WEBI report FORALL function into Equivalent SQL Logic

amitrathi239 — Sat, 21 Oct 2017 16:50:47 GMT

Forall context is same as group by clause in sql.even I don't think so these objects are required in oracle case statement.create object in universe with case statement and try.

Check attached link for to understand different context

http://www.dagira.com/2010/01/04/calculation-context-part-i-overview/

Re: Convert WEBI report FORALL function into Equivalent SQL Logic

Former Member — Sat, 21 Oct 2017 17:27:16 GMT

Amit,

I am not trying to achieve this FORALL context formula in Universe. I have a requirement to convert all the webi formulas into SQL, so that we can include them in database views.

If possible can you provide me a example by converting webi formulas into SQL by using FORALL & FOREACH.

Thanks & Regards.

Naveen.

Re: Convert WEBI report FORALL function into Equivalent SQL Logic

former_member45129 — Mon, 23 Oct 2017 09:55:25 GMT

Try adding like below

ForAll - Remove necessary entity(Dimensions) in Groupby Clause

Foreach - Include those entities in Groupby Clause