NSQL Portlet Creation and Management Overview
"Explore the essentials of creating and managing NSQL portlets in Clarity Educational Community presented by Juan Ortega and David Zywiec. Learn about SQL vs. Object-based queries, custom tab creation, portlet types, object-based portlet examples, NSQL queries, and more. Dive into the world of customizable views and data retrieval strategies."
Download Presentation
Please find below an Image/Link to download the presentation.
The content on the website is provided AS IS for your information and personal use only. It may not be sold, licensed, or shared on other websites without obtaining consent from the author. Download presentation by click this link. If you encounter any issues during the download, it is possible that the publisher has removed the file from their server.
E N D
Presentation Transcript
www.regouniversity.com Clarity Educational Community Getting Started with NSQL Portlet Creation and Management Presented by: Juan Ortega, David Zywiec on 5/4/15
Agenda Create custom tab on Overview Portlet SQL Based vs Object Based NSQL Queries Parameters 2 Clarity Educational Community
Requirements SQL Developer SQL Developer: Here Clarity DB Connection Info and Access Place holder for information 3 Clarity Educational Community
How to Create Custom Tabs on the General Page Creating a custom tab on the general page will allow you to navigate to any development work easily 1. Login to Clarity 2. Click on the Manage My Tabs Icon 3. Click the New button 4. Give the tab a name 5. Click Save and Continue 6. Add the portlet in the contents section 4 Clarity Educational Community
Portlet Types NSQL Object Based Portlets created using queries in Clarity to define the data that can be used. Portlets created using an Object instead of a query to define the data set gathered. Pros Pros Logic Parameters Security Customizable Matrices Customizable Security In-Line Editing Dynamic Time Scaled Values Cons Cons Not Dynamic Development Time No In-Line Editing Multiple Objects Difficulty No Custom Logic 5 Clarity Educational Community
Activity 1 Create Object Based Portlet Create an object based portlet for projects with the following fields. (Juan demonstrate Custom fields added automatically) Project ID Project Name Project Manager Start Date Finish Date 6 Clarity Educational Community
NSQL Queries NSQL queries are read-only operations; they do not change data, they only retrieve it Only SELECT statements that specify which rows and columns to fetch from one or more tables are permitted When you define an NSQL query, you identify the query segments which allow customizable views in a portlet (we will review these in the upcoming slides) Dimensions & Dimension properties Metric values Parameters 7 Clarity Educational Community
NSQL Basics The SELECT statement retrieves column data from tables NSQL Queries must start with SELECT however for each column a @SELECT@ tag must be used. The FROM clause is a standard SQL statement which defines which table to gather data from The WHERE statement filters data returned by a query to be used on portlets The @FILTER@ statement is required and allows the system to filter the values defined with the @SELECT@ tag The GROUP BY clause is typically used to combine database records with identical values in a specified field into a single record, usually for the purposes of calculating some sort of aggregate function The syntax for the HAVING statement is @HAVING_FILTER@ which can be used when a query uses metrics The Developer guide states this is required but it is NOT. 8 Clarity Educational Community
Dimensions and Dimension Properties A dimension is a grouping of similar data elements from one or more tables A commonly used second dimension is date on actuals. Defining Dimensions SELECT @SELECT:DIM:USER_DEF:IMPLIED:<Dimension>:<Table.Field>:<label>@ DIM: Indicates the line is the primary key for the dimension There can only be one DIM to each dimension. @SELECT:DIM_PROP:USER_DEF:IMPLIED:<Dimension>:<Table.Field>:<label>@ DIM_PROP: Indicates columns for the dimension There can be many DIM_PROPs defined to one dimension. <Dimension> is a user-defined name for the dimension <Table.Field> is the table or alias name retrieved in the FROM statement <label> is the name you want to appear in the column list in clarity 9 Clarity Educational Community
Activity 2 Create NSQL Query Create an NSQL query that shows only projects created from converted ideas displaying the following fields. Project ID Project Name Project Start Project Finish Idea ID Idea Name 10 Clarity Educational Community
Parameters There are two types of parameters that can be used in NSQL Queries. User Defined @SELECT:PARAM:USER_DEF:DATA_TYPE:PARAM_NAME[:ALIAS]@ This allows us to create a parameter in the filter that can be defined through a lookup in the filter WHERE Start_Date BETWEEN @WHERE:PARAM:USER_DEF:DATE:startdate@ AND @WHERE:PARAM:USER_DEF:DATE:enddate@ Limitation: No multi-select allowed Built-in Parameters Most commonly used for security and language lookups @SELECT:PARAM:PARAM_IDENTIFIER[:ALIAS]@ PARAM_IDENTIFIER is one of the following USER_ID USER_NAME LANGUAGE LOCALE 11 Clarity Educational Community
Activity 3 Create Dashboard Based on Project Create an NSQL query that shows only tasks on a project for which the user is the Project Manager. Without adding the dates as columns, allow for the user to filter on start and end date. Project ID Project Name Project Start Project Finish Idea ID Idea Name Hint: Use the user defined parameters for the date filters 12 Clarity Educational Community
Metric Values A metric column is similar to a dimension property in that it belongs in the SELECT section of the query, but metric column values can be totaled on a grid or displayed as value(s) on the chart @SELECT:METRIC:USER_DEF:IMPLIED:<Table.Field>:<label>@ Do not use the dimension name because metrics cross dimensions. The agg definition is an optional addition to aggregate the metric. @SELECT:METRIC:USER_DEF:IMPLIED:<Table.Field>:<label>:agg@ Example of a query using multiple dimensions with the metrics is on the following slide 13 Clarity Educational Community
NSQL Query Sample Timesheets by Resource SELECT @SELECT:DIM:USER_DEF:IMPLIED:RESOURCE:R.id:dimid@, @SELECT:DIM_PROP:USER_DEF:IMPLIED:RESOURCE:r.full_name:res_name@, @SELECT:DIM_PROP:USER_DEF:IMPLIED:RESOURCE:rm.full_name:rm_name@, -------Second Dimension @SELECT:DIM:USER_DEF:IMPLIED:RESOURCEDT:tp.prstart:dim2id@, @SELECT:DIM_PROP:USER_DEF:IMPLIED:RESOURCEDT:to_char(tp.prstart, 'MM/DD/YY'):dim2str@, -------Metric Data @SELECT:METRIC:USER_DEF:IMPLIED:SUM(NVL(ts.hrs, 0)):hrs:AGG@, @SELECT:METRIC:USER_DEF:IMPLIED:CASE SUM(NVL(ts.hrs, 0)) WHEN 0 THEN 1 WHEN 40 THEN 2 ELSE 3 END:hrs_lt@ FROM srm_resources r JOIN (SELECT ts.prresourceid resource_id, ts.prtimeperiodid tpid, NVL(te.practsum, 0)/3600 hrs FROM prtimesheet ts LEFT JOIN prtimeentry te ON te.prtimesheetid = ts.prid WHERE ts.prstatus = NVL(@WHERE:PARAM:USER_DEF:INTEGER:tsstatus@, 1) --Default Submitted ) ts ON ts.RESOURCE_ID = r.id JOIN prtimeperiod tp ON tp.prid = ts.tpid AND tp.prstart BETWEEN @WHERE:PARAM:USER_DEF:DATE:startdate@ AND @WHERE:PARAM:USER_DEF:DATE:enddate@ JOIN prj_resources pr ON pr.prid = r.id AND pr.prisrole = 0 AND pr.prisopen = 1 LEFT JOIN srm_resources rm ON rm.user_id = r.manager_id JOIN odf_ca_resource ocr ON ocr.id = r.id WHERE @FILTER@ GROUP BY r.id, r.full_name, rm.full_name, tp.prstart HAVING @HAVING_FILTER@ 14 Clarity Educational Community
Activity 4 Create Dashboard Based on Project Create an NSQL Query and Portlet to retrieve the ETC, Actual, and EAC for each week on a project that the user is the PM. Hints: Two Dimensional Use PRJ_BLB_SLICES table 15 Clarity Educational Community
Questions We hope that you found this session informative and worthwhile. Our primary goal was to increase your understanding of the topic and CA PPM in general. Phone 888.813.0444 Email info@regouniversity.com There were many concepts covered during the session, if you would like to contact any presenter with questions, please reach out to us. Website www.regouniversity.com Thank you for attending regoUniversity 2015! 16 Clarity Educational Community
