Aggregation Issues

building semantic layer
1

Hi Guys,
I am working for a distrubution company. I am working with a universe which was done by someone else.

I have 3objects

  1. Item Num
    2.Item Description
    3.Measesure object

When I have only Item Description and Measure Object in the report the whole Measure column shows a same value for each row (difintly wrong value) and when I bring in the Item Num, it gives me the exact results.

I am aware hierarchies not setted up at the universe level, so can anyone guide me with a solution Please.

Learning designer

Thanks

satishvisu :uk: (BOB member since 2008-06-19)

2

Look at the generated SQL code and compare it!

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

3

Hi,

If you have an access to the universe, then check where the 3 objects are coming from. Is there any relationship between the table where the measure comes from and the table where the Item Description comes from?

Marek Chladny :slovakia: (BOB member since 2003-11-27)

4

Hi Andreas,
Thank for the reply…
Yes I can see these two (Item Num ,Item Description )are coming from two different tables.

satishvisu :uk: (BOB member since 2008-06-19)

5

Well, so draw your own conculsions or post the SQL code, please…

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

6

Hi Andreas,
Query1:
Here is the SQL

SELECT
  ULPE_YIELD_REST.LOC,
  ULPE_YIELD_REST.ITEM,(Item Num)
  ITEM.DESCR,(Item Description )
  ULPE_YIELD_REST.INV_DAT,
  ULPE_YIELD_REST.INV_QTY,
  ULPE_YIELD_REST.PREV_INV_QTY,
  ULPE_YIELD_REST.USE_QTY,
  ULPE_YIELD_REST.USE_STD_QTY,
  ULPE_YIELD_REST.STOCK_QTY,
  ULPE_YIELD_REST.STOCK_STD_QTY,
  ULPE_YIELD_REST.THEOR_YIELD,
  ULPE_YIELD_REST.ACTUAL_YIELD,
  ULPE_YIELD_REST.UPDATED_YIELD,
  ULPE_YIELD_REST.MANUAL_YIELD,
  ULPE_YCF_SETTING.DUR_CAL,
  ULPE_YCF_SETTING.COEF_REF,
  ULPE_YCF_SETTING.TOLERANCE,
  ULPE_YCF_SETTING.ASSIGN_AUTO_SW,
  LOC_SKU.DESCR,
  LOC_SKU.U_USER,
  ITEM.U_PRODUCTTYPE,
  ULPE_YIELD_REST.STATUS
FROM
  ULPE_YIELD_REST,
  ITEM,
  ULPE_YCF_SETTING,
  LOC  LOC_SKU,
  SKU
WHERE
  ( ULPE_YIELD_REST.LOC=SKU.LOC  )
  AND  ( ULPE_YIELD_REST.ITEM=SKU.ITEM  )
  AND  ( ULPE_YCF_SETTING.LOC=SKU.LOC  )
  AND  ( ULPE_YCF_SETTING.ITEM=SKU.ITEM  )
  AND  ( LOC_SKU.LOC=SKU.LOC  )
  AND  ( ITEM.ITEM=SKU.ITEM  )
  AND  
  (
   ULPE_YIELD_REST.LOC  =  @prompt('Loc (code McDonald''s) :','A',,Mono,Free,Persistent,,User:0)
   AND
   ULPE_YIELD_REST.INV_DAT  BETWEEN  @prompt('Date de Début:','D',,Mono,Free,Persistent,,User:1)  AND  @prompt('Date de Fin:','D',,Mono,Free,Persistent,,User:2)
   AND
   ITEM.U_PRODUCTTYPE  IN  @prompt('Type Produit :','A',,Multi,Free,Not_Persistent,{'A','B','C','D','F','K','U'},User:3)
   AND
   ITEM.U_PRODUCTTYPE  NOT IN  ( 'P'  )
   AND
   ITEM.U_ABC  IN  @prompt('Abc :','A',,Multi,Free,Not_Persistent,{'A','B','C','P'},User:4)
   AND
   ITEM.U_PROMOSW  IN  @prompt('Promosw :','N',,Multi,Free,Not_Persistent,{'1','0'},User:5)
  )

Query2:

SELECT
  LRSREF.LRS_LAST_INFO_STOCK.LOC,
  LRSREF.LRS_LAST_INFO_STOCK.ITEM,
  LRSREF.LRS_LAST_INFO_STOCK.LAST_USE_DAT,
  LRSREF.LRS_LAST_INFO_STOCK.LAST_YIELD_CRRM,(Measure Object)
  ITEM.U_PROMOSW
FROM
  LRSREF.LRS_LAST_INFO_STOCK,
  ITEM,
  SKU
WHERE
  ( SKU.LOC=LRSREF.LRS_LAST_INFO_STOCK.LOC  )
  AND  ( SKU.ITEM=LRSREF.LRS_LAST_INFO_STOCK.ITEM  )
  AND  ( ITEM.ITEM=SKU.ITEM  )
  AND  
  (
   LRSREF.LRS_LAST_INFO_STOCK.LOC  =  'R0164'
   AND
   ITEM.U_PRODUCTTYPE  IN  ('A','B','C','D','F','K','U')
   AND
   ITEM.U_PRODUCTTYPE  NOT IN  ( 'P','H'  )
   AND
   ITEM.U_ABC  IN  ('A','B','C','P')
   AND
   ITEM.U_PROMOSW  IN  (1, 0)
  )

Both the queries are merged on dimensions: UYR ITEM,LIS ITEM and UYR LOC ,LIS LOC

Thanks

satishvisu :uk: (BOB member since 2008-06-19)

7

On a related topic:
If you have a measure you should have a SQL aggregate function such as SUM, COUNT, etc. around it in the SELECT statement! This is in addition to projection, which kicks in at report level after initial retrieval from the database.

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

8

Thank you Andreas

satishvisu :uk: (BOB member since 2008-06-19)