Skip to Content
0
Former Member
Feb 18, 2009 at 03:55 PM

Filter works sporadically in WebI report

28 Views

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.