Skip to Content
0
Former Member
Jul 11, 2012 at 03:23 PM

datatype issue in webi reporting

19 Views

We created multisource universe with BW cube and excel. BW cube has obj1, kf1 and Excel has obj2, kf2. obj1 have the data in cube with 18 digits (ex: 00………01234). So to eliminate leading zeros, we converted it to integer by creating new calculated column (Nobj1), we also converted obj2 to integer by creating new calculated column (Nobj2). Then we created join on Nobj1 and Nobj2. But I am facing below issues.

1. If you click on show values for Nobj1 in business layer it is displaying error message. “Cannot convert Varchar to Integer data type”. But Nobj1 is displaying values in webi reporting. And there is no issues with Nobj2 in Business layer and in webi

2. If you create report with obj1, kf1 OR obj2, kf2 then no issues. But displaying above error message with Nobj1, kf1 AND obj1, kf2 AND obj2, kf1

SQL with Nobj1 , kf1

SELECT

View1."Nobj1",

SUM(View1."kf1")

FROM

(SELECT (convert("obj1", Integer)) AS "Nobj1", "kf1"

FROM "CONN1"."PUBLIC"."CUBE") View1

GROUP BY

1

SQL with obj2 , kf1

SELECT

View2."obj2",

Sum(View1."kf1")

FROM

(SELECT (convert("obj1", Integer)) AS "Nobj1", "kf1"

FROM "CONN"."PUBLIC"."CUBE") View1

INNER JOIN (SELECT (convert("obj2", Integer)) AS "Nobj2", "obj1"

FROM "CONN2"."PUBLIC"."Excel") View2

ON (View1."Nobj1"=View2."Nobj2")

GROUP BY

1

If above conversion to integer is not correct solution then please suggest me how to eliminate leading zeros in obj1 to join with obj2

Thanks,