You’re offline. This is a read only version of the page.
Announcements
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:
[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
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)
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:
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?
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?
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?
#1
André Arnaud de Cal...
294,118
Super User 2025 Season 1
#2
Martin Dráb
232,866
Most Valuable Professional
#3
nmaenpaa
101,158
Moderator
Share
Report
All responses (
Answers (
