How to create a three dimensional portlet

Products

Clarity PPM On Premise

Issue/Introduction

How do you create a 2-dimensional or 3-dimensional portlet in Studio?

Environment

Release: Any

Resolution

Multi-Dimensional Portlets
When you construct your NSQL statement and you want to create a multi-dimensional portlet, you simply need to use the 'DIM' keyword to define more than one dimension. In a typical one-dimensional NSQL query, the first field is defined as such, therefore in a multi-dimensional query, you will have more than one field defined as a 'DIM' attribute.
WHERE Clause Parameters
If you want to define parameters within the NSQL query you can specify a special NSQL construct that will create a 'parameter' that will be available to the Portlet Filter Section. The parameters you define in the WHERE clause are usually for 'required' parameters. If you simply want to filter on one of the select-list column fields created from the NSQL, it is not necessary to put the construct within your WHERE clause. The select-list column will appear as an 'Available' filter field automatically within the portlet definition.

Export to Excel

An important note about using multi-dimensional portlets is that the underlying data provider may generate many more rows that what is actually seen in the application user interface. The user interface organizes the data into a table format and the number of total rows shown on the table is less than the actual number of data rows generated by the query. Therefore, if you the number of rows returned in the underlying query is greater than the Export to Excel (Data Only) limitations, the user may see less amount of data than expected compared to what they see in the user interface. If this happens, have the user apply filtering within the portlet to reduce the amount of data retrieved and try the export action again.
If you wish to export data from a user-defined NSQL multi-dimensional portlet be sure to include the 'hg_all_rows' construct.

EXAMPLE NSQL Query

Below is an example 3-dimensional portlet using dummy records. This query can be used immediately. This query could also be modified as a 2-dimensional portlet. Simply create a new NSQL Query and paste the query in to the NSQL Query Definition. Then create a portlet using the NSQL Query Data Provider. Once you see how a multi-dimensional portlet behaves, you should be able to model your 'use case' in the same fashion.

  SELECT @SELECT:DIM:USER_DEF:IMPLIED:DIM1:myDim1:myDim1@,
  @SELECT:DIM:USER_DEF:IMPLIED:DIM2:myDim2:myDim2@,
  @SELECT:DIM:USER_DEF:IMPLIED:DIM3:myDim3:myDim3@,
  @SELECT:METRIC:USER_DEF:IMPLIED:SUM(myValue):DIM_SUM@
  FROM
  (
  SELECT 'dimension1' myDim1, 'dimension2-1' myDim2, 'dimension3-1' myDim3, 11 myValue, 1 is_active
  FROM DUAL
  union
  SELECT 'dimension1' myDim1, 'dimension2-1' myDim2, 'dimension3-1' myDim3, 300 myValue, 0 is_active
  FROM DUAL
  union
  SELECT 'dimension1' myDim1, 'dimension2-1' myDim2, 'dimension3-2' myDim3, 22 myValue, 1 is_active
  FROM DUAL
  union
  SELECT 'dimension1' myDim1, 'dimension2-1' myDim2, 'dimension3-2' myDim3, 500 myValue, 0 is_active
  FROM DUAL
  union
  SELECT 'dimension1' myDim1, 'dimension2-1' myDim2, 'dimension3-3' myDim3, 33 myValue, 1 is_active
  FROM DUAL
  union
  SELECT 'dimension2' myDim1, 'dimension2-1' myDim2, 'dimension3-2' myDim3, 44 myValue, 1 is_active
  FROM DUAL
  union
  SELECT 'dimension3' myDim1, 'dimension2-1' myDim2, 'dimension3-3' myDim3, 55 myValue, 1 is_active
  FROM DUAL
  ) a
  WHERE
  @WHERE:PARAM:USER_DEF:STRING:myActive@ = is_active
  AND
  @FILTER@
  GROUP BY myDim1, myDim2, myDim3

Below you can see how the portlet behaves with different filters. A value of 1 or 0 must be entered for the Active parameter due to the configuration of the WHERE clause. You can modify the clause to accept no value for the filter and results can be returned for both values. This is just one example of how you can configure a three dimensional portlet to suit your needs. For more information, reference the Studio Guide.

Filter for Active = 1 (True)
Filter for Active = 0 (False)
Filter for a specific dimension (level 3-2) plus Active = 1 (True)