Dynamic Data Connections
- by Tim Dexter
I have had a long running email thread running between Dan and David over at Valspar and myself. They have built some impressive connectivity between their in house apps and BIP using web services. The crux of their problem has been that they have multiple databases that need the same report executed against them. Not such an unusual request as I have spoken to two customers in the last month with the same situation. Of course, you could create a report against each data connection and just run or call the appropriate report. Not too bad if you have two or three data connections but more than that and it becomes a maintenance nightmare having to update queries or layouts. Ideally you want to have just a single report definition on the BIP server and to dynamically set the connection to be used at runtime based on the user or system that the user is in.
A quick bit of digging and help from Shinji on the development team and I had an answer. Rather embarassingly, the solution has been around since the Oct 2010 rollup patch last year. Still, I grabbed the latest Jan 2011 patch - check out Note 797057.1 for the latest available patches. Once installed, I used the best web service testing tool I have yet to come across - SoapUI. Just point it at the WSDL and you can check out the available services and their parameters and then test them too.
The XML packet has a new dynamic data source entry. You can set you own custom JDBC connection or just specify an existing data source name thats defined on the server.
<pub:runReport>
<pub:reportRequest>
<pub:attributeFormat>xml</pub:attributeFormat>
<pub:attributeTemplate>0</pub:attributeTemplate>
<pub:byPassCache>true</pub:byPassCache>
<pub:dynamicDataSource>
<pub:JDBCDataSource>
<pub:JDBCDriverClass></pub:JDBCDriverClass>
<pub:JDBCDriverType></pub:JDBCDriverType>
<pub:JDBCPassword></pub:JDBCPassword>
<pub:JDBCURL></pub:JDBCURL>
<pub:JDBCUserName></pub:JDBCUserName>
<pub:dataSourceName>Conn1</pub:dataSourceName>
</pub:JDBCDataSource>
</pub:dynamicDataSource>
<pub:reportAbsolutePath>/Test/Employee Report/Employee Report.xdo</pub:reportAbsolutePath>
</pub:reportRequest>
<pub:userID>Administrator</pub:userID>
<pub:password>Administrator</pub:password>
</pub:runReport>
So I have Conn1 and Conn2 defined that are connections to different databases. I can just flip the name, make the WS call and get the appropriate dataset in my report. Just as an example, here's my web service call java code. Just a case of bringing in the BIP java libs to my java project.
publicReportServiceService = new PublicReportServiceService();
PublicReportService publicReportService = publicReportServiceService.getPublicReportService_v11();
String userID = "Administrator";
String password = "Administrator";
ReportRequest rr = new ReportRequest();
rr.setAttributeFormat("xml");
rr.setAttributeTemplate("1");
rr.setByPassCache(true);
rr.setReportAbsolutePath("/Test/Employee Report/Employee Report.xdo");
rr.setReportOutputPath("c:\\temp\\output.xml");
BIPDataSource bipds = new BIPDataSource();
JDBCDataSource jds = new JDBCDataSource();
jds.setDataSourceName("Conn1");
bipds.setJDBCDataSource(jds);
rr.setDynamicDataSource(bipds);
try {
publicReportService.runReport(rr, userID, password);
}
catch (InvalidParametersException e) {
e.printStackTrace();
}
catch (AccessDeniedException e) {
e.printStackTrace();
}
catch (OperationFailedException e) {
e.printStackTrace();
}
}
Note, Im no java whiz kid or whizzy old bloke, at least not unless Ive had a coffee. JDeveloper has a nice feature where you point it at the WSDL and it creates everything to support your calling code for you.
Couple of things to remember:
1. When you call the service, remember to set the bypass the cache option. Forget it and much scratching of your head and taking my name in vain will ensue.
2. My demo actually hit the same database but used two users, one accessed the base tables another views with the same name. For far too long I thought the connection swapping was not working. I was getting the same results for both users until I realized I was specifying the schema name for the table/view in my query e.g. select * from EMP.EMPLOYEES. So remember to have a generic query that will depend entirely on the connection.
Its a neat feature if you want to be able to switch connections and only define a single report and call it remotely. Now if you want the connection to be set dynamically based on the user and the report run via the user interface, thats going to be more tricky ... need to think about that one!
