Problems modifying out-of-the-box report Activites

Posted on 2009-02-09
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
    LVL 10

    Expert Comment

    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

    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

    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.


    Author Comment

    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

    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

    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

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    Join & Write a Comment

    This is a walkthrough guide I wrote whilst upgrading my on-premise MS Dynamics CRM 3.0 deployment to 4.0. This covers the actual installation of the product to a working level for my system, I ran into a lot of issues that the steps below fixed so h…
    Automatically creating a Trello card using data from a Microsoft Dynamics CRM record turned out to be an easy project that yielded great results.  Here's how I did this for an internal team at General Code.
    To add imagery to an HTML email signature, you have two options available to you. You can either add a logo/image by embedding it directly into the signature or hosting it externally and linking to it. The vast majority of email clients display l…
    In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor ( If you're interested in additional methods for monitoring bandwidt…

    729 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

    Need Help in Real-Time?

    Connect with top rated Experts

    20 Experts available now in Live!

    Get 1:1 Help Now