Clarity

Team,

I have been successful in exporting a hierarchical portlet so that all rows from the expanded result are exported. I am, however, having issues with sorting the results so that the rows export the same way they look on the screen.

All parents and their children should be grouped together appropriately.

Is there an additional NSQL parameter that can easily do this?

Please check the attached !!!

Hope this helps :tongue:

NJ

Hello,

Here is a simple example for a query with sorted excel export capability:

SELECT @SELECT:DIM:USER_DEF:IMPLIED:PT:dim_id:dim_id@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:PT:display_value:display_value@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:PT:project_name:project_name@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:PT:task_name:task_name@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:PT:hg_has_children:hg_has_children@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:PT:sample_value:sample_value@
FROM (
SELECT 'PRJ' || ii.id dim_id
, ii.name display_value
, ii.name project_name
, ' ' task_name
, ii.id hg_has_children
, ocp.partition_code sample_value
FROM inv_investments ii INNER JOIN odf_ca_project ocp ON ocp.id = ii.id
WHERE @WHERE:PARAM:USER_DEF:INTEGER:hg_row_id@ IS NULL OR @WHERE:PARAM:USER_DEF:INTEGER:hg_all_rows@ = 1
UNION ALL
SELECT 'TSK' || tsk.prid
, tsk.prname
, ii.name
, tsk.prname
, NULL hg_has_children
, oct.partition_code sample_value
FROM inv_investments ii
INNER JOIN prtask tsk ON ii.id = tsk.prprojectid
INNER JOIN odf_ca_task oct ON oct.id = tsk.prid
WHERE @WHERE:PARAM:USER_DEF:INTEGER:hg_row_id@ = ii.id OR @WHERE:PARAM:USER_DEF:INTEGER:hg_all_rows@ = 1
) qdata
WHERE @FILTER@

In this example, the field display_value gets populated with either the project name or, when expanding, the task name.
To get a properly sorted excel export, sorting has to be done across the whole query. So the sorting criteria should be present in each row. In your case, you probably sort by the displayed field which, in this expample, results in sorting independent of project by mixed task and project names. Multisorting by project_name and task_name is better in this case because it is exported to excel, too.

Hope this helps,
Alexander Rosiuta