Thread Details
  • State Not Answered
  • +1 person also asked this people also asked this
  • Replies 1 reply
  • Subscribers 372 subscribers
  • Views 46 views
  • Users 0 members are here
Forum Actions
Related

Dashboard > Dashlet > Report

Taufique Ahmed

Hi all Slight smile

We have 250+ dashboards and 13,000+ reports in our SugarCRM instance

I have been asked to cleanse reports, however, we are struggling to identify the reports linked to dashboards

Currently we are painfully walking through each dashboard and collecting each report URL

Just wanted to check if there is anything in the CRM file/database or anything where I can get

Dashboard ID

- Report ID

- Report ID

etc etc

Parents
  • 0 Sugar Employee Badge

    Hello  , 

    Thanks for the question.

    The information you’re looking for is stored in the dashboards table in the database.
    Each time a report is used on a dashboard, Sugar stores it as a report-dashlet dashlet type and saves that information in the metadata column for the corresponding dashboard.

    SELECT
  d.id   AS dashboard_id,
  d.deleted AS deleted,
  d.name AS dashboard_name,
  jt.ord AS dashlet_index,
  jt.dashlet_id,
  jt.report_id,
  jt.label AS dashlet_label,
  jt.type  AS dashlet_type,
  jt.module AS dashlet_module
FROM dashboards d
JOIN JSON_TABLE(
  d.metadata,
  '$.dashlets[*]'
  COLUMNS (
    ord        FOR ORDINALITY,
    dashlet_id VARCHAR(64)  PATH '$.id',
    report_id  VARCHAR(64)  PATH '$.view.reportId',
    label      VARCHAR(255) PATH '$.view.label',
    type       VARCHAR(64)  PATH '$.view.type',
    module     VARCHAR(64)  PATH '$.view.module'
  )
) jt
WHERE jt.report_id IS NOT NULL
ORDER BY d.name, jt.ord;

    I tested this locally and was able to retrieve a list of dashboard names and IDs, along with the corresponding report dashlets on each one, using the query below.
    The query returns one row per dashlet_id, so dashboard will be repeated when a dashboard contains multiple report dashlets.
    This makes it easy to export the results to CSV and slice and dice the data to understand which reports are safe to delete.

    Let me know if this information is what you were looking for.

    Wishing you a great start to the year!

    Cheers,

    André




Reply
  • 0 Sugar Employee Badge

    Hello  , 

    Thanks for the question.

    The information you’re looking for is stored in the dashboards table in the database.
    Each time a report is used on a dashboard, Sugar stores it as a report-dashlet dashlet type and saves that information in the metadata column for the corresponding dashboard.

    SELECT
  d.id   AS dashboard_id,
  d.deleted AS deleted,
  d.name AS dashboard_name,
  jt.ord AS dashlet_index,
  jt.dashlet_id,
  jt.report_id,
  jt.label AS dashlet_label,
  jt.type  AS dashlet_type,
  jt.module AS dashlet_module
FROM dashboards d
JOIN JSON_TABLE(
  d.metadata,
  '$.dashlets[*]'
  COLUMNS (
    ord        FOR ORDINALITY,
    dashlet_id VARCHAR(64)  PATH '$.id',
    report_id  VARCHAR(64)  PATH '$.view.reportId',
    label      VARCHAR(255) PATH '$.view.label',
    type       VARCHAR(64)  PATH '$.view.type',
    module     VARCHAR(64)  PATH '$.view.module'
  )
) jt
WHERE jt.report_id IS NOT NULL
ORDER BY d.name, jt.ord;

    I tested this locally and was able to retrieve a list of dashboard names and IDs, along with the corresponding report dashlets on each one, using the query below.
    The query returns one row per dashlet_id, so dashboard will be repeated when a dashboard contains multiple report dashlets.
    This makes it easy to export the results to CSV and slice and dice the data to understand which reports are safe to delete.

    Let me know if this information is what you were looking for.

    Wishing you a great start to the year!

    Cheers,

    André




Children
No Data