Query Builder - favourite reports for a group

security CMC
1

Hi all,

I am using query builder (BI 4.2) to export data from one BI 4.2 platform to another.

I am struggling to build the query to extract all favourite folders and subfolders and the reports in those folders for a specified group.

Can someone help please?

Thanks,
Chris

ChristianKey :uk: (BOB member since 2008-09-19)

2

This is my query but is not a valid query. What is wrong?

SELECT static, relationships, SI_PARENT_FOLDER_CUID, SI_OWNER, SI_PATH FROM CI_INFOOBJECTS,CI_APPOBJECTS,CI_SYSTEMOBJECTS WHERE
PARENTS(ā€œSI_NAME=ā€˜User-Favouritesā€™ā€, ā€œDESCENDENTS(ā€˜SI_NAME=ā€œUserGroup-Userā€ā€™,'SI_NAME=ā€"ā€™)")

ChristianKey :uk: (BOB member since 2008-09-19)

3

Are you sure you want to know? :twisted:

Nested relationship queries can get very hairy due to the weird rules for delimiter substitution. And more so because they canā€™t contain newline characters.

If we describe your requirement as:

  1. Get all descendant users of group x
  2. Get all favorites folders for identified users
  3. Get all descendant objects of identified favorites folder
  4. Also include the favorites folders themselves.

we see that itā€™s a third-order relationship. The following query will do it for the Administrators group:

select si_name,si_kind from ci_infoobjects
where 
    descendants
    (
        "si_name='folder hierarchy'",
        "children('si_name=''user-favorites'' ',
                  'children(''si_name=''''usergroup-user'''' '',
                            ''si_name=''''administrators'''' '')')"
        
    )
    or 
    children("si_name='user-favorites'",
        "descendants('si_name=''usergroup-user'' ','si_name=''administrators'' ')")

Note that Iā€™ve formatted this for easy reading. Youā€™ll need to remove all the newlines between the double quotes in order for it to work.

joepeters :us: (BOB member since 2002-08-29)

4

Joepeters, you are a STAR!

Thatā€™s exactly what I need! All those quotes and nesting was frying my brainā€¦

Cheers!
Chris :crazy_face:

ChristianKey :uk: (BOB member since 2008-09-19)

5

Glad I could help!

joepeters :us: (BOB member since 2002-08-29)