UDF in framework manager

[div_1234]

hi

how to use table valued user defined function in framework manager,which takes arguments?

Thanks

[cognostechie]

You can bring in the functions into FM by running Metadata Wizard and then create a calculated column in the query subject to use that function.

[div_1234]

its not coming in metadata wizard,like in the wizard,whatever function i have in sql,its nt coming ,plz help me

[blom0344]

You mean it is not listed in the folder 'functions' under the right schema even though you have set the proper rights? Just read/write authority will not be enough, the account will need to have execute rights (like a db_owner would in SQL server)

[div_1234]

the function has dbo schema and i am not able to see in metadata wizard ,under function folder.
its empty over there.

[blom0344]

Is this applicable:

https://www-304.ibm.com/support/docview.wss?uid=swg21343018

[div_1234]

i have done this,both my table and function has dbo schema.i dont see any concern about it

[blom0344]

I do understand I think . There is a known issue with synonyms not being shown in the metadata wizard, but I never had issues with functions in the dbo schema.

If you only grant datareader permissions on the login used for db acces, then the function will not show in metadata wizard. But is you add db_owner permission then the function can be selected.  Read rights itself are not enough for functions..

[njz]

Hi div_1234,
Framework Manager doesn't show any table-valued functions in the metadata wizard, only scalar-valued functions.

Since a table-valued function behaves like a table, you can refer to it explicity in the SQL that you code for your query subject. Use the #prompt# macro to supply the parameters to the function at report run time. You can make these prompts mandatory or optional. When the user runs a report containing the query subject, a prompt page will appear. If the names that you define for the prompts happen to match a report prompt in the user's report, then the user's prompt page will appear, otherwise the generic prompt page appers.

For example, if you have a function that takes 3 parameters, you could code something like this:
select * from fn_MyTableValuedFunction (#prompt('FirstParm','integer')#, #prompt('SecondParm','string','''thisisastring''')#, #prompt('ThirdParm','integer','0')# )

The first prompt is mandatory because there is no default value provided, the second and third prompts are optional because default values are provided. Check the FM User Guide for full syntax etc.

Be sure to set the Query Subject's sql settings to Native or PassThrough.