Categories
- All Categories
- 14 Oracle Analytics Sharing Center
- 15 Oracle Analytics Lounge
- 208 Oracle Analytics News
- 41 Oracle Analytics Videos
- 15.6K Oracle Analytics Forums
- 6.1K Oracle Analytics Idea Labs
- Oracle Analytics User Groups
- 76 Oracle Analytics Trainings
- 14 Oracle Analytics Data Visualizations Challenge
- Find Partners
- For Partners
Add HAVING clause to reports
Hi,
i have developed a simple report in OBIEE and now i would add a HAVING clause. There is a way to add it to the report? Maybe in Advanced tab?
Thanks
Answers
-
what are you trying to achieve with Having clause.
0 -
I want reproduce following query:
SELECT
ABAN8 AS RPAN8;
ABALPH AS RPALPH;
right( trim( ABALKY ); 8 ) AS COLUMN0002;
RPMCU;
right( trim( RPMCU ); 3 ) AS COLUMN0006;
MCDL01;
sum ( CASE
WHEN RPDCT in ( 'RI'; 'RM'; 'U8' ) THEN RPAAP / 100
ELSE 0
END ) AS RPAAP;
sum ( CASE
WHEN RPDCT = 'IF' THEN RPAAP / 100
ELSE 0
END ) AS COLUMN0003;
sum( Case when RPDCT = 'YI' AND RPPYR = '33712631' Then RPAAP / 100 else 0 end ) AS COLUMN0000;
sum( Case when RPDCT = 'YI' AND RPPYR = '33712629' Then RPAAP / 100 else 0 end ) AS COLUMN0000_1;
sum( Case when RPDCT = 'YI' AND RPPYR = '33712628' Then RPAAP / 100 else 0 end ) AS COLUMN0001;
sum( Case when RPDCT = 'YI' AND RPPYR = '33712634' Then RPAAP / 100 else 0 end ) AS COLUMN0001_1;
sum( Case when RPDCT = 'RU' AND RPPYR = RPAN8 Then RPAAP / 100 else 0 end ) AS COLUMN0006_1;
sum( Case when RPDCT = 'AU' Then RPAAP / 100 else 0 end ) AS COLUMN0004;
sum( Case when RPDCT = 'CU' Then RPAAP / 100 else 0 end ) AS COLUMN0005
FROM
IASP01.PDDTA.F03B11 F03B11;
IASP01.PDDTA.F0101 F0101;
IASP01.PDDTA.F0006 F0006;
IASP01.PDDTA.F0010 F0010
WHERE
F03B11.RPAN8 = F0101.ABAN8
AND F03B11.RPMCU = F0006.MCMCU
AND F03B11.RPCO = F0010.CCCO
AND ( ( ( ( RPKCO between '00121' and '00151'
OR RPKCO between '00900' and '00949'))
AND Year( date( CCDFYJ; CYYDDD ) ) = &COLUMN0007
AND CCARPN = &CCARPN1))
GROUP BY
ABAN8;
ABALPH;
right( trim( ABALKY ); 8 );
RPMCU;
right( trim( RPMCU ); 3 );
MCDL01
HAVING
sum ( CASE
WHEN RPDCT in ( 'RI'; 'RM'; 'U8' ) THEN RPAAP / 100
ELSE 0
END ) <> 0
OR sum ( CASE
WHEN RPDCT = 'IF' THEN RPAAP / 100
ELSE 0
END ) <> 0
OR sum( Case when RPDCT = 'YI' AND RPPYR = '33712631' Then RPAAP / 100 else 0 end ) <> 0
OR sum( Case when RPDCT = 'YI' AND RPPYR = '33712629' Then RPAAP / 100 else 0 end ) <> 0
OR sum( Case when RPDCT = 'YI' AND RPPYR = '33712628' Then RPAAP / 100 else 0 end ) <> 0
OR sum( Case when RPDCT = 'YI' AND RPPYR = '33712634' Then RPAAP / 100 else 0 end ) <> 0
OR sum( Case when RPDCT = 'AU' Then RPAAP / 100 else 0 end ) <> 0
OR sum( Case when RPDCT = 'CU' Then RPAAP / 100 else 0 end ) <> 0
OR sum( Case when RPDCT = 'RU' AND RPPYR = RPAN8 Then RPAAP / 100 else 0 end ) <> 0
instead to use a direct database request, i would use the normal procedure dragging and drop dimensions and measures.
I'm able to drag and drop fields in SELECT list. About the WHERE CLAUSE i can add a filter to the request.
About the GROUP BY clause i know that there's a field in Advanced tab where you can specify GROUP BY clause.
But about HAVING clause i don't know where specify it.
Thanks
Regards
0 -
Your approach should not be to reproduce a query ... ever.
What business question(s) is(are) being asked? - model to answer them. OBIEE is not a query tool - its an analytics tool based on logical business models.
0 -
Have you tried just adding the filters on the measure in the criteria filter section? That works for me.
0 -
Create an opaque view in RPD and use it in reports.
0 -
Wrong wrong wrong approach.
OBIEE works with models and not with queries.
0 -
100%
0 -
Opaque view to test it out ... but not as a permanent solution ... OVs do not get predicate filtering - with large data volumes these prove very unwise.
0