on 07-11-2012 4:23 PM
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,
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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)??.
User | Count |
---|---|
84 | |
23 | |
11 | |
9 | |
8 | |
5 | |
5 | |
5 | |
5 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.