SSRS report with CRM search screen as parameter

Bruce Boling

Is it possible to use a CRM search screen as an SSRS parameter to retrieve a value?

Crystal Bruce

This post was in Community help, I moved it over to CRM. Thanks!

Steven Finnell

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

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

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