Crosstab confusion

[PlayCognos]

I am trying to build a simple crosstab report.

The two crosstab node members are 'Year' and 'Region'.
The crosstab intersection is 'Sales'.

The 'Aggregate Function' property is set to:
Year -    None
Region - None
Sales -   Total

and the 'Rollup Aggregate Function' property is set to:
Year -    None
Region - None
Sales -   Automatic

The SQL behind the report is:

Code Select Expand


select "T0"."C0" "Year",
       "T0"."C1" "Region",
       sum("T0"."C2") over() "Sales"
  from (select "AS"."Year" "C0",
               "AS"."Region" "C1",
               sum("EN"."Sales") "C2"
          from ("DS1"."ST" "ST" INNER JOIN
                "DS2"."AS" "AS" on
                "ST"."ID" = "AS"."ID" and
                "ST"."Year" = "AS"."Year")
          LEFT OUTER JOIN "DS1"."EN" "EN" on "ST"."ID" = "EN"."ID"
          and "ST"."Year" = "EN"."Year"
         group by "AS"."Year", "AS"."Region") "T0"


This doesnot give me the right results though. If I modify the SQL to something like this...

Code Select Expand


select "T0"."C0" "Year",
       "T0"."C1" "Region",
       "T0"."C2" "Sales"
  from (select "AS"."Year" "C0",
               "AS"."Region" "C1",
               sum("EN"."Sales") "C2"
          from ("DS1"."ST" "ST" INNER JOIN
                "DS2"."AS" "AS" on
                "ST"."ID" = "AS"."ID" and
                "ST"."Year" = "AS"."Year")
          LEFT OUTER JOIN "DS1"."EN" "EN" on "ST"."ID" = "EN"."ID"
          and "ST"."Year" = "EN"."Year"
         group by "AS"."Year", "AS"."Region") "T0"


i.e., by removing the sum() over() in the 3rd line, then I get the right answer. I have tried playing with the 'Aggregate Function'  and 'Rollup Aggregate Function' property of 'Sales' (Crosstab intersection) but have had no luck.

Any idea how to achieve this?

Thank you for your help in advance.
PC.

[blom0344]

It should come easily if you simply use all defaults settings. However, in your SQL there appears to be an additional level (outer select with the over clause) that is simply redundant. Is this a relational or a DMR package and what version?