BusinessObjects Board

Query Builder Query

Hi,

Iam working with BOXI Rel 2 and Query Builder. I am trying to retrieve all the crystal
report names under folder (SI_ID = 634974). Please can you advise if this possible?

Many Thanks


shin_11 (BOB member since 2008-01-03)

Hi.
Could you try with something like:

select * from ci_infoobjects where SI_KIND = 'CrystalReport' and si_parent_folder=634974

good luck!

[Moderator Edit: Added code formatting - Jansi]


Jaimepe :es: (BOB member since 2006-12-10)

I have run the query but the page keeps timing out…

It is possible this query can be run via a sql instance?

Thanks


shin_11 (BOB member since 2008-01-03)

I would change the query to

select si_name from ci_infoobjects where SI_KIND = 'CrystalReport' and si_parent_folder=634974

Running select * forces the system to do a lot of work that may not be necessary.

You cannot run this query through anything other than query builder or the SDK. The queries require the Bus Obj framework to collect the information, it is not stored in a simpel data structure.


jwhite9 :us: (BOB member since 2006-07-28)

Hi, thanks for the code…I have tried that…and the results coming back are incorrect. I am expecting a list of 159 reports but every query I have tried keeps bringing back 1000 records in the query builder.

Anyone any ideas?

Many Thanks


shin_11 (BOB member since 2008-01-03)

There is a default limit of 1000 records returned by a query. To change that you need to modify the query.

select top <max # records> si_name from ci_infoobjects where SI_KIND = 'CrystalReport' and si_parent_folder=634974

Replace <max # records> with a number larger than you would expect to be returned. Try 10000 in your case, you should see all the data.


jwhite9 :us: (BOB member since 2006-07-28)

You can use the utiity found here


Sushir Patel :uk: (BOB member since 2006-04-19)

Hi, thanks for the code update…but that did not work. Its missing reports from the results and also duplicating report names.

Thanks


shin_11 (BOB member since 2008-01-03)

Hi HRS…please can you advise how your product works…as I have donwloaded it…and I am only after report names from folder in the repository. Thanks


shin_11 (BOB member since 2008-01-03)

  1. Open the excel document on a workstation where BO Client tools are installed (DESKI / Designer)
  2. click the “Get Document Deatils” button on the first page
  3. fill in the Logon details of your system (CMS, Username, pasword and authentication), leave everything else as default click “Extract Data”.
  4. Once the data has finished being retrieved, go to the “Documents” tab and filter by “Top Level Folder” or “Folder Location” to see only reports that are contained in a that particular folder.

Regards

HRS


Sushir Patel :uk: (BOB member since 2006-04-19)

Hi HRS,

thanks for the above but I am getting error message attached…

Thanks
VB Error 1.doc (64.0 KB)


shin_11 (BOB member since 2008-01-03)

Please read the instructions on the dowload page regarding changing the libraries to that of XI R2.


Sushir Patel :uk: (BOB member since 2006-04-19)

HRS, sorry but I cannot find instructions to change the library…I have never used VB…

Many Thanks


shin_11 (BOB member since 2008-01-03)

From the first message in the utility downloaded topic…


Sushir Patel :uk: (BOB member since 2006-04-19)

HRS, that is a brilliant tool…many thanks…

just one thing, the ‘progress’ message does not disappear…any ideas?

Many Thanks


shin_11 (BOB member since 2008-01-03)

it should, but if it does not, just start again.

I will address that in the next version!


Sushir Patel :uk: (BOB member since 2006-04-19)