Details about this thread
  • State Verified Answer
  • Replies 6 replies
  • Answers 1 answer
  • Subscribers 314 subscribers
  • Views 1710 views
  • Users 0 members are here
Related content from around SugarClub

Report on reports

Hugo Geyskens

Hi there,

I'm asked to take up the global support for SugarCRM for my company, and one of the first tasks is to clean up the wild growth on individual reports we have in the meantime (644 reports listed).

So I wanted to make a report on these reports : who is creating them , how many created per user, what modules are most popular to be reported upon , etc....

But... when you start a new report, you can choose from any module...except Reports ! ;-)

Does anybody has a good tip how to achieve this ? Reporting on reports ?

Parents
  • 0

    Hi Hugo Geyskens,

    Are you hosting your SugarCRM instance on-premise? If so, you can query the database directly to find this information. If you are on SugarCRM Enterprise or above, then you can also use advanced reporting to similarly query the database. 

    Here is an example of a simple query you might use to find this information:

    select r.name as "Report Name", r.module, r.date_entered as "Date Created", u.user_name as "Assigned to User"
    from saved_reports r
    join users u on r.assigned_user_id = u.id
    order by u.user_name, r.date_entered;

    Please let us know if you have additional questions. 

    Nathaniel

  • 0 in reply to Nathaniel Ray

    The report_cache table can also be helpful for finding out how often a report is being used. The table stores information on a per user basis of the last time the user accessed a report as well as the last run time filters applied to the report and the last expanded/collapsed state of panels on the report. The date_modified field gives you the date the user last accessed the report. In the past, I have run queries against this table to give me a list of all records for a given report id. That allows me to determine how many people are using the report and the last time anyone accessed it.

    select * from report_cache where id = '<report_id>' order by date_modified desc
  • 0 in reply to Brett Zufelt

    These are all very helpful.  Thank you for the tips.  Still, this should be a delivered feature since SugarCRM is no longer open source.  Everyone suffers from report proliferation.

  • 0 in reply to Bud Conlin

    Here's a SQL showing last run date from reports_cache and includes data from report_schedules.  I believe it's valid....improvements welcomed.  Does not capture report charts that are used in Dashboards.

    SELECT u.user_name as 'Last Run By', sr.name as 'Report Name', sr.id as 'Report ID', rc.date_modified as 'Last Run Date', sr.date_entered as 'Report Create Date', sr.date_modified as 'Report Modified Date', sr.module as 'Report Module', u1.user_name as 'Report Created By', u2.user_name as 'Report Modified By', u3.user_name as 'Report Assigned To', rs.next_run as 'Next Scheduled Run'
    FROM saved_reports sr
    INNER JOIN report_cache rc ON (rc.id = sr.id)
    INNER JOIN users u ON (rc.assigned_user_id = u.id)
    INNER JOIN users u1 ON (sr.created_by = u1.id)
    INNER JOIN users u2 ON (sr.modified_user_id = u2.id)
    INNER JOIN users u3 ON (sr.assigned_user_id = u3.id)
    LEFT OUTER JOIN report_schedules rs ON (sr.id = rs.report_id)
    WHERE sr.deleted = 0 AND rc.deleted = 0
    ORDER BY rc.assigned_user_id, rc.date_modified desc

Reply
  • 0 in reply to Bud Conlin

    Here's a SQL showing last run date from reports_cache and includes data from report_schedules.  I believe it's valid....improvements welcomed.  Does not capture report charts that are used in Dashboards.

    SELECT u.user_name as 'Last Run By', sr.name as 'Report Name', sr.id as 'Report ID', rc.date_modified as 'Last Run Date', sr.date_entered as 'Report Create Date', sr.date_modified as 'Report Modified Date', sr.module as 'Report Module', u1.user_name as 'Report Created By', u2.user_name as 'Report Modified By', u3.user_name as 'Report Assigned To', rs.next_run as 'Next Scheduled Run'
    FROM saved_reports sr
    INNER JOIN report_cache rc ON (rc.id = sr.id)
    INNER JOIN users u ON (rc.assigned_user_id = u.id)
    INNER JOIN users u1 ON (sr.created_by = u1.id)
    INNER JOIN users u2 ON (sr.modified_user_id = u2.id)
    INNER JOIN users u3 ON (sr.assigned_user_id = u3.id)
    LEFT OUTER JOIN report_schedules rs ON (sr.id = rs.report_id)
    WHERE sr.deleted = 0 AND rc.deleted = 0
    ORDER BY rc.assigned_user_id, rc.date_modified desc

Children
No Data