web
You’re offline. This is a read only version of the page.
close
Skip to main content

Notifications

Announcements

No record found.

News and Announcements icon
Community site session details

Community site session details

Session Id :
Dynamics 365 Community / Forums / Microsoft Dynamics CRM (Archived) / Goal rollup query sql ...
Microsoft Dynamics CRM (Archived)

Goal rollup query sql errors

(0) ShareShare
ReportReport
Posted on by ekmpdx 235

I've been tasked with building goals for our sales team that are based more on territories and account groups than ownership.  Each territory has to focus on their top 25 key accounts (existing accounts with ongoing relationships) and a target list of 25 prospects.  We'll need to be able to report on these over the course of the fiscal year, but the lists will likely change from quarter to quarter, so we've opted to use marketing lists as opposed to creating fields on the account entity to designate them.  We'll have to create new marketing lists for each quarter.

Anyway, the roll up query approach seemed fairly straightforward.  Return opportunities where:

8640.Error.png

[We have a custom Account field on our opportunity that's a lookup to the account entity (Potential Customer is exclusively a Contact for us - makes reporting more consistent).]

This ran fine when I fist built it.  I tested it a couple times to make sure it was returning the right results, and then I moved to the next query, which is slightly more complicated in that some of the accounts on these marketing lists are parents of child accounts that actually have the opportunities.  Here I had to add some more logic to make sure I exclude children if they're in a different territory, so I had to build one query for each territory.

This query works just fine in Advanced Find.  If I run it within the Rollup Query, I get a generic "An error has occurred."  The Goal it's connected to now shows this:

Worse, so does every active goal I have that has an opportunity query.  Suddenly, none of the queries returning opportunities will run within the goal rollup query.  Again, they work just fine in Advanced Find, just not here.  What the heck is going on?

*This post is locked for comments

I have the same question (0)
  • ekmpdx Profile Picture
    ekmpdx 235 on at

    2 days on and I still can't get any rollup queries that return opportunities to run.  Not even a query with ZERO filters that I've used for two years in Goals.  Help!

  • mscrmba Profile Picture
    mscrmba on at

    There was an issue a while back that stopped rollups working.

    If you had an ampersand '&' or ' " ' in the display name for any of the rollup fields then rollups wouldn't run.  Removing the '&' or ' " ' and republishing your solution fixed the problem.

    Have you used any special characters in your display names?

  • ekmpdx Profile Picture
    ekmpdx 235 on at

    Just dashes.  

    It makes no sense but it behaves like one of them hung at some point and all the rest just piled up behind and couldn't hit the database, so they just immediately threw a general SQL error.  I wound up solving the problem by deactivating all my existing rollup queries for opportunities and building new ones from scratch.  They are exactly the same queries, but now they run.

    But now I have a new question.  

    The two queries above feed two child goals.  These goals have "Record Set for Rollup" set to ALL, because I want to capture all opportunities won in the region regardless of ownership.  These goals feed into a parent goal that actually has their revenue target, and the parent goal has "Roll Up Only from Child Goals" set to Yes.

    Where I'm confused is with "Record Set for Rollup" on the Parent goal.  I had selected All like I had with the child goals, since the same requirement applies.  But when I look at the Participating Records, rather than seeing opportunities that meet the rollup query criteria in the child goals, I'm seeing ALL opportunities for the period.  The numbers in the Actuals section don't appear to change regardless of my choice for that condition, and they seem to correctly reflect the sum of the child goals, so it looks like the problem is merely with the display of the Participating Records.  Is this a known issue?

  • mscrmba Profile Picture
    mscrmba on at

    What rollup are you on?  Rollup 10 fixed a bug with not all participating records being displayed (perhaps it went too far). 

    According to Microsoft: If you set Goal.RollupOnlyFromChildGoals to true, only child goal records are used in the rollup. If you set it to false, the rollup includes the child records and other goal’s participating records. A participating record must satisfy the following conditions:

    • The source date of the record must be between the start date and the end date of the goal time period, or fall on the start date or the end date of the goal period.

    • The state and status of the record must match the values defined in the goal metric.

    • If a rollup query is specified for the goal, all query conditions must be met.

    • The goal manager must have Read access to the record.

    https://msdn.microsoft.com/en-us/library/gg334597.aspx

    I haven't seen any other similar issues raised.  Perhaps it's as designed - given that more than one filter criteria is in place (at the child level), perhaps it starts to get a bit complicated to combine multiple queries and it defaults to all instead.  What shows at the child level - all or a filtered list? 

  • ekmpdx Profile Picture
    ekmpdx 235 on at

    The error is back. I have a new series of rollup queries, and I had to make a minor edit to each one.  Now they won't run.  Same query in advanced find or a report runs just fine.  Just not in goals.  Here's the error produced in the log.  Can anyone make sense of this for me?

    RequestUrl: xxxx.com/.../AppGridWebService.ashx, StackTrace: [CrmException: Generic SQL error.]

      at Microsoft.Crm.Application.Platform.ServiceCommands.PlatformCommand.XrmExecuteInternal()

      at Microsoft.Crm.Application.Platform.ServiceCommands.RetrieveMultipleCommand.Execute()

      at Microsoft.Crm.ApplicationQuery.RetrieveMultipleCommand.RetrieveData()

      at Microsoft.Crm.ApplicationQuery.ExecuteQuery()

      at Microsoft.Crm.Application.Platform.Grid.GridDataProviderQueryBuilder.GetData(QueryBuilder queryBuilder)

      at Microsoft.Crm.Application.Platform.Grid.GridDataProviderQueryBuilder.LoadQueryData()

      at Microsoft.Crm.Application.Platform.Grid.GridDataProviderQueryBuilder.LoadData()

      at Microsoft.Crm.Application.Platform.Grid.GridDataProviderBase.PrepareGridData()

      at Microsoft.Crm.Application.Platform.Grid.GridDataProviderBase.PrepareData()

      at Microsoft.Crm.Application.Controls.GridUIProvider.Render(HtmlTextWriter output)

      at Microsoft.Crm.Application.WebServices.AppGridWebServiceHandler.GetRefreshResponseHtml(IGridUIProvider uiProvider, StringBuilder sbTemp)

      at Microsoft.Crm.Application.WebServices.AppGridWebServiceHandler.Refresh(String gridXml, StringBuilder sbXml, StringBuilder sbHtml, Boolean returnJsonData)

      at Microsoft.Crm.Application.WebServices.AppGridWebServiceHandler.ProcessRequestInternal(HttpContext context)

    [XmlException: Microsoft.Crm.CrmException: Generic SQL error.

      at Microsoft.Crm.Application.Platform.ServiceCommands.PlatformCommand.XrmExecuteInternal()

      at Microsoft.Crm.Application.Platform.ServiceCommands.RetrieveMultipleCommand.Execute()

      at Microsoft.Crm.ApplicationQuery.RetrieveMultipleCommand.RetrieveData()

      at Microsoft.Crm.ApplicationQuery.ExecuteQuery()

      at Microsoft.Crm.Application.Platform.Grid.GridDataProviderQueryBuilder.GetData(QueryBuilder queryBuilder)

      at Microsoft.Crm.Application.Platform.Grid.GridDataProviderQueryBuilder.LoadQueryData()

      at Microsoft.Crm.Application.Platform.Grid.GridDataProviderQueryBuilder.LoadData()

      at Microsoft.Crm.Application.Platform.Grid.GridDataProviderBase.PrepareGridData()

      at Microsoft.Crm.Application.Platform.Grid.GridDataProviderBase.PrepareData()

      at Microsoft.Crm.Application.Controls.GridUIProvider.Render(HtmlTextWriter output)

      at Microsoft.Crm.Application.WebServices.AppGridWebServiceHandler.GetRefreshResponseHtml(IGridUIProvider uiProvider, StringBuilder sbTemp)

      at Microsoft.Crm.Application.WebServices.AppGridWebServiceHandler.Refresh(String gridXml, StringBuilder sbXml, StringBuilder sbHtml, Boolean returnJsonData)

      at Microsoft.Crm.Application.WebServices.AppGridWebServiceHandler.ProcessRequestInternal(HttpContext context)]

      at Microsoft.Crm.Application.WebServices.AppGridWebServiceHandler.ProcessRequestInternal(HttpContext context)

      at System.Web.HttpApplication.CallHandlerExecutionStep.System.Web.HttpApplication.IExecutionStep.Execute()

      at System.Web.HttpApplication.ExecuteStep(IExecutionStep step, Boolean& completedSynchronously)

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

Responsible AI policies

As AI tools become more common, we’re introducing a Responsible AI Use…

Neeraj Kumar – Community Spotlight

We are honored to recognize Neeraj Kumar as our Community Spotlight honoree for…

Congratulations to the November Top 10 Community Leaders!

These are the community rock stars!

Leaderboard > 🔒一 Microsoft Dynamics CRM (Archived)

#1
SA-08121319-0 Profile Picture

SA-08121319-0 4

#1
Calum MacFarlane Profile Picture

Calum MacFarlane 4

#3
Alex Fun Wei Jie Profile Picture

Alex Fun Wei Jie 2

Last 30 days Overall leaderboard

Featured topics

Product updates

Dynamics 365 release plans