COGNOiSe.com - The IBM Cognos Community

IBM Cognos 8 Platform => COGNOS 8 => Framework Manager => Topic started by: div_1234 on 04 Nov 2011 12:23:50 PM

Title: UDF in framework manager
Post by: div_1234 on 04 Nov 2011 12:23:50 PM
hi

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

Thanks
Title: Re: UDF in framework manager
Post by: cognostechie on 04 Nov 2011 12:55:53 PM
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.
Title: Re: UDF in framework manager
Post by: div_1234 on 04 Nov 2011 01:21:29 PM
its not coming in metadata wizard,like in the wizard,whatever function i have in sql,its nt coming ,plz help me
Title: Re: UDF in framework manager
Post by: blom0344 on 06 Nov 2011 12:42:07 PM
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)
Title: Re: UDF in framework manager
Post by: div_1234 on 07 Nov 2011 08:46:49 AM
the function has dbo schema and i am not able to see in metadata wizard ,under function folder.
its empty over there.


Title: Re: UDF in framework manager
Post by: blom0344 on 07 Nov 2011 03:21:14 PM
Is this applicable:

https://www-304.ibm.com/support/docview.wss?uid=swg21343018
Title: Re: UDF in framework manager
Post by: div_1234 on 07 Nov 2011 03:35:07 PM
i have done this,both my table and function has dbo schema.i dont see any concern about it
Title: Re: UDF in framework manager
Post by: blom0344 on 09 Nov 2011 02:05:08 PM
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..
Title: Re: UDF in framework manager
Post by: njz on 15 Jun 2012 12:52:57 PM
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.