ReportGrid

[davidwatkins73]

Idea is to allow users (or admins) to define reports similar to:

App	Asset Code	Measurable A	Measurable D	Assessment A	Assessment T	...
Zebra	a001	Invest	Poor	Complies	PASS	...

As can be seen, the columns can refer to multiple measurables (potentially from different categories) and my also include datapoints such as assessments. We may also include things suce as is_retiring, complexity_score etc.

[davidwatkins73]

Quick notes from chat with RG:

Summary view, more computed datapoints such as complexity.
Summary view should look to make a judgement call which is shown and also can be included in the grid report.
Rules which allow migration vetos such as `hasMainframe` 

[davidwatkins73]

• Fetch list of grid reports and allow user to chose which one to show (and cancel / go back to list)
  • remember last choice (local storage and default to that when showing widget on new pages)
    • First time users should see grid picker as default
    • make sure gracefully handles report being removed (e.g. last report was 32, which then gets removed)
• Figure out how to make the grid 'bigger' when lots of data (avoid letterbox effect)
• Summary stats
  • Perhaps have some cols defined as headlines which appear as a chart in the summary.
  • Perhaps each report should be 2 sections: grid | summary (tabbed or stacked or something?)
• Investigate option to color background of entire row (i.e. select a column as 'dominant')
  • suggested solution
• non rating based cells (i.e. complexity counts..., inbound/outbound flow counts)
• needs hover-over cell / col detail. (e.g. show measurable description and rating description)
• How to deal with non-leaf measurables
  • possibly need an aggregation rule on the report_grid_column ? (i.e. PRIORITY_ASC | PRIORITY_DESC )
  • poc
  • add to ddl and formalise
  • ensure grid condition is added
  • profile / queryplan
  • perhaps hoverover could show a mini tree view with ratings below the node.....
• Export to xlsx/csv
• Ensure filters are cleared when switching reports
• Retain element in sumamry, even is count is zero (bonus: show raw count as well as filter count)
  • Possibly allow ORing by selecting multiple items in a summary
• hover over to see comment (and somehow flag cell that there is 'more' data to be seen)

[davidwatkins73]

insert into report_grid(id, name, description, last_updated_by, external_id)
values (1, 'Cloud Readiness', 'How ready are we to go to the cloud?' , 'admin', 'CLOUD_READINESS_GRID');

update assessment_definition
set external_id = 'LEGAL_HOLD_STATUS'
where name = 'Legal Hold Status';

insert into report_grid_column_definition(report_grid_id, column_entity_kind, column_entity_id)
select (select id from report_grid where external_id = 'CLOUD_READINESS_GRID'),
       'MEASURABLE',
       id
from measurable
where measurable_category_id = (select id from measurable_category where external_id = 'CLOUD_READINESS');

insert into report_grid_column_definition(report_grid_id, column_entity_kind, column_entity_id)
select (select id from report_grid where external_id = 'CLOUD_READINESS_GRID'),
       'MEASURABLE',
       id
from measurable
where external_id in ('SOMEID');


insert into report_grid_column_definition(report_grid_id, column_entity_kind, column_entity_id)
select (select id from report_grid where external_id = 'CLOUD_READINESS_GRID'),
    'ASSESSMENT_DEFINITION',
       id
from assessment_definition
where external_id in ('Application Health Info', 'LEGAL_HOLD_STATUS');

[davidwatkins73]

WIP screenshot showing summaries (click to filter) and the column drop down to add new summaries. Summaries can be removed via the close (x) icon.

[davidwatkins73]

To allow for summary columns we may need to do a query similar to:

with ratings as (
    select sm.id 'smid',
           mr.entity_id 'eid',
           rsi.id 'rid',
           rsi.position 'rp'
    from measurable sm
    inner join entity_hierarchy eh on eh.ancestor_id = sm.id and eh.kind = 'MEASURABLE'
    inner join measurable_rating mr on eh.id = mr.measurable_id
    inner join measurable_category mc on mc.id = sm.measurable_category_id
    inner join rating_scheme rs on mc.rating_scheme_id = rs.id
    inner join rating_scheme_item rsi on rsi.code = mr.rating and rsi.scheme_id = rs.id
    and mr.entity_kind = 'APPLICATION'
)
select distinct r1.smid, r1.eid, r1.rid, r1.rp
from ratings r1
left join ratings r2 on r1.eid = r2.eid and r1.rp < r2.rp and r1.smid = r2.smid
where r2.rp is null
and r1.smid in (_m1_, _m2_)
and r1.eid in (_appId_);