How to use database user defined functions in Framework Manager

example from Microsoft SQL Server:

Steps:

1. Create a user defined function in SQL Server. Open SQL Server Enterprise Manager and right click on User Defined Functions (for example in Northwind database) and select New User Defined Function. Enter the following function text:

CREATE FUNCTION SquareNumber (@numberIn int)
RETURNS int AS
BEGIN
return @numberIn * @numberIn
END

2. Test the function. Select any table in the sample Northwind database, for example, Categories. Right click the table and select Open Table -> Query.

3. Enter the following SQL statement.

SELECT *, dbo.SquareNumber(2) AS Squared
FROM Categories

4. Run the SQL by pressing the "!" toolbar button. You will see a column called "Squared" which contains all 4s (2 squared = 4).

5. Import the function in Framework Manager and test it with one of the query subjects. Create a new query subject using the Categories table and modify the SQL to show as below:

a) If using Cognos SQL

Select Categories.CategoryID as Catid,
dbo.SquareNumber(2) as CognosSquaredfrom [Northwind].Categories

b) if using Native SQL

Select *, dbo.SquareNumber(2) as CognosSquaredfrom Cateogries

6. Test the query subject and publish for use in Report Studio. You will now have a "CognosSquared" query item for use in your reports.

Note: You can also use a prompt macro (ie. #prompt('parameter')#) with the function as an input parameter.