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

(0) Share

Report

Posted on by Community Member

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

I have the same question (0)

All responses (11)

Answers (2)

Verified answer

Nick.Doelman 1,947 Most Valuable Professional on at

Like (0)

Report

Assumptions:

You have dataset for Incidents and a seperate dataset for Incident Events?

Create a parameter, and in the default values section, choose the "Incident" dataset with the "incidentid" as the field. This will give you the GUID of the incident you are working based on the prefiltering. For the example, this will be called "incident"

In the fetchXML of the Incident Events Dataset, put in the filter something along the lines of <condition attribute="incidentid" operator="eq" value="@incident" />

Hope that gives you enough to work with!

Cheers

Nick

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

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.

Was this reply helpful? Yes No
Verified answer

Nick.Doelman 1,947 Most Valuable Professional on at

Like (1)

Report

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

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

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

Was this reply helpful? Yes No
Nick.Doelman 1,947 Most Valuable Professional on at

Like (1)

Report

That's great news!

Was this reply helpful? Yes No
gtwynstra 510 on at

Like (0)

Report

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.

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

Awesome. This fixed my issue, thanks!

Was this reply helpful? Yes No
Nick.Doelman 1,947 Most Valuable Professional on at

Like (0)

Report

Great to hear!

Cheers

Nick

Was this reply helpful? Yes No
JII SAADUDDIN 1,832 on at

Like (1)

Report

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

the Great

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

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?

Was this reply helpful? Yes No