8 Replies Latest reply on Sep 21, 2017 1:08 PM by Thomas Dodds

    Add HAVING clause to reports

    User294199

      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

        • 1. Re: Add HAVING clause to reports
          asim cholas

          what are you trying to achieve with Having clause.

          • 2. Re: Add HAVING clause to reports
            User294199

            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

            • 3. Re: Add HAVING clause to reports
              Thomas Dodds

              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.

              • 4. Re: Add HAVING clause to reports
                Martin van Donselaar

                Have you tried just adding the filters on the measure in the criteria filter section? That works for me.

                • 5. Re: Add HAVING clause to reports
                  asim cholas

                  Create an opaque view in RPD and use it in reports.

                  • 6. Re: Add HAVING clause to reports
                    Christian Berg

                    Wrong wrong wrong approach.

                     

                    OBIEE works with models and not with queries.

                    • 8. Re: Add HAVING clause to reports
                      Thomas Dodds

                      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.