CMS Self Join

[arno_huerlimann56]

If I want to know, which webi-Document has a Folder as Parent Object, I can do this directly with a SQL to the CMS Database.

Select p.ObjectID Ordner_ObjectID, o.ObjectID Web_ObjectID, p.ParentID, o.TypeID Typ344Webi, p.TypeID Typ1Folder, p.OwnerID, p.Version, CAST(p.SI_CUID AS VARCHAR(MAX)) Ordner_SI_CUID, CAST(o.SI_CUID AS VARCHAR(MAX)) WebID_SI_CUID from CMS_InfoObjects7 o <br>Join CMS_InfoObjects7 p ON p.ObjectID = o.ParentID<br>where<br>o.TypeID = 344 And p.TypeID = 1;

This one does not work:

Select p.si_cuid, o.si_cuid, p.si_parentid, o.si_kind, p.si_kind, p.si_owner, p.si_cuid
From Properties o 
Join Properties p ON p.si_cuid = o.si_parentid
where o.si_kind = 'Webi' And p.si_kind = 'Folder';

Does anyone know how I can do this in a WEBI-Document, maybe with FHSQL? I couldn't not find how to "self-Join" the Database.

[JohnClark]

You won't be able to use FHSQL. The universe is written using the SDK or RESTful web service, I'm not sure which. You will have to use the objects from the universe in a normal query.

[TJe]

As mentioned by John, you should find sample in the CMS universe: Unlock the CMS database with new data access driver for BI 4.2. Using parentId as filter in the query panel should work for you. You can also use a Query to get the parentId with a filter on name and then use the result of this query as a filter for a next query. Ancestor will allow you to get all objects starting from a specific folder (with subfolders).

[JohnClark]

Most of the data in the CMS database is encrypted and you won't be able to query it directly.

Try the BI platform CMS system database.unx universe that comes with Business Objects now.

[arno_huerlimann56]

That is exactly what I am trying... FHSQL in a webi document: