BIRT Report Parameters - Using Hard Coded SQL Statements

Today I would like to talk about BIRT reports and, in particular, report parameters that are hard coded select statements in the report design file. The following will be especially important for those people running their application in SQL Server.

The latest release of TRIRIGA includes several security reports that contain hard coded SQL select statements. Unfortunately, the select statements apparently cause a problem for those clients using SQL Server. The report is supposed to display the list of security groups. It does this via a simple select statement that gets the group name from the t_group table and looks like this:

SELECT name1 FROM t_group WHERE name1 <> 'Admin Group';

If you take that SQL statement and run it in the Admin Console->Database Query Tool you will see the expected results. When run from the context of a BIRT report, however, SQL Server does not appear to handle the select statement properly and you end up with a parameter list with no values. To correct the problem, you need to uppercase the entire SQL statement with the exception of the value in quotes. So the altered SQL statement will appear as follows:

SELECT NAME1 FROM T_GROUP WHERE NAME1 <> 'Admin Group';

In general, SQL Server BIRT report writers, be aware that if you decide to hard code a select statement such as this to provide the values for a prompt and you do NOT see anything in the list box for parameter selection, check the select statement that you used and make sure you uppercase all of the select statement that references either a database object or are reserved SQL keywords. Obviously, in the case of text searches as the one noted here, the case must match the expected value in the database.

You may now be asking yourself, how do I go about changing that in our BIRT reports? It is relatively easy and the following are instructions on how to do this within the report designer tool. I am assuming the report writers are familiar with the report design lifecycle. These instructions were written with the report mentioned above, so you will need to generalize as appropriate.

1 - In the report designer, expand the Data Sets section to reveal the SecurityGroups and SecurityPermissions datasets.
2 - Expand the SecurityGroups dataset to reveal the NAME1 object.
3 - Click on the Advanced vertical tab in the Property Editor.
4 - Scroll down to find the Query property. It will look as if the Value is set to "SELECT name1".
5 - Click on the Value and you should see this SQL:

SELECT name1
FROM t_group
WHERE name1 <> 'Admin Group'

6 - Everything except 'Admin Group' should be in uppercase in order for SQL to process the query correctly in SQL Server environments.

Taken from https://www.ibm.com/developerworks/community/blogs/8eeee156-f1f3-45ec-970c-53b17ccb452a/entry/birt_report_parameters_using_hard_coded_sql_statements?lang=en