Rank Over Partitioning in Universe

[Former Member]

Anyone suggest me the syntax, or how to use the rank over partition function in the BO X1 3.1 universe?.I am using db2 as database.

I don't want to use the derived table and write the sql. I am looking for the in built function in BO universe.

Any suggestions are highly appreciated.

Thanks,

[Former Member]

Use the below Function:

RANK() OVER(PARTITION BY arg1 ORDERBY arg2 ASC/DESC)

From Desginer Guide:

You can automate the insertion of analytic function syntax by adding the analytic function to the Functions list box in the Edit Select Statement dialog box.

You populate the Functions list box by adding the analytic function to the list of functions under the [FUNCTION] section in the appropriate PRM file for the target RDBMS.

When you add the analytic function to the PRM file, you must set the following values:

- GROUP = N

- For IBM DB2 UDB v.7.1 and ORACLE 8.1.6 only: IN_MACRO = N

You can add an analytic function to the [FUNCTION] section in the PRM file as follows:

To add an analytic function to the PRM file:

1. Browse to the Data Access directory in the Business Objects path.

2. Open the PRM file for your RDBMS in a text editor.

3. Scroll to the [FUNCTION] section of the PRM file.

4. Copy an existing function and paste it at the end of the list.

5. Type a unique number for the newly pasted function, and modify the values as appropriate for the analytic function that you want to add to the list.

6. Set the GROUP value to N.

If you are using IBM DB2 UDB, or ORACLE, set the IN_MACRO value to N.

7. Save and close the PRM file.

You need to restart Designer for the changes to be applied.

Note:

When you restart Designer, the syntax for the added analytic function appears under the appropriate Type node (Number, Character, or Date).

[Former Member]

Thanks rachana, for the response. I tried the below XML syntax but the function is not appearing in the character node even after restarting the designer session. Please suggest.Let me know if the below syntax is correct/not

<Function Group="N" ID="RANK () Over (partion by () order by () Asc/Desc)" InMacro="True" Type="String">

<Arguments>

<Argument Type="String"></Argument>

</Arguments>

<SQL>RANK($1) over (partition by ($2) order by ($3) Asc/Desc) /SQL>

</Function>

Thank you!

[Former Member]

I am sorry for making me not so clear.

You have to defined only RANK(). not the complete function. Rather than writing complete function RANK () Over (partion by () order by () Asc/Desc)"

THis is formula you have write for creating hte object..