Clarity

Hi,

I'm developing a hierchial portlet on SQL Server as the one in the print screen below.

On depth level 4 OBS test_4 only one row is shown in the portlet even though there should be 2 values, as can be seen in second picture.

The NSQL for the portlet can be seen under the print screen.

Is it possible to get both values of test_4? Can someone give a hint on what i'm doing wrong?

SELECT  @SELECT:DIM:USER_DEF:IMPLIED:OBS:DATA.id:id@

  ,@SELECT:DIM_PROP:USER_DEF:IMPLIED:OBS:DATA.name:name@

  ,@SELECT:DIM_PROP:USER_DEF:IMPLIED:OBS:DATA.depth:depth@

  ,@SELECT:DIM_PROP:USER_DEF:IMPLIED:OBS:DATA.PARENT_ID:PARENT_ID@

  ,@SELECT:DIM_PROP:USER_DEF:IMPLIED:OBS:DATA.Resource_ID:Resource_ID@

  ,@SELECT:DIM_PROP:USER_DEF:IMPLIED:OBS:DATA.Resource_Name:Resource_Name@

  ,@SELECT:DIM_PROP:USER_DEF:IMPLIED:OBS:DATA.Access_Right_Name:Access_Right_Name@

  ,@SELECT:DIM_PROP:USER_DEF:IMPLIED:OBS:DATA.granted_through_type:granted_through_type@

  ,@SELECT:DIM_PROP:USER_DEF:IMPLIED:OBS:DATA.granted_through:granted_through@

  ,@SELECT:DIM_PROP:USER_DEF:IMPLIED:OBS:DATA.Granted_By:Granted_By@

  ,@SELECT:DIM_PROP:USER_DEF:IMPLIED:OBS:DATA.Granted_Date:Granted_Date@

  ,@SELECT:DIM_PROP:USER_DEF:IMPLIED:OBS:DATA.Access_Right_Type:Access_Right_Type@

        ,@SELECT:DIM_PROP:USER_DEF:IMPLIED:OBS:DATA.HG_HAS_CHILDREN:hg_has_children@

from

