Sometimes the users have the problem, that they want to create a report with data, which are not in a database. Often this data are individual data, that are stored in excel.

With BO 4.1 you can use an excel file as datasource in the webi report. For this, the following steps are necessary:

  • upload the excel file
    • go to the BI Lauchpad – register Documents and go to the folder, where you want to store the file
    • then New and  “local Document”
    • now search the file on your local client an upload the file
  • create a new data provider
    • Data Access – “new data provider”
    • From Excel and select the excel file from the BI-Portal
    • Configure the data provider
    • Run Query

now you can work with the data in the report.

After the report is created, often the problem is, that the excel file is not up to date and the content must be refreshed. If the user tries to upload the new excel file the same way as described, than the old file can not be overwritten. If you make an upload under a different name, you must create a new data provider and all variables in the report has errors.

The question is, how to update the uploaded excel file. You can do this the following way:

  • go to the file in the BI-Portal
  • right click on the file and click on “organize”
  • than you have the option “Replace File”

Now the file is refreshed.

Note: Yoiu can move the report and excel file, but if you copy both files, the excel data provider doesn’t work because the CUID changes and the copied report has the connection to the old excel file

Hope this helps

Andreas

To report this post you need to login first.

3 Comments

You must be Logged on to comment or reply to a post.

  1. Kan Zhang

    Hi Andreas,

    Thank you so posting.This is very helpful. I uploaded the excel to public folder and created a webi against it. Then I followed to Replace File with an updated measure. But data doesn’t change on my WebI….Do you have any idea why it doesn’t get refreshed? Do we need to go to the Input Files Store to update it again? If that is the case, the Replace File feature really doesn’t make a lot of difference.

    Thanks,

    Kan

    (0) 
    1. Andreas Sbrzesny Post author

      If you refresh the datasource in WebI, only the data will be refreshed. If you have changes in the structure (additional column) you must go to the Query Panel and go to “edit settings” under Query definition.  Also the Query Properties must set to refreshable.

      Hope, that helps

      Andreas

      (0) 
      1. Kotha Kris

        Hi Andreas hope you can clarify below

        1. how to combine an existing webi report and excel (both have same no.of columns and same column names)

        2. i have a macro generating the above excel for me, what do i need to do if the macro doesnt result in correct excel

        3. how to refresh the incremental data in the webi report as soon as excel file changes with new data. (in my case a macro runs on excel we get everyday)

        Your help would be greatly appreciated

        Thanks in advace, Kris

        (0) 

Leave a Reply