locked
Prefilter in Reports not working RRS feed

  • Question

  • It looks as though the CRMAF_ functionality works with simple SELECT queries
    but not with UNION queries. I had to create a table-value function
    (parameterized view) using UNION query and then join it with the main entity
    via CRMAF_ alias. This works.

    The same problem i have now...

    Here is the my querey i need to replace with table-value function

    select

     

    CRMAF_Filteredcontact

    .fullname as FullName ,

    a

    .parentaccountidName as ParentAccount,

    CRMAF_Filteredcontact

    .address1_city as [Address1:City],

    CRMAF_Filteredcontact

    .eu_countrycontactidname as Country,

    a

    .customertypecodename as AccountType,

    CRMAF_Filteredcontact

    .owneridname as [Owner],

    CRMAF_Filteredcontact

    .invoke_tiername as [TargetType],

    CRMAF_Filteredcontact

    .eu_contacttypename as ContactType,

    CRMAF_Filteredcontact

    .invoke_other_specialtyname as Speciality,

    CRMAF_Filteredcontact

    .eu_noofpotentialpum as [#of potential pum],

    CRMAF_Filteredcontact

    .eu_noofactualpum as [#of Actual Pum],

    CRMAF_Filteredcontact

    .eu_noofpot as [#of pot],

    CRMAF_Filteredcontact

    .eu_lastappointmentdate as LastAppointmentDate,

    CRMAF_FilteredSystemUser

    .eu_reporthubname as reportingHubname

    from

     

    Filteredcontact CRMAF_Filteredcontact

    inner

     

    join Filteredaccount a

     

    on a.accountid = CRMAF_Filteredcontact.parentcustomerid

    inner

     

    join FilteredSystemUser CRMAF_FilteredSystemUser on CRMAF_FilteredSystemUser.systemuserid = CRMAF_Filteredcontact.Ownerid

    left

     

    join FilteredAppointment AP on ap.Regardingobjectid= CRMAF_Filteredcontact.contactid

    and

     

    AP.scheduledend >= @StartDate and AP.scheduledend <= @EndDate

    where

     

    ap.activityid is null and CRMAF_FilteredSystemUser.eu_securityrole LIKE '%RAM%'

    --and CRMAF_Filteredcontact.eu_countrycontactidname =@country

    --ORDER BY CRMAF_FilteredContact.OwnerIDname

    union all

    select

     

    '' as [FullName] ,

    ''

     

    as ParentAccount,

    ''

     

    as [Address1:City],

    CRMAF_Filteredcontact

    .eu_countrycontactidname as Country,

    --'' as Country,

    ''

     

    as AccountType,

    CRMAF_Filteredcontact

    .owneridname as [Owner],

    ''

     

    as [TargetType],

    ''

     

    as ContactType,

    ''

     

    as Speciality,

    ''

     

    as [#of potential pum],

    ''

     

    as [#of Actual Pum],

    ''

     

    as [#of pot],

    ''

     

    as LastAppointmentDate,

    CRMAF_FilteredSystemUser

    .eu_reporthubname as reportingHubname

    from

     

    Filteredcontact CRMAF_Filteredcontact

    inner

     

    join Filteredaccount a

     

    on a.accountid = CRMAF_Filteredcontact.parentcustomerid

    inner

     

    join FilteredSystemUser CRMAF_FilteredSystemUser on CRMAF_FilteredSystemUser.systemuserid = CRMAF_Filteredcontact.Ownerid

    left

     

    join FilteredAppointment AP on ap.Regardingobjectid= CRMAF_Filteredcontact.contactid

    where

     

    ap.activityid is not null and CRMAF_FilteredSystemUser.eu_securityrole LIKE '%RAM%'

    and

     

    AP.scheduledend >= @StartDate and AP.scheduledend <= @EndDate

     

     

    • Edited by Sri_S Thursday, October 14, 2010 2:52 PM
    Thursday, October 14, 2010 1:44 PM

Answers