BusinessObjects Board

BO Admin - Query Builder

building reports webintelligence
#4

There is no way to do this from within query builder. You would have to write some custom code to get the output in another format.

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

#5

Thanks

One more questions. Is it possible to display data by column and row ?

When we run the query in query builder results are listed as

Column Name : Value

I would like to display the resutls as

Column Name
value
value
value

DesiGuju (BOB member since 2007-04-23)

#6

The formatting of the results is done in the JSP/ASP pages and if you are a GURU in coding play with the application pages.

Sheshachala5 :india: (BOB member since 2004-01-09)

#7

The Query Builder is designed for quick retrieval of isolated or small amount of information. It is not meant to be used for reporting purpose. This is where the SDK comes in.

Some people have succeeded in copy-n-paste the query result into Excel for further manipulation. But it is a tedious task and it may or may not work every time.

substring :us: (BOB member since 2004-01-16)

#8

Hi,
In Query Builder I am trying to club these two queries into a single query but not able to do so, has any one done that ?

Thanks in Advance,
Vinod

vinodk (BOB member since 2006-12-01)

#9

have a look at the excel doc here How to find the list of reports developed on a universe? it may help.

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

To find webi report folder name using metadata
#10

This query returns Webi reports that are based on a particular universe:-

SELECT SI_ID, SI_NAME, SI_WEBI , SI_OWNER
FROM CI_INFOOBJECTS, CI_SYSTEMOBJECTS, CI_APPOBJECTS 
Where PARENTS("SI_NAME='Webi-Universe'","SI_NAME ='Your Universe Name'")

Mak 1 :uk: (BOB member since 2005-01-06)

#11

Check this post for a sample of how you can use the SDK to retrieve data from the InfoStore:
https://bobj-board.org/t/115414

darcstorm :philippines: (BOB member since 2008-07-22)

#12

Mak,
Thanks the query which you gave is working for me, but I am not able to find the folder name of the report, I tried with SI_PATH and SI_FOLDER_NAME1 but no luck. Can you help me in this ?

And for the query which you gave there are no joins between the tables, and how is it working can ou explain me the query ?

And last question…I heard that there is a universe on repository tables, where can I get that ?

Thanks in advance.
Vinod

vinodk (BOB member since 2006-12-01)

#13

Mak thanks, the query is working…but I am not able to find the folder name of the report, I tried with SI_PATH and SI_FOLDER_NAME1 but no luck. Can you help me in this ?

And for the query which you gave there are no joins between the tables, and how is it working can ou explain me the query ?

And last question…I heard that there is a universe on repository tables, where can I get that ?

Thanks in advance.
Vinod

vinodk (BOB member since 2006-12-01)

#14

I’m afraid not, I don’t know the answer to this.

Someone else gave it to me and it works, thats all I know… :slight_smile: .

Its called the Activity universe and should be installed by default.

Mak 1 :uk: (BOB member since 2005-01-06)

#15

Cannot say if that is possible in query builder,but you can get it the other way round
To list reports in a particular folder by running 2 queries
Find the folder name

SELECT SI_NAME, SI_DESCRIPTION, SI_PARENTID FROM CI_INFOOBJECTS WHERE SI_KIND IN ('FullClient', 'Webi')

pass the folder name or parentID from above query

SELECT SI_NAME, SI_DESCRIPTION FROM CI_INFOOBJECTS WHERE SI_KIND IN ('FullClient', 'Webi') AND SI_PARENTID=123

.

haider :es: (BOB member since 2005-07-18)

#16

Hi
I am looking for a single query where I get the list of report names and the folder name for a given universe. Not sure if it is possible.

Thanks,
Vinod

vinodk (BOB member since 2006-12-01)

#17

But you had asked about the documents folder and not universe folder?

I guess you cannot get the universe folder name along with list of reports in one query using query builder

.

haider :es: (BOB member since 2005-07-18)

#18

Thanks for your reply…I don’t want the universe folder name…I want the report name and the report folder name for a given universe using a single query.

Thanks,
Vinod

vinodk (BOB member since 2006-12-01)

#19

To get list of reports for a univese you will have to run one query on CI_APPOBJECTS.
Use this list of reports to run another query on CI_INFOOBJECTS to get report & folder names
But you may not be able to join them to have one single SQL

.

haider :es: (BOB member since 2005-07-18)

#20

Hi All,

Could someone please let me know, how to find out list of ALL reports (not only webi) based on a particular universe.

Thanks,
Amogh

PAmogh (BOB member since 2008-08-30)

#21

Check this

Select SI_WEBI from CI_APPOBJECTS where SI_KIND='Universe' and SI_NAME='universe_name'

this extracts list of report ids for the universe passed & then to get report names run this query

SELECT SI_ID, SI_NAME FROM CI_INFOOBJECTS WHERE SI_ID in (123,456)

.

haider :es: (BOB member since 2005-07-18)

#22

How can I retrieve List of reports available in perticular folder using a single query which can be run from Query Builder.

Thanks…

aniketp :uk: (BOB member since 2007-10-05)

#23

This is what I gave earlier to get list of reports in a particular folder

SELECT SI_NAME, SI_DESCRIPTION FROM CI_INFOOBJECTS WHERE SI_KIND IN ('FullClient', 'Webi') AND SI_PARENTID=123

where 123 is the folderID which can be found either by taking the mouse over a folder in Infoview or by running another query to get a list of folder names and IDs

.

haider :es: (BOB member since 2005-07-18)