Skip to Content

Web Intelligence Free Hand SQL (FHSQL)/Stored Procedures in BI4.1 SP06

As you may of heard already, things are changing in BI4.1 SP06 when it comes to FHSQL (Free-hand SQL) and Stored Procedures. In BI4.1 SP05 we added the ability to convert Desktop Intelligence reports directly to Web Intelligence reports that contained FHSQL. This information was put together in this blog, First look at FHSQL reports in BI4.1.

My colleague, Felicia Shafiq, also documented FHSQL in BI4.1.5 – Limitations, Gotchas, and Troubleshooting steps she went through during her process.

Well, we went ahead and upgraded Web Intelligence in SP06 so you can now edit/create reports using either FHSQL or Stored Procedures. I was able to get SP06 and do some basic testing of the new feature. Here are the steps that I went through creating 2 tests reports. The first one is based off of eFashion using a very basic query. The second one is a report using a Stored Procedure with a prompt connecting to a MS 2008 SQL Server using a JDBC Connection.

1. Create new document

FHSQL New Document Screenshot.jpg

2. Choose relational connection

FHSQL New Relational Connection eFashion.jpg

3. Query Script Editor opens. Place FHSQL into editor

FHSQL Query Script Editor eFashion Basic.jpg

4. Click Validate

FHSQL Query Script Editor eFashion Basic Validate.jpg

5. Click Ok twice and then the Query Panel opens showing the objects

FHSQL Query Panel eFashion Basic.jpg

6. Click Run Query and the report returns

FHSQL eFashion Basic Query Report Results.jpg


As you can see, this is a very basic test showing the creation of a new Web Intelligence report using FHSQL. The next feature that we covered was the use of Stored Procedures. I myself have worked/used Desktop Intelligence for over 7 years and looked forward to when Web Intelligence would have this feature. Its HERE! Granted it works in a different way when comparing to Deski, but it works. You no longer have to use Deski. Here are the steps I went through when creating a new report:


1. Create new document

FHSQL New Document Screenshot.jpg


2. Choose relational connection

FHSQL New Relational SQLServer JDBC Connection .jpg

3. Query Script Editor opens. Place FHSQL calling Stored Procedure into editor

FHSQL Query Script Editor calling SP with Prompt.jpg

4. Click Validate

FHSQL Query Script Editor calling SP Validate with Prompt.jpg

5. Click Ok twice and then the Query Panel opens showing the objects

FHSQL Query Panel calling SP with Prompt.jpg

6. Click Run Query, prompt window displays

FHSQL Query Panel calling SP with Prompt Display.jpg

7. Fill in prompt value, click OK, report runs and completes

FHSQL SP with Prompt Query Report Results.jpg


As you can see you will have to call the Stored Procedure using FHSQL whereas in Deski it would behave differently. You will also have to add prompt syntax in the FHSQL to receive a prompt window as I demonstrated. Other than these minor changes, both FHSQL and Stored Procedures are now available in BI4.1 SP06.


You are no longer bound to Deski, Webi has moved forward! 🙂


During the writing of this blog, testing using and ODBC connection to SQL Server was found not be working correctly. This issue is currently with Development under SAP Note  2174240 – Error “A problem is encountered. The requested action cannot be completed.” when creating Stored Procedure via FHSQL using ODBC connection

