User-defined Hierarchical Portlets do not include the children rows when the data is Exported to Excel.

Products

• Clarity PPM SaaS
• Clarity PPM On Premise

Issue/Introduction

From a Hierarchical Grid (HG) Portlet, only the parent rows are exported to Microsoft Excel when you should expect to get both parent rows and children rows as shown on the Clarity UI.

Steps to Reproduce:

1. Create a user-defined Hierarchical Portlet.
2. On the application side, expand the results of the Hierarchical Grid Portlet to show children rows.
3. Export to Excel the results you see on the screen.

Expected Result: Parents and children will be exported to Excel
Actual Result: Only Parents are exported to Excel

Environment

Any Clarity release

Resolution

A parameter is used for all HG data providers called 'hg_all_rows'. This parameter is set to "1" when an Export to Excel is performed and it tells the data provider to "return all rows in the hierarchical grid in a 'flat' list style". 
For the functionality to work correctly, the NSQL Query author will need to review existing user-defined HG NSQL statements to ensure that it includes this parameter in order for Export to Excel to correctly export "all rows". If this parameter is not processed in the NSQL Query statement, the Export to Excel will still export the "top-level" rows. This means that the NSQL Query must execute a different WHERE clause or sub-SELECT clause as the logic in the NSQL itself must be different than if you were viewing the HG in your browser.

 ... WHERE @WHERE:PARAM:USER_DEF:INTEGER:hg_all_rows@ = 1 ...

Sample NSQL Query statement: Use this sample as a test query to understand the functionality. This query does not rely on any specific data and can be used on both Microsoft SQL Server and Oracle implementations. It is not an example of good NSQL coding practices; just a simple HG query for DUMMY DATA TESTING purposes. This query includes the criteria in the WHERE clause that will allow 'all rows' to be exported.

When implementing the WHERE clause you will also need to be sure that your NSQL Query selects ALL rows for output as shown below:

The grid engine will now pass down a parameter called "hg_all_rows" and when that parameter is "1", the query must return all the child rows. The query author must check for this parameter and adjust the SQL statement accordingly.

In pseudo code using a simple 1 parent to 1 child table example, this means you need to write something like this:

SELECT Dimension_Key, Dimension_Prop1, Dimension_Prop2,   Dimension_Metric1
 
FROM
(
/*Get parent data */ 
   SELECT   Dimension_Key, Parent_Data, null as child_data, some count or flag as HG_HAS_CHILDREN 
   FROM  parent_table, child_table 
   WHERE    @WHERE:PARAM:USER_DEF:STRING:HG_ROW_ID@ IS   NULL
 
UNION
 
/* Get child data */ 
    SELECT   Dimension_Key, null as parent_data, child_data, null as HG_HAS_CHILDREN 
    FROM parent_table, child_table 
      WHERE some_parent_column = @WHERE:PARAM:USER_DEF:string:HG_ROW_ID@ /* or use a substring   */
 
UNION 
 
/* This is the part that enables Export to Excel action to   work - get all the rows for parent and children */
    SELECT Dimension_Key, parent_data, child_data, null as   HG_HAS_CHILDREN 
    FROM parent_table, child_table 
    WHERE   @where:param:user_def:integer:hg_all_rows@ = 1 
) as X
 
WHERE 
/* top level filters here */   @FILTER@