Set query FROM table using report parameter in BIRT

2
votes

I am using the BIRT report writer, and I have multiple tables with the same data structure. In my report design, I want to select the table my query uses as a report parameter (as part of a mysql query in the data set) -- but I can't figure it out.

When I create the data set, it's great that I can use parameters in the form of SELECT * FROM WHERE ?, and set these to report parameters, but I get an error if I set the ? to the table, such as:

SELECT * FROM ? WHERE 1

Is there another way I can do this? As it's java, I assume the syntax follows that of a PreparedStatement. In BIRT 2.5 there is a property binding option under the data set dialog... and I've tried setting my query as: "SELECT * FROM "+params["DataTable"].value+" WHERE 1", but that was also results in an error.

1
reportingprepared-statementbirt
Trying to parameterise the table is usually a big red flag, saying, "Bad idea!" Perhaps a brief explanation of why you need to do this will help others provide a good answer. - Marcelo Cantos
Thanks Marcelo - I have an app to load in excel data (to a non-normalized) table, and then I create a standard view with consistent field names for a standard report. I have multiple of these excel sheets, and have been loading them into different tables, (the structure varies a bit... a more normalized structure is coming later, but the report view will always remain the same). Since each have an identical report view, if I could switch the view name in my report query, I could reuse the same report without having to make modifications. Is this the wrong way to go about it? - Adam Morris

1 Answers

4
votes

I just answered this same question over at BIRT Exchange: http://www.birt-exchange.org/forum/designing-birt-reports/19097-set-query-table-using-report-parameter.html#post62590

I should note that the queries MUST return the same fields & data types after you modify them. I only note this because of your statement above that the table structure varies "a bit". It is perfectly safe to modify the FROM or WHERE clauses in a BIRT query, but you cannot modify the actual SELECT construct.