You’re offline. This is a read only version of the page.
We are using MS Dynamics 365 for our sales in our company. We have a chart related to sales in the CRM system and I can see it when I log in online.
I have obtained the GUID of the chart and I can now retrieve the chart successfully using the following code piece:
Guid myGuid = new Guid("XXXXXXX-XXXX-XXXX-XXXX-XXXXXXXXXXXXX"); SavedQueryVisualization retrievedOrgOwnedVisualization = (SavedQueryVisualization)_orgService.Retrieve(SavedQueryVisualization.EntityLogicalName, myGuid, new ColumnSet(true));
When I debug the code, I can see that retrievedOrgOwnedVisualization parameter is filled with some correct data, however I couldn't find the actual data that is used to construct the chart in this parameter. I see that PresentationDescription has some data to construct the chart but this doesn't say anything with the actual values. It contains some color information etc. DataDescription is almost empty. I have checked the Attributes which has 19 keys but non of them contains the actual data used to construct the chart (x axis, y axis values). So, how can I get x and y axis values of the chart?
*This post is locked for comments
Abhivan, below is my latest code where I tried to add filters in the fetchXML.
Share
Report
All responses (
Answers (
string fetchxml = @"<fetch mapping='logical' aggregate='true'>
<entity name='invoice'><attribute groupby='true' alias='dateDeliveredAlias' dategrouping='month' name='datedelivered' />
<attribute alias='totalAmountAlias' name='totalamount' aggregate='sum' />
</entity>
<filter type='and'>
<condition attribute='new_type' operator='in'>
<value>100000001</value>
<value>100000002</value>
<value>100000003</value>
</condition>
<condition attribute='datedelivered' operator='this-year' />
</filter>
<link-entity name='account' from='accountid' to='customerid' alias='a_34cbb0cfdeee47ad9fb9b943199a54bf'>
<attribute name='territoryid' />
<attribute name='accountnumber' />
<attribute name='new_country' />
<attribute name='customertypecode' />
<attribute name='new_sector' />
<filter type='and'>
<condition attribute='new_businessunit' operator='in'>
<value uiname='ISA' uitype='businessunit'>{EBDA0E41-8FB7-E411-80E4-C4346BAD1078}</value>
<value uiname='ISB' uitype='businessunit'>{5402D047-8FB7-E411-80E4-C4346BAD1078}</value>
<value uiname='ISC' uitype='businessunit'>{5D3FD74D-8FB7-E411-80E4-C4346BAD1078}</value>
</condition>
</filter>
</link-entity>
</fetch>";
EntityCollection result = _orgService.RetrieveMultiple(new FetchExpression(fetchxml));
int i = 1;
foreach (var c in result.Entities)
{
decimal aggregate1 = ((Money)((AliasedValue)c["totalAmountAlias"]).Value).Value;
System.Console.WriteLine(i++ + ".: value: " + aggregate1);
}
This returns:
1.: value: 3254450.710000000 2.: value: 4056319.020000000 3.: value: 3341928.140000000 4.: value: 3702004.680000000 5.: value: 2743552.660000000 6.: value: 2486402.640000000 7.: value: 2700602.730000000 8.: value: 3561049.940000000 9.: value: 3365121.240000000 10.: value: 2979879.830000000 11.: value: 2496133.770000000 12.: value: 3214911.500000000
On the other hand, I tried using the same fetchXML that I got from CRM. I can query on totalamount attribute and I can see all amounts in CRM listed. That's already pretty good. I can even write my application based on this, so I can implement aggregate in my code as well.
Thanks a million.
Abhinav, it works, it returns some values, however I didn't understand what values are those?
Below is the chart that I am retrieving:
The values I get for totalAmountAlias are:
Average estimated value: 3254450.710000000
Average estimated value: 4056319.020000000
Average estimated value: 3341928.140000000
Average estimated value: 3702004.680000000
Average estimated value: 2743552.660000000
Average estimated value: 2486402.640000000
Average estimated value: 2700602.730000000
Average estimated value: 3561049.940000000
Average estimated value: 3365121.240000000
Average estimated value: 2979879.830000000
Average estimated value: 2496133.770000000
Average estimated value: 3213371.500000000
First of all, why do I get 12 values? for 12 months? Why are they not 0 then? Is it related to filters?
Thanks a lot, it at least retrieves some data, just some last bits to make it ready.
you can refer to msdn.microsoft.com/.../microsoft.xrm.sdk.aliasedvalue.aspx
Typecast aliased value to your datatype.
I got 12 times
Microsoft.Xrm.Sdk.AliasedValue
Also, in the fetch xml from chart there are no filters, in other fetch xml there are lots of filters and linked filters available. You might want to include those to get correct output.
in your code
replace "_CRMAutoGen_groupby_column_Num_0" with dateDeliveredAlias"
replace "_CRMAutoGen_aggregate_column_Num_0" with "totalAmountAlias"
and try running System.Console.WriteLine(c.Attributes["totalAmountAlias"]);
Abhinav, the chart won't change at all. Therefore, I will use a fixed fetchxml and keep querying using this all the time. I am very new to CRM and indeed I just need what you describe in 1.
Abhinav,
Below is my code:
string fetchxml = @"<fetch mapping='logical' aggregate='true'>
<entity name='invoice'><attribute groupby='true' alias='_CRMAutoGen_groupby_column_Num_0' dategrouping='month' name='datedelivered' />
<attribute alias='_CRMAutoGen_aggregate_column_Num_0' name='totalamount' aggregate='sum' />
</entity>
</fetch>";
EntityCollection result = _orgService.RetrieveMultiple(new FetchExpression(fetchxml));
foreach (var c in result.Entities)
{
System.Console.WriteLine(c.Attributes["totalamount"]);
}
However, I get an exception at c.Attributes that "The given key was not present in the dictionary.". I tried different names including "name" but same result.
1. I am assuming you are only trying to fetch the data used to generate the chart i.e. aggregated data i.e. Months vs Sum of Total Amount. -> In this case you can get it with above process.
2. In case you are in need of detailed data you can get that by using fetch XML from advanced find you refereed.
One key point to keep in mind is that there are no filters in the FetchXML of chart, Filters come from the System View on which you trigger the chart.
If you are interested in filtered data i.e. View data I would recommend if you can write your own fetch xml (or use one above) with filters and directly use that rather than relying on the chart (assuming your charts are not changing that often).
How to get data from FetchXML is available in the link I shared earlier.
You can parse datadefinition xml to get fetchxml
<fetch mapping=\"logical\" aggregate=\"true\">
<entity name=\"invoice\"><attribute groupby=\"true\" alias=\"_CRMAutoGen_groupby_column_Num_0\" dategrouping=\"month\" name=\"datedelivered\" />
<attribute alias=\"_CRMAutoGen_aggregate_column_Num_0\" name=\"totalamount\" aggregate=\"sum\" />
</entity>
</fetch>
Then pass fetchXML to RetrieveMultiple to get Data.
www.magnetismsolutions.com/.../dynamics_crm_2011_querying_data_using_fetchxml
Stay up to date on forum activity by subscribing. You can also customize your in-app and email Notification settings across all subscriptions.
#1
André Arnaud de Cal...
291,253
Super User 2024 Season 2
#2
Martin Dráb
230,188
Most Valuable Professional
#3
nmaenpaa
101,156