Set up prompts in the model

[alaeddine]

Hi All,
I have a fonction in SQL SERVER that I imported to the model,
It uses 4 integers, the first is a data item and the others should be introduced by the user(prompts).
So this fonction is used to calculate currency rate change, and I'm asked to prepare this rate in the model level (to not calculate it in the report studio).
when I create a new calculation for the rate and put: rate_calculation_function(data_item,#('param1',inte ger)#,('param2',integer)#,#('param3',integer)#)
I have error and framework manger dosen't accept the syntax.
I tried to do it with SQL code but it dosen't work also.
Any Help Please?

[DustPanMan]

You are missing a "#" in front of your second param.  I'm not sure about the syntax you are trying to use though.  Where did you get this?

[alaeddine]

the seconde # exists (it's a mistake when retyping)
I'm not sur about the syntax as well but that's how I imagined the calculation
If you have any other suggestion, You are wellcome, I realy need help :)

[alaeddine]

any help please??
it's very urgent!!

[praveennb]

i have followed the below steps and it works

0.created a table valued function
create function dbo.DIM_X(@id int,@code char(2))
returns Table
as
return
(
select * from dbo.B_DIM_X
where id=@id and code=@code
)
1.created a new query subject by selecting a different table dim_Y
2.on the query information TAB>select options>SQL setting tab>change SQL type to Native>click ok
3.on the SQL Tab i cleared the generated query for dim_Y
4.changed the SQL to
select * from DIM_X(#prompt('prmid','int')#,#prompt('prmcode','string')#)

this worked for me

i was unable to see the function list inside my FM when i ran the wizard to import, may be due to different owner

Hope this helps let me know.

Try changing your query to
select * from rate_calculation_function(data_item,#prompt('param1','int')#,#prompt('param2','int')#,prompt#('param3','int')#)

you were missing the single quotes for datatypes

I'm not clear on the data_item parameter part, from which query will you be passing this value?