locked
Creating SSRS Report similiar to Account Summary report RRS feed

  • Question

  • This is probably a long shot question. But, I'm trying to create a report similar to the Account Summary report. However I want the main report to be on appointments, and the sub report will be the account the appointment is regarding. I have my main report for appointments and have input parameters to specify the dates I want the appointments to be between. I can run the report from the CRM and it runs however it only returns the first appointment in the specified date range and there are no additional pages.

    I'm looking to return all appointments in the specified date range. With one appointment with the sub account information per page in the report. Like the Account Summary does. Does anyone know which step I'm missing so it returns all appointments within the parameter range and not just 1?

    Thanks for any help. I can post additional information, query text, whatever if that would help solve the problem.

    Thanks,


    Wednesday, April 16, 2014 5:00 PM

Answers

  • I was able to solve this. After examining the Account Overview and Account Summary reports closer I realized they were putting the sub reports in a list on the main report page. I was missing that step. After putting the sub report into a list and selecting the "add page break after" option I am getting the results I wanted. 1 record per 1 page of the report.

    Thanks everyone for the help.

    Thursday, April 17, 2014 2:20 PM

All replies

  • can you please provide SQL query or fetchXML related to this?

    MayankP
    My Blog
    Follow Me on Twitter

    Wednesday, April 16, 2014 5:33 PM
  • Sure.

    Parent Report:
    SELECT        subject, regardingobjectidname, scheduledstart, location, activityid, regardingobjectid
    FROM            FilteredAppointment AS CRMAF_FilteredAppointment
    WHERE        (scheduledstart BETWEEN @startdate AND @enddate)

    Child Report:
    SELECT        FilteredAccount.name, FilteredAccount.new_clienttypename, FilteredAccount.new_accountstatusname, FilteredAppointment.regardingobjectidname,
                             FilteredAppointment.subject, FilteredAppointment.scheduledstart, FilteredAppointment.location
    FROM            FilteredAccount INNER JOIN
                             FilteredAppointment ON FilteredAccount.accountid = FilteredAppointment.regardingobjectid
    WHERE        (FilteredAccount.accountid = @CRM_ActivityId)

    The only thing in the designer view of the parent report is the sub report. The sub report contains two tables. One table with the related appointment information and the other with the account information.


    EDIT - As a test I put a table on the parent report with the values of the appointment information. It does return all appointments within the parameter. So maybe there is some logic I am missing that will make the sub report run for each record return in the parent report parameter.
    Wednesday, April 16, 2014 5:38 PM
  • you need to pass relevant filter information from parent to sub report as well

    I can see your child query was not built using CRMAF_FilteredAppointment alias so either try using this alias or this filter should be passed to build sub report query


    MayankP
    My Blog
    Follow Me on Twitter

    Wednesday, April 16, 2014 6:08 PM
  • I'm sorry, this is my first attempt at an SSRS report. Could you please be a little more specific? I tried several things and each time I try to upload the new sub report I get an error message. I've tried several different variations for the report query but this is the one I'm on now.

    SELECT        FilteredAccount.name, FilteredAccount.new_clienttypename, FilteredAccount.new_accountstatusname, CRMAF_FilteredAppointment.subject,
                             CRMAF_FilteredAppointment.scheduledstart, CRMAF_FilteredAppointment.regardingobjectidname, CRMAF_FilteredAppointment.location, FilteredAccount.accountid
    FROM            FilteredAccount INNER JOIN
                             FilteredAppointment AS CRMAF_FilteredAppointment ON FilteredAccount.accountid = CRMAF_FilteredAppointment.regardingobjectid
    WHERE       (FilteredAccount.accountid = @CRM_ActivityId) AND (CRMAF_FilteredAppointment.scheduledstart BETWEEN @CRM_startdate AND @CRM_enddate)

    On the parent report I opened up the subreport view and passed the @CRM_startdate and @CRM_enddate parameter values to the child report. I then put in what you see above, I still get the same results. Only 1 page when the report runs for the first appointment returned. The table on the parent report under the childreportview shows all appointments though.

    Wednesday, April 16, 2014 7:54 PM
  • KawasakiRider,

    Since you want the to show the account details of each appointment, embedding the sub-report in the parent report tablix would be a better approach. Here is a sample that you can refer. Query will have to be changed accordingly.

    If you don't prefer a drill-down and need the details to be displayed separately then try removing this condition from the where clause

     (FilteredAccount.accountid = @CRM_ActivityId)
    HTH


    If my response helps you in finding your answer then please click 'Mark as Answer' and 'Vote as Helpful'


    Wednesday, April 16, 2014 11:05 PM
  • Hello Mamatha Swamy, thank you for your response. I looked into both of your suggestions and I am still not able to get what I'm trying to achieve. Each appointment returned by the report should be on it's on individual page. Just like when I run the account summary report, each account returned is on it's own page with all the relevant details to that one account. When I try the solutions above it still groups all the information on one page.

    This appointment is a request from our sales staff. They travel almost all week, before going out they would like to be able to run a report of their appointments for that week with relevant account information. That way before their appointments they can have a reminder sheet of a quick summary of the account. It may even be closer to the Account Overview report in the CRM but either way both use the functionality I'm looking for of 1 record per page in the report.

    Thanks for your help.

    Thursday, April 17, 2014 1:51 PM
  • I was able to solve this. After examining the Account Overview and Account Summary reports closer I realized they were putting the sub reports in a list on the main report page. I was missing that step. After putting the sub report into a list and selecting the "add page break after" option I am getting the results I wanted. 1 record per 1 page of the report.

    Thanks everyone for the help.

    Thursday, April 17, 2014 2:20 PM