Use an input control to determine which fields are selected in query

[ravenheart]

So this is the concept I want to achive:

A report has a single-value list input control, and depending on which value is chosen, the report's query uses field in the results set. 

Example: 

Input values: Country, State, City, District

SELECT

CASE WHEN P{InputList} = 'country' THEN s.country

       WHEN P{InputList} = 'state' THEN s.state

        WHEN P{InputList} = 'city' THEN s.city

      ELSE s.district END AS locality,

SUM(s.sales) AS sales FROM sales_table s

GROUP BY CASE WHEN P{InputList} = 'country' THEN s.country

                              WHEN P{InputList} = 'state' THEN s.state

                              WHEN P{InputList} = 'city' THEN s.city

                              ELSE s.district END

When I try this kind of query, it complains that the correct field is not present in the GROUP BY clause. Also, I am only assuming I can reference the parameter (which has the exact same name as the input control itself) that way, because the documentation for this kind of input control doesn't actually describe how to use the value selected.

[zh3ntil]

Hi,

You should use dynamic queries for that kind of report.

Create a parameter named 'chosenField' and its type will be 'java.Lang.String'. And its default expression would be sth like:

IF($P{InputList} = ="country","s.country",IF($P{InputList} = ="state","s.state",IF($P{InputList} = ="city","s.city","s.district")))

So, this parameter holds a string according to selected value. Like If you choose country than that parameter holds "s.country". That means your field is ready to use in query :)

After that your dataset should be :

SELECT

$P!{chosenField} AS locality,

SUM(s.sales) AS sales FROM sales_table s

GROUP BY $P!{chosenField}

 

So your field is generated according to chosen value. You should create your parameter after the parameter that holds your input control value not before it is important because input control's parameter should get the value before chosenField parameter is generated.

 

Take care.