Help on Query Builder

[system]

Hi All,

Could you please help me to get the SQL to get below information from the Query Builder.

Reports which are in Personal folders
DeskI Report name
Folder name (Inbox / Favorites)
User Name
DP type
Universe name if DP is Universe

Thanks,
Muthu.

---

msaru (BOB member since 2012-07-19)

[system]

It’s not quite as easy as all that.

You seem to want to audit each user’s personal documents

first you’ll need all the users

SELECT SI_CUID, SI_NAME FROM CI_SYSTEMOBJECTS WHERE SI_KIND = ‘User’

Then you’ll have to go through each user and get their folders, and the documents within each folder.

HOWEVER, for a given DeskI report - information is not stored in the CMS about the dataproviders within the document.

If you want to know that, you’ll have to open each document with the SDK and interrogate it, then save the information off to your own database table/text file.

---

Hayden_Gill (BOB member since 2002-08-15)

[system]

@Hayden_Gill

HOWEVER, for a given DeskI report - information is not stored in the CMS about the dataproviders within the document.

If you want to know that, you’ll have to open each document with the SDK and interrogate it, then save the information off to your own database table/text file.

msaru,
This utility can help you with the above:

DeskI Documents Inspector

Author: Marek Chladny Author notes: Platform: BO XIr2 How to use it: Save all DeskI reports that will be checked into a separate working folder. Open DeskI Documents Inspector in DeskI of BO XIr2. Enable macros (if you get the message that the report contains macros). Use “Select working folder” button and navigate to the folder that contains reports. Type in a keyword in the “search keyword” box. Use * (asterisk) for all. Set what will be searched (variables and formulas, data provider …

---

Marek Chladny (BOB member since 2003-11-27)