System report - Sales by Month showing wrong numbers

[Jakub Grufik]

Hello guys,

I am currently facing an issue where one of our users is using the system report "Sales by Month" on the dashboard and it seems like it is showing wrong numbers. For example, for May he sees €5mil and when I use exactly the same report and add it to my dashboard it shows €1mil in May..

Do you have any idea what could be causing this?

Thanks a lot for the help!

[rabii]

It is normal, this report is using withStrictAccessControl() which means it applies min access control to the current user. the current user would see the number of sales they have achieved based on the opportunities assigned to them. see the report here https://github.com/espocrm/espocrm/b...yMonth.php#L44

[Jakub Grufik]

rabii hey man thanks a lot, is there any info about conditions for records displayed in this report? for example, close date, status "Closed won" etc.? I cannot see it in the script you sent me. I need to figure out why the colleague sees 5mil EUR in the May. Thanks a lot

[rabii]

Yes in fact the report is build based on certain conditions. first thing is the date it is a report for the current fiscal year (starts from 01-01-2023 and ends at 31-12-2023) - the report fetch all opportunities that have a status (closed won) and then it gets the results of the closeDate and the amountConverted see the code below:

$whereClause = [
    'stage' => $this->util->getWonStageList(),
];

$whereClause[] = [
    'closeDate>=' => $from->getString(),
    'closeDate<' => $to->getString(),
];

$queryBuilder
    ->select([
        ['MONTH:closeDate', 'month'],
        ['SUM:amountConverted', 'amount'],
    ])
    ->order('MONTH:closeDate')
    ->group('MONTH:closeDate')
    ->where($whereClause); 

As you can see the query is built here with these conditions. Also notice that the report applied the controlAccess which means that every user will view the result of the opportunities assigned to them and also they need to have access to the amount field in the opportunity entity.

Your colleague sees 5mil EUR because he is assigned opportunities that are closed won for current year which their amountConverted total value is 5mil EUR. are you the admin of the crm correct ? keep in mind even if you are admin still the report will apply the accessControl to fetch the data.

[Jakub Grufik]

ah okay that makes sense, thanks a lot for the help mate! appreciate it

[rabii]

if you are the admin i suggest that you log into your colleague session and check what are the opportunities assigned to him to figure out what is happening.

[Jakub Grufik]

hmm that is weird, I just made quick report where I am using same condition as the system report and I am getting different numbers, maybe it is caused by amountConverted, because I am able to use only Amount in the report not the converted one, I am going to check if the amountConverted is filled incorrectly somewhere..

[rabii]

use just assignedUser and select the correct assigneduser (when using assignedUser.userName starts with the query will be different and will fetch all assigned users that starts with Dav).

[Jakub Grufik]

there is only one user with user.name starting with "david.kubacak"

[Jakub Grufik]

rabii also when I filtered out opportunities in the system and checked ammounConverted it is not 5mil EUR not even close.. I dont know where the system report is getting those numbers

[Jakub Grufik]

When I added stage Closed Won, it returned just two record with SUM: amountConverted 43kEUR...weird

[rabii]

but you have set closeDate to last month only

[Jakub Grufik]

because the system report Sales by month shows wrong numbers in May, which is last month

[rabii]

what your base currency in the system ? do you use only one or multiple currencies ? if yes check out the conversion rate

[Jakub Grufik]

we are using CZK and EUR, conversion rate is 0.04EUR = 1 CZK

[rabii]

i just noticed that the report maybe is wrong only for the label hence the other user use w different language. the chart is correct in both pic you showed there to make sure can you chnage the language of the other user to english and refresh to see if the misatek will be fixed ?

[Jakub Grufik]

sure thing, on it

[Jakub Grufik]

rabii nah the report still shows 5mil EUR in May even in English language (I tried refresh and also add report to the dashboard again after the change)

[rabii]

check the date filter of the dashlet ?

[Jakub Grufik]

Current year, all good. Same numbers in May even when I changed it to Current quarter

[rabii]

that is weird - i can think of any other thing. create a report for david and get sum of all amount no filter

[Jakub Grufik]

no filter, just assignedUser David

[rabii]

You can see that there is only 80K in May 2023.

I have tried all these scenarios on my local dev and it works as expected. which version you are using ?

[Jakub Grufik]

Current version: 7.3.3
Advanced Pack 2.13.9
Reports, Workflows, BPM.


Google Integration 1.5.5
Google Calendar and Google Contacts integrations. Gmail secure authentication.