A Script to Change a Managed Excel 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
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