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
2. Choose relational connection
3. Query Script Editor opens. Place FHSQL into editor
4. Click Validate
5. Click Ok twice and then the Query Panel opens showing the objects
6. Click Run Query and the report returns
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
2. Choose relational connection
3. Query Script Editor opens. Place FHSQL calling Stored Procedure into editor
4. Click Validate
5. Click Ok twice and then the Query Panel opens showing the objects
6. Click Run Query, prompt window displays
7. Fill in prompt value, click OK, report runs and completes
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
Has the Stored Procedure issue been resolved as of the release of SP6?
The link to Note 2174240 do not work as the note has not been released by SAP
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.
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
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
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.
I had different behaviours when updating BOE 4.1 SP5 FP5 to SP6 than using a fresh install of SP6.
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,
Sai
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.
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.
Yep - noted this too - planed to be fixed in SP7 - I heard
- Wobi
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
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
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