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

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) / Retrieving chart data ...
Microsoft Dynamics CRM (Archived)

Retrieving chart data from CRM using SDK

(1) ShareShare
ReportReport
Posted on by Community Member

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

I have the same question (0)
  • Community Member Profile Picture
    Community Member on at

    Is this question not valid at all?

  • Community Member Profile Picture
    Community Member on at

    Guys, sorry for bumping this but can anyone tell me if this is even possible? I am sure it is possible since I can retrieve the chart but where are the actual series and category values?

  • Verified answer
    Community Member Profile Picture
    Community Member on at

    For Chart visualization, we can start from exporting the chart from dynamics CRM and then Analyzing the contents of "Visualizations" in customization.xml.

    What you see in "Visualization" is that output that you will get from fetching a record of "savedqueryvisualization".

    Within Visualization Tag there is all data that is required to generate the chart.

    Information you are looking for is available in DataDescription and PresentationDescription section of the chart.

    Value of DataDescription

    <datadefinition>

       <fetchcollection>

           <fetch mapping="logical" aggregate="true">

               <entity name="incident">

                   <attribute alias="aggregate_column" name="incidentid" aggregate="count" />

                   <attribute groupby="true" alias="groupby_column" name="ownerid" />

                   <attribute groupby="true" alias="groupby_priority" name="prioritycode" />

               </entity>

           </fetch>

       </fetchcollection>

       <categorycollection>

           <category>

               <measurecollection>

                   <measure alias="aggregate_column" />

               </measurecollection>

           </category>

       </categorycollection>

    </datadefinition>

    Value of PresentationDescription

    <Chart Palette="None" PaletteCustomColors="91,151,213; 237,125,49; 160,116,166; 255,192,0; 68,114,196; 112,173,71; 37,94,145; 158,72,14; 117,55,125; 153,115,0; 38,68,120; 67,104,43; 124,175,221; 241,151,90; 186,144,192; 255,205,51; 105,142,208; 140,193,104; 50,125,194; 210,96,18; 150,83,159; 204,154,0; 51,90,161; 90,138,57;">

       <Series>

           <Series ChartType="StackedColumn" Font="{0}, 9.5px" LabelForeColor="59, 59, 59" CustomProperties="PointWidth=0.75, MaxPixelPointWidth=40"></Series>

       </Series>

       <ChartAreas>

           <ChartArea BorderColor="White" BorderDashStyle='Solid'>

               <AxisY LabelAutoFitMinFontSize="8" TitleForeColor="59, 59, 59" TitleFont="{0}, 10.5px" LineColor="165, 172, 181" IntervalAutoMode="VariableCount">

                   <MajorGrid LineColor="239, 242, 246" />

                   <MajorTickMark LineColor="165, 172, 181" />

                   <LabelStyle Font="{0}, 10.5px" ForeColor="59, 59, 59" />

               </AxisY>

               <AxisX LabelAutoFitMinFontSize="8" TitleForeColor="59, 59, 59" TitleFont="{0}, 10.5px" LineColor="165, 172, 181" IntervalAutoMode="VariableCount">

                   <MajorGrid Enabled="False" />

                   <MajorTickMark Enabled="False" />

                   <LabelStyle Font="{0}, 10.5px" ForeColor="59, 59, 59" />

               </AxisX>

           </ChartArea>

       </ChartAreas>

       <Titles>

           <Title Alignment="TopLeft" DockingOffset="-3" Font="{0}, 13px" ForeColor="0, 0, 0"></Title>

       </Titles>

    </Chart>

     

    Retrieval Logic:  (Data Description)

    Fetch XML -> You will not get the data from "savedqueryvisualization" entity but only the logic to retrieve data.

    You can use FetchXML within Datadescription and make another call to get data. 

    Axis Data Logic: (DataDescription)

    GroupBy Column -> Used in Sequence they appear in Fetch XML

    Aggregated Column -> Displayed in Count Axis

    Layout of X-Axis vs Y-Axis: (Presentation Description)

    Its defined by Chart Type

    PieChart -> No Axis

    Column, Stacked Column -> Count is Y-Axis

    Bar, StackedBar -> Count is X-Axis.

  • Community Member Profile Picture
    Community Member on at

    Hi Abhinav,

    First of all, your answer is much appreciated. Thank you!

    Below is what I get in "savedqueryvisualization"

    DataDescription:

    <datadefinition>
  <fetchcollection>
    <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>
  </fetchcollection>
  <categorycollection>
    <category alias=\"_CRMAutoGen_groupby_column_Num_0\">
      <measurecollection>
        <measure alias=\"_CRMAutoGen_aggregate_column_Num_0\" />
      </measurecollection>
    </category>
  </categorycollection>