(

SELECT distinct obs.id

,obs.name

,obs.depth

,obs.PARENT_ID

,srm.unique_name "Resource_ID"

,srm.full_name "Resource_Name"

--,sec_groups_v.group_name "Access_Right_Name"

,STUFF(

  (SELECT distinct ';' + ' ' + sec_groups_v.group_name

  FROM PRJ_OBS_UNITS obs_list

  join cmn_sec_assgnd_right ***_rights on ***_rights.instance_id = obs_list.id

  join srm_resources srm_list on srm_list.user_id = ***_rights.principal_id

  join cmn_sec_groups_v sec_groups_v on sec_groups_v.id = ***_rights.right_id

  where (***_rights.principal_type = 'USER' or ***_rights.principal_type is null)

  AND (sec_groups_v.language_code = 'en' or sec_groups_v.language_code is null)

  and (obs.id = obs_list.id and srm.user_id = srm_list.user_id) FOR XML PATH('')

  ),1 ,1, ''

)  Access_Right_Name

--,sec_groups_v.description description

,tidkort.NAME + ':' + obs.NAME granted_through_type

,***_rights.instance_type granted_through

,(

    SELECT full_name

    FROM   srm_resources

    WHERE  user_id = ***_rights.created_by) "Granted_By"

,convert(char, (***_rights.created_date), 105) "Granted_Date"

,'Resource Access Rights: OBS Unit' "Access_Right_Type"

,(case when (select count(*)

  from prj_obs_units obs2

  where obs2.parent_id = obs.id) > 0 

  then obs.id

  else null

  end) hg_has_children

from PRJ_OBS_UNITS obs

left join cmn_sec_assgnd_right ***_rights on ***_rights.instance_id = obs.id

left join cmn_sec_groups_v sec_groups_v on sec_groups_v.id = ***_rights.right_id

left join srm_resources srm on srm.user_id = ***_rights.principal_id

join prj_obs_types tidkort on tidkort.id = obs.type_id --and tidkort.name = 'tidkort'

where obs.depth = '1'

and (***_rights.principal_type = 'USER' or ***_rights.principal_type is null)

AND (sec_groups_v.language_code = 'en' or sec_groups_v.language_code is null)

and @where:param:user_def:string:hg_row_id@ is null

union

SELECT distinct obs.id

,obs.name

,obs.depth

,obs.PARENT_ID

,srm.unique_name "Resource_ID"

,srm.full_name "Resource_Name"

--,sec_groups_v.group_name "Access_Right_Name"

,STUFF(

  (SELECT distinct ';' + ' ' + sec_groups_v.group_name

  FROM PRJ_OBS_UNITS obs_list

  join cmn_sec_assgnd_right ***_rights on ***_rights.instance_id = obs_list.id

  join srm_resources srm_list on srm_list.user_id = ***_rights.principal_id

  join cmn_sec_groups_v sec_groups_v on sec_groups_v.id = ***_rights.right_id

  where (***_rights.principal_type = 'USER' or ***_rights.principal_type is null)

  AND (sec_groups_v.language_code = 'en' or sec_groups_v.language_code is null)

  and (obs.id = obs_list.id and srm.user_id = srm_list.user_id) FOR XML PATH('')

  ),1 ,1, ''

)  Access_Right_Name

--,sec_groups_v.description description

,tidkort.NAME + ':' + obs.NAME granted_through_type

,***_rights.instance_type granted_through

,(

    SELECT full_name

    FROM   srm_resources

    WHERE  user_id =***_rights.created_by) "Granted_By"

,convert(char, (***_rights.created_date), 105) "Granted_Date"

,'Resource Access Rights: OBS Unit' "Access_Right_Type"

,(case when (select count(*)

  from prj_obs_units obs2

  where obs2.parent_id = obs.id) > 0 

  then obs.id

  else null

  end) hg_has_children

from PRJ_OBS_UNITS obs

left join cmn_sec_assgnd_right ***_rights on ***_rights.instance_id = obs.id

left join cmn_sec_groups_v sec_groups_v on sec_groups_v.id = ***_rights.right_id

left join srm_resources srm on srm.user_id = ***_rights.principal_id

join prj_obs_types tidkort on tidkort.id = obs.type_id --and tidkort.name = 'tidkort'

where (***_rights.principal_type = 'USER' or ***_rights.principal_type is null)

AND (sec_groups_v.language_code = 'en' or sec_groups_v.language_code is null)

and obs.parent_id = @where:param:user_def:string:hg_row_id@ or @where:param:user_def:integer:hg_all_rows@ = 1

union

SELECT distinct obs.id

,obs.name

,obs.depth

,obs.PARENT_ID

,srm.unique_name "Resource_ID"

,srm.full_name "Resource_Name"

--,sec_groups_v.group_name "Access_Right_Name"

,STUFF(

  (SELECT distinct ';' + ' ' + sec_groups_v.group_name

  FROM PRJ_OBS_UNITS obs_list

  join cmn_sec_assgnd_right ***_rights on ***_rights.instance_id = obs_list.id

  join srm_resources srm_list on srm_list.user_id = ***_rights.principal_id

  join cmn_sec_groups_v sec_groups_v on sec_groups_v.id = ***_rights.right_id

  where (***_rights.principal_type = 'USER' or ***_rights.principal_type is null)

  AND (sec_groups_v.language_code = 'en' or sec_groups_v.language_code is null)

  and (obs.id = obs_list.id and srm.user_id = srm_list.user_id) FOR XML PATH('')

  ),1 ,1, ''

)  Access_Right_Name

--,sec_groups_v.description description

,tidkort.NAME + ':' + obs.NAME granted_through_type

,***_rights.instance_type granted_through

,(

    SELECT full_name

    FROM   srm_resources

    WHERE  user_id =***_rights.created_by) "Granted_By"

,convert(char, (***_rights.created_date), 105) "Granted_Date"

,'Resource Access Rights: OBS Unit' "Access_Right_Type"

,(case when (select count(*)

  from prj_obs_units obs2

  where obs2.parent_id = obs.id) > 0 

  then obs.id

  else null

  end) hg_has_children

from PRJ_OBS_UNITS obs

left join cmn_sec_assgnd_right ***_rights on ***_rights.instance_id = obs.id

left join cmn_sec_groups_v sec_groups_v on sec_groups_v.id = ***_rights.right_id

left join srm_resources srm on srm.user_id = ***_rights.principal_id

join prj_obs_types tidkort on tidkort.id = obs.type_id --and tidkort.name = 'tidkort'

where (***_rights.principal_type = 'USER' or ***_rights.principal_type is null)

AND (sec_groups_v.language_code = 'en' or sec_groups_v.language_code is null)

and obs.parent_id = @where:param:user_def:string:hg_row_id@ or @where:param:user_def:integer:hg_all_rows@ = 1

) data

