locked
CRMAF_ filters on SSRS RRS feed

  • Question

  • I have this  query where I've added   CRMAF_ to use CRM's filter but the left join bellow will display nulls if I set

    FilteredNew_Application .createdon > today

    if there is a FilteredNew_Application  that will join the rest, it shows a date after today as expected

    otherwise it shows a blank for a null as not every record has a FilteredNew_Application

    Is there a way to by pass that? like if I set FilteredNew_Application.date > today and FilteredNew_Application  contains data to display only dates and no records with blanks?

     select  CRMAF_FilteredNew_Application.createdon,

    (many other fields)

    from FilteredContact CRMAF_FilteredContact left outer join FilteredNew_Application

    CRMAF_FilteredNew_Application
                          on CRMAF_FilteredContact.contactid = CRMAF_FilteredNew_Application.new_contactid
                          left join new_new_typeofwork_contactBase tc
                          on tc.contactid = CRMAF_FilteredContact.contactid
                          left join FilteredNew_typeofwork  CRMAF_FilteredNew_typeofwork
                          on CRMAF_FilteredNew_typeofwork.new_typeofworkid = tc.new_typeofworkid
                         
                           left join FilteredNew_division CRMAF_FilteredNew_division
                         on CRMAF_FilteredNew_division.new_divisionid = CRMAF_FilteredNew_typeofwork.new_divisionid


    • Edited by blucas2006 Thursday, October 3, 2013 9:06 AM
    Thursday, October 3, 2013 9:05 AM

All replies

  • Hi,

    Please try below query.

    select  CRMAF_FilteredNew_Application.createdon,

    (many other fields)

    from FilteredContact CRMAF_FilteredContact left outer join FilteredNew_Application

    CRMAF_FilteredNew_Application
                           on (CRMAF_FilteredContact.contactid = CRMAF_FilteredNew_Application.new_contactid  and CRMAF_FilteredNew_Application.createdon > today)
                           left join new_new_typeofwork_contactBase tc
                           on tc.contactid = CRMAF_FilteredContact.contactid
                           left join FilteredNew_typeofwork  CRMAF_FilteredNew_typeofwork
                           on CRMAF_FilteredNew_typeofwork.new_typeofworkid = tc.new_typeofworkid
                          
                            left join FilteredNew_division CRMAF_FilteredNew_division
                          on CRMAF_FilteredNew_division.new_divisionid = CRMAF_FilteredNew_typeofwork.new_divisionid

    HTH!

    Thanks!

    Thursday, October 3, 2013 9:18 AM
  • that would work but I should had specified that I can't hardcode  > today. I need to add a parameter there trying to use the CRMAF_ approach.

    but thanks for the answerr

    Thursday, October 3, 2013 8:47 PM