100
Hi All,
I have a requirement of creating a SSRS report joining two Entities.
I have two entities A and B and which are related to each other.
A can have multiple B records. In report I need A with Latest B record.
For ex. Accounts with latest Phone call record.
How to achieve this ? Pls help.
Regards,
Gaurav Thakur
*This post is locked for comments
For testing of FetchXML, you can test here online http://msxrmtools.com
Gaurav Thakur, try to create another Dataset for the phonecalls containing the AccountID and order the records descending.
Then use a lookup function in your table to match the Accountid of the two datasets:
=lookup(AccountId,AccountId,Phonecall,"Phonecall).
had though of one more solution, putting a Yes/No field in Entity B and set to Yes if it is
a latest record, and pull this information in reports. Will that approach be fine ?
This will work but whenever you set yes to latest record you have to set all other B records to No. To do this you may need to write custom workflow/plugin.
Dude.
then you will create a custom entity for each activity type for entity A, called like this A_lastPhoneCall.(contain phone id and date as fields).
then when you associated a new phone call to entity A (use association plugin Msg) then update that record with new phone id and date.
So you will have at any time the report run the last phone call.
Hi,
I need only latest activity at the time of running the report for all the accounts in the system, using group by and order by it will even give all the activities and not the latest activity. (Account and Activity entities is the example entities I have taken to explain the problem, I have custom entities in which I have to implement this concept. I even need to join 3 custom entities and get the results. For Ex. Entities A, B, C. A is related to B and C, so get the latest record to B and C which are associated and show in report as one row, and run the report for all the A records in the system
I am working on CRM online, so only can use Fetch XML SSRS reports. And through fetch XML getting the linked entities latest created record is not possible.
Even after creating the Yes/No option it solves my issue for two Entities relationship Entity A and B., however when I join A, B, C it fails if entity B is not having any data(and c is having), no records comes in the report
Regards,
Gaurav Thakur
Hello.
what's the problem in using group by account and order by created on activities date using ssrs query designer
User does not want a new field to be available in Entity A, as even being made that lookup field unsearchable that is available to add in any system and custom view. So they are not ok to have a new field in Entity A.
I had though of one more solution, putting a Yes/No field in Entity B and set to Yes if it is a latest record, and pull this information in reports. Will that approach be fine ?
Regards,
Gaurav Thakur
I don't think its going to create any ambiguity of data. As record A holds just reference to record B that's it. If you want you can make the lookup non searchable in the advanced find using customization. As well dont add this lookup field on the form so user wont get access to this record at all.
I had done the same thing, however there will be ambiguity of same fields present in both the records and will also be available in Advance find, users will get confused so that solution will not work for me.
Is there any other solution is there for this situation ?
I dont think there is a way using fetchxml to do this. One option would be creating new lookup field on record A to B and updating this field whenever new record B gets associated with A. this way A record will have always latest record B as lookup value and you can use this value to generate report. This way even you dont have to create SSRS report you can use report wizard in crm to get this report.
To update record A you can use workflow on create of record B
#1
André Arnaud de Cal...
291,820
Super User 2024 Season 2
#2
Martin Dráb
230,514
Most Valuable Professional
#3
nmaenpaa
101,156
Share
Report
All responses (
Answers (
