Query Builder Query to Return List of Reports that use a Specific Universe

other Business Query
1

I’m trying to write a Query Builder query that will return all of the reports that use a specific universe. I know this can be obtained in other ways but I need to pass the query into the lcm_cli.bat file for promotion. This is to allow us to promoted all of the reports that use a specific universe. This is part of our effort to convert our UNV universes to UNX.

I can see that the report IDs are listed in the SI_SL_DOCUMENTS property of the universe but a query similar to this isn’t valid:

SELECT SI_NAME FROM CI_INFOOBJECTS, CI_SYSTEMOBJECTS, CI_APPOBJECTS
WHERE SI_ID In (SELECT SI_SL_DOCUMENTS FROM CI_INFOOBJECTS, CI_SYSTEMOBJECTS, CI_APPOBJECTS
WHERE SI_ID = 14409)

This query

SELECT TOP 10000 si_id, si_name,SI_AUTHOR,SI_LAST_RUN_TIME,SI_PARENTID, SI_ISTANCE from ci_appobjects, ci_infoobjects where PARENTS(“SI_NAME=‘Webi-Universe’”, “SI_NAME=‘NAME_OF_THE_UNIVERSE’”)

from this post Bo Query builder. List all report of a universe works for UNV universes but not for UNX universes which is what I need.
I suspect the issue with with th SI_NAME=‘Webi-Universe’ parameter but I can’t find anything that works. I’ve tried DSL.MetaDataFile, DSL.Universe, and CrystalEnterprise.DSL.MetaDataFile.

How can I return a list of report SI_IDs that use a specific UNX universe?

2

This isn’t great, but could you make this work?

select SI_ID, SI_NAME, SI_SL_DOCUMENTS
from CI_APPOBJECTS
where SI_ID = 10556

That give a list of reports that use that universe…

image
image746×427 14.7 KB

I’ll keep looking.

Noel

3

That’s basically what I put in my subquery to select the reports but that SQL wouldn’t run.

4

Can you just hard code the report SI_IDs instead of the subquery?

You can get your list of report SI_IDs and export them using biclever CMS Query Builder.

image
image998×723 42.1 KB

It is pretty sad that seemingly simple things need to be so difficult. :enraged_face:

5

That’s the route I’m going to attempt next. I’m going to try and create a report against the CMS universe to pull the list out and create a csv file of the IDs so I can use PowerShell to pick them up and update a .properties file. At least that is my hope.

1 Like
6

CMS queries are “SQL-ish” – standard joining and subqueries don’t work.

You’re most of the way there with your relationship query - the relationship name you need is ‘document-dsl.universe’. So this should work for you:

select * from ci_infoobjects where parents(" si_name = 'document-dsl.universe'","si_id = 14409 ")

The second part of the query (“si_id=14409”) is what’s used to filter on the universe. I’ve used si_id, but you can use si_cuid, si_name, etc.

You can get a list of all available relationship types by querying for objects under si_id 46:
select si_name from ci_systemobjects where si_parentid = 46

2 Likes
7

That gives me what I am looking for. That should work for my selection query.
Thanks very much for your help.

I’ll need to save off that query for the relationship types. I have a feeling I’ll be needing that again.