cancel
Showing results for 
Search instead for 
Did you mean: 

Query To list out the universes of reports in specific folder

krizz_45107
Explorer
0 Kudos

I have a requirement to collect the list of universes of the reports present in particular folder.

I want to get the result using the query builder.

Kindly help me out with the query to generate a list of universes used in a particular folder.

Accepted Solutions (0)

Answers (2)

Answers (2)

LepreuxLoic
Employee
Employee

Hi,

Following is valid for UNX Universes only

If you already have SI_ID value for the folder you're looking into, then you can use a query such as:

SELECT SI_ID, SI_NAME FROM CI_APPOBJECTS WHERE CHILDREN("SI_NAME='Document-DSL.Universe'", "CHILDREN('SI_NAME=''Folder Hierarchy''', 'SI_ID=123456')")

Otherwise if you only have name of this folder, it will be just about adding a filter for this property:

SELECT SI_ID, SI_NAME FROM CI_APPOBJECTS WHERE CHILDREN("SI_NAME='Document-DSL.Universe'", "SI_KIND='WEBI' AND CHILDREN('SI_NAME=''Folder Hierarchy''', 'SI_KIND=''FOLDER'' AND SI_NAME=''Web Intelligence Samples''')")

Just be careful that this contains some double quotes and some repeated single quotes.Regards,
Loic

Joe_Peters
Active Contributor
0 Kudos

If you need unv universes, just change "Document-DSL.Universe" to "WebI-Universe"

krizz_45107
Explorer
0 Kudos

I want the unv universes and I even changed the query to "Web-Universe" but still I am not getting the required output. Happy to have some suggestions.

LepreuxLoic
Employee
Employee
0 Kudos

Hi,

I guess it's because you forgot a character in name of relation : it's "Webi-Universe"

Otherwise you could have a single CMS query to retrieve all Universes, no matter UNV or UNX, for all WebI documents in a given folder:

SELECT SI_ID, SI_NAME, SI_SPECIFIC_KIND FROM CI_APPOBJECTS WHERE CHILDREN("SI_NAME IN ('Document-DSL.Universe', 'Webi-Universe')", "SI_KIND='WEBI' AND CHILDREN('SI_NAME=''Folder Hierarchy''', 'SI_KIND=''FOLDER'' AND SI_NAME=''Web Intelligence Samples''')")

Regards,
Loic

DellSC
Active Contributor

The challenge with this is that universe names are not stored in the Webi reports, just the SI_ID value of the universe. And, unfortunately, I haven't been able to find a QueryBuilder relationship that will work from Webi to Universe - just ones that go from a specific universe to its Webi reports.

In the past, I have written code that does this type of thing using both the Java and the .NET versions of the BI Platform SDK and exporting the data to a .csv file. I think you could also do this using a combination of the BI Platform and Web Intelligence RESTful Web Services SDKs.

Or there are third-party tools that can get this information for you.

-Dell

krizz_45107
Explorer
0 Kudos

Hi Christy. Thank you for your suggestion. But I am not allowed to use the third party tools. So can you please suggest a query to get the output using the SI_ID. I have tried everything but still I am not getting the desired result. Thanks in advance.

DellSC
Active Contributor
0 Kudos

There is no single query for this. If you're just using QueryBuilder, you would need to get the list of SI_IDs for the universes for each report and then run another query that will pull the universe names based on those SI_ID values.

Or you can use the .NET BI Platform SDK, Java BI Platform SDK, or the RESTful Web Services SDKs (both platform and Webi) to pull this information in code. I've used all of them and I usually export the information to .csv file so that it can be reviewed.

-Dell