on 01-31-2011 6:53 PM
I want to create a universe using excel, but I don't know if it is possible.
If it is possible should I save the excel file in an specific folder? where?
Is it possible to create WebI reports using this universe?
Edited by: PadawanGirl on Jan 31, 2011 7:53 PM
Hi PadawanGirl,
It cannot be that difficult to create a new universe based on Excel spreadsheet. You can log on to the Designer, do a "File-->New", and from the dialog boxes, you should be able to find "Excel Spreadsheet" under "Microsoft". Just follow the dialog boxes and you should be able to create the universe.
If you have WebI, you should be able to run against that universe with no problem. Just make sure your Excel file and the DSN are on the BusinessObjects server.
After you have created the DSN on the BusinessObjects server, go back to Universe Designer, when you select the new connection, you should see your new DSN name from the drop-down list. After you added the table to the universe and created the new objects, you should always go to the Properties tab of the new dimension object, click on the "Display..." button to view the data. This will give you some idea if your universe is actually pulling data or not.
Also, if you are including the headers on the Excel file, it is better to name the headers with no space. And don't forget to name your Excel tab instead of leaving it with the default name of "Report 1". This makes it so much cleaner.
The "properties" tab that I was referring to belongs to the dimension object in the universe. I thought you have already added the table and created the objects. Let me make sure I understand you correctly, have you completed successfully the following steps?
1. Prep the Excel file... i.e. remove spaces in headers (these headers can become your object names in the universe), name the tab instead of the default name of "Report 1", store the file in the BusinessObjects server.
2. Create a system DSN on the BusinessObjects server using the Microsoft Excel Driver to point to the Excel file in the same server. Test the connection to make sure it is good.
3. Open Universe Designer to create a new universe. Click "New" to create a new connection and select the DSN from the drop-down list. Test the connection to make sure it is good with no error.
4. Add table to the universe by selecting the Excel file from the list (there should be just one on the list).
5. Drag the table to the left pane to create the objects.
6. Click on each object to open up a dialog box. On the Properties tab, click on Display to make sure you have data.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Hai
I will tell the steps .
store the Excel file in a Directory or in Local Machine.
.Go to Web-Report :
Click on new , Create a new Report .
Select the Data dource : From Excel file,
An pop-window will appear, Give the path of the Excel file where it has been stored.
Click on next,next. the New webr-report is created .
run the Webi-report .
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Hi Padawan,
Creating a Business Objects Universe from an Excel File
To create an Example Universe, we will first Extract data in a BusinessObjects report and
save it to Excel. Letu2019s create a new Document from the Island Resort Marketing
universe, and include All of the Resort attributes and Revenue.
Generate a New Report from a Universe
Generate a Standard Report
Creating a Business Objects Universe from an Excel File
Choose Island Resorts Marketing.
Choose all of the Resort class, and the <Revenue> Measure object.
Creating a Business Objects Universe from an Excel File
Depending on your preference here, you can either choose Edit > Copy All or you can
edit the Data Provider to export the results of this query to Excel.
I like to use the Data Provider method, since this allows me to specify a path, etc. If you
have used copy/paste, you will have one extra step that will be described, but not
demonstrated.
Export to Local File Format and make sure that the Format is listed as: Microsoft
Excel 97 Files (*.xls). You may even want to Change the Path, or Rename the file at the
Name line, or Browseu2026 to a new path.
Creating a Business Objects Universe from an Excel File
Once the Export has completed, launch Excel and browse open the file that was just
created. If you have Copied/Pasted the values, then you should already be at the next
step.
If you havenu2019t done this already, browse over the Resorts.xls file and open it. If you
have used Copy/Paste, just bring Excel to the foreground.
Creating a Business Objects Universe from an Excel File
Select all of the data. If you Copy/Pasted, make sure that you delete the Header that
copied as well. With all of the data selected, make sure the range has a name. You will
need to enter a name if youu2019ve pasted this, otherwise it should match the Filename minus
the .xls. Save this if youu2019ve made any changes, or created this from scratch.
Jump to the BusinessObjects Designer module and create a New Universe. Iu2019ve named
my Universe: Resort Revenue
Creating a Business Objects Universe from an Excel File
Create a new Connection, and choose ODBC Drivers.
Iu2019ve named my connection: Resorts_xls and chosen Generic ODBC Datasource for my
Database engine. Click on the ODBC Admin button.
Add a new System or User DSN. If you want this available for anyone on your machine,
System DSN is the better bet. Select the appropriate tab and hit Addu2026
Creating a Business Objects Universe from an Excel File
Choose the Microsoft Excel Driver (*.xls) from the list of available data source drivers.
Name the Data Source Name something that is meaningful, as it will be what appears in
the Drop-Down menu. I named it Resorts.xls File and next weu2019ll choose Select
Workbooku2026 and browse to the location of our Resorts.xls file.
Unless youu2019ve changed the location, it should be located under:
C:/Program Files/Business Objects/Business Objects 5.0/UserDocs/
Select the XLS file and click u201COKu201D
Creating a Business Objects Universe from an Excel File
Verify that the path is set for the Workbook and click u201COKu201D
You should see the new DSN created with the name of your Choosing. (In my case, itu2019s
Resorts.xls File.) Go ahead and click u201COKu201D
Back in the Designer Module, Choose the Resorts.xls File from the Data Source Name:
drop-down list, and click u201COKu201D
Creating a Business Objects Universe from an Excel File
Once the connection has been set, click u201COKu201D and weu2019ll get to Universe Building.
Double-Click in the Schema area in the designer, or use the Table Browser to add the
Resorts table (named-range). Once they are added, feel free to move them over to create
Classes and Objects off this table as well.
Creating a Business Objects Universe from an Excel File
Iu2019ve modified the SQL of the Revenue object, to turn this into an aggregate. Set the
formula to: Sum(Resorts.Revenue) and click OK.
My Final Universe looks like the above. Once this Universe is created, Save it and
launch the Business Objects Reporter module.
Creating a Business Objects Universe from an Excel File
Select the newly created Resort Revenue universe and choose the objects youu2019d like to
report on.
Iu2019ve selected the <Country>, <Resort> and <Revenue> objects.
Here are my final resultsu2026
All the best.
Praveen
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
It is possible, but I would strongly recommend importing the Excel file into a database instead. You need to consider that an Excel spreadsheet is a single user data source and won't be as accessible as a database with an OLEDB connection, together with its inherent security features.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
You have to create an ODBC DSN that uses your excel file on your BOBJ server. The file should be located in a folder accessible for the user that runs the BO services.
If you have BO XI 3.1 SP3 then you can use Excel files as data sources directly (no need for a universe) using the WebI rich client. The documentation of the WebI rich client describes where the Excel file should reside in order to be able to refresh your report againt this data source even if the report runs in the InfoView.
http://help.sap.com/businessobject/product_guides/boexir31SP3/en/xi31_sp3_webi_rich_en.pdf
Check the Section "Refresh option in Infoview" in page 33.
Regards,
Stratos
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
94 | |
11 | |
11 | |
10 | |
9 | |
7 | |
6 | |
5 | |
4 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.