Have you ever wanted to add database specific functions into Report Studio for easy access? Here’s how:

1. On your Cognos server navigate to the following directory:
<C10 INSTALLATION DIRECTORY>c10configurationfunctions

2. Open the FileList.xml which contains definitions and descriptions of vendor specific functions. Notice that the first part of the file name is the database name and the second part is Tree.xml and Strings_<lang>.xml.
For example, for SQL Server there is a SQLServerTree.xml definition file and SQLServerStrings_en.xml description file. The *Tree.xml files contains the definition of the functions and the *String_<lang>.xml contains the description, tips, and help used in C10’s UI.
Notice that for each *Tree.xml there are usually four *String_<lang>.xml files for each of the supported languages (SQLServerStrings_en.xml, SQLServerStrings_fr.xml, etc.). FileList.xml is where you define which language description file you would like to use.

3. To add a new function to Framework Manager open SQLServerTree.xml (If a SQL Server function is required) in an XML editor.
a. Find the </datasourceQueryType> tag.
b. After </datasourceQueryType> tag insert the following –
<function>
<id>sql_avg</id>
<name>avg</name>
<canonical>avg</canonical>
<returnType>numeric</returnType>
<parameter>
<type>numeric</type>
</parameter>
</function>
If the function does not require parameters, the <parameter> tags can be omitted. If the function requires more than one parameter each parameter is defined in a separate <parameter><type>…</type></parameter> tag.

4. After defining the function, you can add the description, if required. Open SQLStrings_en.xml file in an XML editor.
a. find the following line –
<string id=”V_SQLServer”>SQLServer</string>.
b. After this line add the following –
<string id=”sql_avg.syntax”>avg(numeric_exp)</string>
<string id=”sql_avg.tip”>AVG ( [ ALL | DISTINCT ] expression ) </string>
When you selects this function in C10 the help window will display –
avg(numeric_exp)
AVG ( [ ALL | DISTINCT ] expression )
Notice that the function is referenced by an id and therefore the id should match the id in SQLServerTree.xml file. The id in our sample is sql_avg.

5. Now restart the Cognos services.

6. To ensure the function was added to Cognos C10 open the Framework Manager project, select the Namespace and create a new calculation. In the Calculation Definition window, click the Functions tab and expand Vendor Specific Functions, SQL Server. Notice that avg is the first function that appears below the SQL Server database node. Drag avg into the Expression window and insert Quantity from Order_details query subject, so the full syntax looks like this:
avg ( [gosales].[ORDER_DETAILS].[QUANTITY] )

7. Click the blue arrow in the right hand corner to test the function. Et voilà!


Back to blog