topic To pass parameters from QlikView application to a stored Procedure in QlikView

To pass parameters from QlikView application to a stored Procedure

rajtechnocraft — Fri, 07 Aug 2009 18:26:38 GMT

Dear All,

I want to pass parametrs from qlikview application to a stored procedure

Right now i am using hard coded parametrs from SP and directly calling SP into Qlikview application

Can kindly any one help me on this urgently.

I will be thankful to you guys.

Regards

Raj,

To pass parameters from QlikView application to a stored Procedure

hector — Fri, 07 Aug 2009 20:50:13 GMT

Have you tried something like this?

SQL

Exec sp_test 'paramvarchar', paramnum

;

etc etc

Greetings

To pass parameters from QlikView application to a stored Procedure

rajtechnocraft — Mon, 17 Aug 2009 15:32:28 GMT

Dear Héctor Muñoz

I tried this but SP doesn't ask me to pass parameter when i execute qlikview application

what i wanted is when i refresh my qlikview application it should ask me to supply parameters for which i want to fetch results and

based on the input provided by user i should be able to refresh qlikview application and fetch the result set.

Regards

Raj Technocraft

To pass parameters from QlikView application to a stored Procedure

hector — Mon, 17 Aug 2009 21:03:11 GMT

Hi, and something like this?

Input:
Load
Input('Enter an integer value', 'Input box') as value_int,
Input('Enter an char value', 'Input box') as value_char
autogenerate 1;

Let vValue_int = peek('value_int',0,'Input');
Let vValue_char = peek('value_char',0,'Input');

drop table Input;

SQL
exec sp_test $(vValue_int),$(vValue_char)
;

C ya!

To pass parameters from QlikView application to a stored Procedure

Wed, 19 Aug 2009 15:25:26 GMT

hi Hector this code is not working ..........

in exec statement is not able to identify $(variable) name .................... it gives "blank field not allowed error"

To pass parameters from QlikView application to a stored Procedure

Bjorn_Wedbratt — Wed, 19 Aug 2009 15:40:06 GMT

Try putting single quotes around the $(variable), like '$(variable)'.

See if that helps.

To pass parameters from QlikView application to a stored Procedure

Wed, 19 Aug 2009 16:18:09 GMT

still not working

To pass parameters from QlikView application to a stored Procedure

Bjorn_Wedbratt — Wed, 19 Aug 2009 18:29:15 GMT

What kind of DB are you using? Are you using OLEDB or ODBC? Can you provide an example of your code?

I tried the exact same thing against AdventureWorks in MS SQL2008 executing the SP uspGetEmployeeManagers, like:

Input:
Load input('Employee Manager','Input box') as val_int
autogenerate 1;
Let vValue=peek('val_int',0,'Input');
SQL EXEC dbo.uspGetEmployeeManagers '$(vValue)'

It's working great here, using OLEDB

To pass parameters from QlikView application to a stored Procedure

Wed, 19 Aug 2009 23:37:11 GMT

we using ODBC .................

Ms sQL server2005

To pass parameters from QlikView application to a stored Procedure

Wed, 19 Aug 2009 23:40:19 GMT

Create Procedure test_proc @date1 datetime, @date2 Datetime, @Vol Float Output
AS
Begin
Select @Vol = Max(Volume) From Test Where date1 >= @date1 and date2 <= @date2
End

this is my proc @ backend which accepts two input parametes date1 & date2 ...... based on that it returns output in @vol.

code i'm using in QV

Input:

load

Input('Enter an from date value', 'Input box') as value1_date,
Input('Enter an end date value', 'Input box') as value2_date
autogenerate 1;

Let FromDate = peek('value1_date','2008/01/04','Input');
Let DateTo = peek('value2_date','2009/01/04','Input');

sql

Declare
@vol int,
@FromDate datetime,
@DateTo datetime

Execute [dbo].[test_proc] '$(FromDate)', '$(DateTo)', @vol = @vol output

Select @vol

when i refresh my appn it asks me for two input dates , but doesnt display any output...

what i believe is that it might not be passing '$variable' to input parameters . i confirmed this by adding same code to another sp which gives error saying" blan fields not allowed in sql"

To pass parameters from QlikView application to a stored Procedure

hector — Wed, 19 Aug 2009 23:49:15 GMT

why you change the '0' by dates?

0 represents the first record, you can't change this, because the table says autogenerate (1)

c ya

To pass parameters from QlikView application to a stored Procedure

Wed, 19 Aug 2009 23:51:52 GMT

oh ............... i thought '0' was for integer type ,,,,,,,,,, thatz y replaced it with date as i wanted to pass date as input variable

To pass parameters from QlikView application to a stored Procedure

hector — Wed, 19 Aug 2009 23:56:57 GMT

I made this test

SQL Server 2000

