Why I cannot have customized query in Web Intelligence - Need Help Urgently

[Former Member]

Hi,

I am working on Web Intelligence. Here I am using universes created.

But I don't want to automatically create SQL query, instead I will write my own query.

I cant write customized query because while saving query, it shows me an error - field does not match.

Also I cannot have field name alias.

I am wondering, if there are such restrictions in Web Intelligence, it is very difficult to achieve our reporting needs.

Thanks & Regards,

Prileep

[Former Member]

If you want write your own query then in query panel edit "SQL" and place the code what you want.

Thank You!

[Former Member]

I already know that feature already.

I cant have more columns in my query because it gives me an error - column not found.

Also I cannot have column alias in my sql query

[Former Member]

It seems to be are you trying to display data that is not in your universe. How you can achieve it??

Use existing columns to get desired result.

Check the column in universe whether it is there or not and Parse the object also.

Thank You!

[Former Member]

Prileep,

"Custom SQL" is a tricky part, but here are the clues:

1) write your SQL query independently in your favorite edit/environment to ensure accuracy

2) look at the output columns and determine if they are of type integer, character, or date

3) go to WebI and "build" a new query, choosing objects that fall in line with the sequence of type output in step 2 (these objects will serve as placeholders so that when your own SQL runs, BO will see things "lining up")

4) once step 3 is perfected, click on Cusom SQL, and choose to use your own SQL, then copy/paste your code from #1, validate, save

5) execute your query

6) The objects you chose in step 3 will appear as column headings in your output grid, so you have two choices:

6a) build local variables taking hostage of the object name as it appears and give your local variable the correct name (heading) (so SEQ_NO was an object from step 3, but in reality it is ITEM_NO; you create a local variable named ITEM_NO and in the formula you use =SEQ_NO; then you use the local variable on the grid, thus the column name is now correct)

6b) just rename the headings in the grid to your more practical names, as applicable

Voila, you have now constructed a custom SQL using "BO Rules"

Thanks,

John

[Former Member]

John, Thank you very much for you solution and understanding my problem here.

Actually I am new to BOXI - Web Intelligence tool.

I am not very clear with the 3rd step you provide. Could you please explain how to do that, because I am new to this tool.

Thanks,

Prileep

[Former Member]

I think you have not answered my question yet, Can I have field name alias in my customized query when I do not have predefined objects with the same name in my universe?

[Former Member]

Prileep,

3) go to WebI and "build" a new query, choosing objects that fall in line with the sequence of type output in step 2 (these objects will serve as placeholders so that when your own SQL runs, BO will see things "lining up")

I'll try to explain: You need to go through the motions of building a new report. Log into BusinessObjects, then click on "New", "Web Intelligence Document". A list of Universes will appear. For the purposes of your custom SQL, it shouldn't matter too much which universe you pick as long as it will support your requirement of data types you identify in step #2. Once you have a universe chosen, one at a time grab an object and place it in the Result Objects pane according to whether you need a character-, interger-, or date-type column according to step #2. These objects will server as placeholders to support the data-types that your ultimate custom SQL will generate. When you are done, you can then use the SQL icon, and choose "use custom SQL", paste your own query replacing the query that is shown, click on "validate", and "save" (if no syntax errors are detected then you're good to go). Click execute query and voila, you have a custom SQL implemented, the BO way....

Thanks,

John

[Former Member]

Can I have field name alias in my customized query when I do not have predefined objects with the same name in my universe?

By alias, I'm assuming the following technique we use in normal SQL:

select Product_ID as SEQ_ID from tablex

So, the Product_ID is aliased as SEQ_ID, right so far? In custom SQL using an alias is okay for your internal use, however, in step 3, when you grab an object and put it in the Result Objects pane, then use your custom SQL, execute query, and receive your initial grid, that object "name" persists in the header of the grid, though your custom SQL pulled the data that you wanted, it is labeled "wrong" due to the a**-backwards way we must trick BO into running our own (custom) SQL code. It is not a good situation we have, but bastardized in trying use custom SQL -- take it or leave it I guess.

Thanks,

John

[Former Member]

John - Thanks for your information.

I was frustrated with this problem.

Now the alternate I have done is, I made an alias name which exist in universe objects and not used in my report for any other purpose.

Assuming the particular field is the one I wanted, even if it is not named properly....

Thanks & Regards,

Prileep

[Former Member]

Prileep,

Okay, now you're getting the hang of it. Kudos to you for persevering -- have a happy custom SQL career

Thanks,

John