Query to Extract details from Query Builder

[Former Member]

Hi Experts,

Can someone please let me know the query to extract list of reports that run within last 6 months from "X" universe in BOXIR3.1 in query builder and

query to extract list of users who have access to "X" universe in BOXIr3.1?

Regards

Mohammed

[Former Member]

Hi all,

I don't know if anyone is still out there looking for this!

But I was able to come up with a query for a customer requirement which is used to find:
- WebI reports

- Based on .UNX universe

- And are saved ONLY in user favorites

The full query is below:

select SI_NAME, SI_ID, SI_CUID from CI_INFOOBJECTS where (SI_ANCESTOR = 18

SI_KIND = 'WebI' "SI_DSL_UNIVERSE.1"=1234)

The breakdown is:

select SI_NAME, SI_ID, SI_CUID

from CI_INFOOBJECTS

where (

SI_ANCESTOR = 18 //root level for all favorites folders

SI_KIND = 'WebI' //Web Intelligence report

"SI_DSL_UNIVERSE.1"=1234) //SI_DSL_UNIVERSE is the field holding the ID of the .unx; 1234 is an example, use the universe object ID here.

This field has sub-fields. there is SI_TOTAL which is number of universes the report is based on, if multi-universe.

And then there is 1, 2, ... n which holds object ID of n universes that the report is based on.

So we use SI_DSL_UNIVERSE.1.

If we do not include the ancestor field, it will show reports from PUBLIC folders also.

Regards,

Sid

[former_member182521]

Check if this helps.

http://scn.sap.com/docs/DOC-43081

[Former Member]

Thanks mani for sharing the Doc but i am unable to find the query for my above mentioned requirement.

Regards

Mohammed

[former_member182521]

Atleast the below query could help you in getting reports that ran in last 6 months.

SELECT SI_ID, SI_NAME,SI_UNIVERSE, SI_DSL_UNIVERSE, SI_LAST_RUN_TIME FROM CI_INFOOBJECTS WHERE SI_KIND='WEBI' AND SI_LAST_RUN_TIME >= '2013.02.26.00:00:00'

For getting Universe association you are required to develop a custom SDK code as this is not possible in single query.

Regards,

Mani

[Former Member]

Mani,

Thanks for sharing your knowledge.

I have executed the below query to extract list of reports,user which ran in last six months for particluar universe.

SELECT SI_ID, SI_NAME,SI_UNIVERSE,SI_TIMESTAMP, SI_WEBI, SI_OWNER,SI_LAST_RUN_TIME FROM CI_INFOOBJECTS, CI_SYSTEMOBJECTS, CI_APPOBJECTS Where PARENTS ("SI_NAME = 'Webi-Universe'", "SI_NAME = 'Universe_name'") and SI_LAST_RUN_TIME >= '2013.02.26.00:00:00'

Let me know if you have any queries.

Regards

Mohammed