BusinessObjects Board

Report not using SUM in the SQL generated

building semantic layer
#1

Hi all,

I have BI4 Version: 14.1.6.1702. I have measures in a universe that have their aggregation set to SUM (from Properties of the object / Function = SUM). Why is it when I create a report and pull in the measure it doesn’t show as sum?

SELECT
  fact_stock.physical_qty,
  fact_stock.physical_value
FROM
  fact_stock

I would expect to see;

SELECT
  sum(fact_stock.physical_qty) physical_qty,
  sum(fact_stock.physical_value) physical_value
FROM
  fact_stock

Likewise when I pull in a dimension why doesn’t it sum and then group by?

SELECT
  dim_supplier.supplier_number,
  fact_stock.physical_qty,
  fact_stock.physical_value
FROM
  fact_stock INNER JOIN dim_supplier ON (fact_stock.dim_supplier_key=dim_supplier.dim_supplier_key)

I would expect to see;

SELECT
  dim_supplier.supplier_number,
  sum(fact_stock.physical_qty) physical_qty,
  sum(fact_stock.physical_value) physical_value
FROM
  fact_stock INNER JOIN dim_supplier ON (fact_stock.dim_supplier_key=dim_supplier.dim_supplier_key)
GROUP BY 
  dim_supplier.supplier_number

I can only assume there is a setting in the universe that I am missing??

Many thanks

[Moderator Edit: Added code formatting - Andreas]

PeppersBI (BOB member since 2015-11-25)

#2

You must add the SQL Aggregation not just to propoerties (which is projection btw), but to the actual SQL code visible in the SELECT box of the univesre object in Universe Designer, e.g.

SUM (TableName.YourMeasure)

Andreas :de: (BOB member since 2002-06-20)

#3

Andreas,

Thank you for your response.

I will do this, however, how do they differ? When would you use one and not the other?

Many thanks

PeppersBI (BOB member since 2015-11-25)

#4

You ought to use both as they are truly a tandem, see here for details please: SQL Aggregates

Andreas :de: (BOB member since 2002-06-20)

#5

Also, you won’t see the columns referenced in the SQL.


SELECT 
   sum(fact_stock.physical_qty), 
   sum(fact_stock.physical_value)
 FROM 
   fact_stock

Mak 1 :uk: (BOB member since 2005-01-06)