invalid AssignmentOperator on variable group by clause in query

[waalp]

I have a report, that uses parameters to define a user controlled group by statement. Depending on choices the user makes in the input controls certain fields are added to the query.

I use non-prompted parameters to create this group by statement like:

$P{p_gr_month} ? "apa.\"MONTH\"" : "null"

So if p_gr_month is true, it will add apa.MONTH to the select of the query, otherwise it will add null instead.

For the group by part it does something simular: $P{p_gr_month} ? ",apa.\"MONTH\"" : ""

Now it does all work in iReport, and as an Report in JasperServer. But when I wish to use this in adhoc mode as a topic, it gives a compilation error saying that there is an invalid AssignmentOperator on token "MONTH".

I think this is because the query in adhoc mode shows this as the value in the parameter ("GROUP BY apa."MONTH" ");

I think it tries to parse month as a value or parameter, but I need to double quote month because of the postgresql setup we have here, and as I said it works in iReport as well as in JasperServer as a report, just not in adhoc mode as a topic.

 

Any ideas on what i'm doing wrong here ?

 

Regards,

Peter

[mdahlman]

Peter,

Can you send a .jrxml with query that can be used to reproduce the issue? The SQL should be something along these lines so that it's runnable by anyone with PostgreSQL (but without your tables):

SELECT count(x.col1) FROM (
SELECT 'abc' as col1, 'Jan' as "MONTH"
UNION SELECT 'xyz' as col1, 'Jan' as "MONTH"
) x
GROUP BY x."MONTH"

Is a query like that sufficient? Is the use of the parameter critical to reproducing the problem? I suppose it is. Maybe your query will end like this:

GROUP BY $P!{MyParam}

Regards,
Matt

[waalp]

It seems to be a problem (at least for a bit) because of our postgres demanding double quotes with columns.

 

In the GroupByTest2 I have all column names surrounded by double quotes, and it gives an error. In the GroupByTest3 I have no double quotes around the columns, and it at least doesn't give an error in the adhoc modes when i press the run report button.

 

It does however give a "no results found" altough in the design mode it does return data. Do i have to make input controls for all parameters, even the ones I do not show on the screen (because they are filled by the other input controls) ?

I currently have input controls for the following parameters:

- p_gr_month

- p_gr_week

-  p_gr_recruiter

- p_gr_hiringmanager

- p_gr_organisation

 

For the other ones I do not have input controls in jasperserver, the report works fine in iReport, works in JasperServer as a nornal report. It also works in adhoc modus (in the design mode), but returns the no results found when you run the adhoc report.

 

Regards,

Peter