Implementation of database function in universe

[Former Member]

HI,

I am using BO XI R3.1 version. I want to implement a stored procedure existing for security in database at universe level. Since we need to create a seperate universe for using the stored procedure in universe as we couldn't club both the tables and stored procedure so i'm feeling to use the in-line function that returns the table (to be created in database which will behave the same way as stored procedure to replace the stored procedure ) so i would like to know about implementing the function in universe that will return the table.

The database being used is sql server 2005. The stored procedure takes the parameter as userid and returns some columns. I want to achieve the same output by implementing a table-valued function in universe but am stuck with the this step. Could any one please explain in detail. I would also appreciate if any method is possible.Thanks in advance.

Eswar

[Former Member]

Pardon me but it seems you have some typo there, so I don't completely understand what you are trying to accomplish. Are you trying to use a stored procedure to set up data security? Or did I totally misunderstand you? Please clarify.

[Former Member]

Thanks for the replies.

There was a stored procedure in sql server 2005 with cursors which deals with the security. I need to implement this stored procedure in Bo to control the data security for the users. I couldn't think of implementing stored procedure directly in universe becuase the universe is already existing with the reports already built. I am thinking to implement the logic of stored procedure in universe as a table so that i can give joins to the tables in universe. I am just looking for the approach to implement.

I couldn't able to use crystal Reports due to the requirement.

Please let me know if i was clear in making you understand my requirement.

Eswar

[Former Member]

Hi,

Could any one please respond to my query mentioned above.

Thanks in advance,

Eswar

[Former Member]

Could you clarify - so you do not want to create a new universe?

[Former Member]

Hi Eswar,

The best way to implement data security is to do it on the database where it truly belongs. You can easily set up the security in a database table, then join this security table with the organizational hierarchy table to determine who can see what.

Hope this helps.

[Former Member]

Thanks for your suggestions.

I do not want to create a new universe and want to implement security in the existing universe. I understood from your response that security should be implemented at database level and is the best practise. I would like to know how i can proceed about the implementation in universe if in the database there is stored procedure with cursors, temp tables which deals with security.

I feel there are various to accomplish that by creating a derived table (but can't use temp tables and cursors) and also view may not suffice which it is not dynamic. Is it possible to use the while loop, for loop in place of cursors in the sql.

I would appreciate if any one could thow light on this issue

Eswar

Edited by: Eswar Rao on Apr 27, 2010 5:40 AM

Edited by: Eswar Rao on Apr 27, 2010 5:42 AM

[Former Member]

Have you considered using Crystal Reports for this purpose? Just create a report off the stored proc and drag the fields onto your report.