Universe simple query

[Former Member]

Hi

Can I create a universe on stored procedure?

Anita

[former_member212749 · Accepted solution]

Hi Anita,

Not sure about creating a Universe based on a Stored Procedure.But you can create reports using a stored procedure.

You can create Derived tables in a universe.

Regards

Prashant

[Former Member · Accepted solution]

Hi Anita,

With BusinessObjects XI 3 Universe Designer, you can definetely create a Universe with Stored Procedure in the Designer tool itself and then you can create report off this Stored Procedure Universe.

The New Universe Wizard has a option for creating a universe from RDBMS or Stored Procedure. You just have to check the option for stored procedure and follow the next steps.

You can dowload the BO XI 3 Universe Designer guide from help.sap.com

Thanks,

Sheeba

[Former Member · Accepted solution]

Hi Sheeba

Thanks for your help.

We are using boxir2. Is there any solution for that?

Anita

[amrsalem1983 · Accepted solution]

hii there

BO XI R2, you can not create universe using stored procedures,

from BO XI 3.0 you can do it,

good luck

Amr

[Former Member]

Hi

i tried to build a report from the deski out of stored procedure universe and ia m getting the error saying"You are not authorise to use this type of universe in this application " i am using Xir3.1

Any suggestion wud be greatly appreciated

Thanks

pavan

[Former Member]

Please check whether universe has been saved for "all users".

[Former Member]

Yep it is saved for all users and still i am getting same error!!!!!!!

Let me know

Thanks

Pavan

[Former Member]

Hi Anita,

As Prashant has mentioned that we can not create a Universe based on a Stored Procedure on BO XIR2( this is by design but this functionality is available in BO XIR3.0 ( i.e. creating a universe based on Stored Procedure) however we can create Deski report from Stored Procedure but not Webi report.

-

---

#

Following are the steps to create a desktop intelligence report based on a stored procedure:

- Login in to Desktop intelligence client tool.

- Click on File-->New. The New Report Wizard will popup.

- Select the option 'Generate a standard report' and Click on 'Begin'.

- Select the option 'Others' and from the drop down list select 'Stored Procedures', Click on 'Next'.

- Select the 'Connection' of the data account where the stored procedures are located.

- Click on 'Test' and check whether the server is responding. Click on 'Next'.

- Select the Stored procedure from the list, on which you want to create the report.

- Click on 'Finish'.

- Stored Procedure Editor window will going to pop-up, here if you want you can apply the prompts on the stored procedure.

- Click on 'View' to preview the results before making the report or click on 'Run'.

-

---

1. Following are the steps to use stored procedure in universe designer XIR 3.0:

- Login in to Universe Designer.

- Start>All programs>Businessobjects XI 3.0>Businessobjects Enterprise>Designer.

- Click on File-->New. (Universe Parameters window will popup).

- In the Definition Tab-

NOTE: Please select the check box in the bottom of the Connection window. i.e. u2018click here to choose Stored Procedure Universe'.

- Enter the Name of the Universe. e.g Oracle stored procedure.

- Enter the Description.

- Click on 'New' Connection. (New Connection Wizard window will going to appear)

- Click on 'Next'.

- Select the Connection Type as 'Secured' from the drop down list.

- Enter the Connection Name. e.g. Oracle_SP.

- Select the Middleware drivers from the list. e.g.(OracleOracle9Oracle Client)

- Click on Next.

- Select Authentication Mode as 'Use specified username and password' from the drop down list.

- Enter the database Username/Password and Service.

- Click on 'Test Connection' and check the server is responding.

- Click on 'Next'.

- Select the Connection Pool Mode as 'Keep the connection active for'.

- Enter the values for Pool timeout, Array fetch size, Array bind size, Login time out and Click on 'Next'.

- Click on Finish.

- Click on 'Ok'.

- Double click in the Structured Pane(right side) of the Universe Designer or Click on Insert-->Stored Procedures.

- Select the Stored procedure from the list, you want to use in universe designer, and Click on 'Insert'.

- Drag and Drop the stored procedure tables from structured pane to object pane (left side) of the universe designer.

- Save the Universe and Export it to the repository.

NOTE: For any more details regarding Stored Procedures in Universe Designer, please refer the XIR3 Universe Designer Guide .

-

---

#

The option to create a Web Intelligence report based on Stored procedure is not visible.

1. Open a Web Intelligence report based on a Universe

2. Click on Edit query << SQL

3. Seletc Custom SQL

4. Make changes to excute the stored procedure

5. An error is produced.

Cause

The feature to use Stored Procedure in Web Intelligence is not present in BO XI R2 and similarly in BO XI R2 SP4.

We can create a Desktop Intelligence report based upon Stored Procedure successfully.

This feature has been considered in the new Product release that is BO XI R 3.0 onwards.

Here we even have the option to create a Stored Procedure based Universe.

I hope this will help you.

Regards,

Deepti Bajpai