Bypassing the Universe – Using Custom SQL

50 thoughts on “Bypassing the Universe – Using Custom SQL”

1. Thank you
   Thank you

   and

   Thanks !!!!

   I would be happy to share stuff as I get going – as for many years, I will start out with the visual interface and as I encounter limitations will do the custom – already have with filters as it is so much easier to nest ORs and ANDs textually when they get a little more complicated, at least for me.

   Chris Bush

2. A tip I developed over the years, after using much custom SQL:
   Once you realize you’re going to be going down the custom SQL path, take an object from the universe, drag it into the conditions section of the query pane, and make it equal to, and then type a value like “SQL MANUALLY EDITED!! DO NOT MODIFY!!”

   This way, when you get back to this query, in 6 months from now, you’ll have a visual cue to the fact the query you see before you is not the one actually being run in this report.

   I wish BO did that for you, and prevented you from altering a manual SQL query without warning you first, but till they do, this is a solution that has saved many reports from being destroyed. This is particularly true in an environment in which several people might work on the same report over time.

3. You are welcome Chris. Glad I could help. You remind me that the “WHERE” clause was the most common place to go custom.

   Thank for the tip Ethan, I really like it. I suppose one could also add something to the report itself or the description of the report if acceptable to users.

4. Hi Julian, is it possible to use the WITH Clause in free-hand sql of a WebI

5. Hi GaryC, in free-hand SQL you can get away with almost anything that that your database allows. This means you can even use custom functions. For example, if you database is Oracle, then if you can run a SQL statement successfully in SQL*Plus then you should be able to paste that same statement in your report’s custom SQL, as long as the select clause items match in type and number to the objects of the report. So you can even do things like derived tables, union, use “having”, and other things. Please share your experiences when you can.

6. Using custom sql with ms sqlserver stored procedures – works with BO XR r2 SQL server 2005driver – anyone know about r3?

   To get data back from a SQL server sp in webi, you can customize the sql AS LONG AS you follow 2 rules:

   – first line must be”SET no count on” so as not to confuse it with multiple resultsets.

   – Also have to include the word “select” within comment delimiters. BO seems to need to see this before it bothers passing the query on to the database to parse.
   So this works (assuming you follow the rules about object typing and so on discussed above.

   SET no count on
   /* select */
   EXECUTE MyProcedure

   Can extend this to use the @prompt syntax to pass runtime parameters to your sp.

   I use a dedicated universe with a set of 20 generic text, number and date type objects (called text1,text2, etc) which I use when I need to use SPs. (Also use the essential technique of persisting the custom sql in a condition object in the query panel so it doesn’t get lost when someone starts fiddling with the query!))

7. I am a beginner in BO. I saw this SQL on joining two tables:

   tableA=tableB(+)

   What is the meaning of this (+) behind the table name in the query?

   Please help.

8. The “(+)” creates an outer join in Oracle. This is standard Oracle SQL syntax, you should probably search google for “SQL outer joins” to learn more about outer joins.

9. I created some months ago reports using the technique you described in your article. I scheduled the daily execution of those reports which is still working fine. I recently wanted to edit the query and I get the following error message: “The current query contains unresolvable objects ( WIJ 20004)”. Of course, the SQL that B.O. proposes me to edit is not the right one but the one automatically built by B.O. with the objects selected from the universe.
   The daily (schduled) execution of the reports is still working fine which means that obviously my custom SQL is stored somewhere but I’m not able to edit it anymore.
   Do you have any idea how I cut bypass this check and edit directly the custom SQL?
   Thx

