Reports issue - incorrect summation & conversion?

Hello, 

I have created a few custom formula fields on Opportunity and Revenue Line Items that allows me to have a more granular view on the ARR. 

The fields on the Revenue Line Item work perfectly, also in respect to conversions. Fields on Opportunity are simply the summation of their respective fields from RLI.

The issue begins when I run reports, e.g. summation report that shows both revenue line item fields & sum for opportunity

1.  I created the Opp & RLI in GBP -  on the Opp & RLI I can see the conversion from GBP to USD (preferred currency) - why can't I see that on the report, and it is only showing me GBP? Do I need to create separate (hidden) USD only fields for reporting purposes?

2. Field summation is totally off - I chose to sum from opportunities fields (the reason for that is that in te future I want to also run reports only with Opportunity details and ARR sums, without each RLI listed) - they are represented in USD, which is correct, but the sums are wrong - e.g. the TCV for Test Opp for currencies is, $71,724.70, however the report is showing $143,449.41 (double the amount that is TCV on the opp) - how can I prevent it? 

The sum is correct only when I have 1 revenue line Item. 

  • Oh wow we are experiencing the same in Sugar Sell, our partner is investigating this at the moment.  However what we think might be happening in our case is the SUM;Revenue is using the current exchange rate and then displays the converted amount with the wrong currency indicator. (Known bug re:display - portal.sugarondemand.com/index.php)

    However my concern is if I'm a user based in US with my preferred currency as $ (default currency is Euros)  I don't want the SUM:Revenue to display as a conversion, I want to see the sum of the RLI in my preferred currency i.e. $ with no conversion.

    This only happens when I update the Currency rate.  I set the currency back to what it was and it reverts the SUM:Revenue back to the expected value.  Have you recently updated your currencies?

  • Hey Samantha, 

    Thank you for your reply.

    Interesting - I tested my reports in Sandbox and the error still occurs - I did not recently update currencies there. 

    Do you also have an issue that if something is not in your preferred currency on the opp, it also won't show like that on the report?

    However my concern is if I'm a user based in US with my preferred currency as $ (default currency is Euros)  I don't want the SUM:Revenue to display as a conversion, I want to see the sum of the RLI in my preferred currency i.e. $ with no conversion.

    --> Every user can set up their preferred currency individually, but I am not sure how that affects reports.

  • Hi Ada

    Whatever the user has chosen as their preferred currency is the currency they will view reports and dashboards in.  Our partner has now raised this with Sugar as the core out of the box version seems to be displaying the SUM:Revenue in reports as a currency conversion?

    I'll keep you posted Slight smile

  • Hi Samantha, 

    I am still experiencing the issue with currencies - both in sandbox and production. 

    Was you partner able to investigate this further?

    Thanks,

    Ada

  • Hi Ada

    They found that this is also in the Core System (OOTB) so they have raised a ticket to understand why this might be have been developed or if it is an expected behaviour?

    When we've heard back from Sugar I will add to here Slight smile

    Thanks

    Sam

  • Summations look quite Okay on the regular demo instance - below is the screenshot of the report built for the RLI module - looks like the sum of addendums matches with the value provided in the row header, right?


    Having that all the reports are actually based on regular SQL queries, the question is what is the criterion used specifically in your report and what is the module the report is built for. Also in rare cases, the customizations made to the modules may lead to the reports unexpected behavior

    In regards to the currencies- if user switches the Preferred Currency from the base currency (e.g. from $ to EUR  in this example) and set a checkbox in the profile to show values in the preferred currency , then all the values will go displayed in EUR and the same report will look like this:



    IMHO, despite some visual discrepancies, that visualization makes sense, so perhaps, that should be considered "as designed"

    I hope this helps

    Best Regards,
    Dmytro Chupylka

    integroscrm.com
    We make work in Sugar CRM system faster, more convenient and efficient

  • Hi Dymtro

    SugarCRM support have confirmed that there is a bug with reporting showing the incorrect currency value when a user has a different preferred currency to default.  Unfortunately no time frame as yet on fix. 

    I hope it is quick though as a global CRM any issues with reporting from multiple currencies could be detrimental to teams and business!

    Thanks

    Sam

  • Hi Samanta,

    Thank you for the information - some visualization discrepancies are usually a thing that corporate system users could get used to in their internal reports -  as you can find on the screenshots above reports a fairly readable in different currencies

    At the same time, correct calculations are important in terms of the trust in the reports engine, so I wonder if support was able to confirm that the calculations (inc. summation) are correct for a properly set up report. 

    Best Regards,
    Dmytro Chupylka

    integroscrm.com
    We make work in Sugar CRM system faster, more convenient and efficient

  • Hi Dymtro

    In our instance it triggers these inconsistencies when you update a currency.  In this example the £ exchange rate has has changed from 0.871 to 0.671.-  My preferred currency is £.  When I view this report which is a simple SUM: Revenue by Sales person the system recalculates the SUM.

    £27,878.50 / 0.871 x 0.671 = £21,477.01

    But my RLI is still being charged at £27,878.50, the total in £ for this users pipeline is still £27,878.50.

    As a user my totals are displaying less than what is actually coming in on invoice.

    And this doesn't happen to all sum totals, its very inconsistent.

  • @ada Napierala, You have now no longer performed whatever is incorrect; you simply have now no longer given whole attention to the problems. Each of them additionally may be approached with the use of the foundation test, with the identical consequences for r. Since the trouble requested for all solutions that make it true, you're incorrect via way of means of omission

    As long as it isn't mixed with the conventional total motion it's correct. The procedure for Cesaro summation is outlined correctly.

    It extends the standard summation (if the normal sum exists the Cesaro sum exists furthermore and is capable of the usual series sum). It lacks some smart properties of the common infinite sums, however, could be a comparatively conservative extension. For example, the Cesaro sum lies between the lower and higher limit of partial sums. therefore if the normal sum diverges to infinity, the Cesaro sum will it as well.

    I just googled it to provide the relevant information,  I hope my answer helped, and if so, please upvote.

    Also, to find more info on other IT-related training, please reach me here>>