SpagoBI DataSet Webservice via Groovy

Note: I have not yet figured out how to get a SpagoBI Data Set (of the type Web Service) to work. However… I’ve figured out a way to make it work by creating a SpagoBI Data Set( type Script) and then using Groovy to make a web service call via Groovy WSLite library.

As part of the POC, I used the publicly available Holiday Web Service to look up Mother’s day for any given year. (Groovy WS-Lite uses it as the simple example).

SO… Round-Trip goes something like this

1. Report is Requested from SpagoBI , given a parameter of “year”.
2. SpagoBI executes the Document, delegating to the BIRT report engine.
3. BIRT report engine executes the Report, passing in the parameter of year
4. The Report has a SpagoBI Data set reference, which passes the year to the SpagoBI Data Set (back in SpagoBI Core)
5. SpagoBI Data Set
6. • Makes a SOAP call via Groovy and Groovy WS Lite library
   • Parses the response
   • Rewrites the response in the XML format standard for SpagoBI Data Sets, and includes the “requested year” and the response (date for Mother’s day)
   • The Report retrieves the data from the SpagoBI Data Set
   • Report renders showing the requested year and the date on which Mother’s Day occurs for that year

Dependencies:

Groovy WSLite 2.0
Groovy-based Web-Service library which supports SOAP and RESTful calls
Versions

• 2.0 Beta . Requires Groovy 2.3 or later. Worked fine for SOAP. Failed for RESTful call
• 1.0 / 0.7.1 : Requires Groovy 1.7.6 or later.  Worked fine for SOAP and REST

Groovy Language
We’re using Groovy WS Lite, required a newer version of Groovy than that which shipped with SpagoBI 5.0 (Groovy 1.5.6)
So, it was necessary to replace the Groovy-all library jar with a later version and add the Groovy WS Lite Jar
Download : groovy-all-2.4.3.jar

Steps

• See instructions for Upgrading Groovy and Installing Groovy WS Lite
• Log into Spagobi
• Create a new data set
• Create a new data set
• Set the Data Set label
• Set the Data Set Type (on the Type tab) to Script
• Set the Language for the Script to Groovy
• Setup a parameter named “param_year”, with the type of “Generic”
• Add the script code (below)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33

import wslite.soap.*
 
 
def param_year = parameters['param_year']
 
// When SpagoBI Studio inquires with SpagoBI Server for the definition of the data set, 
// it will not work because it doesn't pass a default parameter
// Get around this by specifying a parameter value to use when none is provided.
 
if (param_year ==null) {
   param_year  = "2015"
}
 
def client = new wslite.soap.SOAPClient('http://www.holidaywebservice.com/Holidays/US/Dates/USHolidayDates.asmx')
def response = client.send(SOAPAction:'http://www.27seconds.com/Holidays/US/Dates/GetMothersDay') {
    body {
        GetMothersDay('xmlns':'http://www.27seconds.com/Holidays/US/Dates/') {
            year("${param_year.toInteger()}")
        }
    }
}
//assert "2011-05-08T00:00:00" == response.GetMothersDayResponse.GetMothersDayResult.text()
assert 200 == response.httpResponse.statusCode
assert "ASP.NET" == response.httpResponse.headers['X-Powered-By']
 
// Remember Java doesn't know how to interpret a Groovy GString. The resulting output must be cast to a Java String
// If you leave it as a GString preview in SpagoBI Server will work  but Studio will be unable to attach to the data set.
 
"""
 
 
 
""".toString()

• Preview in SpagoBI (Server) providing a year. Press the preview button and you should get the response back from the Holiday Web Service
  

  Preview Groovy-WS Data Set Prompt

  

  SpagoBI Groovy-WS Preview Result

• The next post will cover consuming that SpagoBI Data Set in a BIRT report