HOW TO: Filtered Reports on Current Record? (FetchXML)

(0) Share

Report

Posted on by Community Member Microsoft Employee

Hi there,

I am currently creating reports using SSDT (SQL Server Data Tools) on VS 2012. I will try to explain my issue as clearly as possible.

1. The report is made to run on the Incident form.

2. Incident Events is a separate custom entity that logs specific events that occur in the Incident. Incident has a 1:N relationship with Incident Events.

3. In the FetchXML for the report, I have added prefiltering by,

 <entity name="incident" enableprefiltering="1">

This performs as expected, and when the the report is run, only the relevant Incident data is pulled over.

4. However, I am not able to get the relevant Incident Events. In other words, it's not being filtered based on the current record. Instead, ALL Incident Events from every Incident is being carried over.

5. I cannot filter the XML prior to report creation because I don't know what record the report will be run on - so this filter on Incident Events needs to be dynamic, just like the prefiltering on the Incident entity is.

Any suggestions?

*This post is locked for comments

All responses (11)

Answers (2)

Community Member Microsoft Employee on at

Like (0)

Report

RE: HOW TO: Filtered Reports on Current Record? (FetchXML)

After further research, this solution IS working on the case form when you run the report. It does not work on the appointments screen because it doesn't appear to have the Case ID. The report button says "Run for all records" as opposed to "Run for current record" like on the Case Form. I missed that difference earlier.

The problem I have is the Report button on the case form disappears before it can be clicked. It generally takes me about 10-15 attempts to click the report link before I can actually get the report to run. This is across multiple users, computers and browsers so I opened a ticket with Microsoft for that issue.
Community Member Microsoft Employee on at

Like (0)

Report

RE: HOW TO: Filtered Reports on Current Record? (FetchXML)

Nick,

I realize this is a pretty old thread but am hoping you can help with a very similar issue. I inherited some reports I am trying to get to work. The first one is "Case events for this case." As described, this should list all of the events (Appointments) for the current case. It is only available the from the Appointments screen for the case which filters by the current case. I think this is the exact scenario described by the OP.

I have tried quite a few variations on the original FetchXML but nothing has produced the correct results. It started with the enableprefiltering="1" on a "link-entity" to incident from the appointments entity. That didn't work either.

I came across this post and tried implementing your solution. It works inside Visual Studio but not in CRM.

I created a dataset that filters by incident:

FetchXML for Incident filter dataset:

<fetch version="1.0" output-format="xml-platform" mapping="logical" distinct="true">

<entity name="incident" enableprefiltering="1" >

<attribute name="title" />

<attribute name="incidentid" />

<order attribute="createdon" descending="true" />

</entity>

</fetch>

As soon as I save this dataset, the report automatically generates a new "CRM_incident" parameter:

"<fetch version="1.0" output-format="xml-platform" mapping="logical" distinct="false"><entity name="incident"><all-attributes /></entity></fetch>"

I created a second dataset to use the incident id to get the appointments:

<fetch version="1.0" output-format="xml-platform" mapping="logical" distinct="false">

<entity name="appointment" >

<attribute name="isalldayevent" />

<attribute name="subject" />

<attribute name="location" />

<attribute name="scheduledstart" />

<attribute name="new_scheduledatetype" />

<attribute name="new_eventdescription" />

<attribute name="new_date" />

<order attribute="new_date" descending="true" />

<filter type="and">

<condition attribute="new_caseid" value="@caseId" uitype="incident" operator="eq"/>

</filter>

</entity>

</fetch>

When I pass the appropriate "@CaseId" in the report building in Visual Studio, the results are correct. However, when I update the report in Dynamics CRM Online, the report is empty. I added a couple text boxes to the report to display the information (Title in header and ID in footer) from the selected incident.

When the report runs, the Case Title / ID displayed is not the id of the record currently being viewed. My thought is it is an issue with the auto generated FetchXml in the CRM_incident parameter. It appears to be the most recently created case which would match the order by clause.

Any ideas how to fix this issue? How do I get the current incidentid into the report?
JII SAADUDDIN 1,832 on at

Like (1)

Report

RE: HOW TO: Filtered Reports on Current Record? (FetchXML)

I was searching for a long time ago. I saw this article and voila my problem solved. Thank you sir Nick

the Great
Nick.Doelman 1,947 Most Valuable Professional on at

Like (0)

Report

RE: HOW TO: Filtered Reports on Current Record? (FetchXML)

Great to hear!

Cheers

Nick
Community Member Microsoft Employee on at

Like (0)

Report

RE: HOW TO: Filtered Reports on Current Record? (FetchXML)

Awesome. This fixed my issue, thanks!
gtwynstra 510 on at

Like (0)

Report

RE: HOW TO: Filtered Reports on Current Record? (FetchXML)

Thanks Nick. This post was really helpful. I'd like to contribute by pointing out that I noticed that if you have more than one parameter defined you might receive the error "Forward dependencies are not valid." when you view the report Preview.

In this situation, you can rearrange the order of the parameters using the tiny "Move Up" arrow.

Then when you preview the report, it shouldn't produce the error.

Enjoy.
Nick.Doelman 1,947 Most Valuable Professional on at

Like (1)

Report

RE: HOW TO: Filtered Reports on Current Record? (FetchXML)

That's great news!
Community Member Microsoft Employee on at

Like (0)

Report

RE: HOW TO: Filtered Reports on Current Record? (FetchXML)

Finally got it to work! Thank you so much Nick. I was beginning to lose hope and think that this was not possible.
Verified answer

Nick.Doelman 1,947 Most Valuable Professional on at

Like (1)

Report

RE: HOW TO: Filtered Reports on Current Record? (FetchXML)

Here is a screen shot below of setting up a Parameter in SSDT (This shows "account" but will work for incident as well) If the image is not clear, its in the "Report Data" section, right click and add a parameter, give it a name and use query to set it's default value.

Once the parameter is setup, and default value is set, you can use that as filter in your FetchXML (as I showed above) you should be able to run your report without being prompted for a incidentid

From the sounds of it, you are getting close!

Hope this helps.

Cheers

Nick
Community Member Microsoft Employee on at

Like (0)

Report

RE: HOW TO: Filtered Reports on Current Record? (FetchXML)

Hi Nick,

Yes, you are correct in assuming they are in separate datasets. I should have mentioned that in the question.

I'm confused as to how to create a parameter/provide a default value to get the GUID. Are you talking about within the fetchXML?

I have added the filter in the Incident Events dataset, and SSDT now prompts me to enter in a incident ID to continue viewing the report, which I'm assuming is the correct behavior.