Issue/Question
- How can I paste a list of items into Jaspersoft and use them to filter the results?
Environment
- Oregon State University
- CORE
Resolution
Currently, Jaspersoft doesn't allow you to paste a list of values into a filter. Until the vendor provides that requested functionality, we have come up with a work around.
You can create a calculated field in Jaspersoft, paste the list of values into the calculation, and then use that field to filter the data.
Here are detailed step-by-step instructions for the process. The example shown uses an organization code field and only shows two values, but the same technique will work with any field in any model and a thousand values or more depending on your specific query.
1. In Jaspersoft, create an Ad Hoc View to bring back the data you need.
2. Hover over the fields menu button and choose "Create Calculated Field…" or hover over the measures menu button and choose "Create Calculated Measure", depending on what type of field you want to filter.
3. Specify the Field Name or Measure Name.
4. From the Fields and Measures pane, find the field or measure that you want to filter and double-click to insert it into the Formula area followed by a space.
5. Leaving the Jaspersoft window open, run CORE report "Help - Jaspersoft Field Helper (HLP14)".
6. In the "Enter a data dump to be formatted" parameter, paste your list of values.
7. Leave Include Quotes set to True, and click "View Report". If you get the error: "The function 'in' must have compatible types for argument 1 and 2.", then rerun the report with Quotes set to False.
8. Highlight the resulting IN ( … ) statement and copy it.
9. Return to Jaspersoft and paste the text in the Formula area.
10. Click Validate
11. Click "Create Field".
|
Note: Notice that the new calculated field was added to the list of fields and it has a different icon next to it.
|
12. Create a filter on your new calculated field, choosing "true" from the available options to limit the data to only the values in the list, or choosing "false" to exclude those values from the data.
13. Apply the filter, your ad hoc view will include or exclude the specified values.
|
Note: To run the ad hoc view with a different set of values, you can edit the calculated field by right-clicking on it and choosing "Edit". Replace the existing IN statement with the new one and click "Save".
|
|
Note: If you have built a report from the ad hoc view, you will need to edit the calculated field in the ad hoc view each time you want to run the report with a new set of values.
|
|
Note: Calculated fields/measures are only part of the ad hoc view you created them in. Consider creating a calculated fields "template" ad hoc view that contains the calculated fields to use as a starting point, or to copy from.
|
Assistance
For assistance, Contact IAR.