locked
Dashboard charts (KPIs) based on another currency (not the base currency) RRS feed

  • Question

  • Hello,

    Concerning the Dashboards of Dynamics CRM 2011, is there a way to create a funnel Pipeline with another currency besides the base one?

    I can't figure how to do this by using the out-of-the-box chart designer... Any ideas?

    P.

    Tuesday, April 19, 2011 4:31 PM

Answers

  • The chart Xml embeds a standard CRM fetch Xml (http://msdn.microsoft.com/en-us/library/bb930489.aspx) - so you would add a condition node in the same way as you would do for a standalone fetch Xml.

    To obtain the exact condition node, you may do the following:

    • Launch Advanced Find for the corresponding entity.
    • Create a query by setting an appropriate condition on the currency field.
    • Click on Download Fetch Xml on the ribbon.

    You will find a filter node in the fetch Xml as follows.

    <filter type="and">
          <condition attribute="transactioncurrencyid" operator="eq" uiname="US Dollar" uitype="transactioncurrency" value="{2A61592A-2A6F-E011-B00B-00155D2E2C01}" />
    </filter>

    You would then need to add the above filter node just under the 'entity' node in the fetch Xml that was embedded in the chart definition. And import back the chart Xml.

    Can you describe your chart in detail? If a chart shows blank, that means data is not filled in for the groupby field. This has nothing to do with currency.

    Regarding your question on forcing a currency for the chart - no, that is not possible. A chart can be seen either in the base currency (for base currency fields) or in the user-preferred currency (for non-base currency fields).

    -Arko.

    • Marked as answer by pmdci Friday, April 29, 2011 1:48 PM
    Friday, April 29, 2011 6:35 AM
  • I opened a call with Microsoft do discuss weird chart behaviours, and this particular issue has been confirmed to be a bug, which is scheduled to be fixed with update rollup 4.

    This happens when the exchange rate is lower than 0.5. Setting an exchange rate equal or higher than 0.5 tends to "sort out" the issue (well, the chart shows, but not really a workaround, isn't it?)

    • Marked as answer by pmdci Tuesday, July 26, 2011 10:29 AM
    Wednesday, June 29, 2011 2:33 PM

All replies

  • Yes, it is possible to create the kind of chart you mention - the CRM 2011 charting infrastructure lets you aggregate on non-base currency fields.

    The non-base currency fields do show up in the chart designer - can you be more explicit about the kind of problems you are facing?

    You should keep in mind that the data in non-base currency fields can be in various transaction currencies. So if you select one such field as your Y-axis:

    • Behind the scenes, the aggregation is actually done on the base currency field.
    • The result is converted into the user's preferred currency (one that you set in your personal options), based on the current exchange-rate.

    -Arko.

    Wednesday, April 27, 2011 2:53 PM
  • Arko,

    Thank you for the reply!

    Basically , the idea is that I can allow my European Sales team to visualise their reports and KPIs with their local currency.

    They basically work with two currencies: UK Pounds Sterling and Euro.

    I would like to show four different Sales Pipeline Funnels for the EMEA team. These are:

    1. Only opportunities with Pounds as the currency - To be shown in the funnel in Pounds
    2. Only opportunities with Euro as the currency - To be shown in the funnel in Euros
    3. All EMEA opportunities (regardless if in Pounds or in Euros) - To be shown in the funnel in Pounds
    4. All EMEA opoprtunities (regardless if in Pounds or in Euros) - To be shown in the funnel in Euros

    As for reports, as a start I would like to add a dropdown for the user to select the currency in which to show the report. I understand that the currency conversion will be done based on the exchange rate registered on the CRM (that is the idea).

    Cheers,
    P.

    Wednesday, April 27, 2011 3:05 PM
  • Okay, I understand your requirements.

    There are two things that need to be mentioned here:

    1. The currency in which the chart will be shown is not defined at design-time, and is not part of the chart definition. Instead, this is dynamically selected at runtime as the preferred currency of the user viewing the chart. A user can change her preferred currency in her personal options, and the chart will change accordingly.
    2. Charts (1) and (2) are aggregations on filtered data (only records of a particular currency). Such charts cannot be created in the designer, but it is possible to create them by directly importing a chart Xml. Consider chart (1) - you can create it in the following way.
    • Create a chart in the designer for all opportunities.
    • Export out the chart.
    • Modify the fetch Xml in the exported chart Xml by adding a filter node with an appropriate condition on the transactioncurrencyid field.
    • Import the modified chart Xml. Choose the option to replace the existing chart.

    Another option is to have a grid of records on the dashboard, and use the charts pane that comes by default alongside the grid. The data shown in these charts is always filtered based on the current view in the grid. So you would need to have two grid-views - one for Pounds, the other for Euros. As you change the grid view, the chart will refresh automatically.

    Hope this helps.

    -Arko.

     

    Wednesday, April 27, 2011 4:46 PM
  • Arko,

    Thank you for the reply.

    What I am planning to do is to get the default sales pipeline chart and export it with another name. Then modify it to include the condition for the transactioncurrencyid field. How do I do this condition? Is there a documentation somewhere?

    Something weird: One thing I noticed is that when I choose a default currency for my user, the Sales Pipeline funnel graph shows as blank (!?)

    Question: Is there any way that I can FORCE the currency for a given chart, instead of being dinamicaly specified?

     

    Wednesday, April 27, 2011 5:40 PM
  • The chart Xml embeds a standard CRM fetch Xml (http://msdn.microsoft.com/en-us/library/bb930489.aspx) - so you would add a condition node in the same way as you would do for a standalone fetch Xml.

    To obtain the exact condition node, you may do the following:

    • Launch Advanced Find for the corresponding entity.
    • Create a query by setting an appropriate condition on the currency field.
    • Click on Download Fetch Xml on the ribbon.

    You will find a filter node in the fetch Xml as follows.

    <filter type="and">
          <condition attribute="transactioncurrencyid" operator="eq" uiname="US Dollar" uitype="transactioncurrency" value="{2A61592A-2A6F-E011-B00B-00155D2E2C01}" />
    </filter>

    You would then need to add the above filter node just under the 'entity' node in the fetch Xml that was embedded in the chart definition. And import back the chart Xml.

    Can you describe your chart in detail? If a chart shows blank, that means data is not filled in for the groupby field. This has nothing to do with currency.

    Regarding your question on forcing a currency for the chart - no, that is not possible. A chart can be seen either in the base currency (for base currency fields) or in the user-preferred currency (for non-base currency fields).

    -Arko.

    • Marked as answer by pmdci Friday, April 29, 2011 1:48 PM
    Friday, April 29, 2011 6:35 AM
  • Arko,

    Thank you for the reply. It certainly helps me a lot!

    As for the blank charts, I am looking at the default sales pipeline chart and I really think it is a bug.

    When I don't choose a default currency, the system shows the pipeline chart without any issues. If I choose my base currency as the default currency for my user, it also works without any issues.

    However if I choose either UK Pounds or Euro, the chart disappears! The labels for my pipeline phases do appears at the bottom of the chart, but the funnel itself doesn't show!

    If I hover the mouse over the chart and I click on the list icon that appears on the top right (that is, to list the records that comprise the chart), IT WILL give me a list of records which should be showing.

    TO MAKE MATTERS WORSE, if I choose US Dollar, the chart does show the pipeline funnel. However, it only changes the currency sign but the values are the same. For instance if I had 55,000 in my base currency in my Prospect Phase, it will show 55,000 in US Dollars - thus not considering an exchange rate of about 0.65

    Weird uh? Sounds like a bug to me...

    • Edited by pmdci Friday, April 29, 2011 12:12 PM I think I found bugs...
    Friday, April 29, 2011 12:08 PM
  • Hi,

    Concerning your last problem with the exchangerate, I'am experiencing the same thing and has now concluded that when the system presents the dashbords, it uses the currency exchangerate at precision=0, even though the precision on the currency in my system is set to 3. On the specific entity that I am showing, the baseamount (and exchangerate) is correctly set and calculated using the exchangerate at precision=3.

    I guess that in your example then exchangerate is rounded to 1.00 and that this is a systembug

    JV

     

    Thursday, May 12, 2011 6:04 AM
  • Hi!

    Thanks for your input on this. I raised a "bug" with Microsoft at Microsoft's Connect here: https://connect.microsoft.com/dynamicssuggestions/feedback/details/665233/bug-sales-pipeline-dashboard-funnel-graph-not-showing-when-setting-default-currency

    I'd appreciate if you can provide your input following the link above.

    Regards,

    P.


    Friday, May 13, 2011 12:25 PM
  • I opened a call with Microsoft do discuss weird chart behaviours, and this particular issue has been confirmed to be a bug, which is scheduled to be fixed with update rollup 4.

    This happens when the exchange rate is lower than 0.5. Setting an exchange rate equal or higher than 0.5 tends to "sort out" the issue (well, the chart shows, but not really a workaround, isn't it?)

    • Marked as answer by pmdci Tuesday, July 26, 2011 10:29 AM
    Wednesday, June 29, 2011 2:33 PM