Reports

SuiteCRM Forum - English Language SuiteCRM General Discussion
#1

the reports don’t seem to work the way i guessed they should. firstly, there is no comprehensive documentation for it what is the Accounts : Accounts mean…

and when i group or employ a function in the report it just goes blank…

any help?

#2

Are you using SuiteCRM Reporter or another tool?

#3

Yes the built in Reports module. I have not found any documentation for it.

#4

In Version 7.1.2 Max of Suite CRM
running on mssql on IIS7 the inbuilt reports appear to be broken whenever a group is selected.

here is my error from the error log when setting up simple Opp x User Report.

Query Failed:SELECT TOP 20 * FROM
                                (
                                    SELECT opportunities.id AS N'opportunities_id', opportunities.assigned_user_id AS N'Assigned_to0', COUNT(opportunities.id) AS N'ID1' , ROW_NUMBER()
                                    OVER (ORDER BY opportunities.assigned_user_id ASC) AS row_number
                                    FROM opportunities  WHERE opportunities.deleted = 0  GROUP BY opportunities.assigned_user_id 
                                ) AS a
                                WHERE row_number > 0::: [Microsoft][SQL Server Native Client 10.0][SQL Server]Column 'opportunities.id' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

If anyone could help this would be appreciated.

#5

Hi - we’re just evaluating SuiteCRM alongside Sage CRM and are experiencing the same problem. Wondered if anybody had found a fix / confirmed it as a bug yet? I’m guessing it’s a problem isolated to IIS/MSSQL implementations, as the demo site at suitecrm.com seems to work fine.

We’re running PHP 5.4, IIS 7, MSSQL 2005. We’ve tried updating to PHP 5.5 and 5.6 in case it was a MSSQL-PHP driver thing, but the database throws a ‘Can’t Connect’ error before we get that far, so that’s a none starter.

It does seem to be a syntax error in the SQL generated by the Report builder. Our log looks like this:

Query Failed:SELECT [cases].id AS N’cases_id’, COUNT([cases].assigned_user_id) AS N’Number_of_Cases0’, [cases].assigned_user_id AS N’Assigned_to1’ FROM [cases] WHERE cases.deleted = 0 GROUP BY [cases].assigned_user_id::: [Microsoft][SQL Server Native Client 11.0][SQL Server]Column ‘cases.id’ is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

The SQL query is indeed invalid, I would guess it needs to be something like

SELECT
	COUNT(*) c
FROM (SELECT
	-- [cases].id AS N'cases_id', <----------- this bit is wrong?
	COUNT([cases].assigned_user_id) AS N'Number_of_Cases0',
	[cases].assigned_user_id AS N'Assigned_to1'
FROM [cases]
WHERE [cases].name = N'*test*'
AND cases.deleted = 0
GROUP BY [cases].assigned_user_id) AS n