locked
CRM Report auto filtering and data sets. RRS feed

  • Question

  • Hi to all.

    I am having a problem when trying to have a second data set in a sql 2005 reporting services report.
    While executing the report , inside the CRM the auto filter works only for the first data set.
    Here is the code from the first data set :

    SELECT        CRMAF_FilteredLead.leadid, CRMAF_FilteredLead.address1_line1, CRMAF_FilteredLead.campaignidname, CRMAF_FilteredLead.companyname,
                             CRMAF_FilteredLead.cormos_cityidname, CRMAF_FilteredLead.cormos_countryidname, CRMAF_FilteredLead.fullname, CRMAF_FilteredLead.cormos_jobtitleidname,
                             CRMAF_FilteredLead.cormos_prefectureidname, CRMAF_FilteredLead.cormos_professionalactivityname, CRMAF_FilteredLead.cormos_provinceidname,
                             CRMAF_FilteredLead.cormos_sourceidname, CRMAF_FilteredLead.cormos_zipcodeidname, CRMAF_FilteredLead.description, CRMAF_FilteredLead.emailaddress1,
                             CRMAF_FilteredLead.fax, CRMAF_FilteredLead.leadqualitycodename, CRMAF_FilteredLead.leadsourcecodename, CRMAF_FilteredLead.owneridname,
                             CRMAF_FilteredLead.statecode, CRMAF_FilteredLead.statecodename, CRMAF_FilteredLead.statuscode, CRMAF_FilteredLead.statuscodename,
                             CRMAF_FilteredLead.subject, CRMAF_FilteredLead.telephone1, FilteredActivityPointer.activityid, FilteredActivityPointer.activitytypecode,
                             FilteredActivityPointer.activitytypecodename, FilteredActivityPointer.actualdurationminutes, FilteredActivityPointer.actualstart, FilteredActivityPointer.actualend,
                             FilteredActivityPointer.owneridname AS owner, FilteredActivityPointer.statecode AS activitystate, FilteredActivityPointer.statecodename AS activitystatename,
                             FilteredActivityPointer.statuscode AS activitystatus, FilteredActivityPointer.statuscodename AS activitystatusname, FilteredActivityPointer.subject AS activitysubject,
                             FilteredActivityPointer.scheduledend, FilteredActivityPointer.scheduledstart
    FROM            FilteredLead AS CRMAF_FilteredLead LEFT OUTER JOIN
                             FilteredActivityPointer ON CRMAF_FilteredLead.leadid = FilteredActivityPointer.regardingobjectid
    ORDER BY FilteredActivityPointer.activitytypecode

    and this is the code from the second data set of the report:

    SELECT        FilteredCampaign.budgetedcost, FilteredCampaign.codename, FilteredCampaign.campaignid, FilteredCampaign.expectedrevenue, FilteredCampaign.name,
                             FilteredCampaign.owneridname, FilteredCampaign.proposedstart, FilteredCampaign.proposedend, FilteredCampaign.statuscode, FilteredCampaign.statuscodename,
                             FilteredCampaign.typecode, FilteredCampaign.typecodename, CRMAF_FilteredListMember.entityid
    FROM            FilteredListMember AS CRMAF_FilteredListMember LEFT OUTER JOIN
                             FilteredList ON CRMAF_FilteredListMember.listid = FilteredList.listid INNER JOIN
                             FilteredCampaignItem ON FilteredList.listid = FilteredCampaignItem.entityid INNER JOIN
                             FilteredCampaign ON FilteredCampaignItem.campaignid = FilteredCampaign.campaignid

    When executing the report, in the filter tab there is only one auto filter for the CRMAF_FilteredLead
    but there isn't any filter for the CRMAF_FilteredListMember as i expected.

    Can anyone help what i am doing wrong?

    Thank you for your time.
    Tuesday, February 9, 2010 2:23 PM

