4 Replies Latest reply on Feb 24, 2015 9:34 AM by DTV

    Match any record from all the records in OBIEE 11g

    prassu

      Hello,

      I have a requirement that with 4 conditions and should have the conditional formatting.

      Open_Flag, empno, month,count_flag are columns I am using.

      When Open_Flag='N' for any record in the month it will be red at any case.

      Else count(case when count_flag='Y' then empno)/count(empno) and apply conditional format on the result number like <10 then green,<20 yellow and >20 then red.

       

      Is there anyway I can achieve the requirement from report side? How can I check the Open_flag ='Y' condition for all the records ,if not, I have to go the else conditions?

       

       

      Thanks,

      Prasanna

        • 1. Re: Match any record from all the records in OBIEE 11g
          AjeetR

          Hi Prasanna,

          Can u give some sample data for better understanding?

          • 2. Re: Match any record from all the records in OBIEE 11g
            prassu

            Open_Flag, empno, month,count_flag

            -----------------------------------------------------

            N    123  Jan14 N

            Y    234   Jan14 Y

            Y    2343   Jan14 Y

            Y    2343   Jan14 Y

            Y    123  Feb14 N

            Y    234   Feb14 Y

            Y    2343   Feb14 N

            Y    2343   Feb14 Y

             

            here is the sample data. In jan14 Open_flag for one employee='N' then the conditional format will be red for whole month but Feb14 we have to do calculations.

            • 3. Re: Match any record from all the records in OBIEE 11g
              User13309386-Oracle

              Hi ,

              You can use a condition like this

               

              case when count(Filter("empno" Using "open_flag='N') by Month) > 0 then red else count(empno using open_flag='Y') end

               

               

              Thanks,

              R

              • 4. Re: Match any record from all the records in OBIEE 11g
                DTV

                Hi Prasanna,

                I dont quite understand what you will achieve with such a condition. Nevertheless..

                 

                Considering you want the calculation month wise, looking at the example, your Case condition - count(case when count_flag='Y' then empno)/count(empno) will never be greater than 1? (the denominator being total employee count which will always be greater)

                 

                In case you are trying to apply multiple conditions together - create a new column, say Check, with the required CASE statement where you achieve the desired count correctly. Then on each column you could apply conditional formatting saying if check =0 (for those which has Open Flag='N') then Red, when between 1-10 then Green and so on.

                 

                Is this what you are looking for? hope this helps!

                 

                Regards,

                Divya