CREATE PROCEDURE [dbo].[sp_test] @param1 as int
AS

Select @param1*2 as result
GO

QlikView

ODBC CONNECT TO [My_Server] (XUserId is bMaAdZFMIF);

SQL
exec HM..sp_test 1;

and qlikview gave me the result column

Check the parameters

C ya!

To pass parameters from QlikView application to a stored Procedure

Thu, 20 Aug 2009 11:47:19 GMT

i tried changing following code

Create Procedure test_proc @date1 datetime, @date2 Datetime, @Vol Float Output
AS
Begin
Select @Vol = Max(Volume) From Test Where date1 >= @date1 and date2 <= @date2
End

this is my proc @ backend which accepts two input parametes date1 & date2 ...... based on that it returns output in @vol.

code i'm using in QV

Input:

load

Input('Enter an from date value', 'Input box') as value1_date,
Input('Enter an end date value', 'Input box') as value2_date
autogenerate 1;

Let FromDate = peek('value1_date',0,'Input');
Let DateTo = peek('value2_date,0,'Input');

sql

Declare
@vol int,
@FromDate datetime,
@DateTo datetime

set @FromDate = '$(FromDate)'

set @DateTo = '$(DateTo)'

Execute [dbo].[test_proc] @FromDate, @DateTo, @vol = @vol output

Select @vol as @vol

when i refresh my appn it asks me for two input dates , but doesnt display any output. column (in my case @vol) .............. .. dont know why

To pass parameters from QlikView application to a stored Procedure

Thu, 20 Aug 2009 14:30:39 GMT

hi Hector ........

when i pass hard coded values my sp is running perfectly fine, problem arises when i use input variables to pass values........... go thru code i sent you again........... you missing a line there

archanah

To pass parameters from QlikView application to a stored Procedure

Bjorn_Wedbratt — Thu, 20 Aug 2009 16:49:49 GMT

If the sp is running fine in QV when hardcoding values, like:

sql

Declare
@vol int,

Execute [dbo].[test_proc] '2008/01/04', '2009/01/04', @vol output

Select @vol as vol

the only thing I can think of is that you're getting a miss-match of datatypes when using variables. I wonder if QV is picking up your variables as numeric values? Hmm... could try to add a text-object with an expression like =num('$FromDate') to see if QV will recognize it as a date (it should then convert to numeric format).

I tried both ODBC and OLEDB calling another sp from QV with the following, and it's working just fine:

Input:
Load input('Start Date','Input box') as val_start,
input('End Date','Input box') as val_end
autogenerate 1;
Let vStart=peek('val_start',0,'Input');
Let vEnd=text(peek('val_end',0,'Input'));
SQL /****** Script for SelectTopNRows command from SSMS ******/
declare @inv float
Execute dbo.spInv '$(vStart)', '$(vEnd)', @inv out
Select @inv as tmp
;

I know that datatypes, especially when it comes to date-formats can be a bit tricky so it could be worth a shot to create a simple stored procedure with integers, instead of datetime. Something like:

CREATE PROCEDURE [dbo].[test] (
 -- Add the parameters for the stored procedure here
 @input1 int,
 @input2 int,
 @parmOut int Output )
AS
BEGIN
 set @parmOut = @input1 + @input2
END

and in QV:

input:
Load
input('val1','Input box') as val1,
input('val2','Input box') as val2
autogenerate 1;
Let val1 = peek('val1',0,'input');
let val2 = peek('val2',0,'input');
SQL
declare @out int
Execute dbo.test '$(val1)','$(val2)',@out output
Select @out as tmp;

To pass parameters from QlikView application to a stored Procedure

hector — Thu, 20 Aug 2009 20:02:37 GMT

I agree with Bjorn check the formats and the the internal values, sometimes for the date values QlikView shows 01/01/2009 but internally is a number 39000 something

I don't know, maybe you can pass the parameter something like 20090101 and internally in your sp (SQL Sintax) use the convert/cast function.

you can use the trace function or "Generate Log file" issue.

c ya

To pass parameters from QlikView application to a stored Procedure

Thu, 20 Aug 2009 23:03:14 GMT

hi Bjorn.................

thanks 4 ur reply ............. it was really helpful .............

sometime qlikview behaves so stupidly ........................ i tried date function in two different appn... it is returning me date and in second it returns number......... i'm so confused.....................

anyidea y this cud be happening

To pass parameters from QlikView application to a stored Procedure

Tue, 25 Aug 2009 13:55:22 GMT

thanks Bjorn

my sp is working fine now.............. it was taking date as num............... thanks againg for solving my prob

C Ya

To pass parameters from QlikView application to a stored Procedure

Bjorn_Wedbratt — Tue, 25 Aug 2009 14:25:54 GMT

Great! Please don't forget to mark the answer to help others finding it.

Cheers,

Bjorn