Hello,
I'm new at designing a universe and designing reports. I seem to have difficulty filtering a query.
For example, when I only select a person's name and filter by the person's name, the SQL query in Webi seems fine:
SELECT
People5.PeopleId,
cast(Concat(People5.LastName, ', ', People5.FirstName) as char)
FROM
People People5
WHERE
cast(Concat(People5.LastName, ', ', People5.FirstName) as char) In ( 'Brascoup, Curtis John','Cross, Lorraine','Curotte, Karonhiio' );
This query returns only the selected names, which is what I want.
When I add the person's address, the SQL query is as follows:
SELECT
People5.PeopleId,
cast(Concat(People5.LastName, ', ', People5.FirstName) as char),
cast(concat(Coalesce(Address3.CivicNumber, ''), ' ', Coalesce(Address3.StreetName, ''), ' ', Coalesce(Address3.SuiteApt, ''), '\r', Coalesce(Address3.City, ''), ',', Coalesce(DomainAgregate53.ShortDescription, ''), '\r', Coalesce(Address3.PostalCode)) as char)
FROM
DomainAgregate DomainAgregate53 RIGHT OUTER JOIN Address Address3 ON (Address3.ProvinceDVId=DomainAgregate53.DomainValueId AND DomainAgregate53.locale = 'en')
RIGHT OUTER JOIN People_Address People_Address2 ON (People_Address2.AddressId=Address3.AddressId)
RIGHT OUTER JOIN People People5 ON (People5.PeopleId=People_Address2.PeopleId AND cast(Concat(People5.LastName, ', ', People5.FirstName) as char) In ( 'Brascoup, Curtis John','Cross, Lorraine','Curotte, Karonhiio' ));
It returns everyone, which is not what I am looking for and the "where" clause is no longer like in the query above.
I am wondering if there are special settings I must set in my universe or special settings in my report.
Thanks for your help.