SpagoBI Studio and BIRT Report with String Parameters does not work

Question

SpagoBI Studio BIRT fails to retrieve external data set definition when data set has a String parameter.

I'd like to have an external data set with String parameters, defined in SpagoBI. That data set uses the parameter in the query's WHERE clause. I'd like to use SpagoBI Studio to design a BIRT report that use that same data set and properly pass the data set's required parameters.

When I try to retrieve the External Data set definition (with parameters) into a BIRT report, I get an error and it fails to import the data set definition. If I remove those parameters from the data set, retrieving the data set definition works, but I no longer have parameters.

If the External Data Set has String parameters which are used in the query's WHERE clause, then SpagoBI Studio / BIRT will fail when trying to attach/retrieve the data set. I'm not able to get to the point where I can specify that null is a permissible value on the parameter in BIRT, because... it fails before it can display the data set fields, params, etc in BIRT

When the External Data set has Numeric parameters, the whole scenario works fine but I'm limited to passing numeric parameters to drive record selection in the query.

Please see the following JIRA.

Studio Fails to import External Data Set where query has single quotes wrapping a parameter http://www.spagoworld.org/jira/browse/SPAGOBI-1870

Discussion of the issue on the forum, at http://www.spagoworld.org/jforum/posts/list/3187.page#10288

Answer 1

It turns out that passing a String parameter to SpagoBI and Birt, does in fact work, but there are some snags to work through.

1. Directly On the dataset, the string based parameter is automatically wrapped in single quotes. So, in preview mode, don't put single quotes around your preview parameter.

2. Directly on the dataset, if any part of the WHERE clause for a SQL query uses a Unique Identifier, you'll have to convert it to a String. That can then be compared with a String. This snag caused most of the problems but the error message was non-descriptive.

With the two above points taken care of, the dataset can now be imported into SpagoBI Studio / BIRT.

3. When running the report against that data set, in SpagoBI Studio / BIRT, do not enclose the string parameter in single quotes. The data set's auto-wrapping of string parameters works in this scenario.

4. When running the report doc through SpagoBI GUI or through the SDK, it is necessary to pass in the parameter enclosed in single quotes, because the dataset does not auto-wrap the string parameter as you would expect.