620 likes | 755 Views
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.
E N D
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. • NSQL Code is available online for you to copy and paste.
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.
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
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)
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@
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@
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@
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@
Multi-Dimensional : Create the Query • Create a new query. • Admin / Q ueries / New
Multi-Dimensional : Create the Query • Paste the NSQL and “Save and Return”
Multi-Dimensional : Create the Portlet • Create a new GRID portlet. • Finish and Open to continue configuration.
Multi-Dimensional : Create the Portlet • The Y axis needs to beset. • Set the “stationary” columns.
Multi-Dimensional : Create the Portlet • Add the columns. • Data Columns at the end. • Remember to sort by theTime Period and then byInvestment/Resource
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.
Multi-Dimensional : Create the Portlet • Add the portlet to a page.
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.
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
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.
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@
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”
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”
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
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@
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@
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@
Hierarchy : Create the Query • Create a new query.
Hierarchy : Create the Query • Paste the NSQL and “Save and Return”
Hierarchy : Create the Portlet • Create a new GRID portlet. • Finish and Open to continue configuration.
Hierarchy : Create the Portlet • Create a new GRID portlet. • Finish and Open to continue configuration.
Hierarchy : Create the Portlet • Add your fields. • Hg_has_childrennot visible. • Sort by the name. • Save and publish.
Hierarchy : Create the Portlet • Add the portlet to a page.
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.
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.
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)
Drill-Down : Portlet Page • Create a portlet page • Save and Continue
Drill-Down : Portlet Page • Check the “Linkable” checkbox • Save and Continue
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
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
Drill-Down : Portlet #1 Query • Create a new NSQL query • Name – Drill Resource Manager • ID – regou_RM • Save and Continue
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
Drill-Down : Portlet #1 Query • Click on the “Linking” tab • Click on “New”
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
Drill-Down : Portlet #2 Query • Create a new NSQL query • Name – Drill Resources ID – regou_RES • Save and Continue
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
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