Answers

  • I am actually working on a blog article this week that will provide an example for creating this type of report.  Keep an eye out for it and hopefully it will answer your questions.  In the meantime, review the information below and see if it helps.

    Since you have several datasets, you'll need to first identify what parameter needs to be passed to each subreport.  You may need to create a few subreports with each linking to its parent report.

    Using your example above lets' start with the Lead entity and let's assume you need to pass the Leadid to the Activity Sub-report.  Here are the steps to get started:

    Create a SubReport named Lead Recent Activity Report or whatever you want to call it.
       Create a parameter for the Activity Report and name it LeadID
           Parameter values = String, Hidden, Non Querried for Available Values and Null for Default
       Create a simple query for the Activity Report
           ensure your 'Where Clause' references the LeadID parameter (where @LeadID = FilteredActivityPointer.regardingobjectid) and other criteria to limit the return like Year(createdon) = Year(GetDate())
       For now, just add a simple table control to the report that displays the activity Subject and create date or something like that for testing.

    Create a Main Report Named Lead Report or whatever you want to call it.  
    Create your query for FilteredLead only
         It should be a simple query like 'Select (list of fields from filteredlead that you want to display in a section of the report including the LeadID so we can use it to pass to the subreport) from filteredlead as CRM_AF filteredlead

    Add a subreport control to the Report
    Right-click the subreport control on your Report Layout and select Properties
    On the General tab, select the Lead Recent Activity SubReport
    Select the parameter tab
    Select the Parameter Name and select the LeadID for the parameter value
         The parameter value should look something like this '=First(Fields!leadid.Value, "The dataset name from your Lead Report")'
    Save all your changes and add a leadid to your query to test the report. (where leadid = 'Lead Guid from your database')
    Preview the report and see if it works

    Hopefully I haven't forgotten any of the steps.  I suggest you get the above working first and then expand on it and include other subreports like Campaign activities. 

    I plan to provide screenshots in my article which should help.  The above steps are based on VS 2005 but it should be similar in VS 2008.  Let me know how it goes.
    Best Regards | Twitter: edwardsdna
    • Marked as answer by Jim Glass Jr Thursday, March 18, 2010 8:29 PM
    Monday, March 8, 2010 4:52 PM

All replies

  • Create a subreport for the second dataset, add a subreport control to the parent report.  Pass the required parameter to the subreport from the parent report.  You will not need to use the CRM_AF for the second dataset, only a parameter to received the unique record id.
    Best Regards | Twitter: edwardsdna
    Thursday, February 11, 2010 2:31 PM
  • Thank you! I will try it.
    Friday, February 12, 2010 12:44 PM
  • Hi Donna. I tried it, but, i'm doing something wrong and, did not work.
    I am wondering if somewhere there is a sample of passing parameter to a sub report so i can see what i am doing wrong ang fix it.
    Can you show me a sample or a place to look?

    Thank you for your time.
    Thursday, March 4, 2010 12:47 PM
  • I am actually working on a blog article this week that will provide an example for creating this type of report.  Keep an eye out for it and hopefully it will answer your questions.  In the meantime, review the information below and see if it helps.

    Since you have several datasets, you'll need to first identify what parameter needs to be passed to each subreport.  You may need to create a few subreports with each linking to its parent report.

    Using your example above lets' start with the Lead entity and let's assume you need to pass the Leadid to the Activity Sub-report.  Here are the steps to get started:

    Create a SubReport named Lead Recent Activity Report or whatever you want to call it.
       Create a parameter for the Activity Report and name it LeadID
           Parameter values = String, Hidden, Non Querried for Available Values and Null for Default
       Create a simple query for the Activity Report
           ensure your 'Where Clause' references the LeadID parameter (where @LeadID = FilteredActivityPointer.regardingobjectid) and other criteria to limit the return like Year(createdon) = Year(GetDate())
       For now, just add a simple table control to the report that displays the activity Subject and create date or something like that for testing.

    Create a Main Report Named Lead Report or whatever you want to call it.  
    Create your query for FilteredLead only
         It should be a simple query like 'Select (list of fields from filteredlead that you want to display in a section of the report including the LeadID so we can use it to pass to the subreport) from filteredlead as CRM_AF filteredlead

    Add a subreport control to the Report
    Right-click the subreport control on your Report Layout and select Properties
    On the General tab, select the Lead Recent Activity SubReport
    Select the parameter tab
    Select the Parameter Name and select the LeadID for the parameter value
         The parameter value should look something like this '=First(Fields!leadid.Value, "The dataset name from your Lead Report")'
    Save all your changes and add a leadid to your query to test the report. (where leadid = 'Lead Guid from your database')
    Preview the report and see if it works

    Hopefully I haven't forgotten any of the steps.  I suggest you get the above working first and then expand on it and include other subreports like Campaign activities. 

    I plan to provide screenshots in my article which should help.  The above steps are based on VS 2005 but it should be similar in VS 2008.  Let me know how it goes.
    Best Regards | Twitter: edwardsdna
    • Marked as answer by Jim Glass Jr Thursday, March 18, 2010 8:29 PM
    Monday, March 8, 2010 4:52 PM
  • Hi Donna. Thank you for your reply. I will try it, and will inform you about.
    Tuesday, March 16, 2010 8:22 AM