How to preselect a multiselect parameter using prefiltering in SSRS with fetchxml in Dynamics CRM 2016

Question

I have an SSRS report query for which is in fetchxml. The main dataset "DS_PromissoryNotes" which fetches data for the report has a pretty basic query.

<?xml version="1.0" encoding=""?>
<fetch distinct="false" mapping="logical" output-format="xml-platform" version="1.0" >
    <entity name="promissorynote">
        <attribute name="promissorynoteid" />
        <attribute name="totalamount" />
        <attribute name="name" />
        <attribute name="duedate" />
        <attribute name="pn_customer" />
        <attribute name="pn_distributor" />
        <attribute name="dateofissue" />
        <order descending="false" attribute="name" />
        <filter type="and" >
            <condition attribute="promissorynoteid" value="@PromissoryNoteId" operator="in" />
        </filter>
    </entity>
</fetch>

As you can note that the fetchXML query takes a parameter @PromissoryNoteId which can be multiple so I have "Allow multiple" checked of course. Data for the parameter is fetched from another dataset "DS_PNID" which is almost similar to the above fetchXML query only fetching 'name' and 'promissorynoteid' attribute with no filter condition.

I want to be able to select multiple records from the Promissory Notes grid displayed on the entity's page and run report for the selected record. It should display the report data and also mark the selected PromissoryNoteIds in the multi-select dropdown filter so that I can modify the selection and view updated report from the report page itself.

I tried applying prefiltering on the main dataset "DS_PromissoryNotes" like this

<entity name="core_promissorynote" enableprefiltering="true" prefilterparametername="CRM_promissorynoteid">

After doing this I added it as new report. When I ran the report it gives me same result i.e. I have to manually select the PromissoryIDs from the filter dropdown. Promissory ID belonging to those records are not preselected.

P.S. When I remove the parameter and only use prefilter, it works fine. But I want to have that dropdown as the report is also present in report area where the report is run without selecting the records and the dropdown is the only way for selecting the PromissoryIds. I also don't want to make 2 separate reports.

Please help.

Answer 1

OK so I figured it out the next day. Didn't have time to post so posting it now.

What I basically did is,

• Removed prefiltering from the main dataset. Also deleted the parameter which was created due to prefiltering.
• Created another dataset "DS_PrefilteredPNID" which is same as "DS_PNID" (dataset which fetches Promissory Note IDs for my parameter) and applied prefiltering on the newly created dataset.
• Make sure that the newly created parameter is placed above the old one in sequence.
• Now, edit the old parameter. In the default section, choose "from query". Then select the newly created dataset and value.

BOOM! This works flawlessly.