help with report sql

[rslemer]

I need create a sql report, and try this command


SELECT sum( vtiger_cobropago.amount) as 'Soma Valor', sum(vtiger_cobropago.cost) as 'Soma Custos' , sum(vtiger_cobropago.benefit) as 'Soma Liquido',
COALESCE(CONCAT(vtiger_users.first_name,vtiger_users.last_name),vtiger_groups.groupname)
FROM vtiger_cobropago INNER JOIN vtiger_crmentity ON vtiger_cobropago.cobropagoid = vtiger_crmentity.crmid LEFT JOIN vtiger_users ON vtiger_crmentity.smownerid = vtiger_users.id LEFT JOIN vtiger_groups ON vtiger_crmentity.smownerid = vtiger_groups.groupid WHERE vtiger_crmentity.deleted=0 AND (( vtiger_crmentity.modifiedtime BETWEEN '2020-01-01 00:00:00' AND '2020-01-31 23:59:59') AND (( vtiger_cobropago.paid = '1') )) AND vtiger_cobropago.cobropagoid > 0 group BY COALESCE(CONCAT(vtiger_users.first_name,vtiger_users.last_name),vtiger_groups.groupname)


Working in sql directly command, in mySQL but in SQL reports, not

I think problem is with a user name

How i can solved that?

Thanks!

[joebordes]

in this blog post:  http://blog.corebos.org/blog/reports02

it says that you must:

• each column must have an alias that is the module name followed by the field label with all spaces substituted by underscores.
  
• The last column must be the crmid of the record of the row with an alias of ACTION
  
• you must select the main module of the SQL command when creating the report in order to support ad-hoc conditions
  
• you cannot use conditions on groups (having)
  

You are missing the "ACTION" condition. I added

Code:

, 1 as 'ACTION'

and it worked

[rslemer]

I was unable to make an inner join with the cbemployee table to get some data from there

and it would be interesting for the actions to take there

because if you put the standard it will give error, since it is not a record but a total

how could you do that?

---

I meas my ideia is saved a goal of month for each user in Employees module

And calculate a % of this goal, with sum of payments marked as paid

action should be open a registry in employees ...

[rslemer]

in payments I have tree user fields

created by
related user
assigned to

I do a link with modules, but user in modules not returned right

---

this command appears  something wrong, field at cbemployees return null
SELECT vtiger_cobropago.reference, vtiger_cobropago.cyp_no, vtiger_cobropago.amount, vtiger_cobropago.cost, vtiger_cobropago.benefit, vtiger_cobropago.paid, vtiger_crmentity.smownerid, vtiger_cbemployeeparent_id.userid as cbemployeeuserid, vtiger_cobropago.comercialid, vtiger_crmentity.smcreatorid, vtiger_cbemployeeparent_id.nombre as cbemployeenombre, vtiger_crmentityparent_id.smownerid as smownercbemployee, vtiger_cobropago.cobropagoid FROM vtiger_cobropago INNER JOIN vtiger_crmentity ON vtiger_cobropago.cobropagoid = vtiger_crmentity.crmid LEFT JOIN vtiger_users ON vtiger_crmentity.smownerid = vtiger_users.id LEFT JOIN vtiger_groups ON vtiger_crmentity.smownerid = vtiger_groups.groupid LEFT JOIN vtiger_users AS vtiger_usersreports_to_id ON vtiger_cobropago.comercialid = vtiger_usersreports_to_id.id LEFT JOIN vtiger_groups AS vtiger_groupsreports_to_id ON vtiger_cobropago.comercialid = vtiger_groupsreports_to_id.groupid LEFT JOIN vtiger_users AS vtiger_userscreated_user_id ON vtiger_crmentity.smcreatorid = vtiger_userscreated_user_id.id LEFT JOIN vtiger_groups AS vtiger_groupscreated_user_id ON vtiger_crmentity.smcreatorid = vtiger_groupscreated_user_id.groupid LEFT JOIN vtiger_cbemployee AS vtiger_cbemployeeparent_id ON vtiger_cbemployeeparent_id.cbemployeeid=vtiger_cobropago.parent_id LEFT JOIN vtiger_crmentity AS vtiger_crmentityparent_id ON vtiger_crmentityparent_id.crmid=vtiger_cobropago.parent_id WHERE vtiger_crmentity.deleted=0 AND vtiger_cobropago.cobropagoid > 0 ORDER BY CONCAT(vtiger_usersreports_to_id.first_name,' ',vtiger_usersreports_to_id.last_name) ASC

[joebordes]

I do not see what is wrong. what is not right?

[rslemer]

All values in employee not return a any value in sql or in filter

example, assigned to in payments is the same in employee and my filter listing field both modules, but system returns only real values for one module ...

in image user, and valor considerado para meta return null, in query, but not correct...

correct user should be "João", "Maria", etc ... and in sql/filter always returned null

Perhaps, key of employes not a employeeid but is user (?)

[joebordes]

I don't really understand what you are doing. If you want to match on assigned user on both modules, then compare the smownerid, not the parent_id
Sorry.

[rslemer]

Exactly, but this sql is generated by listview, (debug)

[joebordes]

yes, list view relates using the relation field, not any other relation that you may want to establish

[rslemer]

understand ...

I think assigned to in payments is userid and employes has a userid too

this key is possible, I created my own sql for reporting

You thing is possible?