Skip to Content
Author's profile photo Steve Pruner-Yemm

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

Assigned Tags

      26 Comments
      You must be Logged on to comment or reply to a post.
      Author's profile photo Rick Kruyf
      Rick Kruyf

      Has the Stored Procedure issue been resolved as of the release of SP6?

      Author's profile photo Rick Kruyf
      Rick Kruyf

      The link to Note 2174240 do not work as the note has not been released by SAP

      Author's profile photo Steve Yemm
      Steve Yemm
      Blog 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.

      Author's profile photo Rick Kruyf
      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

      Author's profile photo Steve Yemm
      Steve Yemm
      Blog Post Author

      I used the same steps documented above when calling a Stored Procedure, the only difference was using the ODBC connection to SQL Server.

       

      Another example of using FHSQL is shown here:

       

       

      http://scn.sap.com/docs/DOC-64504

      Author's profile photo Andrey Ivanov
      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

      Author's profile photo Steve Yemm
      Steve Yemm
      Blog 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.

      Author's profile photo Rick Kruyf
      Rick Kruyf

      I had different behaviours when updating BOE 4.1 SP5 FP5 to SP6 than using a fresh install of SP6.

      Author's profile photo Former Member
      Former Member

      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.

      Author's profile photo SAIKUMAR TANGUDU
      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

      Author's profile photo Steve Yemm
      Steve Yemm
      Blog 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?

      Author's profile photo SAIKUMAR TANGUDU
      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

      Author's profile photo Former Member
      Former Member

      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.

      Author's profile photo Steve Yemm
      Steve Yemm
      Blog Post Author

      Hi Wolfgang,

       

      Yes, the issue has been raiseid to Development under SAP Note 2188132 - Free Hand SQL Queries are getting doubled during creation process when any other query is already present

       

      The Note will be updated with the version the correction will be released in, please keep and eye on it.

      Author's profile photo Former Member
      Former Member

      Yep - noted this too - planed to be fixed in SP7 - I heard

       

      - Wobi

      Author's profile photo Darren Skuse
      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.)

      Author's profile photo Former Member
      Former Member

      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.

      Author's profile photo Bjørnødegård Geir
      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.

      Author's profile photo Rick Kruyf
      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.

      Author's profile photo Bjørnødegård Geir
      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>

      )

      Author's profile photo Rick Kruyf
      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

      Author's profile photo Former Member
      Former Member

      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

      Author's profile photo Steve Yemm
      Steve Yemm
      Blog Post Author

      further information about calling Stored Procedures using FHSQL at the bottom of this thread:

       

       

       

      http://scn.sap.com/community/businessobjects-web-intelligence/blog/2014/09/29/freehand-sql-fhsql-is-coming-to-web-intelligence#comment-625753

      Author's profile photo Former Member
      Former Member

      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

      Author's profile photo Former Member
      Former Member

      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.

      Author's profile photo Tarun Gupta
      Tarun Gupta

      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