AnsweredAssumed Answered

Hierarchical Portlets & Exporting to Excel in v12.06

Question asked by Owen_R on Apr 21, 2010
Latest reply on Aug 1, 2012 by Dolly Kirubavathi
After doing some testing on 12.06 I noticed that the known bug (CLRT-42568) which was not exporting child data rows to excel from a hierarchical portlet was not fixed, although the 12.05 release notes stated it was.  I raised a case and CA have provided a solution, and it appears to be undocumented in the latest version of the Studio Guide, so I am sharing it here:   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
Select Dimension Prop
Select Dimension Prop2...etc  From(/*Get parent data */
Select 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 null as parent data, child data, null as HG_HAS_CHILDREN
Fromparent 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 enable export to excel to work */Select parent data, child data, null as HG_HAS_CHILDREN
Fromparent table, child table
Where @where:param:user_def:integer:hg_all_rows@ = 1
) as X  Where/* top level filters here */
@FILTER@  Hope this is of use.

Outcomes