expose SQl behind an object in the universe

building semantic layer
1

Is there anyway to expose the sql behind an object in the universe? I am looking for a way to get at the sql used to generate an lov and expose it up to Crystal so that the BO API can pick it up?

Thanks!

muffntuf :us: (BOB member since 2006-01-04)

2

Double-click the object
Click the Properties tab
Click the Edit button for the LOV query
Click the SQL button on the query panel

There you go 8)

Dave Rathbun :us: (BOB member since 2002-06-06)

3

Thanks Dave,

Maybe my context wasn’t good enough. I need to expose that sql code to a crystal report.

Thanks!

muffntuf :us: (BOB member since 2006-01-04)

4

Expose meaning a “live link” of some sort?

I assumed you were just going to capture the SQL and copy it into Crystal…

Dave Rathbun :us: (BOB member since 2002-06-06)

5

Rather present it somehow in the parameter derived from the object lov for the web api to pick it up.

muffntuf :us: (BOB member since 2006-01-04)

6

Hello,

Does anyone knows how to expose SQL for each and every object in a universe?
Is there anyway to document SQL for every object simply clicking query?

please don’t say that copy query from object properties —>edit query—>SQL as many objects existing in universe it requires much more physical work.

The prepared document will be used for adhoc reporting.
I am using BO XI/R2 version.

Thanks in advance
Kumar

kumar_m01 (BOB member since 2006-07-27)

7

Click here and your wishes will come true. 8) At least they will if I am reading your message correctly. You want a way to capture the object name and the related SQL to some other format, correct? If so, Dwayne’s utility certainly fits the bill.

Dave Rathbun :us: (BOB member since 2002-06-06)

8

Hi Dave,

Thanks for quick reply. Here is your replied solution goes.

With the document you provided we can see business objects query like @select,@aggregate,@variable functions query not the behind actual SQL query.

As my users have knowledge about SQL not about ‘@’ queries.

Thanks in advance
Kumar

kumar_m01 (BOB member since 2006-07-27)

9

So use it as a starting point to build on. :slight_smile:

There is no facility in the tool to do what you’re asking… in fact, printing the universe to PDF would generate the output you’re describing with @Select and @Whatever and so on.

Dave Rathbun :us: (BOB member since 2002-06-06)

10

HI,
If I understand your requirement correctly, you want to capture the actual sql that is generated when a user runs a report in BO?
This without the @varaible/prompt syntax but with the real values entered by the user.

If so, then you can ask your BO admin to turn the sql log on for sql queries (i dont know the exact term for it). You might have to set up a local directory on your local machine, where this log file is stored each time you run a report. This log file will have the sql query without the @ BO syntax, but with the values entered by the user.

Don840 (BOB member since 2005-03-14)