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!