where @filter@

Hello,

I can see that the ID's for both the test_4 are similar. Can you remove the 'DISTINCT' keyword and see if the Portlet gives proper results?

Suhail.

Hi,

I removed the distinct and no differance .

I think the issue here is how the @hg_row_id@ is beeing used and that there is something i quite don't understand with how the obs.id and parent_id are beeing used by the parameters.

/Emre

It would be better if you can post your SQL Query here. There could be a possibility that only one value may be getting displayed here for test_4 instead of two. Try using the LISTAGG function for test_4 (group by id, etc) and see if both the values for test_4 are visible within your Hierarchical Portlet. Also, did you remove the keyword 'DISTINCT' from your entire NSQL Query?

Suhail.

Hi,

I removed distinct from all 3 main queries, and the complete sql is posted in the main question.

I used the format:

Select NSQL data.attribut

from

(Select attribute

from

where

Union

Select attribute

from

where

union...

) data

You have the complete SQL within the "data" and important to remember is that this is SQL Server and not oracle, which can be seen in the query for the Access_Right_Name attribute where i'm using SQL Server style "listagg".

/Emre

As per your first reply, in your SQL code: obs.parent_id = @where:param:user_def:string:hg_row_id@ or @where:param:user_def:integer:hg_all_rows@ = 1, can you replace the OR with the AND and check? Also, I can see that the fields: id and hg_has_children are both similar, whereas, parent_id is having a different value for test_4. If in case this does not work, it may be a case of joining 2 id's that are not relevant to each other.

Suhail.

Hi Suhail,

I don't understand, where am I joining to IDs that aren't relevant to each other. Can you elaborate?

I can't change the OR to AND as this has to be as is. The reason why i have the hg_all_rows = 1 is because the portlet will only export the top level of the hierarchy when exporting to excel if the OR @where... = 1 is not there.

What i could is to remove the @where... = 1 part from the second union and remove the obs.parent_id = @where from the third union and only keep the @where... = 1 in the third union to still be able to export the complete hierarchy. But doing this will not change anything and yes i have tried.

Hello Emre,

I am just trying to help you. I had a detailed look at your NSQL and can see that you've used the STUFF function only for the Access_Right_Name. So, when it is grouping by Resource_Name, as the Resource_Name is different for test_4 unlike in other situations (i.e. Administrator, PPM), it is only giving one result. So, try using a STUFF function for the Resource_Name too and see if it works.

P.S. It's always difficult to review other user's code. I have mentioned "MAY BE" in my sentences above. It does not mean I am certain that there is a wrong join happening on irrelevant ID's. I am just trying to guide you to find a workaround for your issue.

Suhail.

Hi - without disagreeing at all with anything Suhail has said, and without really trying to debug your code (because it is obviously complex!), perhaps I could get you to look at your issue from a different angle...

I think hierarchical portlets (at least the ones I've built) are very complex, but break down into needing to provide 3 different sets of data;

1) The top level(s) of the hierarchy - so row(s) that has no parent but has children

2) Middle levels in the hierarchy - row(s) that have parents and children

3) End levels - rows that have parents but not children

So where does your "test_4" occur in that breakdown?

I clearly don't understand your data-model (or what you are trying to show on the portlet) - but I'm not sure that I can visualise how you expect the "SLL 10" to appear in the portlet? Is it expected to appear below (but at the same level) "SLL 1"? But then how does the "hierarchy" work, which node do you expand to get the children of "test_4"?

I can visualise "SLL 10" appearing in the same 'cell' as "SLL 1" (i.e. not a new row) but I don't think that that is what you are wanting?

--

I don't expect you to answer any of my questions, they are really just to try to get you to think about what it should look like (I'm to be convinced that you can display your data how I think you are expecting to be able to display it), so I'm suggesting you might have a design-issue rather than a code-issue then?

( Feel free to ignore all this, I'm really waffling, I haven't looked at this in depth at all, just throwing ideas out there! )

Hi,

Sorry for late reply.

David you asked some really good questions that got me thinking.

I now have Resource Name and Resouce Access in the same column, which is good enough for customer.

Thanks for all the help.

Just a try, assuming that you have handled hg_all_rows correctly, do an Export to excel and compare the results with the portlet. It may help.