3 Replies Latest reply on Dec 6, 2012 9:51 AM by ora_nut

    Crosstab SUM behaviour query


      I have a crosstab report in Discoverer Plus which does not display the expected results.

      There are organisation areas down the side with their associated staff below (outline style). The data point is a calculation which shows their average monthly hours, so it's a numeric field with a multiply and division performed on it.

      When the report is expanded everything is fine. When it is collapsed the summed amounts for the orangisation levels with all the staff below them are incorrect. Instead of showing the summed amount of all the staff's average monthly hours, it shows a "sum distinct" amount.
      E.g. Expanded:
                     Average Hours
      Procurement      680
       A Smith         160
       B Jones         200
       C Walker        160
       D Brown         160
                           Average Hours
      Procurement          360
      In Discoverer Desktop the collapsed amount is correct.

      I have tried setting different aggregation behaviour for the worksheet in Disco Plus which did not make a difference. When I edit the calculation and try to re-add the numeric field, it does not give me the "detail" option when I expand it; there's only AVG, COUNT, MAX, MIN, SUM.

      Do you have any ideas what might be causing my problem?