andey.wei Posted March 7, 2014 Share Posted March 7, 2014 ProblemWhen I create a custom input, I cannot figure out how to make it turn the results I want.Simplified SenarioSimple Report from a Simple adhoc view, from a simple domain, from a generic mysql data sourceReport Edit --> Controls & Resources --> Add Input ControlDefine an Input Control in the next stepDefine a Query in the next stepName 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 likeThe input looks good. Question:How do I make the custom report filter results to only display user_id = 44 ?Thanks in Advance EDIT: Progress MadeCreating a custom report query was right in front of my faceselect * from user_sites where user_id = $P{Select_The_User_Id}[/code]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? Link to comment Share on other sites More sharing options...
rsuddhala Posted March 10, 2014 Share Posted March 10, 2014 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. Link to comment Share on other sites More sharing options...
andey.wei Posted March 10, 2014 Author Share Posted March 10, 2014 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? Link to comment Share on other sites More sharing options...
marianol Posted March 10, 2014 Share Posted March 10, 2014 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 Link to comment Share on other sites More sharing options...
andey.wei Posted March 10, 2014 Author Share Posted March 10, 2014 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. Link to comment Share on other sites More sharing options...
marianol Posted March 10, 2014 Share Posted March 10, 2014 OK.. Now I get it. I have not tried this but in theory this is what you will need. 1) the Adhoc view must to have filters created for the parameters you want to pass. 2) check the IC ID's of the Adhoc View so you can match them form your Custom Input Controls. In Adhoc this will be the ID of the field you have picked form the domain plue and underscore "_" and an id number starting with 1. SO if your fields ID is user_id the parameter name for the filter you create will be user_id_1. You can see this using the REST V2 API Input control service i.e. http://localhost:8080/jasperserver-pro/rest_v2/reports/[My_Report_repository_path]/inputControls 3) create your input controls using that ID in your generated report. Link to comment Share on other sites More sharing options...
Solution marianol Posted March 10, 2014 Solution Share Posted March 10, 2014 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! Link to comment Share on other sites More sharing options...
andey.wei Posted March 10, 2014 Author Share Posted March 10, 2014 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? Link to comment Share on other sites More sharing options...
marianol Posted March 10, 2014 Share Posted March 10, 2014 You can assume _1 for the fist time you use it if you use the value twice, the second one will be _2. But there is an easier way to know. Just use the RESTv2 Input control service. Once logged into Japserserver go to http://localhost:8080/jasperserver-pro/rest_v2/reports/[repo path to your report]/inputControls ,in the sample report I sent it would be http://localhost:8080/jasperserver-pro/rest_v2/reports/public/Test/Custom_IC_Report/inputControls That will give you an XML file with all the input controls of a specific report. in the tag you will find the ID of each of the created input controls. See the webservices guide for more info. Remember to mark the question as answered (checkmark next to the answer) so if somebody else is looking for this info is easier to find :) Link to comment Share on other sites More sharing options...
Recommended Posts
Create an account or sign in to comment
You need to be a member in order to leave a comment
Create an account
Sign up for a new account in our community. It's easy!
Register a new accountSign in
Already have an account? Sign in here.
Sign In Now