BusinessObjects Board

Query Builder Query

general discussion
#1

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)

#2

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)

#3

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)

#4

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)

#5

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)

#6

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)

#7

You can use the utiity found here

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

#8

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)

#9

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)

#10
  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)

#11

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)

#12

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)

#13

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)

#14

From the first message in the utility downloaded topicā€¦

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

#15

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)

#16

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)