Creating KPI report facing challenges in static columns

[james salvan]

I have report advance pack, I have tried creating joint grid report and grid report, for KPI sales, Like I am using the Financial Year : close date + Assigned User as a filter in opportunity to get close date and stage won, Year wise report thats good

the problem is we need a report like:

Account Manager | Target | April | May | June | July | ............... | Achieved
AssignedUserName | 200000 | 20000| .... | 200000
AssignedUserName2 | 400000 | 20000| .... | 100000

where Target is currency amount static that is decided in the start of financial year

is there any way to may this happen ?

[victor]

Provided that each record in the Opportunity entity contains an Asigned User, Amount and Monthly plan (a fixed amount for 1 month, which is indicated in each Opportunity), it is possible to display planned and actual indicators within the framework of one Grid Report.

[james salvan]

For Understanding, I have created new entity, to store Financial Year wise targets for the Users




This are static for whole financial year


in report by using grid reports or joint grid reports. where I combine opportunity and KPI(custom entity) reports
To get



is this feasible, formula isn't working if it works in sandbox tried. you are help so valuable.

[dreginald]

I have report advance pack, I have tried creating joint grid report and grid report, for KPI sales, Like I am using the Financial Year : close date + Assigned User as a filter in opportunity to get close date and stage won, Year wise report thats good

the problem is we need a report like:

Account Manager | Target | April | May | June | July | ............... | Achieved
AssignedUserName | 200000 | 20000| .... | 200000
AssignedUserName2 | 400000 | 20000| .... | 100000

where Target is currency amount static that is decided in the start of financial year

is there any way to may this happen ?

I suggest you create a separate entity eg. "Sales Performance" and map the values from Opportunity or Sales Order or Invoice to this entity and create a report.

I am attaching the screenshot of a report created with Espo Version 6.2 five years ago and is still the key KPI report.

[james salvan]

I've created custom entity named KPI Sales. with targets for FY year and month but I can't figure out how to map opportunities or invoices to it and make report

[victor]

james salvan,

I've created custom entity named KPI Sales. with targets for FY year and month but I can't figure out how to map opportunities or invoices to it and make report

1. Create similar fields in Opportunity entity (as in KPI Sale) to make it easier for you to create Report.
2. Create relationship between KPI Sale and Opportunity entity (One-to-Many).
3. In Administration > Entity Manager > Opportunity > Formula > Before Save Custom Script create formulas for each field from step 1. For example,

kpiJuneTargetConverted = kPISale.juneTargetConverted;

For new records, the formula will work automatically, and for old ones, use Opportunity List view -> Actions > Recalculate Formula.

Thus, in the fields from point 1 in all Opportunities (which are interconnected by KPI Sales) there will be correct data (assigned using formulas from KPI Sale to Opportunity).

You can also use my first simpler option. In that option, instead of the KPI Sale entity, we immediately enter the planned indicators in each of the Opportunities.

[james salvan]

By Create similar fields in Opportunity entity (as in KPI Sale), that doesn't break the Opportunity entity ? and one more thing can we import and export entity to other espocrm instances.

[victor]

By Create similar fields in Opportunity entity (as in KPI Sale), that doesn't break the Opportunity entity?

Of course, no.

can we import and export entity to other espocrm instances

Of course, yes. There is a special official free extension for this: https://docs.espocrm.com/extensions/...port/overview/.