cancel
Showing results for 
Search instead for 
Did you mean: 

datatype issue in webi reporting

Former Member
0 Kudos

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,

Accepted Solutions (0)

Answers (1)

Answers (1)

former_member188911
Active Contributor
0 Kudos

If you turn on the conversion exit in the attributes ot obj1 it should be converted before getting to WebI so it would be treated as numeric and thus you shouldn't experience the problem

Former Member
0 Kudos

Hi Simone, Thank you for your answer. Could you please more specific on "turn on conversion exit in the attributes of obj1(0Material)". Where I need to do this? (FYI, obj1(0Material)". has key values of Infoobject)

And how that converted object(Nobj1) is displaying values in data foundation? but error in business layer(select Nobj1 - Show values)??.

former_member188911
Active Contributor
0 Kudos

you do this in the SAP GUI in the object properties