excel converting to webi (BI 4.1)

[Former Member]

Greetings,

Every month I receive an excel workbook with two reports (order & receiving). Each report includes around 25 columns whereby I go thru renaming and deleting unwanted columns.

I was wondering if this task can be done in WebI.  1. In the receiving report I’d like to rename receipt date and store name as in order report:  --> order date and supplier.

Order Report ---  Receiving Report
Order Date    ---    Receipt Date (should be named Order Date)
Supplier      ---      Store Name (should be named Supplier)
Account Code  ---  Account Code
Order Number ---   Order Number
Blanket Order ---
Order Type    ---    Order Type
Distribution Amount --- Distribution Amount

2. Delete the remaining columns in both order and receiving reports so in the end I’m left with the following:

Order Number --- Order Date --- Order Type --- Blanket Order --- Supplier --- Account Code --- Distribution Amount

3. Reconcile the two reports: combine duplicates on the basis of order number to get distribution amount totals

4. Subtract receiving from order (order – receiving) to determine what order has been cleared/received, what are outstanding etc. 

(Please let me know the best way to attach a sample excel file)

TIA,

[amitrathi239]

Hi

Order Report ---  Receiving Report
Order Date    ---    Receipt Date (should be named Order Date)
Supplier      ---      Store Name (should be named Supplier)
Account Code  ---  Account Code
Order Number ---   Order Number
Blanket Order ---
Order Type    ---    Order Type
Distribution Amount --- Distribution Amount

For this you can create variables in the Webi and these variables will display the names which you want every month.

2. Delete the remaining columns in both order and receiving reports so in the end I’m left with the following:

Order Number --- Order Date --- Order Type --- Blanket Order --- Supplier --- Account Code --- Distribution Amount

Webi will not delete tyhe columns but webi report will display the columns which you will select in the webi table.Like if you are getting 25 objects in the webi and you will just drag 10 objects in the webi table/cross tab.everytime you will get only 10.

3. Reconcile the two reports: combine duplicates on the basis of order number to get distribution amount totals

You can use create some variables in the webi.

4. Subtract receiving from order (order – receiving) to determine what order has been cleared/received, what are outstanding etc.

This is again for which you need to create some variables.

Amit

[Former Member]

Thanks Amit,

My excel file consists of two reports (order & Receiving).  At the end of each month I manually go thru the formatting process: deleting unwanted columns, renaming etc.to reconcile.

1. it a matter of Importing the excel file directly into Webi & perform formatting, apply formulas etc. every month?

2. Do I need to create a universe, join etc.

3. Do I need to save the excel file in a central location?

Regards,

[amitrathi239]

Hi,

1. 1. it a matter of Importing the excel file directly into Webi & perform formatting, apply formulas etc. every month?

With webi you need to build the report once with required formatting etc. Each month you need to just replace the new file with old one with same name. Once you will refresh the report it will automatically refresh the data with same report. If you have two excel report and want to show data in the single webi report then you can use add query and insert both report data in webi.

1. 2. Do I need to create a universe, join etc.

You can use Webi Rich client to create report directly on the excel file.In rich client you can find excel as source. No need of universe.

1. 3. Do I need to save the excel file in a central location?

It's up to you how you are managing this. One option if every month you need to do this task and you have to mail this to users then you can put excel sheet in your computer and then create report. Everytime you need to replace the excel.

Other option is if you want users will access this report from Launchpad then you have to put this file every month on the BO server.

Amit