You’re offline. This is a read only version of the page.
I have a FetchXML based SSRS report. Which is supposed to show activities at account level.
My client requirement is to execute this report on an open account record by selecting the report from the ribbon. I have tried to parametrise the report but no luck. Highlighted red below.
How do I pass the open/selected record ID to the report so it executes in the context of that record?
<?xml version="1.0"?>
<fetch distinct="true" mapping="logical" enableprefiltering="true" output-format="xml-platform" version="1.0">
<entity name="activitypointer">
<attribute name="activitytypecode"/>
<attribute name="subject"/>
<attribute name="statecode"/>
<attribute name="prioritycode"/>
<attribute name="modifiedon"/>
<attribute name="activityid"/>
<attribute name="scheduledstart" />
<attribute name="scheduledend" />
<order descending="true" attribute="modifiedon"/>
<link-entity name="activityparty" alias="aq" link-type="inner" to="activityid" from="activityid">
<filter type="and">
<condition attribute="participationtypemask" operator="in">
<value>1</value>
<value>2</value>
<value>3</value>
<value>4</value>
<value>5</value>
<value>6</value>
<value>7</value>
<value>8</value>
<value>9</value>
<value>11</value>
</condition>
</filter>
<link-entity name="contact" alias="con" link-type="inner" to="partyid" from="contactid">
<attribute name="fullname"/>
<attribute name="parentcustomerid"/>
<attribute name="jobtitle"/>
<link-entity name="account" alias="acc" link-type="inner" to="parentcustomerid" from="accountid">
<attribute name="name"/>
<filter type="and">
<filter type="or">
<condition attribute="accountid" operator="eq" value="@CRMID"/>
<condition attribute="parentaccountid" operator="eq" value="@CRMID"/>
</filter>
</filter>
</link-entity>
</link-entity>
</link-entity>
</entity>
</fetch>
Run report on the opened record
Thanks
So as @rampprakash you need to define a new dataset to the account , called Accounts as below:
SELECT accountid
FROM FilteredAccount AS CRMAF_FilteredAccount
Then on your report add a Parameter for the accountid as below:
Then pass the parameter to your fetchXML as below:
Share
Report
All responses (
Answers (<?xml version="1.0"?>
<fetch distinct="true" mapping="logical" enableprefiltering="true" output-format="xml-platform" version="1.0">
<entity name="activitypointer">
<attribute name="activitytypecode"/>
<attribute name="subject"/>
<attribute name="statecode"/>
<attribute name="prioritycode"/>
<attribute name="modifiedon"/>
<attribute name="activityid"/>
<attribute name="scheduledstart" />
<attribute name="scheduledend" />
<order descending="true" attribute="modifiedon"/>
<link-entity name="activityparty" alias="aq" link-type="inner" to="activityid" from="activityid">
<filter type="and">
<condition attribute="participationtypemask" operator="in">
<value>1</value>
<value>2</value>
<value>3</value>
<value>4</value>
<value>5</value>
<value>6</value>
<value>7</value>
<value>8</value>
<value>9</value>
<value>11</value>
</condition>
</filter>
<link-entity name="contact" alias="con" link-type="inner" to="partyid" from="contactid">
<attribute name="fullname"/>
<attribute name="parentcustomerid"/>
<attribute name="jobtitle"/>
<link-entity name="account" alias="acc" link-type="inner" to="parentcustomerid" from="accountid">
<attribute name="name"/>
<filter type="and">
<filter type="or">
<condition attribute="accountid" operator="eq" value="@accountid"/>
<condition attribute="parentaccountid" operator="eq" value="@accountid"/>
</filter>
</filter>
</link-entity>
</link-entity>
</link-entity>
</entity>
</fetch>
Hi @Mira_Ghaly
Our Accounts are not directly linked with the activities. In other words we do not set "Regarding" for activities to accounts instead "Contact" or "opportunities" hence above query won't work for us. the query which I have provided in the original thread works perfectly when testing using FetchXML tester/Or query designer in SSRS, its only the filter in report we need to sort out.i.e. run in the context of opened/selected record...
Thanks
Hello @UmerJaved,
You are too close to achieve the same, try creating a new Data Set in the SSRS
SELECT accountid
FROM FilteredAccount AS CRMAF_FilteredAccount
Passed this as a parameter to the first dataset. By doing the id of the opened record to the report as parameter.
Please mark as Answer if it is helpful and provide Kudos
Subscribe : https://www.youtube.com/channel/UCnGNN3hdlKBOr6PXotskNLA
Blog : https://microsoftcrmtechie.blogspot.com
For the prefiltering to work correctly , the dataset or your fetchXML needs to be based on the Account and Linked to the Activities , if you can try the below Fetch Instead it should work, the below fetch is supposed to return all activities regarding the account:
Please also Note I have given the Link-Entity an Alias "Account Activities" for each of use:
<fetch version="1.0" output-format="xml-platform" mapping="logical" distinct="true" enableprefiltering="1" >
<entity name="account" >
<attribute name="name" />
<attribute name="primarycontactid" />
<attribute name="telephone1" />
<attribute name="accountid" />
<order attribute="name" descending="false" />
<link-entity name="activitypointer" from="regardingobjectid" to="accountid" link-type="inner" alias="accountactivities" >
<attribute name="activitytypecode" />
<attribute name="subject" />
<attribute name="statecode" />
<attribute name="prioritycode" />
<attribute name="modifiedon" />
<attribute name="activityid" />
<attribute name="scheduledstart" />
<attribute name="scheduledend" />
<order descending="true" attribute="modifiedon" />
</link-entity>
</entity>
</fetch>
#1
WarrenBelz
146,776
Most Valuable Professional
#2
RandyHayes
76,287
Super User 2024 Season 1
#3
Pstork1
66,093
Most Valuable Professional
