Convert Webi Report Queries to one SQL

[former_member194139]

Hello Experts,

I've a requirement to convert the webi report in to One SQL and give to to client so they can find the SQL in database and get the results.

This WEBI report has 2 Queries and using the MERGE dimension feature.

And I've a column in report having variable as AMT = SUM(Query1.X_Amt + Query2.Y_Amt)

Please help me on how to put these two queries together and calculate the AMT.

Thanks in advance

[former_member194139]

Thank You Sanjay for quick response. These are not Bex queries. The database is Netezza and report created on Universe. All three repor queries using same universe.

Here is query 1

Select Case ID, Dimension Object1, Dimension Object 2, Amt_1

From ............

Where

Condition1

Prompt Enter Month

Query 2

Select Cse ID, Dimension Object 3, Amt 2, Amt3, Amt 4

From ...

Where

Condition2

Prompt Enter Month

Query 3

Select Cse ID, Dimension Object 4, Amt5

From ...

Where

Condition2

Prompt Enter Month

Now final output I need is

Dimenison Object1, Dimenison Object 2, AMT = Amt1+Amt4+Amt5

This is how my queries are. Please help me whether UNION is possible or Not.

Thanks!

[former_member411491]

Hello Stephens,

i would suggest checking the automatic custom query built behind the reports. in Webi Report query panel you can see the custom query behind both the 2 query used in the report then check the operation, in mostly its union.

SO you can create your own SQL query using the 2 queries with union operation. Please keep the prompt in mind so might need to edit the syntex by passing fixed values directly.

for creting calcultion to get result from both the query you can check the backend if it bex you can create the create the formula in bex itself or by making one query bease on beckend infoproviders.

Regards,

Sanjay Guha