[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now


Problems modifying out-of-the-box report Activites

Posted on 2009-02-09
Medium Priority
Last Modified: 2012-05-06
I'm trying to modifying the Activites report in CRM 4.0. I found out that when I exclude a join in the DSActivity dataset from the Activities report then the report is returning what my client wants to see. As the report is now, my clients only sees the results where the regardingobjecttypecode from FilteredActivityPointer is 3.

I would like to get results for all regardingobjecttypecode, so as I mentioned I removed a join in the dataset.

From the DSActivity dataset:
SET              @SQL1 = '
/* Inserting data into the Temp table for activities  related to opportunities which have ended and in between startdate and end date */
insert #temp
SELECT     fap.activityid ,  fap.activitytypecode, fap.activitytypecodename,  fap.ownerid,
 fap.owneridname,  fap.regardingobjectid, fap.regardingobjectidname
 FROM    (' + @CRM_FilteredActivityPointer + ') fap
inner join ( '
                       + @CRM_FilteredOpportunity + ') fo on fap.regardingobjectid = fo.opportunityid
inner join (' + @CRM_FilteredSystemUser + ') fsu on fap.ownerid = fsu.systemuserid

I would like to exclude the following join from the report:
 ( '
                       + @CRM_FilteredOpportunity + ') fo on fap.regardingobjectid = fo.opportunityid

When I do that, and upload it report to CRM, it fails.

I would like to know why this fails, and if there is some other alternative way to do this.
Question by:gosi75
  • 3
  • 3
LVL 10

Expert Comment

ID: 23599638
Yikes!  Updating the default reports in MS CRM is scary business - it can certainly be done, but they tend to be very complex configurations and are prone to creating errors unless you spend a lot of time debugging your work.  Some suggestions:

(1) Consider creating your own activity report to deliver what your client needs (have you tried the Report Wizard?  Sometimes this is all that you need - if not, then try writing a report in SSRS).

(2) The default Activity Report in CRM does return all regarding object types (object type 3 is the Opportunity object type).  So I'm not sure why your default report is not showing activities regarding other entities.  Some possibilities:
* The default report was already modified.  Try re-loading the default report to see if it works
* The user is running the report from the Opportunity entity - and the report is auto-filtering to only those activities that are regarding the Opportunity(ies) in question
* The Default Filter for the report has been altered.  Try changing this in CRM (Reports | Activities | More Actions | Edit Default Filter)

Author Comment

ID: 23601426
I think that there are missing 2 filters, Regarding(Account) and Regarding(Contacts). Currently Activites, Cases, Opportunities and Users are showing in the filters. How can I add Contacts and Account to the CRM filters?

I did go to CRM (Reports | Activities | More Actions | Edit Default Filter) but the dropdown box is placed into the Activities filter and therefore only possbile to add them into the activites filter. How can I add the filter to the report so I will be having those filters:


LVL 10

Expert Comment

ID: 23607666
You cannot easily add these two groups to the filters - but you SHOULD be able to use existing filters to filter on these results.

Before I explain that, let me make sure I am understanding what you are trying to do:

Your initial inquiry led me to believe that they can ONLY see Opportunities (regardingobjecttypecode=3) in the report.  However, by default, you can report on EVERY activity in CRM using this report (not just activities that are related to opportunities).

From your second reply, it sounds as if they can see all of the activities, but they want to ALSO be able to filter them by a specific Contact or Account.

If I am correct in assuming that the latter paragraph is what you are after, there is a simple process that SHOULD work for handling the filtering.  I've outlined it below, but it appears that this does not work (which is a bug with the report in CRM - I've reported it to Microsoft in hopes that it will be fixed.)  I'm including the process below only so if someone finds this in the future, they can give it a try:

(1) Reports | Activities | Double Click.  You will see the report filter.
(2) Click the "Select" link under Activities
(3) Scroll down until you get to Regarding (Account) and select it
(4) Click the Select link under thet Regarding (Account) area and select whatever criteria you wish to add to the filter
(5) Repeat for Contacts if you wish to filter by that.

Okay, since that doesn't work, what are the options until MS gets around to fixing this?

(1) You could create a view on the Activity entity that gives them what they want - then export it to an Excel Pivot Table so they can get the summary that they're looking for (simple)
(2) You could create a custom report in the report wizard (simple)
(3) You could create a custom report in Visual Studio (fairly complex)
(4) You can keep trying to modify that default report (not recommended - VERY complex)

If it were me, I would go for option #2 or #3.  I've modified the reports that come with CRM before - it is a painful process because the queries behind these reports tend to be highly complex.  If you do decide to modify the existing report, keep in mind that the reports built into CRM are usually multiple different reports "rolled into 1" - so the bar charts on the Activities report are really a bunch of reports that are laid one ontop of the other ... you need to find the real report and dataset that you want to update to get everything working the way you want (or you may need to update multiple different bar charts to work with your revised report).

Sorry to not be able to give you a solid answer on this one.  I hope one of the above options turns out well for you.

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!


Author Comment

ID: 23609703
Thank you very much for your detail answear. I apologize but I think that I haven't been to clear about what I'm trying to achieve here.

I attached a pdf document where I go through step by step what my client wants.
LVL 10

Accepted Solution

crm_info earned 2000 total points
ID: 23610495
Thank you for the detailed description - now I can see the specific issue you are trying to work around.

What you are trying to do should be working - this is clearly a bug with the existing report.  If your firm has software assurance, you should use a trouble ticket to report this to Microsoft to see if they can give you a revised report file.  

We will also report this to Microsoft and hopefully they will post a fix in their Knowledge Base (I did a quick search and did not see anything posted on this subject at this time).

I will also try to test this out a bit later today to see if I can come up with a quick fix for you.  Again, those Microsoft queries on their reports are notoriously complex and I can't spend more than a few minutes on it.  But I will give it a quick glance.

Author Comment

ID: 23621506
Ok thank you very much for your help, I look forward to see if you have an quick fix.

Thanks again for your help.


Featured Post

Get quick recovery of individual SharePoint items

Free tool – Veeam Explorer for Microsoft SharePoint, enables fast, easy restores of SharePoint sites, documents, libraries and lists — all with no agents to manage and no additional licenses to buy.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Having trouble getting your hands on Dynamics 365 Field Service or Project Service trial? Worry No More!!!
Desired Skill Set for Microsoft Dynamics CRM Technical Resources – Part I
In a question here at Experts Exchange (https://www.experts-exchange.com/questions/29062564/Adobe-acrobat-reader-DC.html), a member asked how to create a signature in Adobe Acrobat Reader DC (the free Reader product, not the paid, full Acrobat produ…
Screencast - Getting to Know the Pipeline
Suggested Courses

872 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question