wrong values after designing Universe in IDT

[Former Member]

Hi All,

I have three tables in a Universe

Table1 (prod_dt , grade,daily_value, monthly_vaal);
Daily each grade will have only one row like
01/01/2013, A,1234,1234
01/01/2013, B,234,234
01/01/2013, C,1111,1111
01/01/2013, D,134,134

Table2(frm_dt,to_dt,plant,grade,plan_value );
      Table1 prod_dt falls between frm_dt and to_dt  - there will be only only one record for each plant,grade code for this.
      Table2 can have multiple plant with same type of grade.
01/01/2013,12/31/2013,P1,A,666
01/01/2013,12/31/2013,P2,A,755
01/01/2013,12/31/2013,P3,A,665
01/01/2013,12/31/2013,P4,B,669
01/01/2013,12/31/2013,P5,B,363
01/01/2013,12/31/2013,P6,B,989
01/01/2013,12/31/2013,P7,C,666
01/01/2013,12/31/2013,P8,D,777

table3(date_val, grade,plant,suppose_val);
This is a derived table which will get one record for each date,plant.grade

01/01/2013,12/31/2013,P1,A,111
01/01/2013,12/31/2013,P2,A,222
01/01/2013,12/31/2013,P3,A,333
01/01/2013,12/31/2013,P4,B,444
01/01/2013,12/31/2013,P5,B,555
01/01/2013,12/31/2013,P6,B,666
01/01/2013,12/31/2013,P7,C,777
01/01/2013,12/31/2013,P8,D,888

Now my Question is when I am joining this tables together
Table1-table2 on(date,grade)
Table1-table3 on (date,grade)

and trying to query the records like
Table1.grade,table1.daily_value,table2.plan_value ,table3.suppose_val

in WEBi i am getting proper results like

A,1234,325,365
B,1234,325,365
C,1234,325,365
D,1234,325,365

but in Dashboards and IDT query i not getting proper results like

A,1234,325,365
B,1234,
C,1234,325,365
D,1234,null,null
B,null,325,365
D,null,325,365

Is there any idea what I am doing it wrong here. Any solution.

[Former Member]

Hi,

Have you defined the object with SUM ? If yes, make sure your projection function is also sum not count.

[Former Member]

The projection function is for sure set to sum in this case, that is what makes web intelligence aggregate correctly locally using its micro-cube.

I would rather think the SQL in the select statement does not start with sum, and therefore no group-by is being injected in the SQL satement.

[Former Member]

Would you share an screenshot of the properties of one of the measures?

[amrsalem1983]

Have you defined the Correct Context for your joins?

[Former Member]

As Arijit mentioned you will need to have all the measure objects defined in the universe with some type of aggregation like Sum or Max other wise you will get multiple records.

However in webi the report aggregates by design in the reporting layer utilizing the default calculation context so you do not see the duplication. However in dashboard as you are binding data to spreadsheet directly it will not do any aggregation and will show duplication.

[Former Member]

I already have Sum on all the measure objects, but still its the same.

[Former Member]

You will need to define the measure with a sum in the beginning  in the statement itself for the query to add group by clause..

example :

sum(Table.measure)

[arijit_das]

Try using SUM in measure definition in IDT.