Skip to Content
Author's profile photo Former Member

A Script to Change a Managed Excel Datasource of a WEBI Document

Hi All,

I woukd like to share a script in Javascript which main objective is to change a managed datasource of a WEBI document.

Version required BOXI 4.1 SP5

What is the problem to be solved ?

Here, at my company, we frequently feel the need of having a WEBI document to read a Excel file that is the scheduled result of another WEBI document.

When we were on BOXI 3.1 we could use the approach described  by Jonathan Brown in Creating a Webi doc off of an unmanaged Excel data source on a network share .

Somehow, wtih the migration to 4.1 we loose this ability.

With 4.1 came the possibility of reading an Excel file from BI Launch Pad, what is called a managed datasource.

So we can read the managed datasource and buid the document on it.

But what if this Excel file is generated by a scheduled document every day ?

As the datasource is referenced by its ID not by the name, in order to use this new managed Excel file, one has to rebuild the entire document by reading it again.

If we were doing with Universes, we could go through this post Changing Dataproviders for a Webi Report using RESTful Web Services by Ludek Uher

Unfortunately, the re-map of an Excel datasource wasn´t implemented yet.

This script has the purpose of , automatically, change the datasource.without manual effort

Initial assumptions

Your webi doc has just one dataprovider, the one from the excel file

I´m treating the errors

I´m not dealing with drill filters nor report elements filter

The Excel file is scheduled to the Inbox of the user whom did log into the platform through the API with the name of ‘Managed-DP’

Workflow to be followed

1- Log in

2 – Determine the ID of the last excel instance of ‘Managed-DP’ at the Inbox

3 – Get the dataprovider id ( idDpDe – The id of the dataprovider, nomeDPDe – The query name

4 – Insert the new excel file as a dataprovider (with the Name “Consulta 2”, idDPPara)

5 – Gets idDPDe specification

6  Gets initial idDPPara specification

7 – Changes the idDPPara specification id´s datasources from the objects

8 – Saves the new idDPPara specification

9 – Saves the document and refresh it

10 – Gets the variables list

11 – For each variable get  its specification

12 – Changes the specification

13 – Update the variable definition

14 – Gets the report list

15 – Loops through the reports list

16 – Gets the report specification

17 – Changes the report specification

18 – Update the report specification

19 – Gets the reports data filter specification

20 – Changes the data filter specification

21 – Updates the reports data filter

22 – Gets the input control (IC) nlist

23 – Loop through the IC list

24 -Get the IC specification

25 – Deletes the original IC

26 – Changes the IC specification

27 – Insert a new IC with the specification changed

28 – Deletes idDPDe

29 – Alters idDPPara name to “Consulta 1”

30 – Saves the document

31 – Log off

The steps 24 through 27 (deleting the IC and inserting a new one) are necessary accoding to  Updating Input Control Definition.

Perhaps that´s an easier way of accomplish the proposed problem.

I would love to hear from you guys and girls about it.

One side effect of this script is that it deals with the inclusion and exclusion of columns in the excel datasource dinamically.

I´m attaching the script  to this blog post



Assigned Tags

      You must be Logged on to comment or reply to a post.
      Author's profile photo Former Member
      Former Member

      Thanks for sharing Rogerio.

      For some requirements one valid workaround could be:

      -Create a webi schedule to the Destination: File System, Format: Excel or CSV.

      -A new daily instance will update the Excel or CSV file on the BO Server

      -Create a universe on this daily output

      -Create a Web Intelligence on the universe


      Author's profile photo Former Member
      Former Member
      Blog Post Author

      Hi Victor,

      thanks for your comment. The problem with this approach and the one suggested by Ted is that it depends on IT crew.

      I´m looking for solutions that doesn´t depend on it and can be implemented at client side.



      Author's profile photo Former Member
      Former Member

      I've been messing with solutions for this problem for a while now.  I've found that it's cleaner to replace the Excel file directly on the Input FRS as opposed to modifying the WebI document and risking corruption.

      Author's profile photo Former Member
      Former Member
      Blog Post Author

      Hi Paul,

      Thanks for your comment. Do you have, by the way, a snippet of code to replace an Excel file in the CMS.



      Author's profile photo Former Member
      Former Member

      Some of the Java code:

      // get target file from Excel InfoObject

      IProperty siFiles = properties.getProperty(PropertyIDs.SI_FILES);

      String siFile = null;

      String siPath = null;               

      if (siFiles != null && siFiles.isContainer())


        IProperties fileProps = (IProperties)siFiles.getValue();

        // assume there is only one file, use SI_FILE1 property

        if (!fileProps.containsKey(PropertyIDs.SI_FILE0 + 1))


          throw new Exception("SI_FILE1 property not found on Excel InfoObject.");


        if (!fileProps.containsKey(PropertyIDs.SI_PATH))


          throw new Exception("SI_PATH property not found on Excel InfoObject.");


        siFile = (String) fileProps.getProperty("SI_FILE1").getValue();

        siPath = (String) fileProps.getProperty("SI_PATH").getValue();       




        throw new Exception("SI_FILES not found.");


      siPath = siPath.substring(FRS_INPUT.length());

      siPath = siPath.replace('/', '\\');

      String targetPath = frsRootPath + siPath + siFile;                       

      logger.debug("Target Path:   " + targetPath);       


      // ensure file exists and can be written

      File targetFile = new File(targetPath);   

      if (!targetFile.exists() || !targetFile.canWrite())

        throw new Exception("Target file is not accessible.");

      // replace with source file

      FileInputStream fis = new FileInputStream(sourceFile);

      FileOutputStream fos = new FileOutputStream(targetFile);

      byte[] buffer = new byte[4096];

      int bytesRead;

      while ((bytesRead = != -1) {

        fos.write(buffer, 0, bytesRead);





      // trigger an update to the object's last modified time