</datadefinition>

    PresentationDescription:

    <Chart Palette=\"None\" PaletteCustomColors=\"91,151,213; 237,125,49; 160,116,166; 255,192,0; 68,114,196; 112,173,71; 37,94,145; 158,72,14; 117,55,125; 153,115,0; 38,68,120; 67,104,43; 124,175,221; 241,151,90; 186,144,192; 255,205,51; 105,142,208; 140,193,104; 50,125,194; 210,96,18; 150,83,159; 204,154,0; 51,90,161; 90,138,57;\">
<Series>
  <Series ChartType=\"Column\" IsValueShownAsLabel=\"True\" Font=\"{0}, 9.5px\" LabelForeColor=\"59, 59, 59\" CustomProperties=\"PointWidth=0.75, MaxPixelPointWidth=40\">
  </Series>
</Series>
<ChartAreas>
  <ChartArea BorderColor=\"White\" BorderDashStyle=\'Solid\'>
    <AxisY LabelAutoFitMinFontSize=\'8\' TitleForeColor=\'59, 59, 59\' TitleFont=\'{0}, 10.5px\' LineColor=\'165, 172, 181\' IntervalAutoMode=\'VariableCount\'>
      <MajorGrid LineColor=\'239, 242, 246\' /><MajorTickMark LineColor=\'165, 172, 181\' />
      <LabelStyle Font=\'{0}, 10.5px\' ForeColor=\'59, 59, 59\' />
    </AxisY>
    <AxisX LabelAutoFitMinFontSize=\'8\' TitleForeColor=\'59, 59, 59\' TitleFont=\'{0}, 10.5px\' LineColor=\'165, 172, 181\' IntervalAutoMode=\'VariableCount\'>
      <MajorTickMark LineColor=\'165, 172, 181\' />
      <MajorGrid LineColor=\'Transparent\' />
      <LabelStyle Font=\'{0}, 10.5px\' ForeColor=\'59, 59, 59\' />
    </AxisX>
  </ChartArea>
</ChartAreas>
<Titles>
  <Title Alignment=\'TopLeft\' DockingOffset=\'-3\' Font=\'{0}, 13px\' ForeColor=\'59, 59, 59\'>
  </Title>
</Titles>
<Legends>
  <Legend Alignment=\'Center\' LegendStyle=\'Table\' Docking=\'right\' IsEquallySpacedItems=\'True\' Font=\'{0}, 11px\' ShadowColor=\'0, 0, 0, 0\' ForeColor=\'59, 59, 59\' />
  </Legends>
</Chart>

    And here is the FetchXML.xml that is obtained from CRM directly.

    <fetch version="1.0" output-format="xml-platform" mapping="logical" distinct="false">
  <entity name="invoice">
    <attribute name="totalamount" />
    <attribute name="customerid" />
    <attribute name="statuscode" />
    <attribute name="name" />
    <attribute name="totaltax" />
    <attribute name="totalamountlessfreight" />
    <attribute name="discountpercentage" />
    <attribute name="discountamount" />
    <attribute name="totallineitemamount" />
    <attribute name="new_type" />
    <attribute name="new_renewal" />
    <attribute name="datedelivered" />
    <attribute name="transactioncurrencyid" />
    <attribute name="ownerid" />
    <attribute name="new_enduseraddresscountry" />
    <attribute name="new_subscription" />
    <attribute name="new_businesstype" />
    <attribute name="pricelevelid" />
    <attribute name="new_region" />
    <attribute name="new_businessunit" />
    <attribute name="invoiceid" />
    <order attribute="name" descending="true" />
    <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>
  </entity>
</fetch>

    All fine till now. However, I didn't really understand "You can use FetchXML within Datadescription and make another call to get data. "

    What would be an example call? I believe it is another Retrieve method call with different parameters but what would be the parameters based on FetchXML within DataDescription? Could you please guide me? 

    Thank you!

  • Suggested answer
    Community Member Profile Picture
    Community Member on at

    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

    msdn.microsoft.com/.../gg328117.aspx

  • Suggested answer
    Community Member Profile Picture
    Community Member on at

    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. 

  • Community Member Profile Picture
    Community Member on at

    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. 

  • Community Member Profile Picture
    Community Member on at

    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.

  • Community Member Profile Picture
    Community Member on at

    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"]);

  • Community Member Profile Picture
    Community Member on at

    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.

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

Introducing the 2026 Season 1 community Super Users

Congratulations to our 2026 Super Stars!

Meet the Microsoft Dynamics 365 Contact Center Champions

We are thrilled to have these Champions in our Community!

Congratulations to the April Top 10 Community Leaders

These are the community rock stars!

Leaderboard > 🔒一 Microsoft Dynamics CRM (Archived)

Last 30 days Overall leaderboard

Featured topics

Product updates

Dynamics 365 release plans