Customer Engagement & Dynamics CRM Forum

Expand all | Collapse all

Prefiltering with SSRS report

  • 1.  Prefiltering with SSRS report

    TOP CONTRIBUTOR
    Posted Oct 21, 2020 12:21 PM
    Hi everyone-

    I am attempting to create a prefiltered report on the Account entity that will run on just the record showing on the screen.

    When I set up the FetchXML and the SSRS report, I feel like I am doing everything right, but it only gives me the option to Run on All Records and not Run on This Record.

    Clearly I am having some kind of mental block on this.

    Here is my FetchXML in my report:

    <fetch output-format="xml-platform" distinct="false" version="1.0" mapping="logical"><entity name="account" enableprefiltering="1" prefilterparametername="CRM_account"><attribute name="accountid" /><attribute name="name" /></entity></fetch>

    It creates the parameter and everything looks good.

    When I create the report in D365, select the RDL file, pick "Account" as the related record types and "Forms for related record types" where to display it, it never shows Run on This Record when picking Run Report. It just shows Run on All Records.

    As a test, I created a new report using the Report Builder in D365 and it works just fine filtering on the current record.

    What am I doing wrong here? Any assistance is appreciated.

    Thank you.
    Joe

    ------------------------------
    Joseph C. Markovich
    Valparaiso IN
    ------------------------------
    The first step toward cloud success. - Migrate from CRM to D365 with expert guidance from Microsoft. I'm Ready


  • 2.  RE: Prefiltering with SSRS report

    Posted Oct 22, 2020 09:05 AM
    Hi Joe,

    I just built a quick SSRS report with your FetchXML and have it enable for Form access. The report shows under the Run on Selected Records section so I don't think it is anything with your FetchXML pre-filter setup. Are there other data sets in the report?

    Scott

    ------------------------------
    Scott Florance
    Business Applications Consultant
    KTL Solutions, Inc, Frederick MD
    ------------------------------

    The first step toward cloud success. - Migrate from CRM to D365 with expert guidance from Microsoft. I'm Ready


  • 3.  RE: Prefiltering with SSRS report

    TOP CONTRIBUTOR
    Posted Oct 22, 2020 05:13 PM
    Hi Scott-

    Thanks for looking at this. Yes, there is another dataset -- a connection to Dynamics GP for open sales orders.

    Is that causing the issue??

    Thank you.
    Joe

    ------------------------------
    Joseph C. Markovich
    Valparaiso IN
    ------------------------------

    The first step toward cloud success. - Migrate from CRM to D365 with expert guidance from Microsoft. I'm Ready


  • 4.  RE: Prefiltering with SSRS report

    Posted Oct 23, 2020 09:22 AM
    Hi Joe,

    Yes, that very well could be the cause. It is seeing another dataset that doesn't have a filter so it applies it against all records.

    I have built out reports where you want to run the report from a record in CRM but need to query the data from GP and it seems like this is what you are trying to do. The way I have accomplished this is by creating two reports. The first report is just for the connection to CRM. It only has the datasource of CRM and one dataset which is your filtering FetchXML query. For the report itself I fill the entire space with a sub-report and in that sub-report I am passing the Account Name or Customer ID (whatever your GP record filter will be) into the sub-report. For the second report which is the sub-report the datasource is GP and the dataset is your GP query with the parameter passed from the parent report included for the filter.

    Both the parent and sub-report get loaded into CRM.

    Hope that helps.

    Scott

    ------------------------------
    Scott Florance
    Business Applications Consultant
    KTL Solutions, Inc, Frederick MD
    ------------------------------

    The first step toward cloud success. - Migrate from CRM to D365 with expert guidance from Microsoft. I'm Ready


  • 5.  RE: Prefiltering with SSRS report

    TOP CONTRIBUTOR
    Posted 29 days ago
    Hi Scott-

    Thanks so much for the help here. I had to walk away from it last week because it was just stumping me. Turns out it was the stupidest thing I was overlooking. The data source name wasn't called "MSCRM_DataSource" in my report. I had it named something else.

    Now, the report shows up on the Account record! Yay!

    The next issue that is going on is the report still doesn't run. As a test, I have one field on my report: the Name field. The report runs just fine in Visual Studio, but when I run it in D365, it shows an error on the screen when running and this is the error in the Event Viewer on the server:

    Report data set execution failure. Error: Incorrect syntax near '<'.

    This is my FetchXML in the report:

    <fetch distinct="false" version="1.0" mapping="logical" >
    <entity name="account" enableprefiltering="1" >
    <attribute name="accountid" />
    <attribute name="name" />
    <attribute name="accountnumber" />
    </entity>
    </fetch>

    I feel like this is something really, really simple again, but I am just not seeing it.

    Any other assistance would be so helpful.

    Thanks.
    Joe

    ------------------------------
    Joseph C. Markovich
    Valparaiso IN
    ------------------------------

    The first step toward cloud success. - Migrate from CRM to D365 with expert guidance from Microsoft. I'm Ready


  • 6.  RE: Prefiltering with SSRS report

    Posted 29 days ago
    Hey Joe,

    Looks like you are missing prefilterparametername="CRM_account" which you had in the XML in your original post.

    ------------------------------
    Scott Florance
    Business Applications Consultant
    KTL Solutions, Inc, Frederick MD
    ------------------------------

    The first step toward cloud success. - Migrate from CRM to D365 with expert guidance from Microsoft. I'm Ready


  • 7.  RE: Prefiltering with SSRS report

    TOP CONTRIBUTOR
    Posted 21 days ago
    Hi Scott-

    Me again.

    I fixed the FetchXML (thanks for catching that) and I am still getting the error about the report not being able to be displayed. On the D365 server, this is the error in the event viewer:

    Report data set execution failure. Error: Incorrect syntax near '<'.

    Do you know what this error means?

    Thanks.
    Joe

    ------------------------------
    Joseph C. Markovich
    Valparaiso IN
    ------------------------------

    The first step toward cloud success. - Migrate from CRM to D365 with expert guidance from Microsoft. I'm Ready


  • 8.  RE: Prefiltering with SSRS report

    Posted 21 days ago
    Can you run your Fetch through the FetchXML Builder or FetchXML Tester? Usually that means that something is wrong with the schema as in you are missing a closing "/>" or there is something else in between that is not allowing it to read the tags properly. It'll fail in those tools if there is something incorrect with the schema/tags.

    ------------------------------
    Scott Florance
    Business Applications Consultant
    KTL Solutions, Inc, Frederick MD
    ------------------------------

    The first step toward cloud success. - Migrate from CRM to D365 with expert guidance from Microsoft. I'm Ready


If you've found this thread useful, dive deeper into User Group community content by role