Two scenarios - same report design

[starlight]

Puhhh, I have a quite tricky question - which I didn't formerly consider to be such tricky and I am not sure if it should be solved within the SQL-query or within the report design.

Ok, I'll try to explain: The possibility should be given, that the report can be called with the transmission of one parameter or two parameters. Within the report should be checked if the second parameter has been passed or not. Both parameters are conditions for the where-clause within the major query which would imprise to create two subselects. The style of the report should remain the same for both selects.

The idea I had was to check within an expression if the second parameter is filled, creating two different subselects within the from-clause for the two cases and aliasing them. This alias I wanted to put into a parameter or variable, after having checked if par2 has been passed or not, and integrate it into the select-columns - part - but maybe this cannot work.

I couldn't solve it with the sql-statement, because if you use case or if (I'm using MySQL), you just can select one column for each case and I have a lot of columns to display.

Does anybody have some solution for this case?

Greetings

starlight

[mdahlman]

starlight,

You can do this by using the P!{MyParam} syntax.

For example, you define the param InputParam and you define the param MySQLParam. The default value for MySQLParam would look like this:
($P{InputParam} == null) ? "SOME SQL STUFF" : "SOME OTHER SQL STUFF"

Your SQL query would look like this:
SELECT ...
WHERE
$P!{MySQLParam}
AND ...

That way you can completely control the SQL based InputParam.

Regards,
Matt