10. Hi TPE, firstly I think that your error/warning comes from the fact that the report’s associated universe was changed and it seems that one of the fields named/dropped in the results section may no longer exist or has changed in some other way. This is one of the many reasons custom-SQL reports are a bad idea. And a very good reason to store you SQL as text on one of the reports on the tab (so that you can easily retrieve it later. However, I think I know how you can recover you SQL. If you have auditing enabled for “document refresh” then you should look for one of your schedule refreshes and in the many records recorded you could find the SQL stored for the execution. Please have a look and let us know if the SQL you found was the manually edited SQL you needed to recover.

11. TPE, so long as you have not yet made any changes to the report, you can retreive the SQL quite easily:
    Open the report, but don’t edit the query.
    create a blank cell (drag it out of the Templates menu)
    in it, and assuming your query is named Query 1, put the following formula:
    =DataProviderSQL([Query 1]).

    This will show you what BUsinessObjects sent to the SQL server, and in the event of custom SQL – it’s the query you wrote.

    -Ethan

12. Hi,
    Thx for your quick answers.
    I tried the solution with the formula (=DataProviderSQL([Query 1])) since it looked really straightforward and ‘BINGO’: it works perfectly 😉
    I also discovered that indeed one of the ‘fake’ objects I used when I built my SQL seems not to be available anymore. I have to investigate this but I will now be able to rebuilt everything with a minimum of efforts.
    Thx a lot again

13. Can you link to an external file? For example, a query to look up data using an ID. Can point the custom sql to a flat file with list of IDs?

14. Hi Binoy, I don’t know how to accomplish that. I hope if someone else reading this does she or he will share it.

15. Hi all,

    I have a requirement for which I need to know if I can achieve results without having to use custom SQL. Also, I do not have access to the Universe.

    For a BO report whose output excel looks like how it is shown below:

    Order,OrderItem,Status
    A,A-1,’Closed’
    A,A-2,’Pending’
    B,B-1,’Pending’
    C,C-1,’Open’
    C,C-2,’Pending’
    ….

    I need to add a fourth column ‘DerivedStatus’ that will have a value ‘ForceClosed’ for all records of an Order if any record for that Order has got a value of Status = ‘Closed’.

    The above example should look like this:

    Order,OrderItem,Status,DerivedStatus
    A,A-1,’Closed’,’ForceClosed’
    A,A-2,’Pending’,’ForceClosed’
    B,B-1,’Pending’,’Pending’
    C,C-1,’Open’,’Open’
    C,C-2,’Pending’,’Pending’
    ….

    Thanks in advance!

    -NN

16. Hi NN, honestly, you are asking more of a SQL question and less of a BusinessObjects reporting question. I could see a solution using a nested query using the same query without Order Item, but still without knowing your data model its impossible to truly answer your question.

17. Hi Julian,

    The datamodel is simple. I have an existing BO report that gives me an Excel format output with 3 columns: Order, OrderItem and Status all coming from the same Universe (and even the same underlying table infact). I just need to add a 4th column in the report called DerivedStatus. The values for this column is what I need to calculate and get like in the example I gave above.

    What I am really looking for, is if using a Custom SQL is the only option I have for this.

    Thanks once again!

    -NN

18. Hi NN, thanks for the additional details. In BusinessObjects if you can edit the Universe then you usually can overcome any need to use Custom SQL. Most people use Custom SQL because they can’t edit the universe, or they don’t want to edit it for various reasons.

    So if you can’t edit the universe and you can’t use Custom SQL then I don’t think its possible to do what you want. Sorry.

19. Hi Julian,

    I have been creating report along way,but I am stuck with SQL coding.
    As we know we have efashion universe ,in which Iam adding a object called year2,in which I an writing in selsct as @select(class1\object1) .
    This give output in universe ,but when I use this object in my report it gives an error as
    Query 1 – eFashion

    A database error occured. The database error text is: [Microsoft][ODBC Microsoft Access Driver] Syntax error (missing operator) in query expression ‘( )’.. (WIS 10901)

    Please help me to get out of this.

    Regards,
    SOM

20. Hi SOM, without seeing the entire SQL I don’t think anyone can answer your question.

21. Hi,
    You can create a variable in the report called DerivedStatus (assuming you are using WEBI) and specify the formula as such:
    =if(Status = “Closed”; “ForceClosed”; Status)
    This should help you bypass the Universe limitation.

    Hope this helps.

    Cheers,
    Shal

22. What we are seeing is that if I run the Report from Designer, the custom SQL is used. If I schedule that same Report from Central Management Console or BO the custom SQL is not used. Is there some setting that needs to be set to get BO to use the custom SQL on scheduled reports?

23. I had one question on using custom SQL in Webi 4.0.I created a Custom SQL,validated it,saved it then refreshed the report saved the report and closed it.

    Then when again I opened my report in EDIT mode and through Data Access when I tried to see the SQL generated by my Custom SQL ,I saw that the query no longer remains a Custom SQL but it has the query generated by my script.How is it possible?Or when you are in the EDIT mode of the report you won’t able to see the script which contains Custom SQL.

    I hope I have made myself clear.

    Any help on this will be appreciated.

    Thanks in advance.

24. Hi Julian,

    Thanks for your post, We tried following it but are facing an issue with custom sql in BO 4.0 SP3.
    When we save our custom sql and run the report it runs fine and then we save the report, close it and when open it again and check the sql it’s getting rechanged to “use the script generated by your query” instead of custom sql.

    Please let me know is there any setting I need to modify or anything else.

    Regards,
    Tauceef

25. Tauceef –

    If you do ANYTHING to the query after saving out of the SQL window, like add a field or even just a filter, the SQL will revert to the internally generated version.

    For this reason, I have gotten in the habit of saving my custom SQL code in a seperate report page, we have a sort of header page on all our reports – I just save it in a table row there – in case a query gets changed inadvertantly, and the custom SQL gets wiped out.

    Don’t know if it is something simple like this, hope it helps

    🙂

26. Hi Chris,

    Thanks for the reply.

    We are not making any change in the query, only thing in the query is that its have some prompts and we are passing values to these prompts from another report via a link.

    And I just saved the report with custom sql and reopened and check it again without making any change in the query, it goes into default mode.

    Regards,
    Tauceef

27. We’re accessing some data in DB2 zOS. last year, we installed IDAA for ths DB2.
    Now, we like to send also this BO-Queries to IDAA (much faster response-time). For this, you have to execute this statement:
    set current query acceleration all;
    Is this possible in BO ?

28. Hey guys, just a really (really) basic question from a person starting out in this – what type of SQL is used in Business Objects? e.g. Oracle, ServerSQL, MS SQL etc.
    Thanks

29. Hi Andrew, Business Objects can connect to each of those databases. The type of SQL will vary based on the database connection type. However, don’t expect profound support for product-specific SQL. BO strives to remain database independent and as such will not drastically differentiate the SQL it constructs for your database. It will allow you to call product-specific functions, but it won’t give you a drop-down list of which ones are available.

30. I created a report that prompts the user to enter the year(s) and month(s). I then customized the SQL on the report and now the users are not being prompted for the year/month anymore. Does anyone know why the prompts are not appearing when the report is refreashed?

31. Hi Shawn, is it possible that your edits to the SQL impacted the @Prompt() BO functions in the SQL? Those must remain untouched or at least syntactically correct (according to Business Objects, not your database).

32. HI Guys,
    I am working on my webi Reports.The Query generated is not what i wanted SO i choose the Use Costum SQL and modified it but its not affecting my report . So can anybody help me in this How can i make the modification affects my report.

33. This all seems “mad”. You should be able to use the SQL without having to select fields in the top pane using the rules you have helpfully stated. I use another product which I can drag and drop, but, if I choose to go the “write my own SQL” route I can paste SQL into the SQL panel and the headings create themselves. Is BO going to do something about this?

34. Looking for assistance on the use of a custom SQL function that takes a user’s login credentials and checks it against the SQL Server database role they belong to, ie. db_owner etc and returns a 0 or 1, 0 if they don’t belong to the role, 1 if they do. Here is the function:

    CREATE FUNCTION [dbo].[CheckPII](@RoleName varchar(255),
    @GroupName varchar(255))
    RETURNS INTEGER
    AS
    BEGIN
    DECLARE @ColumnValue INTEGER

    select @ColumnValue =
    (
    select count(*) cnt
    from sys.database_role_members rm
    inner join sys.database_principals r on (rm.role_principal_id = r.principal_id)
    inner join sys.database_principals m on (rm.member_principal_id = m.principal_id)
    where
    r.name = @RoleName
    and (IS_MEMBER(@GroupName)) > 0
    );

    RETURN @ColumnValue
    END

    The function works fine in SQL Mgmt Studio but when plugged into a universe it defaults to the application login we use for Business Objects no matter who runs the report and doesn’t use the individual login running the report. What am I missing with regard to how BO passes values into a function?

    TIA

35. I have a query with est cost,actual cost, est labor and act labor. these items are in two different tables and when i join them into one query and run the report it reports the est and actual costs corretly, but because they are materials and line by line, when it shows the est labor and act labor for the items it repeats the same dollar amount on every line for each est labor and actual labor costs. These are all by po codes and i cannot figure out how to make it stop repeating those duplicate costs. can you help. i know that in the universe those costs were created as dimensions instead of measures.

36. Can anyone give me some guidance about how to create functions in BO custom SQL? I am not sure I understood the example Kdsturg5370 has posted. Furthermore, the “Create Function” expression isn’t accepted by BO in my case.
    Thanks in advance.

37. Hi SSB, I am 99% sure that Kdsturg5370 is not suggesting that you place that code in Business Objects in any way. Kdsturg5370 is stating that he/she creates the function directly in their database first and then they call it within a normal SQL. I really don’t know SQL Server, but in Oracle I have done this before. I compile my function in the database and then in my universe I create an object that calls that function appropriately.

38. Hello, I’m having a problem with custom SQL. I’m joining a file to itself and attempting to display the fields of 2 matching records on one line. BO says: “The SQL query has 3 instead of 2 columns.(WIS 10810)

    Here is a simplified version of the SQL. The Universe contains the first 2 fields only (Key,TRC), not the 3rd field because it is already defined as the second field (TRC) of the record being read.

    SELECT D.Key
    ,D.TRC as TRC285
    ,E.TRC as TRC015
    FROM Claim_File D
    Join Claim_File E
    on D.KEY = E.Key
    and D.TRC = ‘285’
    and E.TRC = ‘015’

    Am I supposed to list the second field twice in the universe? How do I do this? When I try, it will show with the same name and is impossible to tell them apart.

39. Does the custom SQL webi report bypasses the Universe parameter controls like ”Limit Execution Time’??

40. Lasech, no it is not possible to bypass BO universe level limits with custom SQL.

41. Hey Julian, great tip!

    Is it possible to update the query generated by a report for all queries?

    e.g.
    ) ASSETLIFECYCLESTATUS INNER JOIN (
    SELECT * FROM ANA_BASEELEMENT_VW
    WHERE EXISTS
    ( SELECT DISTINCT CTM1.COMPANY FROM CTM_PEOPLE_PERMISSION_GROUPS CTM1
    WHERE REMEDY_LOGIN_ID = @VARIABLE(‘BOUSER’)

    This part of the code specifically
    WHERE REMEDY_LOGIN_ID = @VARIABLE(‘BOUSER’)

    I would like to update this to
    WHERE UPPER(REMEDY_LOGIN_ID) = UPPER(@VARIABLE(‘BOUSER’))

    but for all queries. I assume this needs to be updated on the Universe? No too sure where though.

    Hope you can help!

    Cheers!

42. Awesome post.
    Point to point

43. Currently I am using following custom Query to generate monthly report –
    to_char(TICKET.Ticket_opened,’MON’)=to_char(add_months(sysdate, -1),’MON’)

    Which query I set for generate current month data.
    Above Query generate the data after completion of month.How I can generate Oct Month Data for current date 28th Oct 2015.Please suggest.

44. How do I edit a WEBI sql that has two queries that are joined? When I open the sql window, I am not able to edit the sql.

45. No, I don’t think that Business Objects allows you to do this in Web Intelligence. However if you have hey single query then you can add as many unions, minuses, etc to the SQL.

46. Can you do this in BO? I’m trying to alter the SQL in a report to convert a date/time field to JulianDate on the fly and see if it’s between two different Julian Dates? So it’s essentially this:

    (missingConvertedJulianDateCode) BETWEEN 2457407.166667 AND 2457407.791667

    I can’t seem to find something that I can plug into (missingConvertedJulianDateCode) that Business Objects accepts?

    Any thoughts?

47. Hi,

    I have one report with 4 customized query, that report is getting refreshed in 6 mins with administrator ID. but it is going into loop with REad only id.If we refresh queries one by one it is getting refreshed with read only id but for it is going in loop with Refresh All option. THere is no issue with access level because same id able to refresh other reports in the same folder not an issue with query because admin id can refresh it in 6-7 mins.
    One more thing we have noticed , if we logging through Direct link (http:/servername:8080/InfoViewApp) with read only id report is getting refreshed but not with customized link. It is really affecting our deliverable. your ideas/solutions will be appreciable

    Thanks
    Navneet

48. I work for a major company and we adopted the practice where we name all our reports so they mean something. Its more organized than just literals and it works great with Infoview display. For example, a report may start with SC_US_INV100Ls_[description]. This means, Supply Chain, US source, Inventory Report, 100 used as placeholder and allows for inserting new reports, L for local timezone dates and “s” for custom SQL. This is great for Users and Development. Just a tip from experience managing thousands of reports globally.

49. Hi,

    I have used Custom sql in my report. After using it, my report variable objects seems not working. Please let me know how to retain my variables purpose?

50. Ethan

    Thank you so much. You saved my bacon!