1 / 62

Advanced Portlets

Advanced Portlets. Maximizing portlet function to personalize output. About This Session. This is going to be a hands on learning experience. Log into your own system. Examples for Each Portlet. Talk about the portlet. Show you how. Your time to code it. Download Code.

faunus
Télécharger la présentation

Advanced Portlets

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Advanced Portlets Maximizing portlet function to personalize output.

  2. About This Session • This is going to be a hands on learning experience. • Log into your own system. • Examples for Each Portlet. • Talk about the portlet. • Show you how. • Your time to code it. • Download Code. • NSQL Code is available online for you to copy and paste.

  3. Types of Portlets • Common Portlets • Grid • Graph • Multi-Dimensional (Time Scaled) • Typically used to show values over time. • Example : Actuals by Month. • Hierarchy • A grid display that has a + sign to drill into that row. • Example : Master / Sub relationship. • Drill-Down • A set of multiple portlets that are linked. • Clicking on a record on the first portlet opens a second portlet. • Example : A chart portlet that drills into the details of the chart slice.

  4. Multi-Dimensional Portlets

  5. Multi-Dimensional Portlets • What is a Dimension? • A dimension is a grouping of similar data elements from one or more tables. For example, “Project” may be one dimension and “OBS” or “Tasks” could be other dimensions. • Dimensions are defined in the SELECT statement using specific syntax. First, you define a key value for the dimension and then you can define the other data elements in the dimension. • Design • Uses special NSQL constructs. • Single portlet. • Single NSQL query. Portions obtained from CA documentation

  6. Multi-Dimensional : NSQL Query SELECT @SELECT:DIM:USER_DEF:IMPLIED:RESOURCE:(i.code || r.id):uniqueid@, @SELECT:DIM_PROP:USER_DEF:IMPLIED:RESOURCE:i.code:investment_code@, @SELECT:DIM_PROP:USER_DEF:IMPLIED:RESOURCE:i.id:investment_id@, @SELECT:DIM_PROP:USER_DEF:IMPLIED:RESOURCE:i.name:investment_name@, @SELECT:DIM_PROP:USER_DEF:IMPLIED:RESOURCE:r.id:resource_id@, @SELECT:DIM_PROP:USER_DEF:IMPLIED:RESOURCE:r.full_name:resource_name@, @SELECT:DIM_PROP:USER_DEF:IMPLIED:RESOURCE:i.manager_id:manager_id@, @SELECT:DIM:USER_DEF:IMPLIED:PER:s.slice_date:tp_start@, @SELECT:DIM_PROP:USER_DEF:IMPLIED:PER:TO_CHAR(s.slice_date,'yyyy-mm-dd'):tp_start_display@, @SELECT:METRIC:USER_DEF:IMPLIED:SUM(s.slice):hours@ FROM prassignment assign JOIN prj_blb_slices s ON assign.prid = s.prj_object_id JOIN prtask t ON assign.prtaskid = t.prid JOIN srm_resources r ON assign.prresourceid = r.id JOIN inv_investmentsi ON t.prprojectid = i.id WHERE s.slice_request_id = (Select id from prj_blb_slicerequests where request_name = 'MONTHLYRESOURCEACTCURVE' ) AND s.slice > 0 AND s.slice_date BETWEEN @WHERE:PARAM:USER_DEF:DATE:startdate@ AND @WHERE:PARAM:USER_DEF:DATE:enddate@ AND @FILTER@ GROUP BY (i.code || r.id), r.id, r.full_name, i.id, i.code, i.manager_id, i.name,s.slice_date HAVING @HAVING_FILTER@ Dimension 1 (Resource) Dimension 2 (Date)

  7. Multi-Dimensional : NSQL Query SELECT @SELECT:DIM:USER_DEF:IMPLIED:RESOURCE:(i.code || r.id):uniqueid@, @SELECT:DIM_PROP:USER_DEF:IMPLIED:RESOURCE:i.code:investment_code@, @SELECT:DIM_PROP:USER_DEF:IMPLIED:RESOURCE:i.id:investment_id@, @SELECT:DIM_PROP:USER_DEF:IMPLIED:RESOURCE:i.name:investment_name@, @SELECT:DIM_PROP:USER_DEF:IMPLIED:RESOURCE:r.id:resource_id@, @SELECT:DIM_PROP:USER_DEF:IMPLIED:RESOURCE:r.full_name:resource_name@, @SELECT:DIM_PROP:USER_DEF:IMPLIED:RESOURCE:i.manager_id:manager_id@, @SELECT:DIM:USER_DEF:IMPLIED:PER:s.slice_date:tp_start@, @SELECT:DIM_PROP:USER_DEF:IMPLIED:PER:TO_CHAR(s.slice_date,'yyyy-mm-dd'):tp_start_display@, @SELECT:METRIC:USER_DEF:IMPLIED:SUM(s.slice):hours@ FROM prassignment assign HAVING @HAVING_FILTER@

  8. Multi-Dimensional : NSQL Query SELECT @SELECT:DIM:USER_DEF:IMPLIED:RESOURCE:(i.code || r.id):uniqueid@, @SELECT:DIM_PROP:USER_DEF:IMPLIED:RESOURCE:i.code:investment_code@, @SELECT:DIM_PROP:USER_DEF:IMPLIED:RESOURCE:i.id:investment_id@, @SELECT:DIM_PROP:USER_DEF:IMPLIED:RESOURCE:i.name:investment_name@, @SELECT:DIM_PROP:USER_DEF:IMPLIED:RESOURCE:r.id:resource_id@, @SELECT:DIM_PROP:USER_DEF:IMPLIED:RESOURCE:r.full_name:resource_name@, @SELECT:DIM_PROP:USER_DEF:IMPLIED:RESOURCE:i.manager_id:manager_id@, @SELECT:DIM:USER_DEF:IMPLIED:PER:s.slice_date:tp_start@, @SELECT:DIM_PROP:USER_DEF:IMPLIED:PER:TO_CHAR(s.slice_date,'yyyy-mm-dd'):tp_start_display@, @SELECT:METRIC:USER_DEF:IMPLIED:SUM(s.slice):hours@ FROM prassignment assign HAVING @HAVING_FILTER@

  9. Multi-Dimensional : NSQL (Oracle) SELECT @SELECT:DIM:USER_DEF:IMPLIED:RESOURCE:(i.code || r.id):uniqueid@, @SELECT:DIM_PROP:USER_DEF:IMPLIED:RESOURCE:i.code:investment_code@, @SELECT:DIM_PROP:USER_DEF:IMPLIED:RESOURCE:i.id:investment_id@, @SELECT:DIM_PROP:USER_DEF:IMPLIED:RESOURCE:i.name:investment_name@, @SELECT:DIM_PROP:USER_DEF:IMPLIED:RESOURCE:r.id:resource_id@, @SELECT:DIM_PROP:USER_DEF:IMPLIED:RESOURCE:r.full_name:resource_name@, @SELECT:DIM_PROP:USER_DEF:IMPLIED:RESOURCE:i.manager_id:manager_id@, @SELECT:DIM:USER_DEF:IMPLIED:PER:s.slice_date:tp_start@, @SELECT:DIM_PROP:USER_DEF:IMPLIED:PER:TO_CHAR(s.slice_date,'yyyy-mm-dd'):tp_start_display@, @SELECT:METRIC:USER_DEF:IMPLIED:SUM(s.slice):hours@ FROM prassignment assign JOIN prj_blb_slices s ON assign.prid = s.prj_object_id JOIN prtask t ON assign.prtaskid = t.prid JOIN srm_resources r ON assign.prresourceid = r.id JOIN inv_investmentsi ON t.prprojectid = i.id WHERE s.slice_request_id = (Select id from prj_blb_slicerequests where request_name = 'MONTHLYRESOURCEACTCURVE' ) AND s.slice > 0 AND s.slice_date BETWEEN @WHERE:PARAM:USER_DEF:DATE:startdate@ AND @WHERE:PARAM:USER_DEF:DATE:enddate@ AND @FILTER@ GROUP BY (i.code || r.id), r.id, r.full_name, i.id, i.code, i.manager_id, i.name,s.slice_date HAVING @HAVING_FILTER@

  10. Multi-Dimensional : NSQL (SQL) SELECT @SELECT:DIM:USER_DEF:IMPLIED:RESOURCE:(i.code+ r.id):uniqueid@, @SELECT:DIM_PROP:USER_DEF:IMPLIED:RESOURCE:i.code:investment_code@, @SELECT:DIM_PROP:USER_DEF:IMPLIED:RESOURCE:i.id:investment_id@, @SELECT:DIM_PROP:USER_DEF:IMPLIED:RESOURCE:i.name:investment_name@, @SELECT:DIM_PROP:USER_DEF:IMPLIED:RESOURCE:r.id:resource_id@, @SELECT:DIM_PROP:USER_DEF:IMPLIED:RESOURCE:r.full_name:resource_name@, @SELECT:DIM_PROP:USER_DEF:IMPLIED:RESOURCE:i.manager_id:manager_id@, @SELECT:DIM:USER_DEF:IMPLIED:PER:s.slice_date:tp_start@, @SELECT:DIM_PROP:USER_DEF:IMPLIED:PER:CONVERT(VARCHAR(10), s.slice_date, 120):tp_start_display@, @SELECT:METRIC:USER_DEF:IMPLIED:SUM(s.slice):hours@ FROM prassignment assign JOIN prj_blb_slices s ON assign.prid = s.prj_object_id JOIN prtask t ON assign.prtaskid = t.prid JOIN srm_resources r ON assign.prresourceid = r.id JOIN inv_investmentsi ON t.prprojectid = i.id WHERE s.slice_request_id = (Select id from prj_blb_slicerequests where request_name = 'MONTHLYRESOURCEACTCURVE' ) AND s.slice > 0 AND s.slice_date BETWEEN @WHERE:PARAM:USER_DEF:DATE:startdate@ AND @WHERE:PARAM:USER_DEF:DATE:enddate@ AND @FILTER@ GROUP BY (i.code+ r.id), r.id, r.full_name, i.id, i.code, i.manager_id, i.name,s.slice_date HAVING @HAVING_FILTER@

  11. Multi-Dimensional : Create the Query • Create a new query. • Admin / Q ueries / New

  12. Multi-Dimensional : Create the Query • Paste the NSQL and “Save and Return”

  13. Multi-Dimensional : Create the Portlet • Create a new GRID portlet. • Finish and Open to continue configuration.

  14. Multi-Dimensional : Create the Portlet • The Y axis needs to beset. • Set the “stationary” columns.

  15. Multi-Dimensional : Create the Portlet • Add the columns. • Data Columns at the end. • Remember to sort by theTime Period and then byInvestment/Resource

  16. Multi-Dimensional : Create the Portlet • Be sure to add the date parameters to your filter. • Assign a default value as needed. • Save … And then go to the General tab to publish.

  17. Multi-Dimensional : Create the Portlet • Add the portlet to a page.

  18. Questions?

  19. Exercise #1 : Multi-Dimensional Portlet • Log into your own environment. • Build your NSQL Query • Build your Grid Portlet • Add the Dates to the filter. • Sort by the Time Period • Add it to a page.

  20. Hierarchy Portlets

  21. Hierarchy Portlets • Hierarchy • A hierarchical query is used to display values in a hierarchical grid portlet. • Limitations • The parent and child must have the same number of fields. • The parent and child fields must be of the same data type. • Can be single or multi-dimensional. • NSQL needs to be optimized. • Implementation • Uses special NSQL constructs. • Single portlet. • Single NSQL query. Portions obtained from CA documentation

  22. Hierarchy Portlets • High level implementation steps • Create NSQL query. • Create Portlet. • Configure Portlet. • Accept praise for the cool looking portlet you just made! • Cookies are acceptable in lieu of praise.

  23. Hierarchy : NSQL Query SELECT @SELECT:DIM:USER_DEF:IMPLIED:PROJECT:SRC.GRID_ID:GridID@, @SELECT:DIM_PROP:USER_DEF:IMPLIED:PROJECT:SRC.GRID_NAME:GridName@, @SELECT:DIM_PROP:USER_DEF:IMPLIED:PROJECT:SRC.HG_HAS_CHILDREN:HG_HAS_CHILDREN@, @SELECT:DIM_PROP:USER_DEF:IMPLIED:PROJECT:SRC.SCHEDULE_START:GStart@, @SELECT:DIM_PROP:USER_DEF:IMPLIED:PROJECT:SRC.SCHEDULE_FINISH:GFinish@, FROM ( Select INV.ID GRID_ID ,INV.name GRID_NAME ,case when (select count(*) from prteampt where pt.prprojectid = INV.id) > 0 then INV.id else null end HG_HAS_CHILDREN ,INV.SCHEDULE_START ,INV.SCHEDULE_FINISH from INV_INVESTMENTS INV where INV.is_active = 1 and INV.ODF_OBJECT_CODE = 'project' AND @WHERE:PARAM:USER_DEF:INTEGER:hg_row_id@ IS NULL UNION SELECT PRT.PRID GRID_ID ,SRM.FULL_NAME GRID_NAME ,null HG_HAS_CHILDREN ,nvl(PRT.PRAVAILSTART,INV.schedule_start) SCHEDULE_START ,nvl(PRT.PRAVAILFINISH,INV.SCHEDULE_FINISH) SCHEDULE_FINISH from prteamPRT,srm_resources SRM,INV_INVESTMENTS INV where PRT.prresourceid = SRM.id and PRT.PRPROJECTID = INV.ID AND PRT.prprojectid = @WHERE:PARAM:USER_DEF:INTEGER:hg_row_id@ ) SRC where @FILTER@ HAVAING @HAVING_FILTER@

  24. Hierarchy : NSQL Query SELECT @SELECT:DIM:USER_DEF:IMPLIED:PROJECT:SRC.GRID_ID:GridID@, @SELECT:DIM_PROP:USER_DEF:IMPLIED:PROJECT:SRC.GRID_NAME:GridName@, @SELECT:DIM_PROP:USER_DEF:IMPLIED:PROJECT:SRC.HG_HAS_CHILDREN:HG_HAS_CHILDREN@, @SELECT:DIM_PROP:USER_DEF:IMPLIED:PROJECT:SRC.SCHEDULE_START:GStart@, @SELECT:DIM_PROP:USER_DEF:IMPLIED:PROJECT:SRC.SCHEDULE_FINISH:GFinish@, FROM ( Select INV.ID GRID_ID ,INV.name GRID_NAME ,case when (select count(*) from prteampt where pt.prprojectid = INV.id) > 0 then INV.id else null end HG_HAS_CHILDREN ,INV.SCHEDULE_START ,INV.SCHEDULE_FINISH from INV_INVESTMENTS INV where INV.is_active = 1 and INV.ODF_OBJECT_CODE = 'project' AND @WHERE:PARAM:USER_DEF:INTEGER:hg_row_id@ IS NULL UNION SELECT PRT.PRID GRID_ID ,SRM.FULL_NAME GRID_NAME ,null HG_HAS_CHILDREN ,nvl(PRT.PRAVAILSTART,INV.schedule_start) SCHEDULE_START ,nvl(PRT.PRAVAILFINISH,INV.SCHEDULE_FINISH) SCHEDULE_FINISH from prteamPRT,srm_resources SRM,INV_INVESTMENTS INV where PRT.prresourceid = SRM.id and PRT.PRPROJECTID = INV.ID AND PRT.prprojectid = @WHERE:PARAM:USER_DEF:INTEGER:hg_row_id@ ) SRC where @FILTER@ HAVAING @HAVING_FILTER@ Code for “Parent”

  25. Hierarchy : NSQL Query SELECT @SELECT:DIM:USER_DEF:IMPLIED:PROJECT:SRC.GRID_ID:GridID@, @SELECT:DIM_PROP:USER_DEF:IMPLIED:PROJECT:SRC.GRID_NAME:GridName@, @SELECT:DIM_PROP:USER_DEF:IMPLIED:PROJECT:SRC.HG_HAS_CHILDREN:HG_HAS_CHILDREN@, @SELECT:DIM_PROP:USER_DEF:IMPLIED:PROJECT:SRC.SCHEDULE_START:GStart@, @SELECT:DIM_PROP:USER_DEF:IMPLIED:PROJECT:SRC.SCHEDULE_FINISH:GFinish@, FROM ( Select INV.ID GRID_ID ,INV.name GRID_NAME ,case when (select count(*) from prteampt where pt.prprojectid = INV.id) > 0 then INV.id else null end HG_HAS_CHILDREN ,INV.SCHEDULE_START ,INV.SCHEDULE_FINISH from INV_INVESTMENTS INV where INV.is_active = 1 and INV.ODF_OBJECT_CODE = 'project' AND @WHERE:PARAM:USER_DEF:INTEGER:hg_row_id@ IS NULL UNION SELECT PRT.PRID GRID_ID ,SRM.FULL_NAME GRID_NAME ,null HG_HAS_CHILDREN ,nvl(PRT.PRAVAILSTART,INV.schedule_start) SCHEDULE_START ,nvl(PRT.PRAVAILFINISH,INV.SCHEDULE_FINISH) SCHEDULE_FINISH from prteamPRT,srm_resources SRM,INV_INVESTMENTS INV where PRT.prresourceid = SRM.id and PRT.PRPROJECTID = INV.ID AND PRT.prprojectid = @WHERE:PARAM:USER_DEF:INTEGER:hg_row_id@ ) SRC where @FILTER@ HAVAING @HAVING_FILTER@ Code for “Child”

  26. Hierarchy : Parent Query Select INV.ID GRID_ID ,INV.name GRID_NAME ,case when (select count(*) from prteampt where pt.prprojectid = INV.id) > 0 then INV.id else null end HG_HAS_CHILDREN ,INV.SCHEDULE_START ,INV.SCHEDULE_FINISH from INV_INVESTMENTS INV where INV.is_active = 1 and INV.ODF_OBJECT_CODE = 'project' AND @WHERE:PARAM:USER_DEF:INTEGER:hg_row_id@ IS NULL

  27. Hierarchy : Child Query UNION SELECT PRT.PRID GRID_ID ,SRM.FULL_NAME GRID_NAME ,null GRID_HAS_CHILDRES ,nvl(PRT.PRAVAILSTART,INV.schedule_start) SCHEDULE_START ,nvl(PRT.PRAVAILFINISH,INV.SCHEDULE_FINISH) SCHEDULE_FINISH From prteamPRT,srm_resources SRM,INV_INVESTMENTS INV where PRT.prresourceid = SRM.id and PRT.PRPROJECTID = INV.ID AND PRT.prprojectid = @WHERE:PARAM:USER_DEF:INTEGER:hg_row_id@

  28. Hierarchy : NSQL (Oracle) SELECT @SELECT:DIM:USER_DEF:IMPLIED:PROJECT:SRC.GRID_ID:GridID@, @SELECT:DIM_PROP:USER_DEF:IMPLIED:PROJECT:SRC.GRID_NAME:GridName@, @SELECT:DIM_PROP:USER_DEF:IMPLIED:PROJECT:SRC.HG_HAS_CHILDREN:HG_HAS_CHILDREN@, @SELECT:DIM_PROP:USER_DEF:IMPLIED:PROJECT:SRC.SCHEDULE_START:GStart@, @SELECT:DIM_PROP:USER_DEF:IMPLIED:PROJECT:SRC.SCHEDULE_FINISH:GFinish@ FROM ( Select INV.ID GRID_ID ,INV.name GRID_NAME ,case when (select count(*) from prteampt where pt.prprojectid = INV.id) > 0 then INV.id else null end HG_HAS_CHILDREN ,INV.SCHEDULE_START ,INV.SCHEDULE_FINISH from INV_INVESTMENTS INV where INV.is_active = 1 and INV.ODF_OBJECT_CODE = 'project' AND @WHERE:PARAM:USER_DEF:INTEGER:hg_row_id@ IS NULL UNION SELECT PRT.PRID GRID_ID ,SRM.FULL_NAME GRID_NAME ,1 HG_HAS_CHILDREN ,nvl(PRT.PRAVAILSTART,INV.schedule_start) SCHEDULE_START ,nvl(PRT.PRAVAILFINISH,INV.SCHEDULE_FINISH) SCHEDULE_FINISH from prteamPRT,srm_resources SRM,INV_INVESTMENTS INV where PRT.prresourceid = SRM.id and PRT.PRPROJECTID = INV.ID AND PRT.prprojectid = @WHERE:PARAM:USER_DEF:INTEGER:hg_row_id@ ) SRC where @FILTER@ HAVING @HAVING_FILTER@

  29. Hierarchy : NSQL (SQL) SELECT @SELECT:DIM:USER_DEF:IMPLIED:PROJECT:SRC.GRID_ID:GridID@, @SELECT:DIM_PROP:USER_DEF:IMPLIED:PROJECT:SRC.GRID_NAME:GridName@, @SELECT:DIM_PROP:USER_DEF:IMPLIED:PROJECT:SRC.HG_HAS_CHILDREN:HG_HAS_CHILDREN@, @SELECT:DIM_PROP:USER_DEF:IMPLIED:PROJECT:SRC.SCHEDULE_START:GStart@, @SELECT:DIM_PROP:USER_DEF:IMPLIED:PROJECT:SRC.SCHEDULE_FINISH:GFinish@ FROM ( Select INV.ID GRID_ID ,INV.name GRID_NAME ,case when (select count(*) from prteampt where pt.prprojectid = INV.id) > 0 then INV.id else null end HG_HAS_CHILDREN ,INV.SCHEDULE_START ,INV.SCHEDULE_FINISH from INV_INVESTMENTS INV where INV.is_active = 1 and INV.ODF_OBJECT_CODE = 'project' AND @WHERE:PARAM:USER_DEF:INTEGER:hg_row_id@ IS NULL UNION SELECT PRT.PRID GRID_ID ,SRM.FULL_NAME GRID_NAME ,1 HG_HAS_CHILDREN ,is_null(PRT.PRAVAILSTART,INV.schedule_start) SCHEDULE_START ,is_null(PRT.PRAVAILFINISH,INV.SCHEDULE_FINISH) SCHEDULE_FINISH from prteamPRT,srm_resources SRM,INV_INVESTMENTS INV where PRT.prresourceid = SRM.id and PRT.PRPROJECTID = INV.ID AND PRT.prprojectid = @WHERE:PARAM:USER_DEF:INTEGER:hg_row_id@ ) SRC where @FILTER@ HAVING @HAVING_FILTER@

  30. Hierarchy : Create the Query • Create a new query.

  31. Hierarchy : Create the Query • Paste the NSQL and “Save and Return”

  32. Hierarchy : Create the Portlet • Create a new GRID portlet. • Finish and Open to continue configuration.

  33. Hierarchy : Create the Portlet • Create a new GRID portlet. • Finish and Open to continue configuration.

  34. Hierarchy : Create the Portlet • Add your fields. • Hg_has_childrennot visible. • Sort by the name. • Save and publish.

  35. Hierarchy : Create the Portlet • Add the portlet to a page.

  36. Exercise #2 :Hierarchy Portlet • Log into your own environment. • If you do not have access, please see a RegoUniveristy representative at the back of the room. • Build your NSQL • Build your Grid Portlet • Add it to a page.

  37. Questions?

  38. Drill-Down Portlets • Multiple portlets that are linked together by clicking on a link inside one portlet. • Pie chart  Details for the slice. • Grid Summary  Details for the grid. • Requires multiple queries, portlets, and at least one portlet page.

  39. Drill-Down Portlets • High level implementation steps • Create the portlet page with link. • Create NSQL query for the source portlet • Create the source portlet • Create NSQL query for the destination portlet • Create the destination portlet. • Plase both on the portlet page. • Add page to the menu. • Accept praise for the cool looking portlet you just made! • Cake is acceptable in lieu of praise. (Cake due to the complexity)

  40. Drill-Down : Portlet Page • Create a portlet page • Save and Continue

  41. Drill-Down : Portlet Page • Check the “Linkable” checkbox • Save and Continue

  42. Drill-Down : Portlet Page • On the Links tab, click on NEW to create a new link • Save and Return • Then, click on Return, we will come back to the page next

  43. Drill-Down : Portlet Page • Click on the link you just created to view the settings • Make note of the “Parameter Source”, APT_CUST_5040190 • Save and Return • Then, click on Return, we will come back to the page later

  44. Drill-Down : Portlet #1 Query • Create a new NSQL query • Name – Drill Resource Manager • ID – regou_RM • Save and Continue

  45. Drill-Down : Portlet #1 Query • Paste the NSQL. (This is for both Oracle and SQL) SELECT @SELECT:DIM:USER_DEF:IMPLIED:RES:mgr.user_id:MGRDBID@, @SELECT:DIM_PROP:USER_DEF:IMPLIED:RES:mgr.full_name:ResourceManager@, @SELECT:DIM_PROP:USER_DEF:IMPLIED:RES:Count(*):ResourceCount@ from SRM_RESOURCES SRM, SRM_RESOURCES MGR where srm.manager_id = mgr.user_id and @FILTER@ Group by mgr.user_id, mgr.full_name HAVING @HAVING_FILTER@ • Save and Continue

  46. Drill-Down : Portlet #1 Query • Click on the “Linking” tab • Click on “New”

  47. Drill-Down : Portlet #1 NSQL • The portlet page you created earlier, “Drill Down Sample”shows in the action • Select “Drill DownSample” • Once you select your page, the link you made “Manager ID” shows up. We want to map it to the mgrdbid field from our query • Save and Return • Click Return again to go back to the queries list

  48. Drill-Down : Portlet #2 Query • Create a new NSQL query • Name – Drill Resources ID – regou_RES • Save and Continue

  49. Drill-Down : Portlet #2 NSQL • Paste the NSQL. (This is for both Oracle and SQL) SELECT @SELECT:DIM:USER_DEF:IMPLIED:RES:srm.id :RESDBID@, @SELECT:DIM_PROP:USER_DEF:IMPLIED:RES:srm.full_name:Resource_Name@ from SRM_RESOURCES SRM where @WHERE:PARAM:XML:STRING:/data/MGRID/@value@ != APT_CUST_5040190’ And srm.manager_id = @WHERE:PARAM:XML:STRING:/data/MGRID/@value@ and @FILTER@ HAVING @HAVING_FILTER@ • Save and Continue • Then, click Return to exit out of the query

  50. Drill-Down : Portlet #1 Design • Create a new grid portlet • Name = Resource Manager w/Drill • ID = regou_rm_count • Data Provider = Drill – Resource Manager • Click Next • Click Finish and Open

More Related