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

To report this post you need to login first.

25 Comments

You must be Logged on to comment or reply to a post.

      1. Steve Yemm Post author

        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.

        (0) 
        1. Rick Kruyf

          No luck with the URL or trying to access the SAP Note.

          Do you have an example calling a stored proc with a parameter.  I can not get the FHSQL to return the fields from the proc

          (0) 
  1. Andrey Ivanov

    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

    (0) 
    1. Steve Yemm Post author

      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.

      (0) 
  2. Praveen Gaddam

    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.

    (0) 
  3. SAIKUMAR TANGUDU

    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

    (0) 
    1. Steve Yemm Post author

      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?

      (0) 
      1. SAIKUMAR TANGUDU

        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

        (0) 
  4. Wolfgang Bidner

    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.

    (0) 
  5. Darren Skuse

    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.)

    (0) 
  6. Praveen Gaddam

    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.

    (0) 
  7. Bjørnødegård Geir

    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.

    (0) 
  8. Rick Kruyf

    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.

    (0) 
  9. Bjørnødegård Geir

    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>

    )

    (0) 
  10. Rick Kruyf

    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

    (0) 
  11. SatyaPrasad Vallabhaneni

    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

    (0) 
  12. Shlomi Weiss

    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

    (0) 
  13. Tremblay Frederic

    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.

    (0) 

Leave a Reply