AnsweredAssumed Answered

Recreate project General and Labor Effort portlets with additional data?

Question asked by L.Elias Champion on Apr 12, 2016

Wondering if anyone has gone about recreating the General and Labor Effort portlets on the project dashboard since they are oh so helpfully restricted? I found some previous questions/discussions about these portlets but nothing with a final solution. The best I've come up with so far is to create a new grid portlet based on a query that returns the data in multiple rows, but it seems sub-optimal so wanted to see if anyone else had tackled the issue and came up with a better result. For one thing everything has to be returned from the DB as text, so stuff like indicator images is a no go, at least that I have come up with.

SELECT @SELECT:DIM:USER_DEF:IMPLIED:ORH_GEN:SortVal:SortVal@,
       @SELECT:DIM_PROP:USER_DEF:IMPLIED:ORH_GEN:Cols:Cols@,
       @SELECT:DIM_PROP:USER_DEF:IMPLIED:ORH_GEN:Vals:Vals@
  FROM
  (
select 'Project ID', unique_code, 2 as SortVal FROM niku.ODF_PROJECT_V2 WHERE odf_pk=@WHERE:PARAM:XML:INTEGER:/data/id/@value@
union all
select 'Description', description, 3 as SortVal FROM niku.ODF_PROJECT_V2 WHERE odf_pk=@WHERE:PARAM:XML:INTEGER:/data/id/@value@
union all
select 'Schedule Finish', CONVERT(sql_variant,schedule_finish), 5 as SortVal FROM niku.ODF_PROJECT_V2 WHERE odf_pk=@WHERE:PARAM:XML:INTEGER:/data/id/@value@
union all
select 'Schedule Start', CONVERT(sql_variant,schedule_start), 4 as SortVal FROM niku.ODF_PROJECT_V2 WHERE odf_pk=@WHERE:PARAM:XML:INTEGER:/data/id/@value@
union all
select 'Project Manager', r.FULL_NAME, 6 as SortVal FROM niku.ODF_PROJECT_V2
    LEFT OUTER JOIN niku.SRM_RESOURCES r ON r.USER_ID = manager
    WHERE odf_pk=@WHERE:PARAM:XML:INTEGER:/data/id/@value@
union all
select 'Overall Status', status_indicator =
      CASE status_indicator
         WHEN '1' THEN 'On Track'
         WHEN '2' THEN 'Minor Variance'
         WHEN '3' THEN 'Significant Variance'
         ELSE 'Unknown'
      END, 7 as SortVal FROM niku.ODF_PROJECT_V2 WHERE odf_pk=@WHERE:PARAM:XML:INTEGER:/data/id/@value@
    ) data

 WHERE @FILTER@

Outcomes