i want to enable the user to choose a parameter value from a Combobox / list / Dropdown / collection (whatever you choose),
and the value that he chooses will be inserted into the query.
i already created a string parameter that prompt when i preview the report and the string inserted into the query and it works great, but now i need to define a combobox.
i need to provide names of cities so my query is:
SELECT
COUNT(leads.`status`) AS Number_Of_Status,
leads.`status` AS leads_status,
leads.`primary_address_city` AS leads_primary_address_city,
leads.id AS lead_id
FROM
`leads` leads
WHERE
leads.`primary_address_city` = $P{city_combobox}
GROUP BY
leads.`status`
i tried to make the city_combobox parameter a "Java.util.list" but i get "Parameter type not supported" error and also i don't know where to load the strings into it.
by the way, i don't know anything about Jasperreport server, i didn't install it, only Ireport is installed and up to know i did everything with it,
so i'm looking for a way to do that in Ireport only, without an intput control of jasperreport server.
it will be even enough for me to just provide the report with a manully inserted values into the combobox,
it doens't have to be even a query. for example, insert somewhere "NY","Miami", "Washington" into a list parameter,
but how and where do i do that?
2 Answers:
i solved the problem. it turns out zuckerreport is based on jasperreport server and therefore can add input controls too.
The solution is to create the "city_combobox" parameter as a string parameter and leave the query as is , like the above example i gave. thats it for the IReport.
in zuckerreport, add a parameter that its "Default Parameter Name" named city_combobox.
for a dropdown parameter choose a type of "user-defined Query" and fill the “Parameter Settings” and “User-Defined Query” fields with a query that will fill the dropdown with a list of values, for example:
SELECT DISTINCT
leads.primary_address_city AS leads_primary_address_city
FROM
leads leads
if you can't see the “Parameter Settings” and “User-Defined Query” fields (in the new version of zuckerreport they are hidden), then
The code is needed to be added manually in the editview code of zuckerreport.
Add this code in modules/zr2_ReportParameter/metadata/editviewdefs.php :
5 =>
array (
0 => 'range_options',
),
then you'll see the hidden fields.
Create a new template using “jasperReports Template” on the menu, the template is actually the report:
a. Choose a name for the template and choose the jrxml file of the report (from local computer where the Ireport is).
b. Choose PDF.Excel and HTML.
c. Save
Create a new Parameter binding between the report and the parameter:
a. Push “Create”
b. The parameter name has to be the exact parameter name in the IReport.
c. Choose the report and the parameter and no need to choose “Bind to module”
Copy the .jasper file of the report into custom/zuckerreports2/resources in SugarCRM.
Go to “On demand reporting”, choose the report and the parameter is supposed to show.
There you go! :)
Refer following query
SELECT
COUNT(leads.`status`) AS Number_Of_Status,
leads.`status` AS leads_status,
leads.`primary_address_city` AS leads_primary_address_city,
leads.id AS lead_id
FROM
`leads` leads
WHERE
$X{IN, leads.`primary_address_city, city_combobox}
GROUP BY
leads.`status`
City_combobox with datatype java.util.collection.
thank you, but this causes the report to present a result that includes all the cities together. and also , where is the combobox that enables me to choose which city?.
when i preview ther report i get to insert a string. i don;t see any combobox with the city. what did i forget?
create city_combobox input control as a multiselect list of values or multiselect query. so that list populate with all cities with check box infront of it.So you can select multiple values from list.
sorry, but i can't create input controls at all using only the Ireport. as i said , i don't have the jasperreport server installed , only the Ireport. is there a way to do what you wrote in Ireport ? create input controls in Ireport?
You can able to provide input controls in Jaspersever input controls. Using only iReport not possible to provide multiselect list of values with checkbox. Jasperserver required for multilevel selection.
Thank you very much for all your help, i solved the problem. i didn't use the solution you gave but it helped me keep an open mind to find the right one.
as i said, i use zuckerreport component in SugarCRM to run the reports i create in Ireport. it turns out zuckerreport is actually "kind of" a jasperreport server!. all i needed to do is go deeper into learning zuckerreport and to find that it can add input controls too , just like jasperreport server. so i'm posting my solution as an answer. thank you.