The Downfall of Business Objects?
Sometimes the wonderful drag and drop functionality of BusinessObjects can be its downfall as well. When report writers do not have access to Designer or edit access the universes that they must use there exists an opportunity for this downfall. If any object in the world of SAP BusinessObjects follows the rule of trash in-trash out it is the universe.
BusinessObjects Universes Must be Perfect
A universe has to be just right. It can’t be too complex, nor too simple. It can’t have errors and it must be up-to-date with its underlying database. Additionally, different users have different expectations of a universe and therefore all judgements a universe’s adequacy hinge on the collective opinions of that universe’s report writers/developers. Nothing I am saying hear is news to anyone that has struggled with universes and reports; however, I hope it helps some feel more justified or more understanding.
If a universe is deficient, incomplete, or lacking there is a workaround. If a user is very comfortable with the data source data model and they want to bypass the universe, there is a workaround. If you are editing your report’s query and you want to test something not in the universe without editing the universe, there is a workaround. What is this workaround? Use Custom SQL.
How Do You Use Custom SQL?
First of all, you must have the rights of “View SQL” and “Edit Query” granted to you user account (members of Administrators user group should have these already). Both of of these are found in Business Objects XI 3.1 under the object’s “User Security” > “Advanced” > “Content” > “Web Intelligence Report”. Once you have these rights you can open the report (modify) in Java Report Panel. Once everything loads. click on the “Edit Query” button and then click the small “SQL” button on the toolbar. You will then see a new window open named “SQL Viewer”. Here you can see the underlying SQL of the query. Please note that you can copy it to your clipboard by clicking the “Copy” button. You can also pass the point of no return by selecting the radio button option of “User custom SQL”. Actually, don’t let my drama scare you, you can always undo your custom SQL. In fact, it is rather easy to lose your custom SQL and revert to the Business Objects generated SQL; so please take care to back-up your custom SQL statements.
PLEASE NOTE: If any of the above mentioned options are disabled for you then you do NOT have your rights set incorrectly.
The Rules of BO Custom SQL Editing
Here are a few rules that you must follow when working with Custom SQL in Web Intelligence (or Desktop Intelligence):
- The number of fields in your SELECT clause must match the number of objects in the query
- The type of the fields in you SELECT clause must match those of their corresponding BO objects. In other word, if your first query object is “Customer Name” and it has a type of “string” then the first field/item in your SELECT clause must also have a datatype of string. If the third object in you query is of date type then you better make sure that the third field/item/element in your SELECT clause is of the date datatype. Usually a click of the “Validate SQL” will highlight any such violations (see screenshot).
- This rule is really an extension of rule #2. Functions on objects in the SELECT clause are allowed; however, they must output a matching data type
- Don’t forget about object type; the object containers will be treated by BO according to their type. If the BO object you selected, for example, “Customer Number” is a dimension object and you successfully edited the SQL and forced order quantity (an obvious measure candidate) into this object. You cannot expect BO to start treating the container object “Customer Number” like a dimension in any aggregations. This won’t cause errors; nevertheless, for best results please try to use container objects that will match your custom SQL field in object type.
- When your Custom SQL edits are complete I suggest you copy the SQL and save it to a file somewhere, then click the “Save” button, and click “Run Query” to refresh the query and data.
- After you Custom SQL is set then you should take much care not to touch the rest of the query in any way. That is to say, do not attempt to adjust the results section or the conditions sections. Any edits to these will result in reverting your report’s SQL to that generated by BusinessObjects and your custom SQL statement will be lost completely. So if you suddenly decide you need to add another object then you have to be very careful to copy the SQL you have, then add the object and then replace the SQL that you copied. BE CAREFUL.
- Do NOT select the option to “User the SQL generated by your query” unless you want to LOSE all of your custom SQL.
- This is not actually a rule, but I felt I should point out that as long as rules 1, 2, 3, and 4 are followed the fields used in the SELECT clause and the contents of the rest of the the SQL statement do not have to have any relation at all to the objects selected.
Let’s Build BO Custom SQL Knowledge Base Together
I have not played around with Custom SQL as must as some. The best tip I can offer beyond the rules above is that if you plan to do this a lot you might want to prepare some dummy/generic objects that has their data type and object type in the object name. This would make it easy to get the right objects that you need.
Please share any tips that you might have picked up along the way.