Problem
When I create a custom input, I cannot figure out how to make it turn the results I want.
Simplified Senario
- Simple Report from a Simple adhoc view, from a simple domain, from a generic mysql data source
- Report Edit --> Controls & Resources --> Add Input Control
- Define an Input Control in the next step
- Define a Query in the next step
- Name the Query: 'Select_The_User_Id_Query'
- Link a Data Source to the Query: 'selected the mysql data source'
Done!
Now lets look at how the report looks like
The input looks good.
Question:
How do I make the custom report filter results to only display user_id = 44 ?
Thanks in Advance
EDIT: Progress Made
Creating a custom report query was right in front of my face
SELECT * FROM user_sites WHERE user_id = $P{Select_The_User_Id}
- Report --> Edit --> Query
- "Click here to create a new query"
- Name the Query: 'Whatever_query"
- Link to data source: " select my database"
- Definee the query
- .
This is what I don't understand. The second cascading input I created has a query like "select * from users where id = $P{Select_The_User_Id}", which works. But the report "Query" claims that the parameter doesn't exisit. Why is that?
2 Answers:
OK. I did a quick POC and it work as expected.
This is what I did:
- Created an adhoc view and report based on the Simple Domain from JasperServer Samples
- The Adhoc View/report is a simple Table report showing the accounts. I added a filter in the view for the Account State
- As expected the parameter name that was created is "billing_address_state_1". At this point if you execute the report it will render the defailt single select query IC that AdHoc generates.
- I created a new IC in the repository as a List Of Values, I added just 3 of the states to the LoV input control (CA, WA, and DF) with their respective lables. The ID of this input control was of course "billing_address_state_1" that way it matches the parameter the report is expecting. I created this the same way you created the Single Select query IC in your sample, but used a LoV so I can see the difference :)
- I linked the new IC to my existing report, following the same steps you made
- Now if you execute the report the standard IC is replaced with my new IC and it works as expected !!
Attached is a repository export of my sample. Login as Superuser go to Manage Server -> Import select the .zip file attached and import it into your server. I created all in /Public/Test; you will need to have the JasperperServer Provided samples installed since my report uses the simple domain that comes with those.
Cheers!
| Attachment | Size |
|---|---|
 adhoc-custom-ic.zip | 17.42 KB |
WOW thank you Marianol for your help. I was able to delete your custom input, and then recreate it. But, I'm missing one last key. Where do I figure out the filter ID. Where do I figure out that the filter id was "billing_address_state_1". When I look at the schema of the data set you provided, it was just "billing_address_state". Can I assume that "_1" will be appended to every filter created from the adhoc view?
Andrey,
If I undertood correctly, your initial report is an AdHoc report. In that case there is no need to create an input control the way you did it. Just open the AdHoc view that you used, and drag "user_id" to the filter column, or right click on "user_id" in the fields display and select "Create a Filter" from the contex menu.
Then when you save that Adhoc view and create a report, JRS will automatically create an input control for your report.
Best,
marianol
i know jaspersoft creates basic filters like user_id automatically. However, that wasn't why I was asking the question. I was just trying to provide a simple example on how I would proceed when using a custom input. What I'm actually trying to do involves a couple cascading inputs, and a dataset with many joins.
The input parameter need to pass to main report and the main report query browser should the query should filter based on $P(UserId) then you will get seleted userid.
Thank you, I never actually realized I could create a query from the Edit --> Reports, because it was greyscaled. I guess that was me just being dumb. However I'm still having an issue with the query, when I create the query, I get this error: ".... JRRuntimeException: Parameter "Select_The_User_Id" does not exist.". Isn't that what i called the input, in the above example?