SSRS report with CRM search screen as parameter

Bruce Boling
Bruce Boling
Sixth Anniversary Name Dropper Facilitator 1 June 2023 Monthly Challenge
in Blackbaud CRM™ General Discussions
Is it possible to use a CRM search screen as an SSRS parameter to retrieve a value?

Comments

  • Crystal Bruce
    Crystal Bruce admin
    Tenth Anniversary Kudos 5 Commented in Discussion First Reply
    This post was in Community help, I moved it over to CRM. Thanks!
  • Steven Finnell
    Steven Finnell
    Sixth Anniversary Kudos 1 Name Dropper Facilitator 1
    Hey Bruce,


    You can add a FormMetaData block to the ReportSpec after the DataRetrieval block and one of the options available is a SearchList so you should be able to build it out.


    This link might be a good starting point: https://www.blackbaud.com/files/support/guides/infinitydevguide/infsdk-developer-help.htm#../Subsystems/infrep-developer-help/Content/ReportCamp/cochReportParameters.htm?TocPath=Reports|Reporting%20Topics%3A%20Step-by-Step|Report%20Parameters|_____0
  • Joseph Styons
    Joseph Styons
    Fifth Anniversary Kudos 1 Name Dropper Participant
    Hi Bruce!

    This is definitely possible.  As Steven Finnell pointed out, you just need to modify your Report Spec to reference the searchlist.

    On the report side, make sure you have a parameter with a matching name, which will receive the GUID return value of the searchlist.

    Here is a very simple example showing usage of the OOB constituent lookup.

    If this isn't helpful, reply to this message or DM me and we can work out something more useful for you.

    -Joseph Styons

    https://www.styonssoftware.com 

    <ReportSpec
      xmlns="bb_appfx_report"
      xmlns:c="bb_appfx_commontypes"
      ID="4a3e5103-06e5-47f6-8ee2-07fef5c787aa"
      Name="Example Report"
      Description="An example report with a searchlist"
      Author="Styons Software"
      >
        <RDLFileName>MyTestReport.rdl</RDLFileName>
      <Folder>Custom Reports/Training</Folder>

        <DataRetrieval>
        <CreateSQL ObjectName="dbo.USR_SSC_USP_REPORT_TRAINING" ObjectType="SQLStoredProc"/>
        </DataRetrieval>

      <FormMetaData xmlns="bb_appfx_commontypes">
        <FormFields>
          <FormField FieldID="CONSTITUENTID" DataType="Guid" Caption="Constituent">
            <SearchList SearchListID="23C5C603-D7D8-4106-AECC-65392B563887"/>
          </FormField>
        </FormFields>
      </FormMetaData>
    </ReportSpec>
  • Joseph Styons
    Joseph Styons
    Fifth Anniversary Kudos 1 Name Dropper Participant
    Hi Amy!

    When you create a datalist from an ad-hoc query, you can specify any field as a filter parametner.

    But for reports, the only option you have is the context record id.

    You could weasel your way to a filter by

    1) creating a custom table

    2) writing a row of your preferred filters to that table

    3) passing the record id of your custom table as a context to the report


    But that's more trouble than creating a new report from scratch.


    If you really want to base an SSRS report off an existing query, consider doing this:

    1) open your ad-hoc query

    2) click the 'preview results' tab

    3) click the "view sql" button in the upper right corner of the preview pane

    4) copy that sql into a stored procedure

    5) use that stored procedure as the data source for a new report


    I hope this is helpful.  Good luck!

    -Joseph Styons

    https://www.styonssoftware.com

Categories