1 Reply Latest reply: Apr 16, 2012 2:53 AM by MuRam RSS

    SUM over Max of rows

    728159
      Hi all,
      I am facing this problem, where in i have to find the Max of a set of rows based on paritition. Then sum all those max values and show it in the OBIEE. Is there any way we can do that in Criteria expressions of OBIEE Presentation Layer? Basically i am trying to find the header total at line level. Therefore for a given set of records the total amount will always be the same.


      For e.g.
      Header id Amount
      1234 -> 100
      1234 -> 100 MAX value - 100 Total = 100 + 200 = 300
      1234 -> 100

      1235 -> 200
      1235 -> 200 MAX value - 200
      1235 -> 200



      This is how i want to show. Is it possible?


      Thanks,
      Upendra
        • 1. Re: SUM over Max of rows
          MuRam
          Hi,

          Yes tried this once.
          First Solution:
          Use three columns
          1. Dimension
          2. Measure column
          3. Rcount(Measure_column)

          Third column should have the column formula as-
          case when Rcount(Measure_column)=2 then 'Max'||MAX(Measure_column) end

          Second Solution:
          Use the column formula in the second measure column itself as
          cast(Measure_column as char)||case when Rcount(Measure_column)=2 then 'Max'||MAX(Measure_column) end

          NOTE- 2 above refers the position where the message has to be displayed.

          Award if this is helpful.

          Regards
          MuRam