The truth behind rollup processing?

[wilbolite]

I happened to notice something while attempting to fine-tune the performance of a report. 

I have a very simple table model that returns three values: route, order number and quantity.  Here's the SQL from the query upon which this is based.  The "Route" is set up as the only level in the query's Dimension

SELECT "T1"."Calculation8" "Route" ,
       "T1"."OHORNR" "OrderNumber" ,
       "T1"."OIQYOA" "QuantityOrdered"
FROM
    (SELECT "OHORDHO8"."OHORNR" "OHORNR" ,
        trim(both FROM "OHORDHO8"."OHRTEN") "Calculation8" ,
        "OIORDDO8"."OIITMN" "OIITMN" ,
        "OIORDDO8"."OIQYOA" "OIQYOA"
    FROM "HSDATA"."OHORDHO8" "OHORDHO8",
         "HSDATA"."OIORDDO8" "OIORDDO8",
    WHERE "OIORDDO8"."OICMPN"            = "OHORDHO8"."OHCMPN"
    AND "OIORDDO8"."OIORNR"              = "OHORDHO8"."OHORNR"
    AND "OIORDDO8"."OIITMN"              = 'testValue'
    )
    "T1"
ORDER BY "Route" asc OPTIMIZE FOR 1 ROWS

When I set the Aggregrate Function for the Quantity fact item to Sum, all hell breaks loose in the Native SQL.  The SQL generator creates two additional Select statements (nearly identical to that above), but at different grouping levels in order to get the summed quantity values at various levels in the hierarchy and then stitches the results together (see below).

For some reason, I intuitively thought that reportnet would just grab all of the detail into memory and figure out the totals on it's own without having to resort to extra queries.  That's what Microsoft Access would do.

I've attempted to set Rollup Processing for the query to "Local" but that has had no impact.  Any ideas on what I can do to retain my simple SQL and yet have aggregation?


SELECT "T2"."C0" "Route" ,
    "T2"."C1" "OrderNumber" ,
    "T2"."C2" "QuantityOrdered" ,
    "T0"."C0" "QuantityOrdered1" ,
    "T1"."C1" "QuantityOrdered2"
FROM
    (SELECT sum("T1"."OIQYOA") "C0"
    FROM
        (SELECT "OHORDHO8"."OHORNR" "OHORNR" ,
            trim(both FROM "OHORDHO8"."OHRTEN") "Calculation8" ,
            "OIORDDO8"."OIITMN" "OIITMN" ,
            "OIORDDO8"."OIQYOA" "OIQYOA"
        FROM "HSDATA"."OHORDHO8" "OHORDHO8",
            "HSDATA"."OIORDDO8" "OIORDDO8",
        WHERE "OIORDDO8"."OICMPN"            = "OHORDHO8"."OHCMPN"
        AND "OIORDDO8"."OIORNR"              = "OHORDHO8"."OHORNR"
        )
        "T1"
    WHERE "T1"."OIITMN" = 'testValue'
    )
    "T0",
    (SELECT "T1"."Calculation8" "C0" ,
        sum("T1"."OIQYOA") "C1"
    FROM
        (SELECT "OHORDHO8"."OHORNR" "OHORNR" ,
            trim(both
        FROM "OHORDHO8"."OHRTEN") "Calculation8" ,
            "OIORDDO8"."OIITMN" "OIITMN" ,
            "OIORDDO8"."OIQYOA" "OIQYOA"
        FROM "HSDATA"."OHORDHO8" "OHORDHO8",
            "HSDATA"."OIORDDO8" "OIORDDO8",
        WHERE "OIORDDO8"."OICMPN"            = "OHORDHO8"."OHCMPN"
        AND "OIORDDO8"."OIORNR"              = "OHORDHO8"."OHORNR"
        )
        "T1"
    WHERE "T1"."OIITMN" = 'testValue'
    GROUP BY "T1"."Calculation8"
    )
    "T1",
    (SELECT "T1"."Calculation8" "C0" ,
        "T1"."OHORNR" "C1" ,
        "T1"."OIQYOA" "C2"
    FROM
        (SELECT "OHORDHO8"."OHORNR" "OHORNR" ,
            trim(both
        FROM "OHORDHO8"."OHRTEN") "Calculation8" ,
            "OIORDDO8"."OIITMN" "OIITMN" ,
            "OIORDDO8"."OIQYOA" "OIQYOA"
        FROM "HSDATA"."OHORDHO8" "OHORDHO8",
            "HSDATA"."OIORDDO8" "OIORDDO8",
        WHERE "OIORDDO8"."OICMPN"            = "OHORDHO8"."OHCMPN"
        AND "OIORDDO8"."OIORNR"              = "OHORDHO8"."OHORNR"
        )
        "T1"
    WHERE "T1"."OIITMN" = 'testValue'
    )
    "T2"
WHERE "T2"."C0" = "T1"."C0"
ORDER BY "Route" asc OPTIMIZE FOR 1 ROWS

[JoeBass]

You can force the use of your own SQL in FM if you choose the Native SQL Type in the query subject's Options->SQL Settings tab.  You should be able to group and summarize in RN if you have enough details in your query.  I'd be interested to hear if there's a notable performance difference.