Distinct count to exclude empty string values

[sallymasipa]

Hi All

Urgently please assist i have included this filtering in my select expert {A_Super_Regions.A_Super_Regions} <> "".

however when doing distict count in my bar chart it still read/count this empty string and this causes discrepancies in my reports.

Please advise how i can exclude this.

thanks in advance.

 

[IanWaterman]

It is likely that {A_Super_Regions.A_Super_Regions} is null which is not the same as ""

Try

not isnull({A_Super_Regions.A_Super_Regions})

or to cover all options

not isnull({A_Super_Regions.A_Super_Regions})
and
{A_Super_Regions.A_Super_Regions} <> ""

Ian

 

[sallymasipa]

Thanks Ian. It works perfect.

 

[sallymasipa]

HI

Please check for me where i might be getting it wrong. Basically i want to see all the account managers who were and not productive thoughout the year. However my report only includes those who are productive. PLEASE REVIEW AND ADVISE. I pulled the script from Crystal report sql code and i also show what's in my select expert.

I got a cross tab by account manager per month.for instance the month of Feb there was no business, so it shows all the month except Feb. I want it to show Feb but zero value.

And by showing Feb means it will display even those Account managers with 0 sales for the year as currently it only shows those with sales.

-----------------------
SELECT "Opp_View"."Close_Date",
"Opp_View"."Status",
"O_Request_New___Review"."O_Request_New___Review",
"A_Super_Regions"."A_Super_Regions",
"A_Total_no_of_Employees"."A_Total_no_of_Employees",
"A_Scheme_Code"."A_Scheme_Code", "Company"."State_Province",
"ADMN_User_Details"."User_FirstName",
"ADMN_User_Details"."User_Name",
"A_Scheme_Footprint"."A_Scheme_Footprint"

FROM (((((("AbsaWorkplaceBanking"."dbo"."A_Scheme_Footprint" "A_Scheme_Footprint"
INNER JOIN "AbsaWorkplaceBanking"."dbo"."Company" "Company" ON "A_Scheme_Footprint"."Client_Id"="Company"."Client_Id")
INNER JOIN "AbsaWorkplaceBanking"."dbo"."Opp_View" "Opp_View" ON "Company"."Client_Id"="Opp_View"."Client_Id")
LEFT OUTER JOIN "AbsaWorkplaceBanking"."dbo"."A_Total_no_of_Employees" "A_Total_no_of_Employees" ON "Company"."Client_Id"="A_Total_no_of_Employees"."Client_Id")
LEFT OUTER JOIN "AbsaWorkplaceBanking"."dbo"."O_Request_New___Review" "O_Request_New___Review" ON "Opp_View"."Opp_Id"="O_Request_New___Review"."Client_Id")
LEFT OUTER JOIN "AbsaWorkplaceBanking"."dbo"."ADMN_User_Details" "ADMN_User_Details" ON "Opp_View"."Owner_Id"="ADMN_User_Details"."User_Id")
LEFT OUTER JOIN "AbsaWorkplaceBanking"."dbo"."A_Super_Regions" "A_Super_Regions" ON "Opp_View"."Client_Id"="A_Super_Regions"."Client_Id")
LEFT OUTER JOIN "AbsaWorkplaceBanking"."dbo"."A_Scheme_Code" "A_Scheme_Code" ON "Opp_View"."Client_Id"="A_Scheme_Code"."Client_Id"

WHERE "O_Request_New___Review"."O_Request_New___Review"='New'
AND "A_Scheme_Code"."A_Scheme_Code"<>''


---------------------------------------------------------------SELECT EXPERT--------------------

{O_Request_New___Review.O_Request_New___Review} = "New" and
{@Status} = "Won" and
{@Oppotunity closed year} >= "2011" and
{A_Scheme_Code.A_Scheme_Code} <> ""