26 Comments
You must be Logged on to comment or reply to a post.
      • Hi Rick,

         

        Please check the Note again, it should be working now.

         

        If you can reproduce this scenario in the note, please log an incident, and provide your Business Impact. That will help drive the issue to resolution.

  • Hello Steve!

    We update our server from BI 4.1 SP5 to BI 4.1 SP6

    But we can not see new Free-Hand SQL menu..

    How I can add it to BI 4.1 SP6

     

    Best Regards

    Andrey Ivanov

    • Andrey,

       

      If you are not seeing Free-hand SQL in the list for “Create a Document”, something may of not deployed correctly during the install. Try using the Webi Rich Client instead. If you see Free-hand SQL there, I would try redeploying the war files on the application server. If that doesn’t fix it, you may want to open a case with Support to look further into the issue.

  • How to execute(call) a Oracle stored procedure in Webi Free Hand SQL . The version is BI 4.1 sp6.

     

    The Free Hand SQL (call to the Oracle stored procedure) that we wrote in BO XI R2 Deski report does not work in Webi Free Hand SQL (BI 4.1 sp6).

     

    Basic SQL( like Select * from a table) does work in Free-Hand SQL of Webi BI 4.1 sp6.

     

    Thanks.

  • Hello Steve,

     

    We have tried the similar steps suggested by you to execute a orcale based stored procedure (which has one IN and one OUT parameter) using FH SQL as dataprovider and got the below error .

     

    Please suggest incase if you have any inputs regarding this error.

     

    Regards,

    SaiFH SQL Proc Error.jpg

    • Sai,

       

      I have not been able to reproduce this issue. Could you possibly create a Stored Procedure against the Scott database that reproduces the issue and send it to me?

      • Thanks Steve . I will try to create proc against Scott DB but mean time could you please pass me with the sample procedure code which you have used against Adventure Works DB to execute it in FHSQL  data provider .

         

        Also please let us know how to use @prompt’s   in case if the stored proc has multiple input parameters  .

         

         

        Regards,

        Sai

  • I discovered a little bug with the new FHSQL feature. If your query is not the first query in your list of data sources – you had at least run one other, – than your new FHSQL query gets duplicated. – not a big Problem – you can delete the double later, but it’s still a bug. – to be fixed with Sp7 I am told.

  • Also found that during promotion you can’t change the connector unlike a crystal report. You have to go in to the report and edit query to change connector. It would be good to be able to change this in the CMC. (Same goes for universe connections too.)

  • We are able to talk/call to a Sybase stored procedure using JDBC connection from Webi FREE HAND SQL window in BI 4.1 sp6. Installed the latest driver Sybase 15.7. Note: I was able to call the stored procedure hardcoding the parameter values. When I enter @Prompt(‘Select a number’, ‘A’,,Mono,Free) it does not work.

  • Is there anyone that have called a Oracle stored procedure with in and out parameters with success? Care to share a sample code?

    Should it use cursor or variables?

    A simple procedure and how to call it from webi.

    My tests are working fine in SQL Developer but not in webi.

     

    Please…. it would be really useful to get this to work.

  • The syntax is not documented anywhere but here is a sample that works.

     

    Select *

    From

    (

       <Procedure uid=”OWNERPROCEDURE”  owner =”OWNER” name = “Procedure” type = “Procedure”>

       <Parameter name = “USERVALUE” type=”FLOAT64″ value = “@PROMPT(‘USERVALUE’,’N’,, Mono, Free, Not Persistent, {‘000000`’}/>

       <Parameter name = ‘MY_CURSOR” type = “RefCursor” mode=”InOut”/>

       </Procedure>

    )

     

    You have to supply a default value for the numeric prompt to work, also date parameters will not work until BOE 4.1 SP6 FP4 or SP7, see note 2215974

     

    The sample EXEC syntax does not see to work for Oracle at all.

  • Thanks a lot!

    Got it to work after some small changes.

    The parameter type seem to be case sensetive.

    Also the “Not Persistent” should be “Not_Persistent” if used.

     

    Is there anywhere i can find the other possible and correct types to use for parameters?

    Like integer or strings/chars.

     

    Here is my working code:

     

    Select *

    From

    (

       <Procedure uid=”OWNERPROCEDURE”  owner =”my_user” name = “my_proc_name” type = “Procedure”>

       <Parameter name = “USERVALUE” type=”Float64″ value=”@PROMPT(‘USERVALUE’,’N’,,Mono,Free,Not_Persistent,{’12’},User:0)” />

       <Parameter name = “MY_CURSOR” type = “RefCursor” mode=”InOut”/>

       </Procedure>

    )

  • Values are A, N, D and K.  The K is new and stands for Keyword.  It returns alpha values without quotes but the default values must be part of the @prompt syntax and the values are constrained to this list.

     

    If you need to use K with a long list of values, I would recommend a derived table universe to allow the list to values to be imported into the Information Design tool as a list of values and call the list of values from the @prompt command

     

    Example:

    UNX universe with a list of values called mycategories (single column text file imported)

     

    Select * from @Prompt(‘1 enter the category table name:’,’K’,mycategories,MONO,CONSTRAINED,NOT PERSISTENT, {‘CATEGORY1’}) CATG

  • Any one created the temporary table in  FHSQL in SP6? I tried it is not allowing. I got thei message : The Free-hand SQL query does not contain a valid executable statement.

    I am using Oracle Exadata and webi SP6

  • Hi

     

    I’m using BO 4.1 sp6 and I try to use the new free-hand feature to execute a simple procedure that runs a SQL upload job

     

    I get the following error message “ERROR on BindCol”

     

    Can you think of a reason?

    A simple select inside a procedure works fine

     

    Thanks

     

    Shlomi

  • If you use a view as your Data source and are in SQL Server, then remember not to name your fields in the Select Statement of your Freehand data provider.

     

    But to use the Select * From syntax.

     

    Otherwise, as soon as you add a new column in your Select, and use it in your report, it will crash.

     

    using the Select * From syntax resolved it for me.

  • Can i create a Free Hand SQL on BW System. similar way we execute a BW query using db02 in SQL Query Editor.

    or

    IS Free Hand SQL on BW system works.

    Thanks

    Tarun